Reviewed: 9 October 2026
A useful SQL answer explains which rows should appear, why they appear and what happens when the data changes. Memorising a query without its assumptions can hide errors involving duplicates, missing values or ties.
The original practice questions below share a small fictional exhibition dataset. Every displayed query was executed on SQLite 3.53.4, including the changed-input exercises. They are practice questions written for this guide, not questions reported from an employer's interview bank. Use a disposable local database; this exercise needs no production data.
The examples use SQLite syntax with window-function support. If an interview specifies PostgreSQL, MySQL or another engine, confirm that engine's types, functions and version rather than assuming every feature is interchangeable.
Set up the complete dataset
There are four exhibition rooms and five inspection records. A score is an invented exercise value, not a safety assessment. North has multiple records, East has a record with a missing score, South has one scored record, and West has no records.
Run this setup once in an empty practice database:
CREATE TABLE rooms (
id INTEGER PRIMARY KEY,
label TEXT NOT NULL
);
CREATE TABLE checks (
id INTEGER PRIMARY KEY,
room_id INTEGER NOT NULL,
stamp TEXT NOT NULL,
score INTEGER,
FOREIGN KEY(room_id)
REFERENCES rooms(id)
);
INSERT INTO rooms VALUES
(1,'North'), (2,'East'),
(3,'South'), (4,'West');
INSERT INTO checks VALUES
(10,1,'2026-09-01',8),
(11,1,'2026-09-02',NULL),
(12,1,'2026-09-02',9),
(20,2,'2026-09-01',NULL),
(30,3,'2026-09-01',8);
The IDs uniquely identify rows. room_id connects a check to a room. Dates are fixed-width ISO date strings in this finite fixture, so their text order matches their date order. This is not a prescription for handling arbitrary timestamps or time zones. SQLite foreign-key enforcement must be enabled for a connection if you want it enforced; the execution check used PRAGMA foreign_keys=ON before setup.
1. How do you keep rooms that have no checks?
Use rooms as the left side of a left join:
SELECT r.id, c.id, c.score
FROM rooms AS r
LEFT JOIN checks AS c
ON c.room_id = r.id
ORDER BY r.id, c.id;
Expected ordered rows, shown as room ID, check ID and score:
1 | 10 | 8
1 | 11 | NULL
1 | 12 | 9
2 | 20 | NULL
3 | 30 | 8
4 | NULL | NULL
West survives with null right-side values. North appears three times because three checks match it. A join does not promise one output row per room. Explain this multiplication before using a joined result for counts or totals.
PostgreSQL's join tutorial explains matching and unmatched rows; the exercise above was executed in SQLite, not PostgreSQL. The setup and room data are original rather than copied from that tutorial.
2. Why do COUNT(*) and COUNT(score) differ?
Count the joined rows, actual check IDs and non-null scores separately:
SELECT r.id,
COUNT(*) AS joined_rows,
COUNT(c.id) AS checks,
COUNT(c.score) AS scores
FROM rooms AS r
LEFT JOIN checks AS c
ON c.room_id = r.id
GROUP BY r.id
ORDER BY r.id;
Expected rows:
room | joined_rows | checks | scores
1 | 3 | 3 | 2
2 | 1 | 1 | 0
3 | 1 | 1 | 1
4 | 1 | 0 | 0
West has one null-extended joined row, so COUNT(*) is one. It has no actual check ID, so COUNT(c.id) is zero. East has an actual check but no recorded score. Those are different missing-data situations.
SQLite's aggregate documentation defines row counts and non-null expression counts. Choose the expression according to the question: “how many check records?” and “how many available scores?” ask for different quantities.
3. What changes when a predicate moves from ON to WHERE?
This query asks for joined rows whose score is at least nine:
SELECT r.id, c.id
FROM rooms AS r
LEFT JOIN checks AS c
ON c.room_id = r.id
WHERE c.score >= 9
ORDER BY r.id, c.id;
It returns only 1 | 12. The null values on unmatched or unscored rows do not satisfy that predicate.
Now put the restriction in the matching condition:
SELECT r.id, c.id
FROM rooms AS r
LEFT JOIN checks AS c
ON c.room_id = r.id
AND c.score >= 9
ORDER BY r.id, c.id;
Expected rows:
1 | 12
2 | NULL
3 | NULL
4 | NULL
This version preserves all four rooms, attaching only qualifying checks. East, South and West have no qualifying match, even though East and South do have other checks. The queries answer different questions. SQLite's SELECT documentation describes outer-join null extension and subsequent filtering.
4. How do you select the latest check deterministically?
Define “latest” first. Here it means greatest stamp, with greatest unique check ID breaking equal-date ties. That tie-breaker is an explicit exercise rule, not an assumption that larger IDs always mean later dates.
WITH ranked AS (
SELECT id, room_id, score,
ROW_NUMBER() OVER (
PARTITION BY room_id
ORDER BY stamp DESC,
id DESC
) AS rn
FROM checks
)
SELECT r.id, x.id, x.score
FROM rooms AS r
LEFT JOIN ranked AS x
ON x.room_id = r.id
AND x.rn = 1
ORDER BY r.id;
Expected rows:
1 | 12 | 9
2 | 20 | NULL
3 | 30 | 8
4 | NULL | NULL
Checks 11 and 12 have equal dates; the rule selects 12. The final ORDER BY controls output order separately from the window ordering. SQLite's window-function documentation explains that distinction.
Selecting a bare score beside MAX(stamp) in a grouped query is not a portable way to specify this complete tie rule. Select the row deliberately, then take its fields.
5. Where do GROUP BY and HAVING fit?
Find rooms with at least two available scores and their score total:
SELECT room_id,
COUNT(score) AS scores,
SUM(score) AS total
FROM checks
GROUP BY room_id
HAVING COUNT(score) >= 2
ORDER BY room_id;
The result is 1 | 2 | 17. The null score is not counted or added. HAVING filters the computed groups; WHERE filters input rows before grouping. Decide which records belong in the calculation before choosing where to put a condition.
If you joined another one-to-many table before aggregating, a check might be repeated and the total inflated. Inspect the intermediate row grain: does one row still mean one check? Adding DISTINCT without understanding the duplication can discard legitimately separate equal-valued observations.
6. Is an empty SUM the same as a measured zero?
For East, run:
SELECT SUM(score),
COUNT(score), COUNT(*)
FROM checks
WHERE room_id = 2;
The result is NULL | 0 | 1: one record, no available score. For West, change the final line to WHERE room_id = 4;. The result becomes NULL | 0 | 0: no records.
Both totals are null, but their record counts differ. Do not silently report either as a measured zero. A replacement such as COALESCE should follow an explicitly justified reporting rule. SQLite's total() has different empty-input behaviour from SUM; changing functions changes the semantics rather than discovering a missing observation.
7. How do you find the second-highest distinct score?
For this fixture:
SELECT MAX(score)
FROM checks
WHERE score < (
SELECT MAX(score)
FROM checks
);
The answer is eight. It asks for the greatest value below the maximum, so repeated eights do not make them different ranks. “Second row after sorting” is a different question. If fewer than two distinct non-null values exist, this expression returns null.
In a salary interview question, clarify whether ties share a rank, whether the answer should be a value or complete employee rows, and how missing salaries should be treated. Do not introduce real salary data to practise the logic.
Test your answer when the input changes
Starting from the original setup, insert:
INSERT INTO checks VALUES
(99,1,'2026-08-31',7);
Predict the latest-check query before running it. North still selects check 12 because 31 August is earlier than 2 September. A shortcut using MAX(id) would select 99 and violate the stated rule. The execution check confirmed that all four latest-query rows remain unchanged.
For a separate variation, reset the original setup in a new empty database and run:
UPDATE checks SET score=9
WHERE score IS NOT NULL;
Only one distinct non-null score remains. The second-highest-distinct query now returns null. This variation tests the missing-second-value case; it should not produce nine merely because several rows contain nine.
Explain correctness before claiming performance
A useful interview explanation states the output grain, join condition, null treatment and tie rule. Then discuss performance using the specified engine and representative data. An index is not a guarantee that a query will become faster; inspect the execution plan and measured behaviour for the actual workload. This small fixture verifies correctness, not production speed or scaling.
Should I memorise all these queries?
Practise deriving them from the requested output. Rebuild the fixture, predict the rows and explain a changed input. That demonstrates more understanding than remembering a code block without its assumptions.
Do these exercises cover every SQL interview?
No. Transactions, constraints, data modelling and engine-specific features may also matter. Use the target role's stated requirements to choose further practice. These checked examples cover joins, aggregates, nulls, groups and deterministic row selection; they do not certify job readiness.
How can I describe the practice on a resume?
Call it a personal practice project and describe the queries and edge cases you actually tested. Do not claim client work, commercial outcomes or database-administration experience. The resume writing guide helps present owned project evidence. For explaining your approach aloud, use the STAR interview guide when the question calls for a work example rather than a SQL result.