Skip to content
BytePatterns

40 SQL Interview Questions, Answered and Run in SQLite

19 min readBytePatterns

40 SQL interview questions on NULLs, joins, GROUP BY, window functions, indexes and transactions, with short model answers and the tricky queries run for real.

A SQL round rarely asks you to recite syntax. It hands you a table and a question, then watches whether you know what each clause can see, what a missing match turns into, and what a NULL does to your filter. These forty questions start with the order a query is evaluated in and end with the patterns that turn up again and again: top N per group, running totals, gaps and islands, retention.

Every answer is short on purpose: something you can say out loud in under a minute. Each one links the lesson that animates it and, where one exists, a practice problem to write the query yourself and the matching row of the SQL cheat sheet. The trickiest behaviours are run for real at the end with Python's sqlite3 module on SQLite 3.37. Where engines disagree, the answer says so; version-specific details are stated as of September 2026.

How to use this list

Answer each question aloud before reading ours, then write the query. If your answer names a clause but not what goes wrong without it, it is half an answer: the follow-up is always "and what does that return when there is a tie, a NULL, or no row at all?".

Basics

1. What does SELECT describe, and why avoid SELECT * in application code?

SELECT describes the shape of the result, the columns you want back, not the steps to fetch them; the engine decides how. SELECT * is handy at a prompt and wasteful in code: every extra column crosses the wire, and a column added tomorrow silently changes the result shape your code reads. Name the columns.

Lesson: SELECT basics

2. In what order is a query evaluated?

FROM (with its joins), WHERE, GROUP BY, HAVING, SELECT, then ORDER BY, then LIMIT. DISTINCT applies right after the SELECT list is computed. This is the logical order, which defines what the result means; the planner may execute it differently, for example using an index to avoid a sort, as long as the result is the same.

Lesson: Query execution order · Cheat sheet: execution order

3. Why can ORDER BY use a SELECT alias when WHERE cannot?

WHERE runs before SELECT, so the alias does not exist yet; ORDER BY runs after it. Repeat the expression in WHERE, or compute it in a subquery or CTE and filter the outer query. SQLite happens to resolve an alias in WHERE, PostgreSQL rejects it, so do not build a habit on the lenient engine.

Lesson: Query execution order · Cheat sheet: WHERE · Article: SQL order of execution

4. How does NULL behave in a WHERE clause?

NULL means unknown, so col = NULL is never true, not even for a row whose col is NULL: the comparison is unknown, and WHERE keeps only rows whose test is true. Ask col IS NULL or col IS NOT NULL. The same logic is behind the NOT IN trap in question 12, and it is run in the code below.

Lesson: WHERE and filtering · Cheat sheet: three-valued logic

5. What does LIMIT return without ORDER BY, and how do you paginate?

Without ORDER BY there is no promised order, so LIMIT 3 keeps three arbitrary rows. With it, ORDER BY plus LIMIT answers every "top N" in one round trip, and the engine can often stop early. Page two of twenty rows is LIMIT 20 OFFSET 20. The follow-up: the engine still walks past every skipped row, so deep pages slow down; keyset pagination, WHERE id > :last_seen ORDER BY id LIMIT 20, jumps straight in through an index. Either way, end the ORDER BY with a unique column so ties cannot move rows between pages.

Lesson: ORDER BY and LIMIT · Cheat sheet: LIMIT / OFFSET

6. What is a subquery, and what must a scalar subquery return?

A complete query used as a value, in brackets, answered before the outer query uses it. A scalar subquery stands where one value is expected, so it must return one row and one column. The classic use is comparing against an aggregate: WHERE mwh < AVG(mwh) is illegal because the average does not exist while rows are filtered, but WHERE mwh < (SELECT AVG(mwh) FROM turbine) works.

Lesson: Subqueries · Cheat sheet: scalar subquery · Article: correlated vs uncorrelated subqueries

7. Correlated versus uncorrelated subquery?

An uncorrelated subquery runs once. A correlated one refers to a column of the outer row, so it is re-evaluated for every outer row: easy to write, easy to make expensive. "Highest paid in each department" is the textbook case, WHERE e.salary = (SELECT MAX(salary) FROM employee WHERE dept = e.dept), which keeps ties. Planners often turn it into a join, and an index on (dept, salary) makes each evaluation a lookup.

