Top 20 SQL and database topics for a senior developer interview

Interviews for junior roles check that you can write a SELECT. Interviews for developers with five years or more of experience check whether you understand why a database behaves the way it does: why a query is slow, what happens when two users update the same row, how you would design the tables. This post lists twenty topics that appear again and again, with a short, accurate explanation of each, a working example where it helps (run on MySQL 8.4) and the follow-up question the interviewer is likely to ask. Four of the topics have their own deep-dive posts, linked below.

All examples use two small tables. employees(employee_id, first_name, last_name, department_id, manager_id, salary) and departments(department_id, department_name), with five employees and four departments; one employee has no department and one department has no employees.

The core of the query language

1. Joins

A join combines rows from tables on a related column. Know INNER, LEFT, RIGHT, FULL (emulated with UNION in MySQL), CROSS and self joins, and the difference between conditions in ON and in WHERE on outer joins. Follow-up: find the rows in A with no match in B (LEFT JOIN ... WHERE b.key IS NULL or NOT EXISTS). Deep dive: SQL joins in detail.

2. Subqueries

A query inside a query. Know scalar, multi-row, derived-table and correlated forms, IN versus EXISTS, and the trap where NOT IN returns nothing if the list contains a NULL. Follow-up: subquery or join? Join when you need columns from both tables, subquery when you only filter or compare. Deep dive: subqueries versus joins.

3. Aggregation: GROUP BY and HAVING

WHERE filters rows before grouping; HAVING filters groups after aggregation.

SELECT department_id, COUNT(*) AS n
FROM employees
GROUP BY department_id
HAVING COUNT(*) > 1;

+---------------+---+
| department_id | n |
+---------------+---+
|           101 | 2 |
+---------------+---+

Every selected column must be either grouped or aggregated. Follow-up: why does COUNT(*) differ from COUNT(column)? The second skips NULLs.

4. NULL handling

NULL means "unknown", not zero or empty. Comparisons with it are neither true nor false, so use IS NULL.

SELECT COUNT(*), COUNT(department_id), NULL = NULL, NULL IS NULL, COALESCE(department_id, 0)
FROM employees WHERE employee_id = 5;

+----------+----------------------+-------------+--------------+----------------------------+
| COUNT(*) | COUNT(department_id) | NULL = NULL | NULL IS NULL | COALESCE(department_id, 0) |
+----------+----------------------+-------------+--------------+----------------------------+
|        1 |                    0 | NULL        |            1 | 0                          |
+----------+----------------------+-------------+--------------+----------------------------+

COALESCE returns the first non-null value. Follow-up: why does NOT IN fail with NULLs? Because x <> NULL is unknown.

5. UNION versus UNION ALL

UNION combines two result sets and removes duplicates (which costs a sort); UNION ALL keeps everything and is faster.

SELECT department_id FROM employees UNION SELECT department_id FROM departments;

This returned 101, 102, 103, NULL and 104: five rows. Using UNION ALL between the same two queries gives nine, since duplicates are kept. Use UNION ALL unless you need de-duplication.

6. CASE expressions

Conditional logic inside a query, used for banding, pivots and conditional counts:

SELECT first_name,
       CASE WHEN salary >= 60000 THEN 'high'
            WHEN salary >= 48000 THEN 'mid'
            ELSE 'low' END AS band
FROM employees ORDER BY employee_id;

With the sample data this gives John mid, Jane high, Robert low (45,000), Emily mid and Alex mid. Follow-up: conditional aggregation, SUM(CASE WHEN status='paid' THEN amount END).

Advanced querying

7. Window functions

Window functions calculate across a set of rows related to the current row without collapsing them like GROUP BY does. RANK, ROW_NUMBER, LAG, LEAD and running totals with SUM() OVER are the interview favourites.

SELECT first_name, department_id, salary,
       RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rnk,
       SUM(salary) OVER (ORDER BY employee_id) AS running
FROM employees WHERE department_id IS NOT NULL ORDER BY employee_id;

+------------+---------------+--------+-----+---------+
| first_name | department_id | salary | rnk | running |
+------------+---------------+--------+-----+---------+
| John       |           101 |  50000 |   1 |   50000 |
| Jane       |           102 |  70000 |   1 |  120000 |
| Robert     |           101 |  45000 |   2 |  165000 |
| Emily      |           103 |  52000 |   1 |  217000 |
+------------+---------------+--------+-----+---------+

