Query Belt Coming to Google Play

SQL interview questions, with answers you can run

Twelve questions that come up again and again, answered the way you would say them out loud, with real queries and real results. Then deeper pages by topic.

By Andrii Boiko · Updated 2026-11-18 · Every result on this page was produced by running the query on the sample database below in SQLite 3.37.2.

Go deeper by topic

JOIN questions

LEFT JOIN, anti-joins, self joins, row fan-out and the WHERE-versus-ON trap.

Window functions

RANK, DENSE_RANK, top N per group, running totals, LAG, moving averages.

NULL and GROUP BY

The NOT IN trap, COUNT variants, conditional aggregation, integer division.

The core questions

The queries run on the same small sample database as the cheat sheet: departments, employees and sales, with a few traps built in.

What is the difference between WHERE and HAVING?

WHERE filters rows before they are grouped. HAVING filters groups after aggregation, so only HAVING can use COUNT, SUM and friends. If a condition does not need an aggregate, put it in WHERE: it removes rows early and usually runs faster.

SELECT department_id, COUNT(*) AS n
FROM employees
WHERE salary > 4500          -- row filter, before grouping
GROUP BY department_id
HAVING COUNT(*) >= 2         -- group filter, after grouping
ORDER BY department_id
department_idn
12
24
Result: 2 rows

What is the difference between INNER JOIN and LEFT JOIN?

An inner join keeps only rows that have a match on both sides. A left join keeps every row from the left table and fills the right side with NULLs when there is no match. In the sample data, Lea has no department: an inner join drops her, a left join keeps her. Worked examples, including the WHERE-versus-ON trap, are on the JOIN questions page.

SELECT COUNT(*) AS inner_rows FROM employees e JOIN departments d ON d.id = e.department_id
inner_rows
9
Result: 1 row
SELECT COUNT(*) AS left_rows FROM employees e LEFT JOIN departments d ON d.id = e.department_id
left_rows
10
Result: 1 row

What is the difference between UNION and UNION ALL?

UNION combines two results and removes duplicate rows. UNION ALL just stacks them. Use UNION ALL unless you need the de-duplication, because removing duplicates costs a sort or a hash. Both queries need the same number of columns with compatible types.

SELECT region FROM sales WHERE amount > 100
UNION ALL
SELECT region FROM sales WHERE amount < 100
ORDER BY region
region
North
North
North
North
South
South
Result: 6 rows · UNION would return only 2 rows

What is the difference between COUNT(*), COUNT(column) and COUNT(DISTINCT column)?

COUNT(*) counts rows. COUNT(column) counts rows where the column is not NULL. COUNT(DISTINCT column) counts different non-NULL values.

SELECT COUNT(*) AS all_rows, COUNT(amount) AS with_amount, COUNT(DISTINCT region) AS regions FROM sales
all_rowswith_amountregions
762
Result: 1 row

How does NULL behave in comparisons?

NULL means unknown, so NULL = NULL is not true; it is NULL. WHERE keeps only rows where the condition is true, so = NULL matches nothing. Use IS NULL. Aggregates skip NULLs, and NOT IN with a NULL in the list returns nothing. All of it, with examples, is on the NULL and GROUP BY page.

SELECT COUNT(*) AS matches_equals_null FROM employees WHERE department_id = NULL
matches_equals_null
0
Result: 1 row
SELECT COUNT(*) AS matches_is_null FROM employees WHERE department_id IS NULL
matches_is_null
1
Result: 1 row

In what order is a query evaluated?

Not in the order you write it. Logically: FROM and JOIN, then WHERE, GROUP BY, HAVING, window functions, SELECT (and DISTINCT), ORDER BY, and finally LIMIT. That is why a WHERE cannot use an aggregate, and why standard SQL does not let you use a SELECT alias in WHERE. SQLite is lenient and accepts the alias, which is a portability trap: the same query fails in PostgreSQL and SQL Server.