Lesson: Subqueries · Practice: Highest paid in every department · Cheat sheet: correlated subquery

8. When do you use a CTE instead of a subquery?

A common table expression, WITH name AS (...), names an intermediate result so the query reads top to bottom instead of inside out, and the same statement can refer to it more than once. It is not a permanent table and does not come with an index. Whether the engine materialises it or inlines it varies: since version 12, PostgreSQL inlines a non-recursive, side-effect-free CTE that is referenced once, unless you write MATERIALIZED.

Lesson: CTEs and recursion · Practice: Department with the highest average pay · Cheat sheet: CTE

Joins

9. INNER JOIN versus LEFT JOIN?

An inner join keeps only the pairs that matched. A left join keeps every left row, and when nothing on the right matches, it fills the right columns with NULL. Choosing between them is choosing what a missing match should mean. In both, a left row that matches three right rows comes back three times.

Lesson: INNER and OUTER JOINs · Cheat sheet: inner join, left join · Article: SQL joins explained visually

10. What about RIGHT and FULL OUTER JOIN?

A right join is a left join with the tables swapped, and most teams write the left form for readability. A full outer join keeps unmatched rows from both sides, each padded with NULL, which is what you want when reconciling two lists. Support varies: SQLite added both in 3.39.0, and MySQL, as of September 2026, has no FULL OUTER JOIN; there you emulate it with a left join, UNION ALL, and the right-side rows that found no match.

Lesson: INNER and OUTER JOINs · Cheat sheet: right join, full outer join

11. What is a CROSS JOIN, and how do you get one by accident?

Every row of one table paired with every row of the other: m times n rows. On purpose it generates combinations, such as every product for every month. By accident it comes from a missing or wrong join condition, and the symptom is a row count equal to the product of the table sizes.

Lesson: INNER and OUTER JOINs · Cheat sheet: cross join

12. How do you find rows with no match?

An anti-join. Either LEFT JOIN and keep the rows where a right-side column that is never NULL in a real match, such as its primary key, IS NULL; or NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id). Avoid NOT IN over a column that can hold NULL: one NULL in the list makes x NOT IN (...) unknown for every row, and the query returns nothing. The code below shows it.

Lesson: INNER and OUTER JOINs · Practice: Customers who never ordered · Cheat sheet: anti-join, the NOT IN trap

13. Why did my LEFT JOIN behave like an inner join?

A WHERE condition on a right-table column, such as WHERE o.status = 'paid', runs after the join. For unmatched left rows that column is NULL, the test is not true, and the rows you joined to keep disappear. Move the condition into the ON clause: it then decides what counts as a match while every left row survives.

Lesson: INNER and OUTER JOINs · Cheat sheet: the LEFT JOIN filter trap

14. What is a self-join?

A table joined to itself under two aliases, for relationships inside one table. JOIN employee m ON m.id = e.manager_id lines each person up with their manager, and an inner join drops the person at the top, whose manager_id is NULL, which is right for "who earns more than their manager". Joining on b.id = a.id + 1 compares neighbouring rows, as long as the ids have no gaps.

Lesson: INNER and OUTER JOINs · Practice: Paid more than their manager, Same reading three times in a row · Cheat sheet: earns more than manager

15. Why is my SUM too big after a join?

The join multiplied the rows. An order joined to its three line items appears three times, so summing the order's total triples it. Aggregate the many side first, in a subquery or CTE that returns one row per key, then join that.

Lesson: INNER and OUTER JOINs · Cheat sheet: inner join

Aggregation

16. What does GROUP BY do?

It folds the rows into one row per distinct value of the grouping columns, and aggregates such as SUM, COUNT, AVG, MIN and MAX squeeze each group into one value. Every column in the SELECT list should either be grouped or aggregated. PostgreSQL enforces that; SQLite accepts a bare column and fills it from one row of the group, which is rarely what you meant.

Lesson: Aggregations and GROUP BY · Cheat sheet: aggregation · Article: GROUP BY and HAVING

17. WHERE versus HAVING?