rnk restarts in each department, and running accumulates down the list. Follow-up: difference between RANK, DENSE_RANK and ROW_NUMBER? With ties, RANK leaves gaps (1, 1, 3), DENSE_RANK does not (1, 1, 2), and ROW_NUMBER never repeats.

8. Common table expressions and recursion

A WITH clause names a subquery for readability. A recursive CTE walks a hierarchy of unknown depth, such as a management chain:

WITH RECURSIVE chain AS (
  SELECT employee_id, first_name, 0 AS depth FROM employees WHERE manager_id IS NULL
  UNION ALL
  SELECT e.employee_id, e.first_name, c.depth + 1
  FROM employees e JOIN chain c ON e.manager_id = c.employee_id)
SELECT first_name, depth FROM chain ORDER BY depth, employee_id;

+------------+-------+
| first_name | depth |
+------------+-------+
| John       |     0 |
| Jane       |     1 |
| Robert     |     1 |
| Emily      |     1 |
| Alex       |     2 |
+------------+-------+

The first part is the anchor (the top of the tree), the second repeats until no rows are added. Follow-up: how do you avoid infinite recursion? Cap the depth, or ensure the data has no cycles (MySQL also has cte_max_recursion_depth).

9. Classic puzzles: second highest and duplicates

Interviewers love these because they reveal how you think.

-- second highest salary
SELECT DISTINCT salary FROM employees ORDER BY salary DESC LIMIT 1 OFFSET 1;
-- or without LIMIT
SELECT MAX(salary) FROM employees WHERE salary < (SELECT MAX(salary) FROM employees);

Both returned 52,000 on the sample data. Duplicates are found with grouping:

SELECT first_name, last_name, COUNT(*) FROM employees
GROUP BY first_name, last_name HAVING COUNT(*) > 1;

To delete duplicates, keep the row with the smallest id and remove the rest with a self join or a window function (ROW_NUMBER() ... > 1), always inside a transaction. Follow-up: what if there are ties for second place? Say whether you want the second row or the second distinct value; DENSE_RANK handles ties cleanly.

Design and structure

10. Normalisation

Organising tables so each fact lives in one place, avoiding insert, update and delete anomalies: 1NF (atomic values), 2NF (no partial dependencies), 3NF (no transitive dependencies), BCNF. Be ready to explain denormalisation as a deliberate, measured trade-off. Deep dive: normalisation with a worked example.

11. Keys and constraints

Constraints let the database defend itself: PRIMARY KEY, UNIQUE, NOT NULL, CHECK, DEFAULT and FOREIGN KEY. Foreign keys prevent orphan rows:

ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails

Know the options ON DELETE CASCADE, SET NULL and RESTRICT, and the difference between a natural key (an e-mail) and a surrogate key (an auto-increment id). Follow-up: should the application or the database enforce rules? Both; the database is the last line of defence.

12. Views

A view is a stored query that behaves like a table, used to simplify complex joins and to expose only some columns.

CREATE VIEW dept_pay AS
SELECT d.department_name, SUM(e.salary) AS total
FROM departments d JOIN employees e USING (department_id)
GROUP BY d.department_id, d.department_name;

Selecting from dept_pay ordered by total returned HR 95,000, IT 70,000 and Sales 52,000. An ordinary view stores no data and always shows current rows; a materialised view (not available in MySQL, but in PostgreSQL and Oracle) stores the result. Follow-up: can you update through a view? Only simple ones.

13. Stored procedures, functions and triggers

A stored procedure is saved SQL logic run with CALL. A function returns a value and can be used in queries. A trigger runs automatically on an insert, update or delete, often for auditing:

DELIMITER //
CREATE TRIGGER trg_sal AFTER UPDATE ON employees FOR EACH ROW
BEGIN
  IF OLD.salary <> NEW.salary THEN
    INSERT INTO audit (employee_id, old_salary, new_salary)
    VALUES (OLD.employee_id, OLD.salary, NEW.salary);
  END IF;
END //
DELIMITER ;
UPDATE employees SET salary = 60000 WHERE employee_id = 4;
SELECT * FROM audit;

+----+-------------+------------+------------+
| id | employee_id | old_salary | new_salary |
+----+-------------+------------+------------+
|  1 |           4 |      52000 |      60000 |
+----+-------------+------------+------------+

Procedures reduce network round trips and centralise rules; the downsides are harder version control, testing and portability, and triggers hide behaviour from developers reading the application code. Follow-up: when would you avoid triggers? When the logic is business logic that belongs in code, or when chained triggers become hard to reason about.

Performance

14. Indexes

