Almost every backend role touches SQL, and almost every performance fire starts there. These questions separate people who write queries from people who understand them.
Hiring a SQL developer is easy. Telling a real one from a convincing résumé is the hard part — and it’s most of what we do. These are grouped by level, because the same question that stretches a junior is a warm-up for a senior.
Junior SQL interview questions
0–2 years
Query fundamentals.
What are the different types of JOIN?
INNER returns matching rows; LEFT/RIGHT keep all rows from one side with NULLs for non-matches; FULL keeps both; CROSS is a Cartesian product.
Only knows INNER JOIN and is unsure what LEFT does.
What is the difference between WHERE and HAVING?
WHERE filters rows before grouping; HAVING filters after aggregation.
Tries to use an aggregate in WHERE.
What does GROUP BY do?
Collapses rows sharing the grouped columns so aggregates (COUNT, SUM, AVG) are computed per group.
Selects non-aggregated, non-grouped columns and expects it to work.
What is a primary key vs a foreign key?
A primary key uniquely identifies a row; a foreign key references a primary key elsewhere, enforcing referential integrity.
Confuses the two or thinks a table can have many primary keys.
What is the difference between UNION and UNION ALL?
Both combine result sets; UNION removes duplicates (and sorts to do so), UNION ALL keeps them and is faster.
Always uses UNION unaware of the dedup cost.
How do NULLs behave in comparisons?
NULL is unknown, so = NULL is never true; you must use IS NULL, and NULLs affect aggregates and joins.
Compares with = NULL and expects matches.
What is the difference between DELETE, TRUNCATE and DROP?
DELETE removes rows (logged, filterable); TRUNCATE quickly empties a table; DROP removes the table entirely.
Thinks TRUNCATE can use a WHERE clause.
What is the difference between CHAR and VARCHAR?
CHAR is fixed length and padded; VARCHAR is variable length. They pick based on data shape.
No idea there’s a storage difference.
Mid-level SQL interview questions
2–5 years
Indexing, subqueries and transactions.
How does an index speed up queries, and what is the cost?
It gives fast lookups (typically a B-tree) instead of a full scan, at the cost of extra storage and slower writes; the wrong indexes hurt.
Thinks indexes are free and adds one per column.
What is a composite index and does column order matter?
An index on multiple columns; order matters because it can only be used left-to-right (the leftmost-prefix rule).
Assumes column order is irrelevant.
What is the N+1 query problem?
Running one query per row of a previous result instead of a single join or batched query, causing many round-trips; common with lazy ORMs.
Has never heard of it despite writing ORM code.
What are ACID properties?
Atomicity, Consistency, Isolation, Durability — the guarantees a transaction provides. They can give a concrete example of each.
Recites the acronym but can’t explain isolation.
What is the difference between a correlated and a non-correlated subquery?
A non-correlated subquery runs once; a correlated one references the outer query and runs per outer row, which can be slow.
Writes correlated subqueries unaware of the performance cost.
When do you use a WITH (CTE) vs a subquery?
CTEs improve readability and enable recursion; they are otherwise similar, though materialisation behaviour varies by engine.
Never uses CTEs and nests subqueries deeply.
What are transaction isolation levels?
Read Uncommitted, Read Committed, Repeatable Read and Serializable, trading concurrency against anomalies like dirty and phantom reads.
Unaware isolation levels exist.
What is a deadlock and how do you avoid one?
Two transactions each waiting on locks the other holds; you reduce them by consistent lock ordering, short transactions and appropriate indexing.
No concept of lock ordering.
Senior SQL interview questions
5+ years
Optimisation and modelling.
How do you diagnose a slow query?
Read the EXPLAIN/execution plan for scans vs index use, check statistics and cardinality estimates, and look at joins and filters that can’t use an index.
Rewrites queries randomly without reading a plan.
What is normalisation and when do you denormalise?
Organising data to remove redundancy (up to 3NF); you denormalise deliberately for read performance, accepting update complexity.
Denormalises by default with no reason.
What is a covering index?
An index that contains all columns a query needs, so the engine answers from the index without touching the table.
Never heard of index-only scans.
How do window functions differ from GROUP BY?
Window functions compute across a set of rows while keeping each row (ranking, running totals), unlike GROUP BY which collapses rows.
Emulates running totals with slow self-joins.
How do you handle very large tables?
Partitioning, appropriate indexing, archiving cold data, batched writes, and avoiding full scans; sometimes read replicas.
Runs unbounded queries against a huge table.
What causes index bloat or an unused index?
Low-selectivity columns, functions on indexed columns preventing use, and redundant indexes; monitoring usage stats helps prune them.
Assumes every index they added is being used.
How do you keep a schema evolving safely in production?
Backward-compatible migrations, adding columns before backfilling, avoiding long locks, and expand/contract patterns for renames.
Runs a blocking ALTER on a huge live table at peak.
When is a relational database the wrong choice?
When access is purely key-value, document-shaped, or needs extreme horizontal scale with simple queries; they can justify the tradeoff either way.
Treats relational or NoSQL as universally superior.
Build and score a full interview with our free interview scorecard tool, browse the full question hub, or see how we interview engineers.