Intermediate SQL and MySQL Interview Questions
SQL and MySQL interview questions for developers with a few years' experience: LEFT JOIN counts, NOT IN and NULL, composite indexes, window functions, isolation levels, deadlocks, EXPLAIN and SQL injection.
CoversGROUP BY and aggregatesIndexesJoinsMySQLNULL handlingQuery optimisationSQL injectionSubqueries and CTEsTransactionsWindow functions
Practice questions with answers
Tap an option: green is right, red is wrong, and the explanation says why.
What does this query return?
CREATE TABLE customers (id INT, name VARCHAR(20));
CREATE TABLE orders (id INT, customer_id INT, total INT);
INSERT INTO customers VALUES (1, 'Asha'), (2, 'Ben'), (3, 'Chen');
INSERT INTO orders VALUES (10, 1, 50), (11, 1, 70), (12, 3, 20);
SELECT c.name, COUNT(o.id) AS orders
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.id, c.name
ORDER BY c.id;Show answer and explanation
Answer: B. Asha 2, Ben 0, Chen 1
The LEFT JOIN keeps Ben even though he has no orders; his order columns are NULL. COUNT(o.id) counts non-NULL order ids, so Ben gets 0. Writing COUNT(*) here would be a bug: it counts the joined row itself and would give Ben 1. An INNER JOIN would drop Ben entirely, and COUNT never returns NULL.
Why does this query return no rows?
CREATE TABLE products (id INT, name VARCHAR(20));
CREATE TABLE discontinued (product_id INT);
INSERT INTO products VALUES (1, 'Pen'), (2, 'Ink'), (3, 'Pad');
INSERT INTO discontinued VALUES (2), (NULL);
SELECT name FROM products
WHERE id NOT IN (SELECT product_id FROM discontinued);Show answer and explanation
Answer: A. Because the subquery contains a NULL, so id NOT IN (...) is never TRUE; use NOT EXISTS or filter out NULLs
id NOT IN (2, NULL) means id <> 2 AND id <> NULL. The second comparison is UNKNOWN for every id, so the whole condition is never TRUE and no rows come back. NOT EXISTS (SELECT 1 FROM discontinued d WHERE d.product_id = p.id) doesn't have this trap, nor does adding WHERE product_id IS NOT NULL to the subquery. NOT IN with subqueries is allowed, and one discontinued product can't remove the others on its own.
You have a composite index on (last_name, first_name). Which query can use it best?
Show answer and explanation
Answer: C. WHERE last_name = 'Shah' AND first_name = 'Riya'
A composite B+tree index is sorted by its first column, then by the second within each first value: the leftmost-prefix rule. Equality on both columns is a direct seek. A filter on first_name alone skips the leading column, so MySQL usually can't seek (8.0 can sometimes use a skip scan, but don't rely on it). Wrapping columns in a function hides them from the index, and a LIKE pattern that starts with % can't use the sorted order.
What is the difference between ROW_NUMBER(), RANK() and DENSE_RANK()?
Show answer and explanation
Answer: C. ROW_NUMBER gives unique numbers even for ties; RANK gives ties the same number and leaves gaps; DENSE_RANK gives ties the same number with no gaps
For salaries 900, 800, 800, 700: ROW_NUMBER gives 1, 2, 3, 4 (ties broken arbitrarily unless ORDER BY decides), RANK gives 1, 2, 2, 4, and DENSE_RANK gives 1, 2, 2, 3. That is why "Nth highest distinct salary" uses DENSE_RANK. All three are window functions (MySQL 8.0 and later) used with OVER (PARTITION BY ... ORDER BY ...), not GROUP BY, and RANK is the one that leaves gaps.
What is MySQL's default transaction isolation level for InnoDB?
Show answer and explanation
Answer: B. REPEATABLE READ
InnoDB defaults to REPEATABLE READ: plain SELECTs in a transaction read one consistent snapshot taken at the first read, and locking reads use next-key locks that also block inserts into the scanned gaps. PostgreSQL, SQL Server and Oracle default to READ COMMITTED, which is a frequent interview trap when people move between databases. READ UNCOMMITTED allows dirty reads, and SERIALIZABLE is the strictest level and costs the most locking.
Which query finds the customers who have never placed an order?
Show answer and explanation
Answer: C. SELECT c.* FROM customers c LEFT JOIN orders o ON o.customer_id = c.id WHERE o.id IS NULL
This is an anti-join: the LEFT JOIN keeps every customer, customers without orders get NULL order columns, and WHERE o.id IS NULL keeps just those. NOT EXISTS is an equally good form. An INNER JOIN has already removed those customers, = NULL is never true, and <> against a subquery that returns several rows raises an error.
Why does WHERE YEAR(created_at) = 2025 often make a query slow, and what is the fix?
Show answer and explanation
Answer: A. The function hides the column from its index; rewrite it as a range: created_at >= '2025-01-01' AND created_at < '2026-01-01'
The index on created_at is sorted by the raw value, not by YEAR(created_at), so MySQL must compute the function on every row: a full scan. A half-open range on the bare column lets it seek straight to the first 2025 row. If you really need the expression, MySQL 8.0.13+ supports functional indexes on it. SUBSTR has the same problem, dates index fine, and LIMIT doesn't fix a scan that finds matches late.
Two transactions update the same two rows in opposite order and MySQL returns error 1213. What happened, and what should the application do?
Show answer and explanation
Answer: A. A deadlock: InnoDB detected the lock cycle and rolled back one transaction; retry it, and lock rows in a consistent order to make deadlocks rarer
Error 1213 is ER_LOCK_DEADLOCK. Each transaction held a row lock the other needed, so InnoDB's deadlock detector chose a victim, rolled it back and let the other continue. Deadlocks are normal under concurrency: wrap the transaction in a retry, keep transactions short, and touch rows in the same order (for example sorted by primary key). It isn't corruption, and the connection count is unrelated. A lock-wait timeout is a different error, 1205.
What does EXPLAIN showing type: ALL and rows: 2000000 for a table tell you?
Show answer and explanation
Answer: B. MySQL plans a full table scan of about two million rows; an index on the filtered or joined columns is probably missing
In EXPLAIN, type is the access method. ALL is the worst: read every row. rows is the optimiser's estimate of rows it will examine, not the size of the result. Better types are range, ref, eq_ref and const. The usual fix is an index that matches the WHERE and JOIN columns, then EXPLAIN ANALYZE (8.0.18+) to see real timings. ALL never means "all indexes", and MySQL 8 has no query cache.
A search page builds SQL like "... WHERE name = '" + input + "'". What is the correct fix?
Show answer and explanation
Answer: C. Use prepared statements with bound parameters, and allow-list anything that can't be a parameter, such as a sort column
Concatenating input into SQL is SQL injection: ' OR '1'='1 changes the query's meaning. Bound parameters (WHERE name = ?) send the value separately from the SQL text, so it can never become code. Identifiers such as column names or ASC/DESC can't be bound, so map them through an allow-list. Hand-rolled escaping misses cases such as character sets and numeric contexts, keyword filters are trivially bypassed, and logged-in users can attack too.
Take it as a timed test
- 12 questions
- 15 min
- Pass mark 60%
- Answers shown at the end
Sign in with your email to take the test: we send a 6-digit code, no password. Your scores and reports are saved to your account. The test also uses 10 questions that aren't shown above, so practise for understanding, not memory.
About this paper
This paper is for developers who write SQL every day and are interviewing for mid-level backend or data roles. It goes past syntax into the questions that separate people who have debugged real queries: why a LEFT JOIN with COUNT(*) miscounts, why NOT IN returns nothing when the subquery holds a NULL, the leftmost-prefix rule for composite indexes, ROW_NUMBER versus RANK versus DENSE_RANK, MySQL's default isolation level, what error 1213 means, how to read EXPLAIN, and how prepared statements stop SQL injection. Ten questions are on this page with explanations; the timed test draws 12 from a bank of 20, so each attempt is different.
Read each explanation even when you get the answer right: they cover the follow-up questions interviewers ask next.