Databases & SQL interview questions
Reviewed by Mark Dickie · Last updated
Databases and SQL are systems and languages for storing, querying, and managing structured data in tables defined by a schema. For interviews, you need fluency in writing SQL queries by hand (joins, aggregations, subqueries, window functions), a working understanding of how database engines execute those queries (indexes, query plans, partitioning), and knowledge of data modeling trade-offs (normalization vs. denormalization, when to use them). Strong candidates can also discuss transaction isolation levels, ACID guarantees, and common performance bottlenecks like full table scans or N+1 query patterns.
What topics show up in a SQL interview?
Most database interviews cluster around a predictable set of themes. The table below maps each area to what interviewers typically ask you to do:
| Topic area | What you'll be asked |
|---|---|
| Joins (INNER, LEFT, RIGHT, FULL, CROSS) | Write queries combining 2+ tables; predict row counts |
| Aggregation & GROUP BY | Compute sums, averages, counts with HAVING filters |
| Window functions | Rank rows, compute running totals, find top-N per group |
| Indexing & query plans | Explain when an index helps; read an EXPLAIN output |
| Normalization & data modeling | Identify normal-form violations; design a schema from requirements |
| Transactions & isolation | Explain ACID; reason about dirty reads, phantom reads, deadlocks |
How should you prepare?
- Write queries from memory on a whiteboard or plain text editor — no autocomplete, no IDE hints.
- Practice with real schemas (multi-table, with nulls and duplicate rows) so edge cases become second nature.
- Study one window function at a time until you can write
ROW_NUMBER,RANK,LAG, andSUM() OVER()without looking up syntax. - Read
EXPLAIN ANALYZEoutput on your own queries and learn to spot sequential scans that should be index scans. - Review isolation levels and locking until you can explain what goes wrong under READ COMMITTED vs. SERIALIZABLE.
What mistakes cost candidates the most?
Common failure modes include confusing LEFT JOIN row retention with INNER JOIN filtering, misusing HAVING vs. WHERE, and forgetting that COUNT(column) ignores nulls while COUNT(*) does not. Interviewers also watch for candidates who write correct but catastrophically slow queries — a correlated subquery that fires per row when a join would do the job in a single pass. Knowing the difference between a query that returns the right answer and one that scales is often what separates a pass from a strong hire signal.
Key facts
- Tarmac has 162 Databases & SQL interview questions on this topic, 10 of them on this page, at difficulty 2–5 of 5.
- Tarmac last reviewed these Databases & SQL interview questions on 18 August 2026.
At a glance
| Questions | 10 shown · 162 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
Databases & SQL/db-performance/query-planning
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.
Databases & SQL/querying/select-basics
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).
Databases & SQL/querying/aggregation
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.
Databases & SQL/db-performance/query-planning
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: (a) the access method chosen for each table (Sequential Scan vs. Index Scan), (b) estimated vs. actual row counts at every plan node — crucial for spotting stale statistics — and (d) 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 (a vs. b) and expensive nodes (d). Network latency (c) 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 (e).
Databases & SQL/db-performance/query-planning
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.
Databases & SQL/db-performance
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. Option A 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. Option C is wrong: LIMIT actually enables an early-stop optimization, reducing work. Option D 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.
Databases & SQL/db-performance/query-planning
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.
Databases & SQL/db-performance/query-planning
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.
Databases & SQL/indexes/covering-index
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.
Databases & SQL/db-performance/query-planning
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
The other 152 questions
This page shows 10. A free account opens the rest and marks your answers, which a page of answers can't do. Miss one and it returns a few days later, then a week after that, until you stop missing it.
Free · the whole bank · 100 graded answers per 30 days · written feedback and job-ad quizzes on the paid plan