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 1.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 for paid orders.
Group by region.
Sort paid_revenue descending, then region ascending.

SELECT o.region, COUNT(*) AS order_count,SUM(o.amount) AS paid_revenue FROM orders o WHERE o.status='paid' GROUP BY o.region ORDER BY paid_revenue DESC,region ASC

Source objectives: https://sqlbolt.com/. Independent original materials. CC BY 4.0.