Turn a business question into a SQL query and prove the number is right, using a small online shop whose data you can check by hand. No SQL needed to start.
Learn the few ways a SQL query can run and still be wrong, and the checks that catch each one before you send a number.
Written and reviewed with AI assistance, and checked by automated rules. No subject-matter expert has signed it off, and it is not a certification.
Learning outcomes
Preview
Read the complete course here. Reading does not create a private copy, save progress, or record learning evidence.
Preview material
Before you write a query, finish this sentence: “one row of this table is one …”. What you put after “one” is the table’s grain, and it decides what every count, sum and filter means. COUNT(*) does not count orders or customers. It counts rows, so the number it returns depends on what a row stands for.
This course follows one small online shop that sells kettles, mugs, tea tins and filters. Prices are whole dollars, an assumption of the example, and the SPRING coupon gives free shipping. Three tables describe the shop, and each has its own grain.
To find a grain, look for the column that never repeats, then say a real row aloud: “this row is the order customer 2 placed on 9 March.” If the sentence needs an “and also”, two grains are mixed in one table.
Counting goes wrong when the grain is finer than the thing you are counting. Order lines are finer than orders, because an order has one row for each product on it. A question about orders, asked of the lines table, needs more than a row count.
Every query in this course runs as it stands. It builds its own small tables from typed-in values (the WITH lines at the top), so all you need is SQLite or PostgreSQL; other databases may need small changes to build the same tables. The result under each query comes from running it in SQLite, and PostgreSQL gives the same values, though it may show more decimal places.
A query reads like a sentence. SELECT lists the columns you want back, FROM names the table they come from, and WHERE keeps only the rows that pass a test. UNION ALL, which you will meet in several queries, stacks the rows of the SELECT above it and the SELECT below it into one result.
Compare
| Dimension | One row is | Never repeats | Rows in the sample |
|---|---|---|---|
| customers | one person with an account | customer_id | 5 |
| orders | one order a customer placed, with its time, status, coupon and shipping fee | order_id | 9 |
| order_lines | one product on one order, with its quantity and unit price | line_id (order_id repeats once for each product on the order) | 15 |
Join tables without multiplying or dropping rows, check a number a second way, and deliver a defined report.