SQL interviews test three things: whether you understand the concepts (joins, grouping, NULLs, indexes, transactions), whether you can write correct queries for realistic questions (top-N per group, running totals, retention, duplicates), and whether you can explain trade-offs. The 30 questions below, grouped by level, cover what is asked most often.
Beginner questions
1. What is the difference between WHERE and HAVING? WHERE filters rows before grouping; HAVING filters groups after aggregation. Use HAVING for conditions on aggregates, such as HAVING COUNT(*) > 5.
2. What are primary and foreign keys? A primary key uniquely identifies each row; a foreign key references a primary key in another table and enforces that the reference exists.
3. What does GROUP BY do? It collapses rows with the same values in the grouped columns into one row per group, so aggregates (COUNT, SUM, AVG, MIN, MAX) are computed per group.
4. What is the difference between DELETE, TRUNCATE and DROP? DELETE removes selected rows and can be filtered and rolled back; TRUNCATE quickly removes all rows; DROP removes the table itself.
5. What is the difference between UNION and UNION ALL? UNION removes duplicate rows (extra work); UNION ALL keeps them and is faster.
6. How do NULLs behave? NULL means unknown. Comparisons with NULL return unknown, so use IS NULL; aggregates ignore NULLs except COUNT(*); COALESCE replaces NULLs with a default.
7. What is the difference between COUNT(*), COUNT(column) and COUNT(DISTINCT column)? Rows; non-null values; distinct non-null values.
8. What are the join types? INNER, LEFT, RIGHT, FULL OUTER, CROSS and SELF; see SQL joins explained.
9. What is the logical order of a SELECT query? FROM/JOIN → WHERE → GROUP BY → HAVING → SELECT → DISTINCT → ORDER BY → LIMIT, which is why a column alias usually can't be used in WHERE.
10. What does DISTINCT do? Removes duplicate result rows across the selected columns.
Intermediate questions
11. What is a subquery, and what is a correlated subquery? A query inside another. A correlated subquery references the outer query and runs per outer row, which can be slow; joins or window functions are often better.
12. What is a CTE? A common table expression (WITH name AS (...)) names an intermediate result, making complex queries readable; recursive CTEs handle hierarchies.
13. How do you find duplicate emails? SELECT email, COUNT(*) FROM users GROUP BY email HAVING COUNT(*) > 1;
14. How do you find the second-highest salary? Use DENSE_RANK() and filter rank 2, or MAX(salary) where salary is below the overall maximum. Clarify how ties should behave.
15. How do you find customers with no orders? An anti join: LEFT JOIN orders and keep rows where the order ID IS NULL, or use NOT EXISTS.
16. What is an index, and what are its trade-offs? A structure (usually a B-tree) that speeds lookups and sorting on columns, at the cost of extra storage and slower writes. See indexing explained.
17. What is normalisation? Organising tables so each fact is stored once to avoid anomalies; see normalisation explained.
18. What is the difference between CHAR and VARCHAR? CHAR is fixed length (padded); VARCHAR is variable length.
19. How do CASE expressions help? They create conditional values, such as bucketing scores into grades, or conditional aggregates like SUM(CASE WHEN status = 'paid' THEN 1 ELSE 0 END).
20. What is a view? A saved query that behaves like a virtual table, useful for reuse and limiting what users can see.
Advanced questions
21. What are window functions? Functions computed over a set of rows related to the current row without collapsing them, using OVER (PARTITION BY ... ORDER BY ...): ROW_NUMBER, RANK, DENSE_RANK, LAG, LEAD, running SUM and moving averages.
22. How do you get the top 3 products per category? ROW_NUMBER() OVER (PARTITION BY category ORDER BY sales DESC) in a CTE, then filter row number ≤ 3.
23. What is the difference between ROW_NUMBER, RANK and DENSE_RANK? For tied values 100, 100, 90: ROW_NUMBER gives 1, 2, 3; RANK gives 1, 1, 3; DENSE_RANK gives 1, 1, 2.
24. How do you compute a running total? SUM(amount) OVER (ORDER BY order_date), optionally partitioned by customer.
25. How do you compare each row with the previous one? LAG(value) OVER (ORDER BY date), for example to compute day-over-day change.
26. What are ACID transactions? Atomicity, consistency, isolation, durability: a group of operations either fully happens or not at all, and stays correct under concurrency and failures.
27. What are isolation levels? Read uncommitted, read committed, repeatable read and serializable trade concurrency for protection against anomalies such as dirty reads and phantom rows.
28. How do you read a query plan? Use EXPLAIN (or EXPLAIN ANALYZE) to see scans, index use, join methods and row estimates; look for full scans on large tables and misestimated row counts.
29. How would you compute 7-day retention? Find each user's first activity date, then check for activity 7 days later with a join or EXISTS, and divide retained users by cohort size per signup date.
30. When would you denormalise? For read-heavy reporting or performance, accepting duplication and keeping copies in sync deliberately.
Which topics carry the most weight?
Counted from this list; actual interview mixes vary by company and role.
How should you answer query-writing questions?
- Restate the question and clarify assumptions: ties, NULLs, time zones, what counts as "active".
- Sketch the steps before writing: filter, join, aggregate, rank.
- Write readable SQL with CTEs and clear aliases.
- Check edge cases: empty groups, duplicates, ties, users with no activity.
- Mention performance: which indexes would help on large tables.
What mistakes cost candidates the most?
- Filtering a LEFT JOIN's right table in WHERE and losing unmatched rows.
- Selecting non-aggregated columns that aren't in GROUP BY.
- Comparing with
= NULL. - Ignoring ties in "top N" questions.
- Joining before aggregating and double-counting.
What does a full worked query look like?
Question: "For each city, show the top two learners by total quiz score, including ties." Step one, total scores per learner with GROUP BY. Step two, join to learners to get the city. Step three, rank within each city with DENSE_RANK() OVER (PARTITION BY city ORDER BY total DESC). Step four, filter rank ≤ 2 in an outer query, because window functions can't be used directly in WHERE. Explaining these four steps before typing, and mentioning that DENSE_RANK keeps ties while ROW_NUMBER would drop them, is exactly the reasoning interviewers want to hear.
What do take-home SQL tests look like?
Some companies send a small dataset and several business questions to answer in a few hours. Treat it like a real analysis: write readable queries, state assumptions at the top, check row counts at each step, and add a short written summary of findings. Reviewers judge clarity and correctness, not clever one-liners.
How should you practise?
Create a small schema (users, courses, enrolments, payments, activity) in PostgreSQL with realistic data, then answer one business question a day with a query, including retention and top-N-per-group questions. SQL, joins, indexing, transactions and NoSQL are part of Program Zero's five-week database course, and SQL is used throughout the data science course.