This is a working question bank: 40 real SQL interview questions grouped by topic, each with a concise answer and runnable SQL where it helps. Drill them until the answers are automatic.
If you want the strategy side of interviewing, how the rounds are structured, how to narrate your thinking, and a four week study plan, read the companion piece, the SQL interview guide. This page is purely the questions and answers. Every query below is standard SQL that runs on the major engines with tiny syntax tweaks; when a detail is vendor specific, check the PostgreSQL docs or the MySQL docs. If a concept feels shaky, the SQL cheat sheet is a fast refresher.
What this bank covers
1. Topics by difficulty and frequency
Before the questions, here is the lay of the land. The table maps each topic to a rough difficulty tier and how many questions in this bank cover it, so you can prioritize your weak spots. SQL itself is a standardized language; the concept overview on Wikipedia is a decent primer if the terminology is new.
| Topic | Difficulty | Questions here | Why it matters |
|---|---|---|---|
| Fundamentals | Easy | 7 | Warm ups; hesitation signals shaky basics |
| Joins | Easy to Medium | 6 | The single most tested topic |
| Aggregation and grouping | Medium | 5 | Core for analyst and reporting roles |
| Subqueries and CTEs | Medium | 4 | Readability and correlated logic |
| Window functions | Medium to Hard | 5 | Separates junior from mid level |
| Classic puzzles | Hard | 6 | Pattern recognition under pressure |
| Indexes and performance | Hard | 4 | Where senior candidates are judged |
| Design and normalization | Medium | 3 | Reveals data modeling judgment |
And here is how often each family actually shows up in reported interview loops. Spend your time top down.
2. Fundamentals
These are warm ups. Answer them instantly and confidently in one or two crisp sentences.
1. What is the difference between WHERE and HAVING?
WHERE filters individual rows before grouping. HAVING filters after aggregation, so it can reference aggregate functions like COUNT or SUM that WHERE cannot see.
SELECT customer_id, SUM(amount) AS total
FROM orders
WHERE order_date >= '2026-01-01' -- rows first
GROUP BY customer_id
HAVING SUM(amount) > 1000; -- groups after2. DELETE vs TRUNCATE vs DROP?
All three remove data, but at different levels. DELETE removes selected rows and can be rolled back. TRUNCATE empties the whole table fast with minimal logging. DROP removes the table structure itself.
| Command | Removes | WHERE? | Rollback | Resets identity |
|---|---|---|---|---|
DELETE | Rows | Yes | Fully | No |
TRUNCATE | All rows | No | Limited | Usually yes |
DROP | The table itself | No | No | N/A |
3. Primary key vs unique key?
A primary key uniquely identifies each row: unique and not null, one per table. A unique key also enforces uniqueness but allows one null (in most engines) and you can have several. Use unique keys for business rules like one email per user.
CREATE TABLE users (
id INT PRIMARY KEY, -- one identity, never null
email VARCHAR(255) UNIQUE -- business rule, allows one null
);4. What is a NULL?
NULL means unknown or missing, not zero and not an empty string. The key point: NULL is never equal to anything, including another NULL, so you must test it with IS NULL.
SELECT * FROM t WHERE deleted_at = NULL; -- never matchesSELECT * FROM t WHERE deleted_at IS NULL;5. What does DISTINCT do?
DISTINCT removes duplicate rows considering all selected columns together. A gotcha: SELECT DISTINCT a, b deduplicates on the pair, not on a alone.
SELECT DISTINCT country FROM orders;
SELECT COUNT(DISTINCT customer_id) FROM orders;6. CHAR vs VARCHAR?
CHAR(n) is fixed length and pads shorter values with spaces, so every value takes the full n characters. VARCHAR(n) stores only what you put in, up to a limit. Use CHAR for genuinely fixed codes like a two letter country code, and VARCHAR for everything variable like names and emails.
7. UNION vs UNION ALL?
Both stack two result sets with matching columns. UNION removes duplicate rows, which forces a sort and is slower. UNION ALL keeps every row and is faster. Prefer UNION ALL unless you truly need deduplication.
SELECT id, amount FROM orders
UNION ALL
SELECT id, amount FROM orders_archive;3. Joins
Joins are the most tested topic; expect at least one in every loop. For a visual refresher see the SQL joins guide.
8. INNER JOIN vs LEFT JOIN?
An INNER JOIN returns only rows with a match in both tables. A LEFT JOIN returns every row from the left table, filling the right side with NULL when there is no match.
-- Every customer, with order count (0 if none)
SELECT c.name, COUNT(o.id) AS orders
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.name;9. What does a FULL OUTER JOIN return?
Every row from both tables, matched where possible and NULL filled where not. It is the union of a left and a right join. MySQL lacks it natively, so you emulate it with LEFT JOIN UNION RIGHT JOIN. Compare all four types in the join comparison guide.
10. Find rows with no match (anti-join)?
Which customers never placed an order? The cleanest answers are a LEFT JOIN where the right side is NULL, or NOT EXISTS.
-- LEFT JOIN ... IS NULL
SELECT c.id, c.name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.id IS NULL;
-- NOT EXISTS (safer with NULLs)
SELECT c.id, c.name
FROM customers c
WHERE NOT EXISTS (
SELECT 1 FROM orders o WHERE o.customer_id = c.id
);Watch out: prefer NOT EXISTS over NOT IN. If the subquery inside NOT IN returns a single NULL, the whole condition becomes unknown and you get zero rows back.
11. Self join: match each employee to a manager?
A self join joins a table to itself with different aliases. Using LEFT JOIN keeps the CEO, whose manager_id is NULL, in the result.
SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;12. Why did my SUM double after adding a join?
Joining on a one to many relationship repeats each parent row once per child, so any aggregate over the parent columns is inflated. Pre-aggregate the many side before joining.
SELECT c.id, c.credit, o.total
FROM customers c
JOIN (
SELECT customer_id, SUM(amount) AS total
FROM orders GROUP BY customer_id
) o ON o.customer_id = c.id;13. When would you use a CROSS JOIN?
A CROSS JOIN produces the Cartesian product, every row of one table paired with every row of the other. It is intentional when you need all combinations, for example generating a calendar of every product for every date so reports have no gaps. An accidental cross join (a missing ON clause) is a classic cause of runaway row counts.
4. Aggregation and grouping
14. What is the GROUP BY rule?
Every column in the SELECT list must appear in GROUP BY or be wrapped in an aggregate. Strict engines error out on violations; older MySQL silently picked an arbitrary value.
SELECT department, name, AVG(salary) -- name is loose
FROM employees GROUP BY department;SELECT department, AVG(salary) AS avg_salary
FROM employees GROUP BY department;15. COUNT(*) vs COUNT(col) vs COUNT(DISTINCT col)?
COUNT(*) counts rows. COUNT(col) counts non-null values of that column. COUNT(DISTINCT col) counts unique non-null values. When asked how many customers placed an order, the answer is almost always COUNT(DISTINCT customer_id).
16. Conditional counts with CASE?
Wrap a CASE inside SUM or COUNT to count categories in a single pass. This is a favorite because it separates memorizers from people who understand aggregation.
SELECT
SUM(CASE WHEN status = 'paid' THEN 1 ELSE 0 END) AS paid,
SUM(CASE WHEN status = 'refunded' THEN 1 ELSE 0 END) AS refunded
FROM orders;17. Show every category, even ones with zero rows?
A plain GROUP BY only returns values present in the data, so empty groups vanish. Start from the full list of categories and LEFT JOIN the counts.
SELECT s.status, COUNT(o.id) AS n
FROM statuses s
LEFT JOIN orders o ON o.status = s.status
GROUP BY s.status;18. What do ROLLUP and GROUPING SETS do?
They add subtotal and grand total rows to a grouped result in one query. GROUP BY ROLLUP(region, product) returns per product totals, per region subtotals, and one grand total, so you avoid stitching several queries together with UNION ALL.
5. Subqueries and CTEs
19. What is a correlated subquery?
A subquery that references a column from the outer query, so it is re-evaluated once per outer row. Powerful but potentially slow. This finds employees earning above their department average.
SELECT e.name, e.salary
FROM employees e
WHERE e.salary > (
SELECT AVG(x.salary) FROM employees x
WHERE x.department = e.department -- correlation
);20. What is a CTE (WITH clause)?
A Common Table Expression is a named, temporary result set defined with WITH that lives only for the query. CTEs make complex logic readable by naming each step and can be referenced multiple times.
WITH dept_avg AS (
SELECT department, AVG(salary) AS avg_sal
FROM employees GROUP BY department
)
SELECT e.name, e.salary
FROM employees e
JOIN dept_avg d ON d.department = e.department
WHERE e.salary > d.avg_sal;21. Subquery vs JOIN: which is faster?
For most cases the optimizer rewrites a simple subquery into a join and the plans are identical, so pick whichever reads clearer. The real difference is correctness with NULL (see NOT IN above) and readability. The subqueries vs joins guide walks through the tradeoffs.
22. Write a recursive CTE?
A recursive CTE references itself to walk hierarchies or generate sequences. It has an anchor member, a UNION ALL, and a recursive member that stops when it returns no rows.
WITH RECURSIVE chain AS (
SELECT id, name, manager_id
FROM employees WHERE id = 1 -- anchor
UNION ALL
SELECT e.id, e.name, e.manager_id
FROM employees e
JOIN chain c ON e.manager_id = c.id -- recursive step
)
SELECT * FROM chain;6. Window functions
Window functions compute across related rows without collapsing them into a group. They are the biggest differentiator between junior and mid level. Study the window functions guide if this feels new.
23. ROW_NUMBER vs RANK vs DENSE_RANK?
They differ only on ties, and ties are where bugs hide.
| Function | Behavior on ties | Sequence |
|---|---|---|
ROW_NUMBER | Always unique | 1, 2, 3, 4 |
RANK | Ties share, then skips | 1, 2, 2, 4 |
DENSE_RANK | Ties share, no gaps | 1, 2, 2, 3 |
24. Find the 2nd (Nth) highest salary, three ways?
The most asked SQL question in existence. Showing more than one method demonstrates range.
-- Way 1: LIMIT / OFFSET (Postgres, MySQL)
SELECT DISTINCT salary FROM employees
ORDER BY salary DESC LIMIT 1 OFFSET 1;-- Way 2: correlated subquery (portable)
SELECT MAX(salary) FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);-- Way 3: DENSE_RANK (generalizes to Nth, handles ties)
WITH ranked AS (
SELECT salary,
DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
FROM employees
)
SELECT DISTINCT salary FROM ranked WHERE rnk = 2;25. Top-N per group?
The top 3 earners in each department. Partition, rank, then filter.
WITH ranked AS (
SELECT name, department, salary,
ROW_NUMBER() OVER (
PARTITION BY department
ORDER BY salary DESC) AS rn
FROM employees
)
SELECT name, department, salary
FROM ranked WHERE rn <= 3;26. Running total?
SELECT order_date, amount,
SUM(amount) OVER (
ORDER BY order_date
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS running_total
FROM orders;27. What do LAG and LEAD do?
They read a value from a previous or following row in the window, which is how you compute period over period change without a self join. This finds each month's revenue growth versus the prior month.
SELECT month, revenue,
revenue - LAG(revenue) OVER (ORDER BY month) AS growth
FROM monthly_sales;7. Classic puzzles
These pattern based problems recur across companies. Learn the shape of each solution: once you name the pattern, the query almost writes itself.
28. Find duplicate rows?
SELECT email, COUNT(*) AS n
FROM users
GROUP BY email
HAVING COUNT(*) > 1;29. Delete duplicates, keeping one?
Number the copies within each group, then delete everything past the first.
WITH dups AS (
SELECT id,
ROW_NUMBER() OVER (
PARTITION BY email ORDER BY id) AS rn
FROM users
)
DELETE FROM users
WHERE id IN (SELECT id FROM dups WHERE rn > 1);30. Consecutive streaks (gaps and islands)?
Find users who logged in on 3 or more consecutive days. The trick: subtract a ROW_NUMBER from the date, so consecutive dates share a constant group key.
WITH marked AS (
SELECT user_id, login_date,
login_date - (ROW_NUMBER() OVER (
PARTITION BY user_id
ORDER BY login_date) * INTERVAL '1 day') AS grp
FROM logins
)
SELECT user_id, MIN(login_date) AS streak_start,
COUNT(*) AS streak_len
FROM marked
GROUP BY user_id, grp
HAVING COUNT(*) >= 3;31. Pivot rows to columns?
SELECT product,
SUM(CASE WHEN quarter = 'Q1' THEN amount END) AS q1,
SUM(CASE WHEN quarter = 'Q2' THEN amount END) AS q2
FROM sales
GROUP BY product;32. Compute a median without a MEDIAN function?
Many engines lack a MEDIAN aggregate. Rank values from both ends and average the middle one or two.
WITH ranked AS (
SELECT salary,
ROW_NUMBER() OVER (ORDER BY salary) AS asc_r,
ROW_NUMBER() OVER (ORDER BY salary DESC) AS desc_r
FROM employees
)
SELECT AVG(salary) AS median
FROM ranked
WHERE asc_r IN (desc_r, desc_r + 1, desc_r - 1);33. Which department has the highest total salary?
Group, sum, order, and take the top row. Use a tie safe approach when several departments could tie for the top.
SELECT department, SUM(salary) AS total
FROM employees
GROUP BY department
ORDER BY total DESC
LIMIT 1;8. Indexes and performance
For backend and data engineering roles this is where seniority shows. The cheat sheet lists the syntax; below is the reasoning.
34. What is an index and how does it help?
An index is a separate sorted structure (usually a B-tree) that lets the database find rows without scanning the whole table, like the index at the back of a book. It turns a full scan into a fast lookup for equality and range queries and can satisfy ORDER BY without sorting. The cost is slower writes and extra storage.
CREATE INDEX idx_orders_customer ON orders (customer_id);
-- Composite: leftmost prefix wins
CREATE INDEX idx_orders_cust_date
ON orders (customer_id, order_date);35. Why might a query be slow?
- No index on the
WHEREorJOINcolumns, forcing a full scan. - A function wrapping an indexed column makes the index unusable.
SELECT *pulling far more data than needed.- Stale statistics leading the planner to a bad join order.
36. What is a sargable query?
Sargable means the search argument can use an index. Wrapping the column in a function breaks that; a plain range keeps it.
SELECT * FROM orders WHERE YEAR(order_date) = 2026;SELECT * FROM orders
WHERE order_date >= '2026-01-01'
AND order_date < '2027-01-01';37. How do you use EXPLAIN?
EXPLAIN, or EXPLAIN ANALYZE which actually runs the query, shows the execution plan. Look for sequential scans on big tables, a large gap between estimated and actual rows (stale stats), expensive sorts, and the join strategy chosen. The senior move is to name a specific change and predict its effect.
EXPLAIN ANALYZE
SELECT * FROM orders WHERE customer_id = 42;
-- "Index Scan" good, "Seq Scan" often bad on large tables9. Design and normalization
38. Explain 1NF, 2NF, and 3NF in one line each?
Normalization removes redundancy so data is stored once. Learn it fully in the joins guide alongside how the tables connect.
| Form | One line rule |
|---|---|
| 1NF | Atomic values only; no repeating groups or comma lists in a column. |
| 2NF | 1NF plus every non-key column depends on the whole primary key. |
| 3NF | 2NF plus no non-key column depends on another non-key column. |
39. When would you denormalize?
Deliberately, when read performance matters more than write simplicity: reporting tables, dashboards, read heavy APIs. You trade duplicated data and heavier writes for fewer joins. The honest answer is normalize first for correctness, then denormalize specific hot paths with evidence from EXPLAIN, not by guessing. Techniques include summary tables, cached counts, and materialized views.
40. What is a foreign key and why use one?
A foreign key references the primary key of another table, and the database enforces that every value matches an existing parent row or is null. This is referential integrity: you cannot insert an order for a customer that does not exist. Rules like ON DELETE CASCADE or RESTRICT control what happens to children when a parent is removed.
CREATE TABLE orders (
id INT PRIMARY KEY,
customer_id INT NOT NULL,
FOREIGN KEY (customer_id)
REFERENCES customers(id) ON DELETE CASCADE
);Strong answer: frame denormalization as a measured tradeoff. Saying "I would add a materialized daily sales table because the dashboard joins five tables every 30 seconds" shows judgment, not just knowledge.
Work through every question until you can reproduce the answer on a blank editor in under three minutes. When you have the mechanics down, go back to the SQL interview guide for how to present it in the room, and build real fluency with the beginner course. If you want feedback you cannot get solving alone, structured SQL mentorship closes the gap fastest.