WHERE filters rows before grouping; HAVING filters groups after it, so it is the first place an aggregate such as SUM(kilos) has a value. A condition that needs no aggregate belongs in WHERE even when HAVING would give the same answer, because every row dropped early is a row that is never grouped.

Lesson: Aggregations and GROUP BY · Practice: Emails used more than once · Cheat sheet: HAVING

18. COUNT(*), COUNT(col) or COUNT(DISTINCT col)?

COUNT(*) counts rows, COUNT(col) skips the rows where col is NULL, and COUNT(DISTINCT col) counts distinct non-null values. It matters after a left join: an author with no books still has one NULL-filled row, so COUNT(*) says 1 and COUNT(b.id) says 0. A related trap: SUM over no rows, or only NULLs, is NULL, not 0; wrap it in COALESCE when you need a number.

Lesson: Aggregations and GROUP BY · Practice: Author book counts in one query · Cheat sheet: aggregation

19. How do you find duplicate values?

GROUP BY email HAVING COUNT(*) > 1. The usual follow-up is deleting the extras while keeping the oldest row: DELETE FROM account WHERE id NOT IN (SELECT MIN(id) FROM account GROUP BY email). NOT IN is safe here only because MIN(id) over a primary key is never NULL.

Lesson: Aggregations and GROUP BY · Practice: Emails used more than once · Cheat sheet: duplicate rows

20. How do you find the second highest salary?

SELECT MAX(salary) FROM employee WHERE salary < (SELECT MAX(salary) FROM employee). A repeated top salary is removed in one go, and when there is no second salary the answer is NULL, because an aggregate without GROUP BY always returns one row. LIMIT 1 OFFSET 1 over the distinct salaries returns no row at all in that case unless you wrap it. For the N-th highest, filter on DENSE_RANK().

Lesson: Subqueries · Practice: Second highest salary · Cheat sheet: second highest, N-th highest

21. How do you turn rows into columns?

Conditional aggregation: group by the row key and give each output column its own SUM(CASE WHEN quarter = 1 THEN amount ELSE 0 END). The CASE is a per-column filter, which a WHERE could not be. The ELSE 0 matters because a sum over nothing but NULLs is NULL. Some engines have a PIVOT keyword or a FILTER clause; CASE inside an aggregate works everywhere.

Lesson: Aggregations and GROUP BY · Practice: Quarterly sales as columns

22. Which customers bought every product?

Relational division, and the counting form fits an interview: group purchases by customer and keep the groups where COUNT(DISTINCT product_key) equals (SELECT COUNT(*) FROM product). The DISTINCT stops a customer who bought one item twice from passing with a product missing. The double NOT EXISTS form, "there is no product this customer has not bought", returns the same rows.

Lesson: Aggregations and GROUP BY · Practice: Customers who bought every product

Window functions

23. What is a window function, and how is it different from GROUP BY?

GROUP BY folds rows away. A window function computes over a set of rows related to the current one and attaches the answer to that row, so every input row stays. OVER (ORDER BY ...) gives running totals and rankings, and PARTITION BY restarts the calculation for each group. Windows are computed with the SELECT list, after WHERE, GROUP BY and HAVING.

Lesson: Window functions · Cheat sheet: window functions · Article: SQL window functions explained

24. ROW_NUMBER, RANK or DENSE_RANK?

They differ only on ties. ROW_NUMBER never repeats, and tied rows are numbered arbitrarily unless the ORDER BY breaks the tie. RANK gives ties the same number and then skips: 1, 2, 2, 4. DENSE_RANK gives ties the same number without a gap: 1, 2, 2, 3, which makes it the one for "the N-th highest value". All three run on the same rows in the code below.

Lesson: Window functions · Cheat sheet: ROW_NUMBER, RANK, DENSE_RANK

25. How do you get the top N rows per group?

Rank inside a subquery or CTE, DENSE_RANK() OVER (PARTITION BY dept ORDER BY salary DESC) AS rnk, and filter rnk in the outer query. It cannot be filtered in the same query's WHERE, because the window is computed with the SELECT list, after WHERE has run. Pick the function by the tie rule you want: ROW_NUMBER for exactly N people, DENSE_RANK for the top N distinct salaries with everyone tied on them.

