Reading the database, then asking it questions
Two skills, one lesson. First we learn to read a database — the schema and ER diagram that say what tables exist and how they connect. Then we learn to question it with SQL: SELECT, WHERE, ORDER BY, and grouping. We'll use a made-up Volcanic Pantheon leaderboard database the whole way through, so every query is asking something real.
You'll run the queries in a separate SQL environment — here, the focus is reading queries, predicting their output, and writing them correctly.
A relational database stores data in tables — exactly like a spreadsheet. Some vocabulary you need before anything else:
Here's a players table with three rows:
| player_id | username | country | level |
|---|---|---|---|
| 1 | ashfall | AU | 12 |
| 2 | emberlord | NZ | 8 |
| 3 | quietmagma | AU | 20 |
3 rows · 4 columns. player_id is what makes each row uniquely identifiable.
player_id is the PK of players.scores table has a player_id FK saying "this score belongs to that player".A schema lists every table, its columns, the data type of each column, and which columns are keys. Here's our leaderboard database. PK marks primary keys, FK marks foreign keys.
Reading the relationship: the line between the tables is the heart of an Entity Relationship (ER) diagram. The player_id FK in scores matches the player_id PK in players. One player can submit many scores, but each score belongs to exactly one player. That's a one-to-many relationship.
ER diagrams mark the "how many" end of each relationship with little symbols. The splayed three-pronged end (the "crow's foot") means many; a single bar means one:
| Symbol at the end of the line | Means |
|---|---|
| │ (single bar) | exactly one |
| < (crow's foot) | many |
| ○ (circle) | optional (zero is allowed) |
So players │──< scores reads as "one player relates to many scores". Same idea you've seen for a Vercel project: one user account, many saved sessions.
Their scores reference a player_id that no longer exists — they'd become "orphaned". A well-designed database stops this with rules (e.g. block the delete, or cascade-delete the scores too). This is exactly what foreign keys protect against.
It could work if usernames are guaranteed unique — but they're text, can change, and are bigger to store/compare. A small, stable, auto-numbered player_id is safer and faster, which is why most tables use a dedicated ID as the PK.
A query has a fixed skeleton. Two clauses to start:
* for all).-- Every column, every row, from the players table
SELECT * FROM players;
-- Just two columns
SELECT username, level FROM players;WHERE keeps only the rows that match a condition. The query checks every row and throws away the ones that fail.
-- Players who are level 10 or higher
SELECT username, level
FROM players
WHERE level >= 10;Conditions can use these operators:
| Operator | Meaning | Example |
|---|---|---|
= | equals | country = 'AU' |
!= or <> | not equal | level != 1 |
> < >= <= | comparisons | points > 5000 |
AND / OR | combine conditions | level > 5 AND country = 'AU' |
BETWEEN | in a range (inclusive) | level BETWEEN 5 AND 10 |
IN | matches any in a list | country IN ('AU','NZ') |
LIKE | text pattern (% = wildcard) | username LIKE 'ash%' |
Using the players table from Section 1 (ashfall/AU/12, emberlord/NZ/8, quietmagma/AU/20):
SELECT username
FROM players
WHERE country = 'AU' AND level > 15;Only quietmagma. ashfall is AU but level 12 (not > 15); emberlord is level 8 and NZ. Both conditions must be true because of AND.
With OR, a row passes if either condition holds: country is AU or level > 15. That returns ashfall and quietmagma (both AU). emberlord still fails both.
The database returns rows in no guaranteed order. ORDER BY sorts them. Add ASC for ascending (smallest first — the default) or DESC for descending (largest first).
-- Highest level first
SELECT username, level
FROM players
ORDER BY level DESC;For a leaderboard you almost always combine filtering, sorting, and a row limit. LIMIT keeps only the first N rows of the result:
-- Top 3 Australian scores, biggest points first
SELECT points, deaths, played_on
FROM scores
WHERE points > 0
ORDER BY points DESC
LIMIT 3;Order of writing matters. SQL clauses must appear in a set sequence — SELECT → FROM → WHERE → ORDER BY → LIMIT. Put WHERE after ORDER BY and it's a syntax error. Keep this skeleton in your head:
SELECT username, level
FROM players
ORDER BY level ASC
LIMIT 5;(ASC is optional since it's the default, but writing it makes your intent clear.)
Sometimes you don't want individual rows — you want a summary. Aggregate functions take a whole column of values and collapse it to a single answer:
| Function | What it returns |
|---|---|
COUNT(*) | how many rows |
SUM(column) | total of all values |
AVG(column) | average (mean) |
MAX(column) | biggest value |
MIN(column) | smallest value |
-- How many players are there, and the highest level reached?
SELECT COUNT(*), MAX(level)
FROM players;On our 3-row table that returns a single row: 3 and 20. The whole table collapsed into one summary line.
Here's the key idea. A bare aggregate summarises the whole table. GROUP BY first splits the rows into buckets that share a value, then runs the aggregate once per bucket.
"How many players are there?" → one number. "How many players are there in each country?" → one number per country. That "in each ___" phrasing is your signal to reach for GROUP BY.
-- Number of players in each country
SELECT country, COUNT(*)
FROM players
GROUP BY country;SQL groups the rows by country first — AU = {ashfall, quietmagma}, NZ = {emberlord} — then counts each group:
| country | COUNT(*) |
|---|---|
| AU | 2 |
| NZ | 1 |
The rule: every column in your SELECT must either be one you grouped by (country) or be inside an aggregate function (COUNT(*)). You can't select a raw username here — there are two usernames in the AU group, so SQL wouldn't know which to show.
This trips people up, so go slow. WHERE filters individual rows before grouping. HAVING filters whole groups after the aggregate is calculated. You need HAVING whenever your condition uses an aggregate like COUNT or AVG.
-- Only countries that have more than 1 player
SELECT country, COUNT(*)
FROM players
GROUP BY country
HAVING COUNT(*) > 1;This returns only AU (2). NZ's group has just 1 player, so HAVING drops the whole group. You couldn't use WHERE COUNT(*) > 1 — the count doesn't exist yet when WHERE runs.
WHERE vs HAVING in one line: WHERE filters rows before grouping; HAVING filters groups after. If the condition mentions an aggregate, it belongs in HAVING.
Use the scores table (columns: score_id, player_id, points, deaths, played_on).
SELECT player_id, SUM(points)
FROM scores
GROUP BY player_id;One row per player, each showing their combined points. "Each player" → group by player_id.
SELECT player_id, AVG(deaths)
FROM scores
GROUP BY player_id
HAVING AVG(deaths) < 3;The condition uses an aggregate (AVG), so it must go in HAVING, not WHERE.
SELECT player_id, MAX(points)
FROM scores
GROUP BY player_id
ORDER BY MAX(points) DESC;This is a real leaderboard query: group by player, take each one's MAX, then sort the groups.
One last query that uses almost everything from today. Read it slowly and say out loud what each line does:
SELECT player_id, SUM(points)
FROM scores
WHERE deaths < 5
GROUP BY player_id
HAVING SUM(points) > 1000
ORDER BY SUM(points) DESC
LIMIT 10;"Looking only at games where the player died fewer than 5 times (WHERE), add up each player's points (GROUP BY + SUM), keep only players whose clean-game total tops 1000 (HAVING), and list the top 10 by total points, highest first (ORDER BY + LIMIT)."
Notice the order the database actually applies them: filter rows → group → aggregate → filter groups → sort → cut off.
SELECT → FROM → WHERE → GROUP BY → HAVING → ORDER BY → LIMIT. Get them out of order and SQL throws an error.
scores table from this lesson (one each using WHERE, ORDER BY, COUNT, GROUP BY, and HAVING). Write the English question above each one.players and scores tables, add a few rows, and check that your homework queries return what you predicted.