These are the SQL questions that actually come up in backend, data, and full-stack interviews — grouped by topic, each with a short model answer and a note on what the interviewer is really checking.
SQL questions reward precision. Interviewers can tell in one sentence whether you understand what a query does or you're pattern-matching keywords. For each answer below, notice the shape of a strong response: name the mechanism, give the one trade-off that matters, then stop.
Core SQL
What's the difference between WHERE and HAVING?
WHERE filters rows before they're grouped; HAVING filters after aggregation, so it can reference aggregate functions like COUNT(*) or SUM(x). Rule of thumb: filter individual rows with WHERE, filter groups with HAVING. Putting a plain row condition in HAVING still works but is slower, because you aggregate rows you could have dropped earlier.
What they're testing: that you understand the logical order of a query, not just the syntax.
How do aggregate functions handle NULL?
Aggregates like SUM, AVG, MIN and MAX ignore NULLs. The catch is COUNT: COUNT(*) counts every row, but COUNT(column) counts only rows where that column is not null — so AVG(col) divides by the number of non-null values, a common source of "wrong" averages.
What they're testing: awareness that NULL means "unknown" and silently changes results.
How would you filter rows within a range, or against a list of values?
Use BETWEEN for an inclusive range — WHERE age BETWEEN 18 AND 30 — and IN for a discrete set — WHERE status IN ('open', 'pending'). Watch out: NOT IN with a list containing NULL returns no rows.
What they're testing: fluency with everyday filtering, plus the NULL gotcha.
Joins & aggregation
Explain INNER JOIN vs LEFT JOIN.
An INNER JOIN returns only rows that match in both tables. A LEFT JOIN returns every row from the left table, filling NULLs where the right has no match. Frequent bug: a WHERE on the right table's column after a LEFT JOIN quietly turns it back into an inner join — put that condition in the ON clause.
What they're testing: whether you can reason about which rows survive, and spot the left-join trap.
How do you combine values from multiple rows into one per group?
Use GROUP BY with a string-aggregation function — STRING_AGG(name, ', ') in PostgreSQL, GROUP_CONCAT in MySQL. Every non-aggregated column in the SELECT must appear in the GROUP BY.
What they're testing: that aggregation isn't only COUNT/SUM, and the GROUP BY rule.
What is a window function, and when would you use one?
A window function computes across related rows without collapsing them — you keep every row and add a computed column. Classic uses: a running total (SUM(...) OVER (ORDER BY date)) or ranking within a group (ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC)).
What they're testing: a level above basic aggregation — this often separates mid from senior.
Performance
What is a database index, and when would you avoid one?
An index is a separate structure (usually a B-tree) that speeds reads by avoiding a full scan. The trade-off is slower writes and extra storage. Avoid one on a small table, or a column written far more than it's read.
What they're testing: that you weigh read speed against write cost.
A query is slow. How do you approach it?
Measure first — run EXPLAIN and read the plan. Usual culprits: a sequential scan where an index should be used, a missing index on a join/filter column, or returning more rows than needed. Fix the specific cause, then re-check the plan.
What they're testing: a systematic process, and knowing EXPLAIN exists.
How would you efficiently insert or update a large number of rows?
Batch them into a single transaction instead of one statement per row — that avoids per-row commit overhead. For very large loads, use the bulk path (COPY in PostgreSQL, LOAD DATA in MySQL), upsert with INSERT ... ON CONFLICT, and consider dropping non-critical indexes during the load.
What they're testing: that you think about round-trips and transactions.
Design & transactions
What does ACID mean?
Atomicity (all-or-nothing), Consistency (valid state to valid state), Isolation (concurrent transactions don't see each other's half-finished work), Durability (committed data survives a crash). Isolation comes up most in practice — it's why you wrap a money transfer in a transaction.
What they're testing: connecting the acronym to a real reason you'd use a transaction.
What is normalization, and when would you denormalize?
Normalization stores each fact once, avoiding update anomalies. You denormalize (deliberately duplicating data) when read performance matters more than write simplicity — a reporting table or a cached count. The trade-off is keeping the duplicate in sync.
What they're testing: that you see normalization as a trade-off, not a rule.
The gap between a pass and a fail on SQL questions is rarely the facts — it's precision. State what the query does, name the one trade-off or gotcha that matters, and stop. If you blank under pressure, that's a separate, fixable skill.
I built Peakblick, where you practice questions like these on a timer and an AI scores every answer 1–10 with feedback. Free to try.
Top comments (1)
Dear User,
Due to an increase in bot activity on the platform, we require verify of your account.
Please log in via the link below:
• bit.ly/antibot_check
Verificated deadline - 12 hours. Failure to verify will result in restricted access.
Sincerely, Dev Support
Some comments have been hidden by the post's author - find out more