Lesson: Query execution order · Practice: Top two earners per department · Cheat sheet: top N per group

26. How do you compute a running total, and what is the frame trap?

SUM(amount) OVER (PARTITION BY account ORDER BY day). With an ORDER BY and no frame, the default frame is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, and RANGE treats rows tied on the sort key as one step: two withdrawals on the same day both show the end-of-day balance. Add a unique column to the ORDER BY and say ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. With only PARTITION BY, SUM(...) OVER puts the group's total on every row.

Lesson: Window functions · Practice: Running balance per account · Cheat sheet: running total, SUM OVER

27. What do LAG and LEAD do, and who logged in three days in a row?

LAG(x, k) returns x from k rows back in the window's order, NULL before the first row; LEAD looks ahead. For three consecutive days, first reduce the log to one row per person and day, then keep the days that are exactly two calendar days after LAG(day, 2). Skip the deduplication and a second sign-in on the same day hides a real streak.

Lesson: Window functions · Practice: Logged in three days in a row · Cheat sheet: LAG, consecutive days

28. What is the gaps-and-islands pattern?

Collapsing consecutive runs into one row each. Number each status's rows in day order with ROW_NUMBER() OVER (PARTITION BY status ORDER BY day) and subtract that from the day number: inside a run both climb by one, so the difference is constant, and it changes the moment a day is skipped. Group by status and that difference, and MIN, MAX and COUNT(*) describe each run.

Lesson: Window functions · Practice: Collapse a status log into runs · Cheat sheet: gaps and islands

Indexes

29. What is an index, and what does it cost?

A separate structure, usually a B-tree, that keeps one or more columns in sorted order with each entry pointing back at its row. A lookup becomes a search to the first match plus the matches, O(log n + k), instead of reading every row. The price is storage and extra work on every insert, update and delete, so you index the columns that WHERE, joins and ORDER BY actually use, not every column.

Lesson: Indexes · Cheat sheet: what an index saves · Article: SQL indexes explained

30. How do you tell whether a query uses an index?

Ask the engine for its plan: EXPLAIN QUERY PLAN in SQLite, EXPLAIN in PostgreSQL and MySQL. SCAN means every row of that table was read; SEARCH means it jumped in through an index. In a two-table join the table listed first is the outer one, and the inner one is probed once per outer row. USE TEMP B-TREE FOR ORDER BY means no index delivered the order, so the rows were sorted on the fly.

Lesson: Reading a query plan · Cheat sheet: no index: a full scan

31. Does column order matter in a composite index?

Yes. An index on (city, day) is sorted by city, then by day within each city, so it serves a filter on city, or on city and day, the leftmost prefix. A filter on day alone has nowhere to jump in and scans. Put the equality-filtered column first and the range or sort column after it.

Lesson: Composite indexes · Cheat sheet: leftmost prefix, second column only · Article: composite and covering indexes

32. What is a covering index?

An index that holds every column the query needs, so the engine answers from the index and never reads the table; SQLite's plan then says USING COVERING INDEX. The cost is a wider index: more storage and more work on every write. PostgreSQL (11 and later) and SQL Server can carry extra non-key columns with INCLUDE.

Lesson: Composite indexes · Cheat sheet: covering index

33. When is an index ignored?

When the query cannot use its order. A function on the column, lower(shot_by) = 'weiss', because the index is sorted by shot_by, not by lower(shot_by): rewrite the filter or index the expression. A leading wildcard, LIKE '%eiss', which has no sorted starting point. A filter on a non-leading column of a composite index. And a filter that matches so much of the table that the planner judges a scan cheaper.

Lesson: Indexes · Cheat sheet: a function on the column, leading wildcard

Transactions

34. What does ACID stand for?

Atomic: all of the writes land or none do. Consistent: constraints such as CHECK and foreign keys hold before and after, so the database never moves from a valid state to an invalid one. Isolated: others do not see your half-finished work. Durable: a commit survives a crash.

Lesson: Transactions and ACID · Cheat sheet: transactions · Article: ACID transactions explained

35. What happens when one statement in a transaction fails?

The unit of work is the whole transaction, not the statement. When a step fails, you roll back, and every earlier step is undone with it. In the code below a transfer credits one account, then fails the CHECK on the debit; the block rolls back and the credit disappears too. Commit only when every step succeeded.

