Ultimate SQL Mastery Challenge: Advanced Concepts and Pitfalls Quiz

1. Primary Keys and Uniqueness

   Which of the following statements correctly describes the behavior of a primary key in an SQL table that contains duplicate values for the key column?

   1. A. The table will reject any insert operation that introduces a duplicate primary key value.
   2. B. The table will accept duplicates and automatically set them to NULL.
   3. C. The table will merge duplicate rows into a single row.
   4. D. The table will create a warning but allow the duplicate values.
   5. E. The primary key column is automatically updated with random unique numbers.

2. Complex JOIN Scenarios

   Given tables orders and customers, which SQL query retrieves all customers and their orders, showing NULLs where there are customers without orders?

   1. A. SELECT * FROM customers LEFT JOIN orders ON customers.id = orders.customer_id;
   2. B. SELECT * FROM customers RIGHT JOIN orders ON customers.id = orders.customer_id;
   3. C. SELECT * FROM customers INNER JOIN orders ON customers.id = orders.customer_id;
   4. D. SELECT * FROM customers FULL JOIN orders ON customers.id = orders.customer_id;
   5. E. SELECT * FROM customers CROSS JOIN orders;

3. Indexing and Performance

   If a table contains a clustered index on the 'order_date' column, which statement about how the data is stored is TRUE?

   1. A. The physical rows in the table are stored in order according to 'order_date'.
   2. B. The data is randomly stored and only the index maintains ordering.
   3. C. The table can have multiple clustered indexes on different columns.
   4. D. Clustered indexes are only used for text-based columns.
   5. E. The clustered index duplicates the full table data storage.

4. Transactions and ACID Properties

   Which scenario best exemplifies the Isolation property of ACID in database transactions?

   1. A. Two users executing bank transfers do not see each other's partial, uncommitted changes.
   2. B. A transaction is saved even after a power failure.
   3. C. A transfer operation completes fully or not at all.
   4. D. Database tables are grouped into schemas.
   5. E. All SELECT queries retrieve the latest snapshot of data, regardless of transaction state.

5. Advanced Query Construction

   Which query correctly finds the second highest unique salary from a table named 'employees' with a column 'salary'?

   1. A. SELECT MAX(salary) FROM employees WHERE salary u003C (SELECT MAX(salary) FROM employees);
   2. B. SELECT salary FROM employees ORDER BY salary DESC LIMIT 2;
   3. C. SELECT SECOND(salary) FROM employees;
   4. D. SELECT MAX(salary) FROM employees WHERE salary != MAX(salary);
   5. E. SELECT TOP 2 salary FROM employees ORDER BY salary ASC;