The total that is too big
A report says Sam paid in £70. Sam really paid in £35. Nothing is wrong with the data. The fault is in the calculation.
Clean data in a faulty model still gives wrong answers. Task 2 asks you to test both.
Key words
| Word | What it means |
|---|---|
| Requirement | something the finished work must do |
| Model | the tables, formulas and queries that turn data into answers |
| Join | a link that puts rows from two tables side by side |
| Reference | a pointer to a cell or a value, such as B4 |
| Absolute reference | a pointer that stays fixed when a formula is copied, written with $ |
| Logic error | a mistake in the rules, so the code runs but gives a wrong answer |
| SQL | a language for asking questions of data kept in tables |
The cells students fill in are left blank in this preview.
By the end of this lesson
Note
I can test a model against its requirements, find common calculation faults and fix them with a reason.
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.
Run it: a join that repeats rows
SELECT m.name, SUM(d.amount) AS total
FROM member m
JOIN phone p ON p.member_ref = m.member_ref
JOIN deposit2 d ON d.member_ref = m.member_ref
GROUP BY m.name;Why Sam shows £70
Sam has two phone numbers and two deposits. The join makes 2 times 2 rows, so each deposit is counted twice.
A good test is to compare against a total you can check another way. The whole table adds up to £75. The model said £110.
Real spreadsheets are used on site
Important
The real task may give you a spreadsheet. This platform has no spreadsheet, so the cells here are shown as a table with the formula written as text. Do the real spreadsheet work on site.