Question 6 of 38

You have two test tables:

✑ Code_innodb as InnoDB engine

✑ Code_ myisam as MYISAM engine

The tables have the same structure:

The tables have one row of data:

You execute an INSERT statement on both code_myisam tables and receive duplicate key errors: mysql> INSERT INTO code_innodb VALUES (1, Alpha), (2, Beta), (3, charlie,),(4, Delta);

ERROR 1062 (23000): Duplicate entry 3 for key PRIMARY

Mysql> INSERT INTO code_myisam VALUES (1, Alpha), (2, Beta), (3, charlie),

(4, Delta);

ERROR 1062 (23000); Duplicate entry 3 for key PRIMARY

What is the expected output of the SELECT statements?

    Correct Answer: B

    The expected output of the SELECT statements from both tables after receiving duplicate key errors and not inserting new rows would just be the original records present before the insert attempts. Since both tables had the row (3, 'Charlie') initially, and the INSERT statement failed due to the duplicate primary key for ID 3, the tables remain unchanged. Therefore, the output should show the single row (3, 'Charlie') in both code_innodb and code_myisam tables.

Question 7 of 38

A table (t1) contains 1000 random integer values in the first column (col1).The random values are in the range of 0-1000.

Examine this query:

SELECT col1 FROM t1 WHERE col1 <=100 UNION

SELECT col1 FROM t1 WHERE col1 >=900 ORDER BY col1 DESC

What is the expected output?

    Correct Answer: D

    The query uses the UNION operator, which by default removes duplicates between the two SELECT statements. The FILTER conditions limit the results to values between 0-100 from the first SELECT and values between 900-1000 from the second SELECT. Finally, the combined result is ordered in descending order. Therefore, the expected output is a list of all unique values within the ranges of 0-100 and 900-1000, sorted in descending order.

Question 8 of 38

A SELECT statement without an ORDER BY clause return some rows.

Which statement is always true about the order of the returned results?

    Correct Answer: D

    If a SELECT statement is executed without an ORDER BY clause, the order of the returned results is not guaranteed. The database engine may return rows in any order, which could be based on factors like insertion order, physical storage, or internal query processing mechanisms. Therefore, it is not reliable to assume any specific order without explicitly using an ORDER BY clause.

Question 9 of 38

Using the MYSQL command –line client you have received the error "Lost connection to MYSQL server query"

Which three are possible causes of the error?

    Correct Answer: A, B, C

    Possible causes of the 'Lost connection to MYSQL server during query' error include the MYSQL server stopping during query execution, the network connection being interrupted during query execution, or the connection that issued the query being killed. These scenarios can lead to a disconnection while a query is being processed, resulting in this specific error message.

Question 10 of 38

Which three view types are not updateable?

    Correct Answer: B, D, E

    A view containing a GROUP BY clause is not updateable because aggregate functions in the GROUP BY clause change the nature of the view so that individual rows cannot be uniquely identified for update operations. A view containing a HAVING clause is also not updateable because the HAVING clause, typically used with GROUP BY, affects the result set in a way that prevents identification of individual rows for updates. Lastly, a view that contains a literal column is not updateable because literal values are constant expressions and not derived from base table columns, making it impossible to determine how changes should be mapped back to the base tables.