The total that was too good
The made-up shop owner reads the new report. "Sales are up!" she says. Then somebody notices one sale is dated next month and another has a price below zero.
A dataset (a set of data ready to analyse) that has never been tested is a guess. Today we write the tests first, then run them.
Key words
| Word | What it means | Example |
|---|---|---|
| SQL | A language for asking questions of data held in tables. | select count(*) from sales_dataset |
| Requirement | What the finished data must be able to do or show. | Every sale has a category |
| Test plan | A list of checks, with what a pass looks like for each. | No repeated ref numbers |
| Assertion | A statement that must be true. A check counts the rows where it is not. | No price is below zero |
| Duplicate | The same thing recorded more than once. | Sale T3 twice |
| Range check | A check that values sit between a lowest and a highest allowed value. | Price from 0.50 to 50 |
| Appropriate | Right for the job: enough rows, the right type, and a usable form. | A month of sales for a month report |
| Review | Look back at the results and decide what to do next. | Fix, retest or accept |
The cells students fill in are left blank in this preview.
By the end of this lesson
Note
I can plan tests for a dataset, run them in SQL, read the results against the requirements and review the outcome.
Starter: last lesson
Have a go
Starter: last lesson
3 questionsYour answers are marked here and are not kept. A college account keeps every student’s marks.
Plan the tests from the requirements
Start with the requirements. Turn each one into a check that counts the rows that break it. Decide before you run what a pass is.
The shop owner has five requirements. A pass means zero rows break the rule. Writing the plan first stops you from bending the rule to fit the result.
Worked example: a test plan
| Requirement | The check | A pass is |
|---|---|---|
| R1: one row per sale | Count rows minus different ref numbers | 0 repeats |
| R2: every sale has a category | Count rows with a null category | 0 nulls |
| R3: price from 0.50 to 50 | Count rows outside the range | 0 outside |
| R4: only September 2026 | Count rows with a date outside the month | 0 outside |
| R5: at least 5 rows | Count rows, then compare with 5 | 5 or more |
The cells students fill in are left blank in this preview.
Try it: run the test plan
select 'R1 repeats' as requirement, count(*) - count(distinct ref) as failures from sales_dataset
union all select 'R2 no category', count(*) - count(category) from sales_dataset
union all select 'R3 price range', count(*) from sales_dataset where price < 0.50 or price > 50
union all select 'R4 September', count(*) from sales_dataset where sold_on < '2026-09-01' or sold_on > '2026-09-30'
union all select 'R5 enough rows', case when count(*) >= 5 then 0 else 1 end from sales_dataset;Read the results against the plan
Every failures figure above 0 is a fail. Four requirements fail here. Only R5 passes.
For each fail, find the rows. Decide the cause: a repeat to remove, a gap in a lookup table, a bad price to check with the shop. Fix the cause, then run every test again. A fix can break another test.