SQL interview questions, with answers you can run
Twelve questions that come up again and again, answered the way you would say them out loud, with real queries and real results. Then deeper pages by topic.
Go deeper by topic
JOIN questions
LEFT JOIN, anti-joins, self joins, row fan-out and the WHERE-versus-ON trap.
Window functions
RANK, DENSE_RANK, top N per group, running totals, LAG, moving averages.
NULL and GROUP BY
The NOT IN trap, COUNT variants, conditional aggregation, integer division.
The core questions
The queries run on the same small sample database as the cheat sheet: departments, employees and sales, with a few traps built in.
What is the difference between WHERE and HAVING?
WHERE filters rows before they are grouped. HAVING filters groups after aggregation, so only HAVING can use COUNT, SUM and friends. If a condition does not need an aggregate, put it in WHERE: it removes rows early and usually runs faster.
SELECT department_id, COUNT(*) AS n
FROM employees
WHERE salary > 4500 -- row filter, before grouping
GROUP BY department_id
HAVING COUNT(*) >= 2 -- group filter, after grouping
ORDER BY department_id| department_id | n |
|---|---|
| 1 | 2 |
| 2 | 4 |
What is the difference between INNER JOIN and LEFT JOIN?
An inner join keeps only rows that have a match on both sides. A left join keeps every row from the left table and fills the right side with NULLs when there is no match. In the sample data, Lea has no department: an inner join drops her, a left join keeps her. Worked examples, including the WHERE-versus-ON trap, are on the JOIN questions page.
SELECT COUNT(*) AS inner_rows FROM employees e JOIN departments d ON d.id = e.department_id| inner_rows |
|---|
| 9 |
SELECT COUNT(*) AS left_rows FROM employees e LEFT JOIN departments d ON d.id = e.department_id| left_rows |
|---|
| 10 |
What is the difference between UNION and UNION ALL?
UNION combines two results and removes duplicate rows. UNION ALL just stacks them. Use UNION ALL unless you need the de-duplication, because removing duplicates costs a sort or a hash. Both queries need the same number of columns with compatible types.
SELECT region FROM sales WHERE amount > 100
UNION ALL
SELECT region FROM sales WHERE amount < 100
ORDER BY region| region |
|---|
| North |
| North |
| North |
| North |
| South |
| South |
What is the difference between COUNT(*), COUNT(column) and COUNT(DISTINCT column)?
COUNT(*) counts rows. COUNT(column) counts rows where the column is not NULL. COUNT(DISTINCT column) counts different non-NULL values.
SELECT COUNT(*) AS all_rows, COUNT(amount) AS with_amount, COUNT(DISTINCT region) AS regions FROM sales| all_rows | with_amount | regions |
|---|---|---|
| 7 | 6 | 2 |
How does NULL behave in comparisons?
NULL means unknown, so NULL = NULL is not true; it is NULL. WHERE keeps only rows where the condition is true, so = NULL matches nothing. Use IS NULL. Aggregates skip NULLs, and NOT IN with a NULL in the list returns nothing. All of it, with examples, is on the NULL and GROUP BY page.
SELECT COUNT(*) AS matches_equals_null FROM employees WHERE department_id = NULL| matches_equals_null |
|---|
| 0 |
SELECT COUNT(*) AS matches_is_null FROM employees WHERE department_id IS NULL| matches_is_null |
|---|
| 1 |
In what order is a query evaluated?
Not in the order you write it. Logically: FROM and JOIN, then WHERE, GROUP BY, HAVING, window functions, SELECT (and DISTINCT), ORDER BY, and finally LIMIT. That is why a WHERE cannot use an aggregate, and why standard SQL does not let you use a SELECT alias in WHERE. SQLite is lenient and accepts the alias, which is a portability trap: the same query fails in PostgreSQL and SQL Server.
SELECT full_name, salary * 12 AS yearly FROM employees WHERE yearly > 100000 ORDER BY id| full_name | yearly |
|---|---|
| Mara Lind | 110400 |
What are a primary key, a foreign key and a UNIQUE constraint?
A primary key identifies each row: unique and not NULL (one per table). A foreign key points at a key in another table and stops you from storing a child row with no parent. A UNIQUE constraint forbids duplicates but, depending on the database, allows NULLs. In SQLite, foreign keys are enforced only after PRAGMA foreign_keys = ON.
What is an index, and when does it not help?
An index is a sorted structure that lets the database find rows by a column without reading the whole table. It costs disk space and makes writes slower. It does not help when the condition wraps the column in a function, when a LIKE pattern starts with %, or when the condition matches most of the table. Check with EXPLAIN.
EXPLAIN QUERY PLAN SELECT * FROM employees WHERE department_id = 2| id | parent | notused | detail |
|---|---|---|---|
| 2 | 0 | 0 | SCAN employees |
CREATE INDEX idx_employees_department ON employees(department_id);
EXPLAIN QUERY PLAN SELECT * FROM employees WHERE department_id = 2| id | parent | notused | detail |
|---|---|---|---|
| 3 | 0 | 0 | SEARCH employees USING INDEX idx_employees_department (department_id=?) |
Subquery or CTE?
Most of the time it is about readability. A CTE (WITH name AS (...)) gives a name to a step, can be referenced several times and is the only way to write a recursive query. Performance depends on the database's optimizer, so do not assume either one is faster: read the plan.
WITH ranked AS (
SELECT full_name, salary, RANK() OVER (ORDER BY salary DESC) AS r FROM employees
)
SELECT full_name, salary FROM ranked WHERE r <= 3 ORDER BY r, full_name| full_name | salary |
|---|---|
| Mara Lind | 9200 |
| Ines Cole | 7600 |
| Joel Park | 7600 |
What is the difference between DELETE, TRUNCATE and DROP?
DELETE removes rows, can take a WHERE, and fires triggers. TRUNCATE removes all rows in one operation without a WHERE; it is usually faster, and how it behaves in transactions differs between databases. SQLite has no TRUNCATE; use DELETE FROM table. DROP TABLE removes the table itself, with its data and indexes.
What is normalization?
Organizing tables so each fact is stored once. First normal form: one value per cell, no repeating groups. Second: every non-key column depends on the whole key, not part of a composite key. Third: non-key columns do not depend on other non-key columns. The benefit is fewer update mistakes; the cost is more joins. Reporting tables are often denormalized on purpose.
What is the difference between a window function and GROUP BY?
GROUP BY collapses rows into one per group. A window function keeps every row and adds a value computed over a related set of rows. Use it when you need the detail and the group figure side by side, such as each salary next to its department average. More on the window functions page.
SELECT full_name, salary, ROUND(AVG(salary) OVER (PARTITION BY department_id)) AS dept_average
FROM employees
WHERE department_id = 1
ORDER BY full_name| full_name | salary | dept_average |
|---|---|---|
| Nadia Voss | 6400 | 5867 |
| Owen Hart | 4300 | 5867 |
| Tomas Reyes | 6900 | 5867 |
How to answer in the room
- Restate the question and ask about the edge cases that change the answer: NULLs, ties, duplicates, an empty table.
- Describe the idea in one sentence before you type: "join orders to customers, group by customer, keep groups with more than two orders".
- Build the query in layers and check each layer on two or three rows of made-up data.
- Say what you would check on a real database: an index on the join and filter columns, the plan, the size of the result.
- Mention the dialect you assume. The core is the same everywhere; dates, strings and LIMIT are not.
Nobody can promise what a particular interviewer will ask. What you can do is make the basics automatic, so your attention is free for the unusual part.
Keep going
- JOIN questions
- Window function questions
- NULL and GROUP BY questions
- SQL cheat sheet: syntax and dialect notes
Practice it, don't just read it. Query Belt has mock interviews with a timer, no hints and one submit per task, then a report with your weak topics. It works offline.
Coming to Google Play