A deposit of minus thirty pounds
Row 7 says a member paid in -30 pounds. Row 9 says 5000. Row 6 says "twenty". Row 5 says nothing at all.
Each one breaks a different rule. Each one needs a different test.
Key words
| Word | What it means |
|---|---|
| Null | a value that is missing: there is nothing in the cell |
| Data type | the kind of value a column holds, such as a number, text or a date |
| Range | the lowest and highest values that make sense |
| Boundary data | test values at the very edge of a range |
| Erroneous data | data that is wrong and should be refused |
| Validation | a check that data is sensible when it comes in |
| SQL | a language for asking questions of data kept in tables |
| Query | a question you ask of data, written in SQL |
| Test data | the values you feed into a test |
| Normal data | ordinary values that should be accepted |
The cells students fill in are left blank in this preview.
By the end of this lesson
Note
I can write tests that find missing values, wrong types, out of range values and bad dates, and choose test data at the boundaries.
Starter
Have a go
Starter
3 questionsYour answers are marked here and are not kept. A college account keeps every student’s marks.
This is practice
Important
The real task comes from the exam board. This lesson uses an invented organisation and its own made-up data. Your teacher tells you how the real tasks are set and what you may bring.
Worked example: four rules for the amount
| Rule | Plain words | SQL idea |
|---|---|---|
| Present | Every deposit has an amount | amount IS NULL finds the gaps |
| Type | The amount is a number | typeof(amount) shows what is stored |
| Range | From 1 to 1000 pounds | amount < 1 OR amount > 1000 |
| Date | The date is a real date | date(deposit_date) gives null if it is not |
The cells students fill in are left blank in this preview.
Run it: missing and wrong type
SELECT deposit_id, typeof(amount) AS stored_as, amount
FROM deposit
WHERE amount IS NULL OR typeof(amount) <> 'real';Never write = NULL
Warning
A missing value is not equal to anything, not even to another missing value. So amount = NULL finds nothing. Write amount IS NULL.
Run it: range and date
SELECT deposit_id, deposit_date, amount
FROM deposit
WHERE typeof(amount) = 'real' AND (amount < 1 OR amount > 1000)
OR date(deposit_date) IS NULL;