learn deep. ship safe.Practice testsFollow ByteMunSubscribe
SQL and MySQL · Mid-level

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.

0/10 answered · ✓ 0 ✗ 0
Question 1 of 101 / 10
Q1What does this code output?medium

What does this query return?

sql
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.

Q2What does this code output?medium

Why does this query return no rows?

sql
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.

Q3Choose one answermedium

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.

Q4Choose one answermedium

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.

Q5Choose one answermedium

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.

Q6Choose one answermedium

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.

Q7Choose one answerhard

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.

Q8Choose one answerhard

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.

Q9Choose one answerhard

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.

Q10Choose one answerhard

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.

Written by Published Answers checked by Deepak

More SQL and MySQL quizzes

All SQL and MySQL quizzes →