SQL interview questions

What SQL interview questions actually test

A recruiter screen or an online form will ask definitions. Later rounds ask you to write queries while someone watches, usually in a shared document or a browser editor with a tiny database behind it. Nobody is checking whether you remember every function name. They are checking that you can read an unfamiliar schema, pick the right join, aggregate at the right grain, and notice the rows that do not fit.

The emphasis shifts with the role. For a data analyst job, expect aggregation, window functions and questions about data quality. For a data engineer job, expect deduplication, incremental loads and performance. For a backend or testing role, expect queries an application would run, plus indexes and transactions.

One habit separates strong candidates: they ask about the data before writing anything. Is order_id unique in this table, or is there one row per order line? Can customer_id be NULL? Is amount stored in cents? Those three questions take fifteen seconds and change the query you write.

Join questions, and the ones that catch people out

The standard opener is the difference between join types. An INNER JOIN keeps only rows that match on both sides. A LEFT JOIN keeps every row from the left table and fills the right-hand columns with NULL when there is no match. A RIGHT JOIN is the mirror image, and a FULL OUTER JOIN keeps unmatched rows from both sides.

Make it concrete with an employees table where one person has no department yet:

-- 5 rows: the employee with a NULL dept_id disappears
SELECT e.name, d.name AS dept
FROM employees AS e
JOIN departments AS d ON d.dept_id = e.dept_id;

-- 6 rows: that employee is kept, with dept = NULL
SELECT e.name, d.name AS dept
FROM employees AS e
LEFT JOIN departments AS d ON d.dept_id = e.dept_id;

The follow-up is usually an anti-join: list the departments that have nobody in them. Two correct answers exist, and saying both is a good sign.

SELECT d.name
FROM departments AS d
LEFT JOIN employees AS e ON e.dept_id = d.dept_id
WHERE e.emp_id IS NULL;

SELECT d.name
FROM departments AS d
WHERE NOT EXISTS (SELECT 1 FROM employees AS e WHERE e.dept_id = d.dept_id);

The trap in this family is row multiplication. If the right-hand table has several rows per key, a join multiplies the left-hand rows, and any SUM you run afterwards is quietly too big. If you join orders to order_items and then sum the order total, every order is counted once per item. Mention that you would check the row count before and after the join, or aggregate the detail table first in a subquery and join to that.

NULL, COUNT and WHERE versus HAVING

NULL means unknown, not zero and not an empty string, so comparing to it gives unknown rather than true or false. That is why NULL = NULL does not return true and why you need IS NULL. On a six-row table where one department is missing, the counts come out like this:

SELECT COUNT(*)                 AS rows_total,      -- 6
       COUNT(dept_id)           AS dept_not_null,   -- 5
       COUNT(DISTINCT dept_id)  AS distinct_depts   -- 2
FROM employees;

COUNT(*) counts rows. COUNT(column) skips NULLs. AVG and SUM skip them too, which is the right answer to “what happens to the average if some values are missing”: the missing rows are excluded from the divisor, so the average is over the known values only.

The sharpest version of this question is NOT IN against a subquery that can return NULL. The PostgreSQL documentation spells out the rule: if there are no equal right-hand values and at least one right-hand row yields null, the result of the NOT IN construct will be null, not true. Because unknown is not true, the row is filtered out, and the query returns nothing at all. Run the anti-join above with NOT IN (SELECT dept_id FROM employees) and you get an empty result, which looks like a data problem but is three-valued logic working as specified. Use NOT EXISTS, or add IS NOT NULL inside the subquery.

WHERE and HAVING differ in when they run. WHERE filters individual rows before grouping; HAVING filters the groups after aggregation, so it is the only place an aggregate can go:

SELECT dept_id, COUNT(*) AS headcount, AVG(salary) AS avg_salary
FROM employees
WHERE salary > 50000      -- drops rows
GROUP BY dept_id
HAVING COUNT(*) > 1       -- drops groups
ORDER BY dept_id;

SQL queries asked in interviews, with answers

Second-highest salary is the classic. The subquery version works everywhere and is easy to explain out loud:

SELECT MAX(salary) AS second_highest
FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);

If the interviewer asks about ties, switch to DENSE_RANK, which gives equal salaries the same rank so the second distinct salary is still rank 2:

SELECT salary
FROM (SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
      FROM employees) AS ranked
WHERE rnk = 2
LIMIT 1;

Finding duplicates is GROUP BY plus HAVING on the columns that are supposed to be unique together:

