Curious Pandas
Explorer0 XP

Tables, queries and joins

Ask a real SQLite database questions about a small zoo - and catch the two queries that return a tidy table, no error, and the wrong answer.

Runs in your browserOn your machine
16 min+35 XP for finishing

A relational database keeps data in tables: rows of the same shape, each column with a name. You don’t tell it how to find anything. You describe the rows you want in SQL, and the database works out how to get them.

The console below runs real SQLite, the database built into every phone and browser, compiled to WebAssembly and running in your tab. There’s no server and nothing to install, and nothing you type leaves your machine.

The zoo has three tables:

  • keepers: who works here, and on which shift.
  • pandas: each panda, with a keeper_id saying who looks after it.
  • feedings: what each panda ate, how much, and on which day.

Four words do most of the work

SELECT name, age          -- which columns
FROM pandas               -- from which table
WHERE age > 10            -- which rows
ORDER BY age DESC;        -- in what order

Without ORDER BY, rows come back in whatever order is cheapest for the database. That’s often the order they were inserted, until one day it isn’t. If order matters, say so.

GROUP BY folds rows that share a value into one row, so you can total them: SUM(kg), COUNT(...), AVG(age).

Joins: one question, two tables

Each panda row stores its keeper’s id, not their name. To show names you join the tables on that key:

Visual guideTwo ways to line up pandas and keepers
Two ways to line up pandas and keepersEach panda row has a keeper_id. A join matches it to the keeper with the same id. A plain JOIN keeps only pandas that found a keeper, so Pip, who has none yet, disappears. A LEFT JOIN keeps every panda and fills the keeper columns with NULL when there is no match.pandaskeeper_idmatch rowskeeper_id = idkeepersidJOINmatched rows onlyLEFT JOINevery panda, NULLseach pandaeach keeperPip droppedPip keptTwo ways to line up pandas and keepersEach panda row has a keeper_id. A join matches it to the keeper with the same id. A plain JOIN keeps only pandas that found a keeper, so Pip, who has none yet, disappears. A LEFT JOIN keeps every panda and fills the keeper columns with NULL when there is no match.pandaskeeper_idmatch rowskeeper_id = idkeepersidJOINmatched rows onlyLEFT JOINevery panda, NULLseach pandaeach keeperPip droppedPip kept
  1. Each panda row stores a keeper_id, not a name. Each keeper row has an id.
  2. A join pairs every panda with the keeper whose id equals its keeper_id.
  3. A plain JOIN (an inner join) keeps only pairs that matched. Pip has no keeper yet, so Pip silently disappears from the answer.
  4. A LEFT JOIN keeps every row from the left table. Where nothing matched, the keeper columns are NULL. Pip stays.
The zoo databasereal SQLite, in your browser

Loading SQLite...

Check yourself+10 XP

A query with a plain JOIN returns 7 pandas, but the pandas table has 8 rows. What is the most likely reason?

Check yourself+10 XP

Why add ORDER BY when the rows already come back in the order you want?

Do it for real · your machine+50 XP bonus

Everything above ran in your browser. Now try it in a real terminal: any Linux machine, a Mac's Terminal app, or Windows with WSL. Nothing to install beyond that.

macOS and most Linux systems ship the sqlite3 shell (on Debian or Ubuntu: sudo apt install sqlite3). Try the COUNT trap on your own machine:

sqlite3 :memory:
.mode box
CREATE TABLE keepers (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE pandas (id INTEGER PRIMARY KEY, name TEXT, keeper_id INTEGER);
INSERT INTO keepers VALUES (1, 'Mei'), (2, 'Ravi');
INSERT INTO pandas VALUES (1, 'Bao', 1), (2, 'Lin', 1);
SELECT k.name, COUNT(*), COUNT(p.id) FROM keepers k LEFT JOIN pandas p ON p.keeper_id = k.id GROUP BY k.id;

Ravi’s row shows 1 and 0 side by side.

Self-reported. We can't see your machine, and we don't want to. This is a bonus, never required.

Sources

Finished reading?

Missions and quizzes above earn their own XP. This marks the lesson done.