What it shows
Routes and riders are stored in two tables, and queries give riders per route, fare income and routes with nobody on them using LEFT JOIN. It teaches the difference between inner and outer joins.
The science
An inner JOIN keeps only rows that have a match in both tables, so a route with no riders disappears. A LEFT JOIN keeps every row from the left table and shows NULL for missing matches, which lets us find the empty routes. COUNT of a column ignores NULL, so it counts riders correctly as zero for an empty route.
Materials
Cost: free. Many items come from home or from waste; spare parts such as LEDs, motors and wire are in the store.
- A computer, laptop or phone with a browser (free)
- DB Browser for SQLite (free) or a free online SQLite page
- Made-up data: 5 routes and 20 student riders with no real names
- A notebook and a pencil
Safety
This project uses safe, everyday materials. Still, read the rules below with a parent before you start.
Never use mains electricity: power models with cells, batteries or a low-voltage adapter. Sharp and hot tools, flames and lab chemicals need an adult or your teacher beside you. Keep magnets, button cells and small parts away from young children, and wash your hands after making.
All the safety rulesStep by step
8 steps. Do them in order, and test each part before you go on to the next. Write what you notice in a notebook as you go.
Create CREATE TABLE routes (route_no INTEGER PRIMARY KEY, area TEXT, fare INTEGER); and CREATE TABLE riders (id INTEGER PRIMARY KEY, name TEXT, class TEXT, route_no INTEGER);.
Insert five routes with areas and monthly fares such as 600, 800 and 1000, using made-up values.
Insert twenty riders with made-up names (use Rider 1 to Rider 20) and give them route numbers, leaving route 5 with nobody.
Count riders per route using SELECT routes.route_no, COUNT(riders.id) AS riders FROM routes LEFT JOIN riders ON routes.route_no = riders.route_no GROUP BY routes.route_no;.
Compare with the same query written with JOIN and notice that route 5 is missing.
Find the monthly income per route with SELECT routes.route_no, COUNT(riders.id) * routes.fare AS income FROM routes LEFT JOIN riders ON routes.route_no = riders.route_no GROUP BY routes.route_no;.
Find empty routes with ... LEFT JOIN ... WHERE riders.id IS NULL; and write why the IS NULL test is needed instead of = NULL.
Move a rider to another route with UPDATE riders SET route_no = 2 WHERE id = 7; and re-run the counts to see the change.
What you should see
The queries list riders and fare income for every route, show route 5 as empty only with LEFT JOIN, and update after a rider changes route.
Viva questions
A teacher or a judge will ask how it works. Try to answer each question before you open it, then compare.
1What is a LEFT JOIN?
A join that keeps every row of the left table even when there is no match.
2What does NULL mean?
No value, which is not the same as zero.
3Why use IS NULL?
Comparing with = NULL never gives true, so IS NULL must be used.
4Why does the inner JOIN lose route 5?
It has no matching rows in riders.
Take it further
Add a drivers table and show the driver, area and riders for each route with two joins.
Change one thing, measure what happens, and add a table or chart of your readings: that is what turns a model into your own investigation.
Free ebooks for this project
Programming
Databases and SQL Study Book
The complete study notes, practice questions and glossary for Databases and SQL