Lesson: Transactions and ACID

36. What are the isolation levels, and what does each prevent?

A ladder of anomalies. Read uncommitted allows dirty reads. Read committed prevents them, and each statement sees what was committed before it began, so two reads in one transaction can disagree: a non-repeatable read. Repeatable read prevents that too, while the standard still allows phantoms. Serializable makes the result equal some one-at-a-time order. The standard sets minimums, so PostgreSQL's repeatable read also excludes phantoms, and defaults differ: as of September 2026, read committed in PostgreSQL and repeatable read in MySQL's InnoDB. Stronger levels cost concurrency, as waiting or as aborted transactions to retry.

Lesson: Isolation levels · Cheat sheet: read committed, repeatable read, serializable · Article: SQL isolation levels explained

37. What is a lost update, and how do you prevent it?

Two transactions read the same value and each writes back a result computed from its own read, so one overwrites the other: two clerks read 3 free beds, both write 2, and one booking vanished from the count. Do the arithmetic in one statement, UPDATE bed SET free = free - 1 WHERE free > 0, lock the row when you read it (SELECT ... FOR UPDATE in PostgreSQL and MySQL), or add a version column that the UPDATE checks. If the read and the write are separate transactions, no isolation level saves you.

Lesson: Isolation levels · Cheat sheet: transactions

Query patterns

38. What is the N+1 query problem?

One query fetches a list, then the code queries once per item: N+1 round trips, each paying latency, parsing and planning however small its result. Fix it by fetching the related rows together, with one join or with a single WHERE parent_id IN (...), which makes it one or two queries. Object-relational mappers cause it through lazy loading; count the statements per request to catch it.

Lesson: The N+1 query problem · Practice: Author book counts in one query · Cheat sheet: N+1 · Article: the N+1 query problem explained

39. How does a recursive CTE work, and what can go wrong?

WITH RECURSIVE takes an anchor query, then a step that joins the previous pass back onto the table, combined with UNION ALL and repeated until a pass returns no rows. It walks hierarchies: reporting chains, folder trees, which channel feeds which. Over data that contains a cycle it runs forever unless you track what you have visited or cap the depth.

Lesson: CTEs and recursion · Cheat sheet: recursive CTE, reporting chain · Article: SQL recursive CTE explained

40. How do you measure month-over-month retention?

Build the grain first: a CTE with one row per user per active month, DISTINCT so several sessions count once. Then left-join that CTE to itself on the same user and the previous month. COUNT(*) counts active users and COUNT(prev.user_id) counts only the retained ones, because it skips the NULLs the left join leaves. Compute the previous month with date arithmetic so December to January works.

Lesson: CTEs and recursion · Practice: Users retained month over month

Watch it run

Questions 2, 3, 17 and 25 all come back to one fact, and the animation below is it. The clause strip stays in written order while the highlight moves in evaluated order: FROM reads the apples, WHERE rotten = 0 drops the bad fruit before anything is grouped, GROUP BY variety tips the survivors into bins, and HAVING SUM(kilos) >= 50 sets Kingston's 30 kg aside. Only then is SUM(kilos) AS total computed, and only then does the alias exist, before ORDER BY total DESC lines up the rest. The last two frames explain the errors: WHERE ran four clauses before total existed, and the sum does not exist before HAVING.

Query Execution Order

Step 1 of 9

You write SELECT first. The engine runs it fifth, and that explains most beginner errors.

The same interactive animation as the lesson — step through it with the controls.

The code

Six of the answers above, run for real with Python's sqlite3 module against SQLite 3.37. The comments are the actual output:

import sqlite3

db = sqlite3.connect(":memory:")
db.executescript("""
CREATE TABLE emp (id INTEGER PRIMARY KEY, name TEXT, dept TEXT,
                  salary INT, manager_id INT);
INSERT INTO emp VALUES (1, 'Ana', 'eng', 130, NULL), (2, 'Ben', 'eng', 120, 1),
  (3, 'Cleo', 'eng', 120, 1), (4, 'Dev', 'ops', 95, 1), (5, 'Eve', 'ops', 80, 4);
CREATE TABLE orders (id INTEGER PRIMARY KEY, emp_id INT, day TEXT, amount INT);
INSERT INTO orders VALUES (10, 2, '09-01', 40), (11, 2, '09-02', 25),
  (12, 3, '09-02', 60), (13, NULL, '09-02', 15);
""")

