SQL Essentials Quiz

Which SQL command removes all rows from a table without removing the table structure?

• A) DROP
• B) DELETE
• C) TRUNCATE
• D) ALTER

Answer: C Explanation: TRUNCATE removes all rows and deallocates the data pages but keeps the table structure. DROP removes the table entirely. DELETE removes rows one by one but keeps the table structure.

What is the difference between WHERE and HAVING?

• A) WHERE filters before GROUP BY, HAVING filters after
• B) There is no difference
• C) WHERE can use aggregate functions, HAVING cannot
• D) HAVING is faster than WHERE

Answer: A Explanation: WHERE filters individual rows before aggregation, and cannot use aggregate functions. HAVING filters groups after GROUP BY, and can use aggregate functions.

Which JOIN returns all rows from the left table and matching rows from the right?

• A) INNER JOIN
• B) RIGHT JOIN
• C) LEFT JOIN
• D) CROSS JOIN

Answer: C Explanation: LEFT JOIN (also called LEFT OUTER JOIN) returns every row from the left table. If no match exists in the right table, the result shows NULL for the right-table columns.

What does a correlated subquery do?

• A) Runs once independently of the outer query
• B) References a column from the outer query and runs once per outer row
• C) Is faster than a non-correlated subquery
• D) Cannot be used with WHERE

Answer: B Explanation: A correlated subquery references one or more columns from the outer query. It executes once for each row processed by the outer query, making it slower but more flexible.

Which window function returns 1, 2, 2, 3 for tied values (no gaps)?

• A) ROW_NUMBER
• B) RANK
• C) DENSE_RANK
• D) LEAD

Answer: C Explanation: DENSE_RANK assigns consecutive ranks without gaps, even when values are tied. RANK would skip numbers after ties (1, 2, 2, 4). ROW_NUMBER assigns unique sequential numbers regardless of ties.

What does UNION do?

• A) Combines columns from two tables horizontally
• B) Combines rows from two queries vertically, removing duplicates
• C) Joins two tables on a common key
• D) Creates a Cartesian product

Answer: B Explanation: UNION combines the result sets of two or more queries into a single result set, removing duplicate rows. UNION ALL includes duplicates. JOIN combines columns, not rows.

Which of these is NOT a DDL command?

• A) CREATE
• B) ALTER
• C) SELECT
• D) DROP

Answer: C Explanation: SELECT is a DML (Data Manipulation Language) command. CREATE, ALTER, and DROP are DDL (Data Definition Language) commands that modify the database structure.

What does the COALESCE function do?

• A) Returns the first non-NULL value from a list
• B) Converts data types
• C) Aggregates rows
• D) Removes duplicates

Answer: A Explanation: COALESCE accepts multiple arguments and returns the first non-NULL value. It is commonly used to handle NULL values in calculations or display.

SELECT COALESCE(Salary, 0) FROM Employees;
-- Returns 0 if Salary is NULL

What is the correct SQL execution order?

• A) SELECT → FROM → WHERE → GROUP BY → HAVING → ORDER BY
• B) FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY
• C) FROM → SELECT → WHERE → GROUP BY → HAVING → ORDER BY
• D) WHERE → FROM → GROUP BY → HAVING → SELECT → ORDER BY

Answer: B Explanation: SQL follows this logical order: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY. Understanding this order is essential for writing correct queries.

Which join type is most efficient for finding records that exist in one table but not another?

• A) INNER JOIN with WHERE
• B) LEFT JOIN with WHERE IS NULL
• C) RIGHT JOIN with aggregate
• D) CROSS JOIN

Answer: B Explanation: To find records in Table A with no matching records in Table B, use LEFT JOIN and check for NULL in the B table’s primary key column. This is efficient when B’s primary key is indexed.