What it shows
Players and match scores in two tables give totals, averages and strike rates with JOIN, GROUP BY and HAVING. It shows how statistics in sports are calculated by queries.
The science
The players table stores each name once and the scores table stores one row per player per match, linked by player id. GROUP BY gathers each player’s rows together, SUM and AVG work on them, and HAVING filters the groups after the calculation, unlike WHERE which filters rows before it. Strike rate is runs divided by balls faced times 100.
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
- Scores of 8 players in 5 matches from your school or street team, or made up
- 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 players (id INTEGER PRIMARY KEY, name TEXT, role TEXT); and CREATE TABLE scores (id INTEGER PRIMARY KEY, player_id INTEGER, match_no INTEGER, runs INTEGER, balls INTEGER);.
Insert eight players, then 40 score rows, five for each player, with INSERT INTO scores (player_id, match_no, runs, balls) VALUES (1, 1, 34, 28);.
Show each player’s total runs with SELECT players.name, SUM(scores.runs) AS total FROM players JOIN scores ON players.id = scores.player_id GROUP BY players.name ORDER BY total DESC;.
Add AVG(scores.runs) AS average and MAX(scores.runs) AS best to the same query.
Find the strike rate with ROUND(SUM(scores.runs) * 100.0 / SUM(scores.balls), 1) AS strike_rate; the 100.0 forces decimal division.
Use HAVING SUM(scores.runs) > 100 after the GROUP BY to list only the players with over 100 runs.
Find the match with the highest team total using SELECT match_no, SUM(runs) FROM scores GROUP BY match_no ORDER BY SUM(runs) DESC LIMIT 1;.
Check one player’s total by adding by hand, and write three findings such as the best finisher by strike rate.
What you should see
The queries show a leaderboard with total runs, average, best score and strike rate, a filtered list of players above 100 runs, and the highest scoring match, and the hand check agrees.
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 the difference between WHERE and HAVING?
WHERE filters rows before grouping; HAVING filters groups after the calculation.
2What is strike rate?
Runs per 100 balls faced.
3Why multiply by 100.0 and not 100?
To avoid whole number division, which would drop the decimals.
4Why two tables?
Each name is stored once and the scores table only refers to the id.
Take it further
Add a bowling table with wickets and runs conceded and compute economy rate.
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