sql · level 2 · unit 2 · lesson 1
- meet it
- guess first
- your turn
- fix it
- nailed it
Tap a green word if you get lost.
meet it
A points at a row in another table.
Picture one big table with the owner's name and town typed on every pet's row. Ana has two pets, so her town appears twice. If she moves and you miss one, the table tells two different stories.
So you : store each fact once. Owners get their own table, each with an id. Each pet holds its owner's id in owner_id, a foreign key that points at one owner.
This is a link: one owner can have many pets, but each pet has one owner. Nibbles has no owner yet, so that owner_id is NULL.
SELECT * FROM owners;
SELECT name, owner_id FROM pets;A foreign key is usually named after the table it points at, plus _id.
guess first
Who owns Biscuit? Follow the id.
Guess before you run it. Wrong guesses are how you learn, and nothing is marked.
SELECT name, owner_id FROM pets WHERE name = 'Biscuit';
SELECT name, town FROM owners WHERE id = 3;your turn
Your turn. Find one owner's pets.
Owners have the ids 1 to 4. Type one in the blank to list that owner's pets. One owner has no pets at all.
result
Your answer appears here as a table.
fix it
Ana has id 1, but her pets went missing. Fix it!
result
Your answer appears here as a table.
- What kind of value does the
owner_idcolumn hold? owner_idholds an owner's id number, not their name. Ana's id is 1.- Change 'Ana' to 1, with no quotes, then press run.
nailed it
you followed a link between tables. :D
- Normalise: store each fact once, in one table.
- A foreign key holds the id of a row elsewhere.
- One owner with many pets is a one-to-many link.
new words in your collection
- normalise
- foreign key
- one-to-many
This lesson isn’t saved. A free account remembers every lesson you finish.