SQL Analyst Starter: handouts

Choose Print / Save as PDF, then select Save as PDF in your browser. The learning copy hides answers.

SQL Analyst Starter

Original practice materials · Version 2.0.0 · Synthetic business data

Practice database

CREATE TABLE orders(order_id INTEGER, customer_id INTEGER, status TEXT, amount INTEGER, region TEXT, ordered_at TEXT);
INSERT INTO orders VALUES (1,1,'paid',120,'East','2026-09-01'),(2,2,'pending',140,'West','2026-09-02'),(3,1,'paid',100,'East','2026-09-03'),(4,3,'paid',180,'West','2026-09-03'),(5,99,'paid',80,'East','2026-09-04'),(6,NULL,'cancelled',0,NULL,'2026-09-04'),(7,2,'paid',120,'West','2026-09-05'),(8,4,'paid',30,'North','2026-09-06');
CREATE TABLE customers(customer_id INTEGER PRIMARY KEY,name TEXT,email TEXT);
INSERT INTO customers VALUES(1,'Avery','team@example.test'),(2,'Morgan','team@example.test'),(3,'Riley',NULL),(4,'Casey',''),(5,'Sky','sky@example.test');
CREATE TABLE returns(return_id INTEGER,order_id INTEGER,refund INTEGER);
INSERT INTO returns VALUES(1,1,20),(2,1,10),(3,4,180),(4,99,25);
CREATE TABLE inventory(sku TEXT,on_hand INTEGER,reorder_at INTEGER);
INSERT INTO inventory VALUES('PEN',5,10),('BOOK',20,10),('MUG',0,5),('BAG',NULL,4),('CARD',4,4);
CREATE TABLE events(event_id INTEGER,customer_id INTEGER,stage TEXT);
INSERT INTO events VALUES(1,1,'visit'),(2,1,'visit'),(3,1,'checkout'),(4,2,'visit'),(5,3,'visit'),(6,3,'checkout'),(7,4,'visit');

1. Filter with both conditions

Select paid orders strictly above a threshold.

SELECT declares the output columns.
WHERE combines status and amount with AND.
An amount equal to the threshold does not pass a strict > test.

Find paid orders over $100

Return order_id and amount.
Include only paid orders with amount strictly greater than 100.
Order is not required. Amounts are whole dollars.

SELECT * FROM orders;

Reference answer

SELECT order_id, amount FROM orders WHERE status='paid' AND amount>100

Exclude pending order 2 and boundary order 3. Include paid orders 1, 4 and 7.

Find West orders worth at least $120

Return order_id and amount.
Include West orders with amount at least 120, regardless of status.

SELECT * FROM orders;

Reference answer

SELECT order_id, amount FROM orders WHERE region='West' AND amount>=120

At least includes 120; all statuses are allowed.

Extension: Try a WITH expression to name the paid-order subset before filtering.

2. Group without losing rows

Aggregate revenue by a declared grouping key.

WHERE applies before GROUP BY.
SUM(amount) totals whole-dollar amounts.
COUNT(*) counts rows, including repeated values.

Calculate paid revenue by region

Return region and paid_revenue.
Group paid orders by region.

SELECT * FROM orders;

Reference answer

SELECT region, SUM(amount) AS paid_revenue FROM orders WHERE status='paid' GROUP BY region

Keep the North sale and the East orphan order; customer existence is not a condition.

Preserve repeated amounts

Return amount for every paid order.
Keep repeated amounts; do not use DISTINCT.

SELECT * FROM orders;

Reference answer

SELECT amount FROM orders WHERE status='paid'

Two separate orders have amount 120. A set would lose one.

Extension: Compare COUNT(*) with COUNT(customer_id) when IDs can be NULL.

3. Join and keep unmatched orders

Use a left join for a complete order list.

Join orders.customer_id to customers.customer_id.
LEFT JOIN keeps unmatched and NULL-key orders.
A missing name remains SQL NULL, not an empty string.

Attach names to every order

Return order_id and name for all orders.
Keep unmatched orders; use NULL for their name.

SELECT * FROM orders;

Reference answer

SELECT o.order_id, c.name FROM orders o LEFT JOIN customers c ON o.customer_id=c.customer_id

An inner join drops order 5 and order 6. Join on the ID.

Find customers without orders

Return customer_id and name.
Include customers with no orders.

SELECT * FROM orders;

Reference answer

SELECT c.customer_id,c.name FROM customers c LEFT JOIN orders o ON c.customer_id=o.customer_id WHERE o.order_id IS NULL

Sky has no orders. Do not filter on customer email.

Extension: Joining by email would duplicate customers sharing an email.

4. Treat NULL explicitly

Distinguish missing values from zero and blank text.

NULL is an unknown value, not 0 or empty text.
Use IS NULL rather than = NULL.
Use COALESCE only when the output contract declares a default.

Find genuinely missing emails

Return customer_id and name.
Select SQL NULL email only; exclude the empty string.

SELECT * FROM orders;

Reference answer

SELECT customer_id,name FROM customers WHERE email IS NULL

NULL and an empty string are different values.

Label an unknown order region

Return order_id and region for every order.
Replace SQL NULL region with Unknown.

SELECT * FROM orders;

Reference answer

SELECT order_id,COALESCE(region,'Unknown') AS region FROM orders

COALESCE supplies the declared label; no order should disappear.

Extension: Write separate diagnostics for NULL email and empty email.

Final project

Build a checked paid-order report

Return region, order_count, paid_revenue in that order. Revenue is integer whole dollars. Sort revenue descending, then region ascending (SQLite NULL sorts first on a tie).
Use paid orders with 2026-09-01 <= ordered_at < 2026-10-01. Include unmatched and NULL customer IDs; this report does not require a customer join.
Preserve a SQL NULL region as NULL. Keep repeated amounts from different order IDs and genuine zero amounts.

Use the complete project source data in the course download. Every output check must pass.

SELECT region,COUNT(*) AS order_count,SUM(amount) AS paid_revenue FROM orders WHERE status='paid' AND ordered_at>='2026-09-01' AND ordered_at<'2026-10-01' GROUP BY region ORDER BY paid_revenue DESC,region ASC;

Related public objectives: https://sqlbolt.com/.

Independent original materials. CC BY 4.0.