Five hundred loans, one question
A school library has a list of 500 book loans. The librarian asks: who has kept a book longest? Which year group borrows most?
Nobody reads 500 rows. You ask the table questions by changing its order and hiding rows.
Key words
| Word | What it means |
|---|---|
| Interrogate | ask a table questions by sorting and filtering it |
| Sort | put the rows in order, using one or more fields |
| Ascending | smallest or A first |
| Descending | largest or Z first |
| Filter | show only the rows that meet a test and hide the rest |
| Field | one column of a table, such as days |
| 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 order a table by a field, sort on two fields, and filter on a field, and say when to use each.
Starter
Have a go
Starter
3 questionsYour answers are marked here and are not kept. A college account keeps every student’s marks.
A table as it looks in a spreadsheet
| Row | A pupil | B year | C book | D days |
|---|---|---|---|---|
| 1 | Amy | 8 | Maps | 5 |
| 2 | Bo | 7 | Space | 2 |
| 3 | Cy | 8 | Space | 2 |
| 4 | Di | 7 | Maps | 5 |
| 5 | Eli | 9 | Chess | 9 |
The cells students fill in are left blank in this preview.
No spreadsheet here
Important
This platform has no spreadsheet program. Here the cells are a table and the formula is shown as text. You do the same job with code. On site you use a real spreadsheet program.
Three ways to interrogate
| Technique | What it does | Spreadsheet | SQL |
|---|---|---|---|
| Order by a field | Rows in order of one field | Sort A to Z or Z to A | `ORDER BY days DESC` |
| Sort on several fields | The second field breaks ties | Add a second sort level | `ORDER BY year, days` |
| Filter on a field | Shows only rows that pass a test | Filter arrows on the header | `WHERE year = 7` |
The cells students fill in are left blank in this preview.
Try it: the same questions in SQL
-- Who kept a book longest?
SELECT pupil, days FROM loans ORDER BY days DESC;
-- Year first, then days.
SELECT * FROM loans ORDER BY year, days;
-- Only year 7.
SELECT * FROM loans WHERE year = 7;
A filter hides rows and deletes none
A filter only changes what you can see. The hidden rows are still in the file. Clear the filter and they come back.
Sorting does not delete rows either. It changes their order. Both are safe ways to look at data.