SELECT full_name, salary * 12 AS yearly FROM employees WHERE yearly > 100000 ORDER BY id
full_nameyearly
Mara Lind110400
Result: 1 row · accepted by SQLite only

What are a primary key, a foreign key and a UNIQUE constraint?

A primary key identifies each row: unique and not NULL (one per table). A foreign key points at a key in another table and stops you from storing a child row with no parent. A UNIQUE constraint forbids duplicates but, depending on the database, allows NULLs. In SQLite, foreign keys are enforced only after PRAGMA foreign_keys = ON.

What is an index, and when does it not help?

An index is a sorted structure that lets the database find rows by a column without reading the whole table. It costs disk space and makes writes slower. It does not help when the condition wraps the column in a function, when a LIKE pattern starts with %, or when the condition matches most of the table. Check with EXPLAIN.

EXPLAIN QUERY PLAN SELECT * FROM employees WHERE department_id = 2
idparentnotuseddetail
200SCAN employees
Result: 1 row · SCAN: the whole table is read
CREATE INDEX idx_employees_department ON employees(department_id);
EXPLAIN QUERY PLAN SELECT * FROM employees WHERE department_id = 2
idparentnotuseddetail
300SEARCH employees USING INDEX idx_employees_department (department_id=?)
Result: 1 row · SEARCH: the index is used

Subquery or CTE?

Most of the time it is about readability. A CTE (WITH name AS (...)) gives a name to a step, can be referenced several times and is the only way to write a recursive query. Performance depends on the database's optimizer, so do not assume either one is faster: read the plan.

WITH ranked AS (
  SELECT full_name, salary, RANK() OVER (ORDER BY salary DESC) AS r FROM employees
)
SELECT full_name, salary FROM ranked WHERE r <= 3 ORDER BY r, full_name
full_namesalary
Mara Lind9200
Ines Cole7600
Joel Park7600
Result: 3 rows

What is the difference between DELETE, TRUNCATE and DROP?

DELETE removes rows, can take a WHERE, and fires triggers. TRUNCATE removes all rows in one operation without a WHERE; it is usually faster, and how it behaves in transactions differs between databases. SQLite has no TRUNCATE; use DELETE FROM table. DROP TABLE removes the table itself, with its data and indexes.

What is normalization?

Organizing tables so each fact is stored once. First normal form: one value per cell, no repeating groups. Second: every non-key column depends on the whole key, not part of a composite key. Third: non-key columns do not depend on other non-key columns. The benefit is fewer update mistakes; the cost is more joins. Reporting tables are often denormalized on purpose.

What is the difference between a window function and GROUP BY?

GROUP BY collapses rows into one per group. A window function keeps every row and adds a value computed over a related set of rows. Use it when you need the detail and the group figure side by side, such as each salary next to its department average. More on the window functions page.

SELECT full_name, salary, ROUND(AVG(salary) OVER (PARTITION BY department_id)) AS dept_average
FROM employees
WHERE department_id = 1
ORDER BY full_name
full_namesalarydept_average
Nadia Voss64005867
Owen Hart43005867
Tomas Reyes69005867
Result: 3 rows

How to answer in the room

  1. Restate the question and ask about the edge cases that change the answer: NULLs, ties, duplicates, an empty table.
  2. Describe the idea in one sentence before you type: "join orders to customers, group by customer, keep groups with more than two orders".
  3. Build the query in layers and check each layer on two or three rows of made-up data.
  4. Say what you would check on a real database: an index on the join and filter columns, the plan, the size of the result.
  5. Mention the dialect you assume. The core is the same everywhere; dates, strings and LIMIT are not.

Nobody can promise what a particular interviewer will ask. What you can do is make the basics automatic, so your attention is free for the unusual part.

Keep going

Practice it, don't just read it. Query Belt has mock interviews with a timer, no hints and one submit per task, then a report with your weak topics. It works offline.

Coming to Google Play