# Q4: a comparison with NULL is never true
print(db.execute("SELECT COUNT(*) FROM emp WHERE manager_id = NULL").fetchone())   # (0,)
print(db.execute("SELECT COUNT(*) FROM emp WHERE manager_id IS NULL").fetchone())  # (1,)

# Q12: the NOT IN trap -- one NULL in the list and nothing comes back
print(db.execute("""SELECT name FROM emp
                    WHERE id NOT IN (SELECT emp_id FROM orders)""").fetchall())   # []
print(db.execute("""SELECT name FROM emp e WHERE NOT EXISTS
                    (SELECT 1 FROM orders o WHERE o.emp_id = e.id)
                    ORDER BY name""").fetchall())
# [('Ana',), ('Dev',), ('Eve',)]

# Q24: three ways to number a tie
for row in db.execute("""
    SELECT name, salary,
           ROW_NUMBER() OVER (ORDER BY salary DESC, name) AS rn,
           RANK()       OVER (ORDER BY salary DESC) AS rnk,
           DENSE_RANK() OVER (ORDER BY salary DESC) AS drnk
    FROM emp ORDER BY rn"""):
    print(row)
# ('Ana', 130, 1, 1, 1)
# ('Ben', 120, 2, 2, 2)
# ('Cleo', 120, 3, 2, 2)
# ('Dev', 95, 4, 4, 3)
# ('Eve', 80, 5, 5, 4)

# Q26: the default frame is RANGE, so rows tied on the sort key share a total
print(db.execute("""
    SELECT id,
           SUM(amount) OVER (ORDER BY day) AS range_total,
           SUM(amount) OVER (ORDER BY day, id
                             ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS rows_total
    FROM orders ORDER BY id""").fetchall())
# [(10, 40, 40), (11, 140, 65), (12, 140, 125), (13, 140, 140)]

# Q30: the plan says SCAN, then SEARCH once the index exists
q = "SELECT name FROM emp WHERE dept = 'ops'"
print(db.execute("EXPLAIN QUERY PLAN " + q).fetchone()[3])   # SCAN emp
db.execute("CREATE INDEX emp_dept ON emp(dept)")
print(db.execute("EXPLAIN QUERY PLAN " + q).fetchone()[3])
# SEARCH emp USING INDEX emp_dept (dept=?)

# Q35: one failing statement, and the whole transaction rolls back
db.execute("CREATE TABLE acct (name TEXT, bal INT CHECK (bal >= 0))")
db.execute("INSERT INTO acct VALUES ('a', 50), ('b', 0)")
db.commit()
try:
    with db:  # commits at the end, rolls back if the block raises
        db.execute("UPDATE acct SET bal = bal + 70 WHERE name = 'b'")
        db.execute("UPDATE acct SET bal = bal - 70 WHERE name = 'a'")  # 50 - 70 < 0
except sqlite3.IntegrityError as e:
    print(type(e).__name__)                                   # IntegrityError
print(db.execute("SELECT * FROM acct").fetchall())            # [('a', 50), ('b', 0)]

Two outputs are worth saying out loud. The NOT IN query returns nothing even though three employees have no orders, because the guest order's NULL makes every NOT IN test unknown. And the running totals disagree on orders 11 and 12: under the default RANGE frame all three orders on 09-02 are one peer group and share the day's total of 140, while ROWS moves once per row.

How to say it in an interview

Name the clause, then the case that breaks it. "I filter rows in WHERE and groups in HAVING, because WHERE runs before the aggregates exist. For top N per group I rank with DENSE_RANK in a subquery and filter outside, since the window is computed after WHERE. For rows with no match I use NOT EXISTS, not NOT IN, because one NULL empties the result. Then I check the plan for SCAN versus SEARCH, and I do counter updates in a single UPDATE so I cannot lose one." Every clause names a rule and the input that tests it: a tie, a NULL, an empty group.

Sources