SELECT email, COUNT(*) AS n
FROM users
GROUP BY email
HAVING COUNT(*) > 1;

Top N per group is the question that separates people who have used window functions from people who have read about them. ROW_NUMBER numbers the rows inside each partition, and you filter on that number in an outer query. The reason for the outer query is worth saying aloud: window functions are permitted only in the SELECT list and the ORDER BY clause, and are forbidden in WHERE, GROUP BY and HAVING, because they run after those clauses are processed. PostgreSQL’s manual recommends exactly this sub-select pattern.

SELECT dept_id, name, salary
FROM (
  SELECT dept_id, name, salary,
         ROW_NUMBER() OVER (PARTITION BY dept_id
                            ORDER BY salary DESC, emp_id) AS pos
  FROM employees
  WHERE dept_id IS NOT NULL
) AS ranked
WHERE pos <= 2
ORDER BY dept_id, pos;

Adding emp_id to the ORDER BY inside OVER is deliberate: with two identical salaries the order would otherwise be arbitrary, and interviewers do notice the tie-breaker. Use RANK instead of ROW_NUMBER when the brief says to keep ties, and DENSE_RANK when you want consecutive rank numbers.

Running totals and period comparisons use the same mechanism with a frame and with LAG:

SELECT order_day,
       amount,
       SUM(amount) OVER (ORDER BY order_day
                         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total,
       amount - LAG(amount) OVER (ORDER BY order_day) AS change_vs_prev
FROM orders
ORDER BY order_day;

On four days of 100, 250, 50 and 400 the running total reads 100, 350, 400, 800, and change_vs_prev is NULL on the first day because there is no previous row. Saying that out loud shows you know the first row is a real edge case rather than a bug.

Index and performance questions

Expect “this query is slow, what do you do” in any role above junior. The answer is a method, not a trick. Look at the execution plan first with EXPLAIN, see whether the engine scans the whole table or uses an index, and check how far the estimated row counts are from reality.

An index is useful when a query selects a small part of a large table, which is why indexes go on the columns you filter and join on, not on everything. For a multicolumn index, column order matters. The PostgreSQL documentation puts the rule this way: equality constraints on leading columns, plus any inequality constraints on the first column that does not have an equality constraint, will always be used to limit the portion of the index that is scanned. In interview terms: an index on (customer_id, order_day) serves a query that filters on customer_id, and serves one that filters on both, but a query that filters only on order_day gets much less from it.

Two more points are worth having ready. Wrapping an indexed column in a function, as in WHERE YEAR(order_day) = 2026, usually stops the plain index being used for that comparison, so a range such as order_day >= ‘2026-01-01’ AND order_day < ‘2027-01-01’ is the safer form. And indexes are not without cost: every write has to maintain them, so a table with a dozen indexes pays for them on every insert.

If you have never tuned a real query, say so and describe the method anyway. Honest method beats memorised jargon.

Dialect differences worth knowing

Interviewers often ask which database you used, then stay in that dialect. The gap people trip over is row limiting, which is genuinely different between engines. Both forms below are documented behaviour, and both need an ORDER BY to be deterministic.

The ORDER BY is not a nicety. PostgreSQL’s manual warns that when using LIMIT it is important to use an ORDER BY clause that constrains the result rows into a unique order, otherwise you get an unpredictable subset. In T-SQL, OFFSET and FETCH are part of the ORDER BY clause itself, so the sort is required by the syntax, and Microsoft’s guidance adds that stable paging also needs a column or combination of columns guaranteed to be unique.

For a SQL Server interview, read up on the T-SQL extras: TOP with PERCENT and WITH TIES, temporary tables versus table variables, stored procedures. For an Oracle or PL/SQL role, the written SQL is similar but the procedural language, cursors and exception blocks are their own topic. If you learned on one engine, say which, and ask whether they want standard SQL or their dialect.

How to work through a query out loud

Read the question back in your own words before typing. “So you want one row per country, with the two largest orders in that country, ordered by amount.” Half of all wrong answers are answers to a slightly different question.

Then build the query in layers instead of writing it in one go. Start with FROM and the joins, run it, and look at a few rows. Add the filters. Add the grouping or the window. Add the ordering last. If the tool has no run button, say what each layer would return; the interviewer is following your reasoning, not your typing.

Name the edge cases as you go: ties, NULLs in the join key, rows with no match, duplicate keys, dates near a month boundary. Finish by stating the grain of your result, for example one row per customer per month. If you blank on exact syntax, describe the function you want and write your best guess; interviewers care far more about whether your logic holds.