Databases and SQL interview questions with a live practice quiz
Reviewed by Mark Dickie · Last updated
6 questions on this page you can answer and see marked on the spot. Go to the first one
Databases and SQL are the tools developers use to store structured data and ask questions of it in a declarative language. Interviewers mostly check whether you can write joins, aggregates, and subqueries correctly, and whether you know what the engine does with an index, a transaction, or a NULL. Be ready to explain isolation levels and normalization in terms of the specific anomalies each one prevents.
What does a SQL interview test?
| Topic | What interviewers probe |
|---|---|
| JOIN types | How an inner join and a left join differ when a key has no match, and where a filter belongs in an outer join |
| Indexes | B-tree lookups, column order in a composite index, and why wrapping an indexed column in a function can skip the index |
| Transactions | BEGIN, COMMIT, ROLLBACK, and which isolation level allows dirty or non-repeatable reads |
| NULL handling | Three-valued logic, why NULL = NULL is not true, and how COUNT(*) differs from COUNT(column) |
| Normalization | Third normal form, and when denormalizing a reporting table is the right trade |
Which steps should you take before the interview?
- Write an inner join, a left join, and a NOT EXISTS anti-join against the same two tables, then compare the row counts.
- Run EXPLAIN on a query with a range filter, add a composite index, and run it again to see the plan change.
- Open two sessions, update the same row in each, and note what READ COMMITTED lets you see that REPEATABLE READ does not.
- Find the second-highest salary per department with a window function, then solve it again with a correlated subquery.
Key facts
- Tarmac has 197 Databases & SQL interview questions on this topic, 10 of them on this page, at difficulty 2–5 of 5.
- Tarmac tracked 6,677 job postings asking for Databases & SQL in October 2026.
- Roles asking for Databases & SQL advertise a median base salary of £75,000, across 1,666 job postings as of October 2026.
- Tarmac last reviewed these Databases & SQL interview questions on 2 November 2026.
At a glance
| Questions | 10 shown · 197 in the bank |
|---|---|
| Difficulty | 2–5 of 5 |
| Formats | True / false, Code output, Coding exercise, Multiple answer, Fill in the blank, Find the bug, Multiple choice, Ordering, Flashcard, Short answer |
| Interactive | 1 run your code against tests, in the app |
What you'll review
- query planning
- select basics
- aggregation
- db performance
- covering index
Practice questions
Try one before you open the answer. Pick an option and press Check; it's marked on the spot.
In most relational databases (e.g., PostgreSQL, MySQL InnoDB), adding an index on a column used only in the SELECT list (but never in WHERE, JOIN, ORDER BY, or GROUP BY clauses) will improve the query planner's ability to speed up that query.#
Options
Show answer
False. An index on a column that only appears in the SELECT list provides no speed benefit to the query planner. Indexes help by narrowing down which rows to read (via WHERE, JOIN) or eliminating sorts (ORDER BY/GROUP BY). If the column is never used for filtering or ordering, the planner won't use that index and it simply adds overhead to write operations.
Indexes help the query planner speed up row lookups by filtering (WHERE), joining (JOIN), sorting (ORDER BY/GROUP BY), or enabling index-only scans. If a column appears only in the SELECT list and not in any filtering or ordering clause, an index on that column alone provides no benefit to the planner — the database still needs to access the table rows by another means and then simply read the column value. The index would be unused and just add overhead to writes.
Given employees(name, salary) with rows (Ann, 90), (Bo, 70), (Cy, 90), (Di, 50), what does this query return?#
SELECT name, salary
FROM employees
WHERE salary >= 70
ORDER BY salary DESC, name ASC
LIMIT 2;Options
Show answer
(Ann, 90), (Cy, 90)
WHERE salary >= 70 drops Di (50). The remaining three sort by salary DESC first, tying Ann and Cy at 90; the secondary name ASC orders Ann before Cy. LIMIT 2 then keeps the top two: (Ann, 90), (Cy, 90).
The users table has columns id (INTEGER) and email (TEXT). Write a query returning each email address that appears more than once, with columns email and cnt (how many times it appears), ordered by cnt descending, then email ascending.#
Starter code
-- Return: email, cnt (count > 1), ordered by cnt DESC, email ASC
SELECT
Your solution must pass
- finds both duplicated emails with their counts
This one is written and run, not read. Solve it in the app and your code is executed against these tests and the hidden ones.
You run EXPLAIN ANALYZE on a slow query in PostgreSQL. Which of the following are valid and actionable insights you can directly obtain from its output? Select all that apply.#
Options
Pick every one that applies.
Show answer
From EXPLAIN ANALYZE output you can directly learn: the access method chosen for each table (Sequential Scan vs. Index Scan), estimated vs. actual row counts at every plan node — crucial for spotting stale statistics — and the total wall-clock execution time. Network latency between client and server and specific OS page-cache blocks read are not reported in EXPLAIN ANALYZE output.
EXPLAIN ANALYZE executes the query and annotates each plan node with estimated cost/rows, actual rows returned, and actual timing (wall-clock time per node and total). This lets you identify bad row-count estimates (estimated vs. actual rows) and expensive nodes (total wall-clock execution time). Network latency is outside the scope of the query planner output entirely. Buffer/block-level cache statistics are only visible with EXPLAIN (ANALYZE, BUFFERS) and even then show buffer hit counts, not specific block addresses.
After loading millions of new rows into a table, the query planner may produce poor plans because its internal _____ are stale. Running the _____ command updates them. Separately, when a WHERE clause matches a very large percentage of rows, the planner considers the predicate to have low _____ and may prefer a sequential scan over an index scan.#
Show answer
After loading millions of new rows into a table, the query planner may produce poor plans because its internal statistics are stale. Running the **ANALYZE** command updates them. Separately, when a WHERE clause matches a very large percentage of rows, the planner considers the predicate to have low selectivity and may prefer a sequential scan over an index scan.
The FILL_BLANK tests understanding of two fundamental query-planning concepts: (1) after bulk data changes the query planner relies on table statistics, which must be refreshed via ANALYZE (or VACUUM ANALYZE) so that cardinality estimates remain accurate; and (2) an index is not used when the planner estimates the query will touch a large fraction of the table — this threshold is called the 'selectivity' of the predicate. Low selectivity (many matching rows) typically leads the planner to prefer a sequential scan over an index scan because random I/O for most rows is more expensive.
A developer adds the following index and query to power a leaderboard page that should show the top 10 players (highest scores) for a given game. The query returns incorrect results. Identify the line that contains the bug.#
CREATE INDEX idx_leaderboard
ON player_scores (game_id, score);
SELECT player_id, score
FROM player_scores
WHERE game_id = 7
ORDER BY score ASC
LIMIT 10;Options
Show answer
Line 7 — ORDER BY score ASC returns the 10 lowest scores; it should be ORDER BY score DESC to retrieve the top (highest) 10 scores
The intent is to fetch the top 10 players by highest score, making this a leaderboard. ORDER BY score ASC LIMIT 10 is a purely logical bug: it returns the 10 lowest scores, not the highest. Changing it to ORDER BY score DESC LIMIT 10 fixes the correctness issue. Importantly, B-tree indexes on (game_id, score) support both forward (ASC) and backward (DESC) scans with equal efficiency — modern optimizers (PostgreSQL, MySQL, SQL Server) simply traverse the index leaf pages in the opposite direction for DESC, incurring no extra cost. So the bug is entirely about correctness, not performance. The claim that the index should be on (score, game_id) instead of (game_id, score) is wrong: (game_id, score) is the correct column order — placing the equality-filtered column (game_id) first and the sort column (score) second is the standard pattern for this access pattern. The claim that LIMIT is incompatible with indexed lookups and should be removed is wrong: LIMIT actually enables an early-stop optimization, reducing work. The claim that WHERE game_id = 7 should use a range predicate (e.g., game_id >= 7) to allow the index to be used is wrong: WHERE game_id = 7 is an exact equality predicate, which is ideal for the leading index column and allows the index to be used efficiently.
A PostgreSQL table orders has 10 million rows. A B-tree index exists on the status column. The following query ignores the index and performs a sequential scan:#
Options
Show answer
The planner correctly chooses a sequential scan because fetching ~9.4 million rows via random index lookups would be far more expensive than one sequential pass over the heap. PostgreSQL's cost model weights random I/O (random_page_cost) higher than sequential I/O (seq_page_cost), so when a predicate matches a large fraction of rows the index offers no benefit.
PostgreSQL's query planner chooses between a sequential scan and an index scan based on cost estimates derived from statistics (pg_statistic). When a predicate matches a large fraction of rows (e.g., a low-selectivity condition like status = 'active' on a table where 95% of rows are active), the planner correctly decides that reading the entire heap sequentially is cheaper than random index lookups plus heap fetches. The planner uses the page-level cost model: random I/O has a higher cost constant (random_page_cost) than sequential I/O (seq_page_cost). An index is only beneficial when selectivity is high (few rows returned). Options about outdated statistics (ANALYZE not run) and fillfactor are distractors — the described behavior is correct planner behavior, not a bug.
Place the following PostgreSQL query-processing stages in the correct order, from first to last, for a standard SELECT query.#
Put these in order
Show answer
The correct order is: (1) Parser – converts raw SQL into a parse tree; (2) Analyzer & Rewriter – resolves names and expands views; (3) Planner/Optimizer – costs candidate plans and picks the cheapest; (4) Executor – walks the plan tree to produce result rows. Each stage transforms the query representation before handing it to the next.
The correct sequence for how PostgreSQL processes a query through the planner/optimizer is: (1) Parser – turns SQL text into a parse tree; (2) Analyzer/Rewriter – resolves names, applies rules/views to produce a query tree; (3) Planner/Optimizer – generates and costs candidate plans using statistics, then selects the cheapest; (4) Executor – executes the chosen plan. Understanding this pipeline is essential for diagnosing why plans are chosen and how statistics feed into cost estimation.
What is a covering index, and why is it faster than a normal index lookup?#
Show answer
A covering index contains every column a query needs (in its key or, in PostgreSQL, an INCLUDE clause), so the query is answered entirely from the index. This gives an index-only scan that skips the extra heap/table fetch a normal index lookup needs to retrieve the remaining columns.
Normally an index points back to the table row to fetch columns it doesn't store, costing an extra read per match. When the index already holds all referenced columns, the engine does an index-only scan and never touches the table — a big win for hot, selective queries.
A table events has ~500 million rows. A DBA creates the following composite B-tree index:#
Show answer
A B-tree index is traversed by walking down the tree using the leading column first. With (status, created_at), the planner uses the high-selectivity equality predicate status = 'PENDING' to jump directly to the subtree for that status value, then applies the range scan on created_at within that narrow band. Because status may have only a handful of distinct values (e.g., PENDING, PROCESSED, FAILED), the equality predicate is very selective — it eliminates most rows immediately. The index then delivers the matching rows in created_at order, so the ORDER BY created_at DESC LIMIT 100 can be satisfied with a single backward index scan without a sort step. With (created_at, status), the leading column is a timestamp with extremely high cardinality; the planner scans the 7-day range (potentially tens of millions of rows) and applies status = 'PENDING' as a residual filter on each fetched row. EXPLAIN ANALYZE would show a large 'rows removed by filter' count, high actual rows, and likely a sort node for the ORDER BY. With (status, created_at), EXPLAIN ANALYZE would show a very small 'rows removed by index recheck', low actual rows fetched, and no extra sort node because the index already provides the required order.
This question tests deep knowledge of multi-column index column ordering and query planner selectivity. The composite index on (status, created_at) is defined with status as the leading column. Because the query uses an equality predicate on status AND a range predicate on created_at, the planner can use both columns of the index efficiently (equality on the prefix, then range on the second column). Reversing the order to (created_at, status) would allow only the range prefix to be used — the status equality filter would have to be applied as a residual filter on the rows fetched by the range scan, which is less selective. The index on (status, created_at) therefore narrows the result set far more tightly using the B-tree structure, leading to fewer index pages read. EXPLAIN ANALYZE would show a much lower 'rows removed by index recheck' count for the correctly ordered index.
Sources
The official documentation these questions are checked against:
Related interview questions
Job market
See databases-sql salaries and hiring demand from live job postings.
The other 187 questions
This page shows 10 and marks what you pick. That's as far as a page can go. A free account opens the other 187 and keeps every answer. What you miss comes back until it's right: after a day, then at longer gaps.
Free · the whole bank · 100 answers per 30 days, each marked with written feedback