Stage 1 review
This page proves the whole stage stuck. Each challenge pulls from a different lesson, and the later ones combine several. Write each query in the live box and press Run - the box checks your result against the expected answer, so you get immediate feedback. Stuck? Open the worked answer.
Everything runs against the same store schema you have used throughout. Reset data restores the rows.
Challenge 1 - SELECT and WHERE
List the names of every customer in Ireland (country = 'IE'), alphabetically.
Show answer
SELECT name
FROM customers
WHERE country = 'IE'
ORDER BY name;
Three IE customers - Ana, Ben, Finn - sorted by name. Eve's country is NULL, so country = 'IE' is unknown and she is correctly excluded.
Challenge 2 - GROUP BY and HAVING
Find the customer_id and order count for every customer who has placed more than one order.
Show answer
SELECT customer_id, COUNT(*) AS orders
FROM orders
GROUP BY customer_id
HAVING COUNT(*) > 1;
Only customer 1 (Ana) has two orders. The filter is on the aggregate, so it must go in HAVING, not WHERE - the count does not exist yet when WHERE runs.
Challenge 3 - LEFT JOIN and COALESCE
List every customer's name and their total spend, ordered by name. Customers with no orders must show 0, not NULL.
Show answer
SELECT customers.name, COALESCE(SUM(orders.total), 0) AS spent
FROM customers
LEFT JOIN orders ON orders.customer_id = customers.id
GROUP BY customers.id
ORDER BY customers.name;
A LEFT JOIN keeps Cleo and Eve, who have no orders; SUM over their (absent) rows is NULL, and COALESCE(..., 0) turns that into 0. A plain JOIN would drop them.
Challenge 4 - Subquery with NOT EXISTS
List the name of every customer who has never placed an order, alphabetically.
Show answer
SELECT name
FROM customers c
WHERE NOT EXISTS (
SELECT 1 FROM orders o WHERE o.customer_id = c.id
)
ORDER BY name;
Cleo and Eve have no matching order. NOT EXISTS is the safe choice over NOT IN here - if customer_id ever contained a NULL, NOT IN could silently return nothing.
Challenge 5 - CTE
Using a common table expression, report total spend per country, ordered by country. Build per-customer spend in the CTE, then group it by country.
Show answer
WITH spend AS (
SELECT customer_id, SUM(total) AS total_spent
FROM orders
GROUP BY customer_id
)
SELECT customers.country, SUM(spend.total_spent) AS country_spend
FROM spend
JOIN customers ON customers.id = spend.customer_id
GROUP BY customers.country
ORDER BY customers.country;
The CTE first sums each customer's orders; the main query joins to customers and re-groups by country. Only customers who have ordered appear (an inner JOIN), so IE totals 245 and LT totals 25.
Challenge 6 - Window function
Show each order's id, created_at, and total, plus a running total of total ordered by date - without collapsing the rows.
Show answer
SELECT id, created_at, total,
SUM(total) OVER (ORDER BY created_at) AS running_total
FROM orders;
SUM(total) OVER (ORDER BY created_at) accumulates down the dates - 40, 65, 155, 180, 270 - while every order keeps its own row. GROUP BY would have collapsed them into a single sum.
Comprehensive quiz
Stage 1 cumulative review
7 questions1You write SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY. In what order does the database logically evaluate them?
2A 'list every customer and their orders' report is missing the customers who have no orders. The most likely cause?
3WHERE country = NULL returns no rows even for customers with a missing country. Why?
4Which choice best handles 'show the country, or the word unknown when it is missing'?
5You need a per-row running total of order amounts while keeping every order row. Which tool fits?
6Which statement about subqueries and EXISTS is correct?
7When is a CTE (WITH) the better choice over a subquery?
You can read and write multi-table queries, summarise with aggregates, join with the right join type, nest questions with subqueries and EXISTS, compose with CTEs, compute across rows with window functions, and change data safely. Next is Stage 2 - Designing a Database, where you decide what the tables should be.
Stage 1 cheatsheet (reference)
Reading one table
| Clause | Does | Example |
|---|---|---|
SELECT ... AS | choose / rename columns | SELECT total * 1.23 AS gross |
DISTINCT | drop duplicate rows | SELECT DISTINCT country |
WHERE | keep matching rows | WHERE total >= 30 |
ORDER BY ... ASC|DESC | sort the result | ORDER BY total DESC, name |
LIMIT / OFFSET | cap / skip rows | LIMIT 3 OFFSET 10 |
WHERE building blocks: = <> < > <= >=, AND/OR/NOT, BETWEEN a AND b, IN (...)/NOT IN, LIKE (% = any run, _ = one char), IS NULL/IS NOT NULL.
Aggregating - COUNT(*), SUM, AVG, MIN, MAX.
SELECT customer_id, SUM(total) AS spent
FROM orders
WHERE created_at >= '2026-01-01' -- WHERE filters rows (before grouping)
GROUP BY customer_id
HAVING SUM(total) > 50; -- HAVING filters groups (after aggregate)
Every non-aggregated SELECT column must appear in GROUP BY.
Joins
| Join | Keeps |
|---|---|
INNER JOIN (plain JOIN) | rows matched on both sides |
LEFT JOIN | every left row (right is NULL when unmatched) |
RIGHT JOIN | mirror of LEFT |
FULL OUTER JOIN | every row from both sides |
CROSS JOIN | every left row paired with every right row |
SELECT c.name, COUNT(o.id) AS orders
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.id;
Subqueries - WHERE total > (SELECT AVG(total) FROM orders), WHERE id IN (SELECT ...), and correlated EXISTS / NOT EXISTS (safe with NULL, unlike NOT IN).
CTE (WITH) - name a step, then build on it:
WITH spend AS (
SELECT customer_id, SUM(total) AS total_spent FROM orders GROUP BY customer_id
)
SELECT * FROM spend WHERE total_spent > 30;
Set operations - UNION (dedup), UNION ALL (keep dups), INTERSECT (in both), EXCEPT (in first not second). Both queries must return the same columns.
Window functions - per-row calculation over an OVER (...) window, without collapsing rows:
SELECT id, total,
SUM(total) OVER (ORDER BY created_at) AS running_total,
ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY total DESC) AS rn,
LAG(total) OVER (ORDER BY created_at) AS prev_total
FROM orders;
Ranking: ROW_NUMBER (1,2,3), RANK (1,1,3), DENSE_RANK (1,1,2). Frames: ROWS BETWEEN 1 PRECEDING AND CURRENT ROW.
Expressions & NULL - CASE WHEN ... THEN ... ELSE ... END, COALESCE(a, b) (first non-NULL), NULLIF(a, b).
String funcs - UPPER/LOWER, LENGTH, SUBSTR(s, start, len) (1-based), a || b, TRIM, REPLACE.
Date funcs (SQLite) - date(d, '+7 days'), strftime('%Y-%m', d), julianday(d2) - julianday(d1). Store timestamps in UTC; convert only at the edges.
Changing data - INSERT INTO t (...) VALUES (...), UPDATE t SET ... WHERE ..., DELETE FROM t WHERE .... Always preview the WHERE with a SELECT first.