SQL quiz for testers: 20 questions with explanations

Which statement about the WHERE and HAVING clauses is correct?

• HAVING filters rows before grouping, WHERE filters after
• WHERE filters rows before grouping, HAVING filters groups after aggregation
• They are interchangeable in every query
• HAVING can only be used without GROUP BY

Answer: WHERE filters rows before grouping, HAVING filters groups after aggregation. WHERE removes rows before they are grouped. HAVING works on the groups, so it can use aggregates such as COUNT(*) > 3.

Which statement removes ALL rows from a table quickly and resets identity counters in most databases?

• DELETE employees WHERE id > 0
• DROP TABLE employees
• REMOVE FROM employees
• TRUNCATE TABLE employees

Answer: TRUNCATE TABLE employees. TRUNCATE empties the table without row-by-row logging. DROP would remove the table itself, and DELETE with a condition removes only matching rows.

Which is true of a primary key?

• It uniquely identifies each row and cannot be NULL
• A table can have many primary keys
• It can contain duplicate values if indexed
• It must be a number

Answer: It uniquely identifies each row and cannot be NULL. One primary key per table, unique and never NULL. It can be text or a combination of columns. A unique key, by contrast, can allow NULLs.

Which pattern returns names that start with the letter A?

• WHERE name = 'A*'
• WHERE name LIKE '%A'
• WHERE name LIKE 'A%'
• WHERE name STARTS 'A'

Answer: WHERE name LIKE 'A%'. % matches any number of characters. 'A%' means A followed by anything, whereas '%A' means ending in A.

What does UNION ALL do differently from UNION?

• It sorts the results
• It keeps duplicate rows instead of removing them
• It joins the tables side by side
• It only works with two columns

Answer: It keeps duplicate rows instead of removing them. UNION removes duplicates, which costs extra work. UNION ALL simply appends the results and is faster, so use it unless you need de-duplication.

Which join returns all rows from the left table and the matching rows from the right table, with NULLs where there is no match?

• INNER JOIN
• CROSS JOIN
• LEFT JOIN
• SELF JOIN

Answer: LEFT JOIN. A LEFT JOIN keeps every left-side row. INNER JOIN keeps only matches, and CROSS JOIN produces every combination.

Which query finds departments that have no employees?

• SELECT * FROM departments d INNER JOIN employees e ON e.dept_id = d.id
• SELECT * FROM departments WHERE id = NULL
• SELECT * FROM employees WHERE dept_id IS NULL
• SELECT d.* FROM departments d LEFT JOIN employees e ON e.dept_id = d.id WHERE e.id IS NULL

Answer: SELECT d.* FROM departments d LEFT JOIN employees e ON e.dept_id = d.id WHERE e.id IS NULL. The left join keeps all departments, and the employee id is NULL exactly when no employee matched. This find-the-orphans pattern is used constantly in data testing.

Table A has 3 rows and table B has 4 rows. How many rows does A CROSS JOIN B return?

• 12
• 7
• 3
• 4

Answer: 12. A cross join returns every combination of rows: 3 × 4 = 12.

To list each employee next to their manager's name, where managers are stored in the same employees table, you use:

• A subquery in the FROM clause only
• A self join, joining employees to itself with two aliases
• UNION
• TRUNCATE

Answer: A self join, joining employees to itself with two aliases. A self join treats the same table as two, for example employees e LEFT JOIN employees m ON m.id = e.manager_id.

Which query finds email addresses that appear more than once in the employees table?

• SELECT email FROM employees WHERE COUNT(*) > 1
• SELECT DISTINCT email FROM employees
• SELECT email FROM employees ORDER BY email
• SELECT email FROM employees GROUP BY email HAVING COUNT(*) > 1

Answer: SELECT email FROM employees GROUP BY email HAVING COUNT(*) > 1. Group by the column and keep groups with more than one row. WHERE cannot use an aggregate, so HAVING is needed.

Why does SELECT * FROM employees WHERE manager_id = NULL return no rows?

• NULL means unknown, so nothing is equal to it. Use IS NULL instead
• NULL must be written in quotes
• The column must be indexed first
• The query returns an error in all databases

