SQL is the one technical skill that shows up in almost every data, backend, and analyst interview — and the one candidates most often wing. The questions are predictable, the concepts are finite, and a few hours of focused practice puts you ahead of most of the field. This guide covers the SQL interview questions that actually get asked, with answers and the concept each one tests.
The fundamentals interviewers always check
- What is a JOIN, and what types exist? INNER (matching rows only), LEFT (all left + matching right), RIGHT, FULL OUTER, and CROSS. Know exactly what rows each returns.
- Difference between WHERE and HAVING? WHERE filters rows before grouping; HAVING filters groups after GROUP BY.
- Primary key vs unique key? A primary key is unique and not null, one per table; a unique key allows one null and you can have several.
- What is normalization? Organizing tables to reduce redundancy (1NF, 2NF, 3NF).
- DELETE vs TRUNCATE vs DROP? DELETE removes rows (can filter, logged), TRUNCATE empties the table fast, DROP removes the table entirely.
The classic query: second-highest salary
This is the single most-asked SQL interview question. One clean answer:
SELECT MAX(salary) FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);
Or with a window function:
SELECT DISTINCT salary FROM (
SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk FROM employees
) t WHERE rnk = 2;
Knowing both approaches — and when DENSE_RANK beats RANK — signals real depth.
GROUP BY and aggregation questions
- Find the number of employees in each department. (GROUP BY + COUNT)
- Find departments with more than 5 employees. (GROUP BY + HAVING)
- Find the average salary per department, highest first. (GROUP BY + AVG + ORDER BY)
- Find duplicate rows in a table. (GROUP BY + HAVING COUNT(*) > 1)
Window functions (the senior signal)
- ROW_NUMBER vs RANK vs DENSE_RANK — how each handles ties.
- Find the top earner per department. (PARTITION BY department ORDER BY salary DESC)
- Running totals and moving averages with SUM() OVER (...).
- LEAD and LAG to compare a row with the next/previous one.
How to practise SQL for interviews
Solve real query problems (start with the joins and the second-highest-salary pattern), then practise explaining your approach out loud — because many SQL rounds are live and verbal, and a correct query you can't explain still loses points. Greenroom runs spoken technical interviews where you talk through your reasoning and get feedback on clarity. Pair it with our OOPs interview questions and backend interview questions guides.
Frequently asked questions
What are the most common SQL interview questions?
The most common are: explain the types of JOINs and what rows each returns; the difference between WHERE and HAVING; primary key vs unique key; what normalization is; DELETE vs TRUNCATE vs DROP; and the classic 'find the second-highest salary' query. GROUP BY aggregation and window-function questions round out a typical round.
How do you find the second-highest salary in SQL?
Two clean approaches: use a subquery — SELECT MAX(salary) FROM employees WHERE salary < (SELECT MAX(salary) FROM employees); or use a window function — DENSE_RANK() OVER (ORDER BY salary DESC) and filter where the rank equals 2. Knowing both, and when DENSE_RANK beats RANK for ties, signals real depth.
What window functions should I know for SQL interviews?
Know ROW_NUMBER, RANK and DENSE_RANK and how each handles ties; PARTITION BY to compute per-group results like the top earner per department; SUM() OVER for running totals and moving averages; and LEAD/LAG to compare a row with the next or previous one. Window functions are a strong senior-level signal.
How should I practise SQL for an interview?
Solve real query problems starting with joins and the second-highest-salary pattern, then practise explaining your query logic out loud, since many SQL rounds are live and verbal. A correct query you can't explain still loses points, so rehearsing your reasoning with a voice-based mock interview helps you talk through joins and grouping clearly.