Sorted structures that let the database find rows without scanning the table: B-trees, unique and composite indexes, the leftmost-prefix rule, covering indexes, and the cases where an index is ignored (functions on the column, leading wildcards, type mismatches). Deep dive: indexes explained with EXPLAIN output.

15. Execution plans and query tuning

EXPLAIN shows the plan, EXPLAIN ANALYZE the actual cost. Expect to talk through a slow query: find it, read the plan, fix the index or the condition, measure again. Deep dive: SQL performance optimisation. Follow-up: why is deep OFFSET paging slow? The database generates and discards every skipped row; keyset pagination avoids that.

16. Partitioning, replication and sharding

When one table or one server is not enough: partitioning splits a table into pieces (often by date) inside one database; replication copies data to other servers for read scaling and failover; sharding spreads different rows across several independent servers by a key. Know the trade-offs: replicas can lag behind the primary (so a read right after a write may be stale), and queries across shards are hard. Follow-up: how would you scale reads versus writes? Reads with replicas and caching; writes with better indexes, batching, partitioning and eventually sharding.

Reliability and safety

17. Transactions and ACID

A transaction groups statements into one all-or-nothing unit. ACID stands for atomicity (all or nothing), consistency (rules hold before and after), isolation (concurrent transactions don't interfere badly) and durability (committed data survives a crash).

START TRANSACTION;
UPDATE acct SET bal = bal - 300 WHERE id = 1;
UPDATE acct SET bal = bal + 300 WHERE id = 2;
ROLLBACK;                 -- or COMMIT

Inside the transaction the balances showed 700 and 800; after ROLLBACK they were back to 1,000 and 500, and a repeat with COMMIT made the transfer permanent. The table must use a transactional engine such as InnoDB. Follow-up: what happens if the server crashes mid-transaction? Uncommitted changes are undone on recovery.

18. Isolation levels and concurrency problems

Isolation controls what one transaction can see of another's work. The problems are a dirty read (seeing uncommitted data), a non-repeatable read (the same row reads differently twice) and a phantom read (a repeated query returns new rows).

Level	Dirty read	Non-repeatable read	Phantom
READ UNCOMMITTED	possible	possible	possible
READ COMMITTED	prevented	possible	possible
REPEATABLE READ	prevented	prevented	mostly prevented in InnoDB
SERIALIZABLE	prevented	prevented	prevented

MySQL InnoDB defaults to REPEATABLE READ (check with SELECT @@transaction_isolation;), while PostgreSQL and SQL Server default to READ COMMITTED. Higher isolation means fewer anomalies but more locking. Follow-up: which level would you use for a bank transfer? Use transactions with row locks (SELECT ... FOR UPDATE) at a level your database documents as safe for the case, and test the concurrent scenario.

19. Locking and deadlocks

Writers lock rows (or tables), and readers may wait. A deadlock happens when transaction A holds a lock that B needs while B holds one that A needs; the database detects the cycle and aborts one transaction with an error (MySQL error 1213), and the application should retry. Avoid them by updating rows in a consistent order, keeping transactions short, and indexing the columns used to find rows so that fewer rows are locked. Follow-up: optimistic versus pessimistic locking? Pessimistic locks the row up front (FOR UPDATE); optimistic uses a version column and fails the update if someone else changed the row first.

20. Security: injection, privileges and backups

SQL injection happens when user input is pasted into SQL text. The fix is parameterised queries (prepared statements), never string concatenation. Give application accounts the least privileges they need (not root), never store passwords in plain text (hash them with a strong, salted algorithm), encrypt connections, and test that backups can be restored, because an untested backup is only a hope. Know the difference between a logical backup (mysqldump) and a physical one, and what point-in-time recovery with binary logs adds. Follow-up: how would you recover a table someone dropped by mistake? Restore the last full backup to a separate server, replay the binary log up to just before the mistake, and copy the table back.

How to prepare

1. Build a scratch database and run every example here yourself. Predict the output first.
2. Practise explaining aloud, with the problem first and the syntax second. Seniors are hired for judgement.
3. Read execution plans until type: ALL and Using filesort jump out at you.
4. Know one database deeply (MySQL or PostgreSQL) and the differences in the others you have used: LIMIT versus TOP, FULL JOIN support, default isolation level.
5. Prepare a story about a slow query you fixed or a data bug you solved, with numbers.

Next steps

Work through the deep dives: joins, subqueries, normalisation, indexes and performance. For broader practice use the MySQL interview questions, the MySQL cheat sheet and the free MySQL course.