Answer: NULL means unknown, so nothing is equal to it. Use IS NULL instead. Comparisons with NULL give an unknown result, which is not true. IS NULL and IS NOT NULL are the correct tests.

A column has 10 rows, 3 of them NULL. What do COUNT(*) and COUNT(column) return?

• 10 and 10
• 7 and 7
• 10 and 7
• 7 and 10

Answer: 10 and 7. COUNT(*) counts rows. COUNT(column) counts only non-NULL values in that column.

Which query returns the second highest distinct salary in a way that works in most databases?

• SELECT MIN(salary) FROM employees
• SELECT MAX(salary) FROM employees WHERE salary < (SELECT MAX(salary) FROM employees)
• SELECT salary FROM employees ORDER BY salary
• SELECT SECOND(salary) FROM employees

Answer: SELECT MAX(salary) FROM employees WHERE salary < (SELECT MAX(salary) FROM employees). Take the maximum of everything below the overall maximum. Window functions such as DENSE_RANK() also work and generalise to the Nth highest.

A query selects dept_id, name and COUNT(*) with GROUP BY dept_id only, and many databases reject it. Why?

• COUNT(*) cannot be used with GROUP BY
• dept_id cannot be grouped
• GROUP BY needs an ORDER BY
• Every selected column must be grouped or aggregated, and name is neither

Answer: Every selected column must be grouped or aggregated, and name is neither. Within a group there may be several different names, so the database cannot decide which one to show. Add name to GROUP BY or wrap it in an aggregate.

A subquery in NOT IN returns a list that contains a NULL. What is the effect on the outer query?

• It returns no rows at all, because the NOT IN comparison with NULL is never true
• It ignores the NULL and works normally
• It returns all rows
• It raises a syntax error in every database

Answer: It returns no rows at all, because the NOT IN comparison with NULL is never true. x NOT IN (1, 2, NULL) is unknown for every x not in the list, so nothing passes the filter. NOT EXISTS or filtering out NULLs avoids the trap.

What is a foreign key for?

• To speed up every query
• To store passwords safely
• To keep data consistent by ensuring a value refers to an existing row in another table
• To remove duplicates automatically

Answer: To keep data consistent by ensuring a value refers to an existing row in another table. A foreign key stops you from inserting an order for a customer who does not exist and, depending on settings, from deleting a customer who still has orders.

You want to find orders that have no order items, which would indicate a half-saved order. Which query is right?

• SELECT * FROM orders o INNER JOIN order_items i ON i.order_id = o.id
• SELECT o.* FROM orders o LEFT JOIN order_items i ON i.order_id = o.id WHERE i.id IS NULL
• SELECT * FROM order_items WHERE order_id = NULL
• SELECT COUNT(*) FROM orders

Answer: SELECT o.* FROM orders o LEFT JOIN order_items i ON i.order_id = o.id WHERE i.id IS NULL. The left join keeps all orders and the item id is NULL when no item matched. A non-empty result is a data integrity bug to report.

A payment fails half-way through placing an order. Which ACID property says the order should not be left partly saved?

• Atomicity
• Durability
• Isolation
• Consistency of the display

Answer: Atomicity. Atomicity means a transaction is all or nothing. If any step fails, everything it did is rolled back.

Why do performance tests need production-sized data?

• Because small tables cannot be queried
• Because indexes only work on large tables
• Because SQL is slower with less data
• Because a query that is fast on 100 rows can be very slow on millions, for example when it lacks a useful index

Answer: Because a query that is fast on 100 rows can be very slow on millions, for example when it lacks a useful index. Query plans change with data size. Missing indexes and inefficient joins only hurt once the tables are large.

After a UI action should have deleted a user, which check best confirms the data is really gone?

• Check that the page shows a success message
• Check that the page refreshed
• Query the table for that user's id and confirm no row is returned, and check related tables for orphan rows
• Run SELECT COUNT(*) on a different table

Answer: Query the table for that user's id and confirm no row is returned, and check related tables for orphan rows. A success message proves only that the UI reported success. Querying the database confirms the effect and orphaned related rows show cascade problems.