SQL Interview Practice Test: Classic Questions and Theory Quiz
A final SQL interview practice test: 12 questions and three queries on the classic problems, WHERE vs HAVING, ranking, NOT IN with NULL and fan-out joins.
- Course: SQL study plan
- Module: Interview idioms
- Kind: Checkpoint — cleared at 70%
- Reading time: 30 min
- Runtime: MySQL 8 syntax on SQLite 3.49
Final checkpoint — Interview idioms is the checkpoint that closes the Interview idioms module: a graded quiz and whole-program exercises, passed at 70%.
Instructions
The last checkpoint of the SQL plan. It covers the classic interview questions, the theory drill, the idiom sheet and the pitfalls — and, being the last one, it reaches back across the whole track: filtering, NULLs, grouping, joins, subqueries, window functions, data changes, keys and transactions.
How it works. Twelve questions and three queries; 70% on the questions and every query accepted clears the module. With every other module complete, that also completes the plan.
Before you start, make sure you can answer these from memory:
- The second highest salary two ways, and how each returns NULL when there is none.
- Why "employees earning more than their manager" is a self-join, and what happens to the employee with no manager.
- Why
NOT INreturns nothing when the subquery holds a NULL, and the two safe alternatives. - WHERE vs HAVING, DELETE vs TRUNCATE vs DROP, UNION vs UNION ALL — one sentence each, plus one detail.
- The logical order of a query's clauses, and why an alias cannot be used in WHERE.
- ROW_NUMBER, RANK and DENSE_RANK on the values 100, 90, 90, 80.
- Why a join to two child tables inflates sums, and how pre-aggregating fixes it.
- What each ACID property guarantees, and the leftmost-prefix rule of a composite index.
The three queries are the classics with a twist: the highest earners of each department with ties kept, the customers who placed no order in 2024 (the anti-join with its condition in the right place), and the duplicate emails once case and stray spaces are ignored.
Common questions
Which ranking function answers "the highest-paid employees in each department, ties included"?
RANK() or DENSE_RANK() partitioned by department and ordered by salary descending, keeping rank 1. Both give every tied top earner rank 1; ROW_NUMBER() would keep just one of them, chosen arbitrarily unless the order breaks the tie.
Why put a date filter in the ON clause when finding customers with no orders in a year?
The anti-join keeps customers whose LEFT JOIN found no matching order. A date condition in WHERE would test the NULL order date of those customers and discard them; in ON it limits which orders can match, so customers with orders only in other years are kept as having none.
How do you find duplicate emails case-insensitively in SQL?
Group by a normalised form and keep groups with more than one row: GROUP BY LOWER(TRIM(email)) HAVING COUNT(*) > 1. MySQL's default collation already ignores case, but other databases and case-sensitive collations do not, and neither ignores leading spaces.
Exercises
Highest earners per department, ties kept
departments(id, name) and employees(id, name, salary, dept_id):
| department | employees (salary) |
|---|---|
| Engineering | Bo 120, Asha 120, Cara 90 |
| Ops | Dev 70 |
| Sales | Eli 80, Fay 85 |
| HR | — |
List the highest-paid employee(s) of each department — every employee tied for the top salary. Return department, employee and salary, ordered by department, then employee.
department | employee | salary
Engineering | Asha | 120
Engineering | Bo | 120
Ops | Dev | 70
Sales | Fay | 85No orders in 2024
customers(id, name) and orders(id, customer_id, ordered_on):
| customer | order dates |
|---|---|
| Asha | 2023-12-31, 2025-01-01 |
| Ben | 2024-06-01 |
| Cara | — |
| Dev | 2024-01-01 |
| Eli | 2024-12-31, 2023-05-05 |
The starter tries an anti-join but returns nothing. Fix it: list the customers who placed no order dated in 2024 (including those who never ordered at all). One column, name, ordered by name.
name
Asha
CaraDuplicate emails, any case
users(id, email) was filled by hand, so the same address appears with different capitals and stray spaces.
| id | |
|---|---|
| 1 | a@x.com |
| 2 | A@x.com |
| 3 | ␣a@x.com |
| 4 | b@x.com |
| 5 | c@x.com |
| 6 | C@X.COM |
| 7 | d@x.com␣ |
(␣ marks a space.) Report every address that appears more than once once case and surrounding spaces are ignored: the columns email (lower-cased and trimmed) and copies, ordered by copies descending, then email.
email | copies
a@x.com | 3
c@x.com | 2In this module: Interview idioms
- The classic SQL questions — six problems every interview reuses
- The SQL theory drill — the questions asked without a keyboard
- The idiom sheet — the shapes SQL questions take and the query for each
- SQL pitfalls — the twelve mistakes that fail interview queries
- Final checkpoint — Interview idioms (this lesson)
← SQL pitfalls — the twelve mistakes that fail interview queries