Counted twice, counted never
A club list has 6 rows. Two members appear twice. One row is completely empty. One member left the club in 2019.
The club says it has 6 members. It really has 3 or 4. Some rows should never have been counted.
Key words
| Word | What it means |
|---|---|
| Record | one row of a table: all the facts about one thing |
| Blank record | a row with nothing in it |
| Duplicate | a record that appears more than once |
| Out of date | a record that is too old to be useful for the job |
| Out-of-date field | one value in a record that has not been checked for too long |
| Unfit for processing | not usable in a calculation or a report as it stands |
| Database | an organised store of tables that you can ask questions |
| SQL | the language used to ask a database for data |
The cells students fill in are left blank in this preview.
By the end of this lesson
Note
I can spot records that are unfit for processing and remove them, and say why each one is a risk.
Starter
Have a go
Starter
3 questionsYour answers are marked here and are not kept. A college account keeps every student’s marks.
Records and fields that cause trouble
| Problem | Example | What goes wrong | Usual fix |
|---|---|---|---|
| Blank record | A row of empty boxes | Counted as a member | Remove it |
| Duplicate record | Amy 8 appears twice | Counted twice | Keep the first, remove the rest |
| Out-of-date record | Cy joined in 2019 and left | Counted as current | Remove or archive it |
| Out-of-date field | A phone number last checked in 2020 | Calls reach nobody | Mark for a re-check |
The cells students fill in are left blank in this preview.
Try it: see the repeats with SQL
-- Every row, including repeats.
SELECT COUNT(*) AS rows_in_table FROM members;
-- DISTINCT keeps one copy of each different row.
SELECT DISTINCT name, year, joined FROM members;
-- Who is old? Joined before 2022.
SELECT name, joined FROM members WHERE joined < 2022;
Be careful before you delete
Removing a record is a decision. A row that looks like a repeat may be two real people with the same name and year.
Compare all the fields, not just the name. Keep the original file, clean a copy, and write down what you removed and why.
Extra: Finding repeats with HAVING
Group the rows by all their fields and keep the groups with more than one row: SELECT name, year, joined, COUNT(*) FROM members GROUP BY name, year, joined HAVING COUNT(*) > 1;
This lists each repeated record once, with how many times it appears.
Check
Have a go
Check
4 questionsYour answers are marked here and are not kept. A college account keeps every student’s marks.