Quizz

The full text of this chapter, to read or revisit. To practise, do the exercises.

▶ Do the exercises

Exercise 1 — Junction table

What is a junction table?

Options
• A table whose rows link two other tables, used for many-to-many relationships
• A table with no primary key
• The first table written after FROM
• A temporary table created by a JOIN

Hint : Think of badges: one trainer, one gym per row.

Exercise 2 — Through the junction

Complete the query that lists each trainer with the gyms they beat.

Hint : Each ON matches a foreign key of badges with the primary key it points to.

Exercise 3 — Keys of the league

Match each column with what it is.

Items to match
• badges.trainer_id
• badges.gym_id
• badges.badge_id
• battles.winner_id
Definitions
• A foreign key to trainers
• A foreign key to gyms
• The primary key of the junction table
• A third foreign key to trainers in the same table

Exercise 4 — Rows after two joins

Morgan has 5 badges. How many rows does this query return?

SELECT g.name FROM trainers t INNER JOIN badges bd ON bd.trainer_id = t.trainer_id INNER JOIN gyms g ON g.gym_id = bd.gym_id WHERE t.name = 'Morgan';
Options
• 5
• 1
• 10
• 20

Hint : One row per badge of Morgan.

Exercise 5 — Neighbours

Complete the self-join that returns each pair of trainers living in the same city, once.

Hint : The second copy needs its own alias, and the pair must appear only once.

Exercise 6 — Why two aliases?

Why are aliases mandatory in FROM trainers t1 INNER JOIN trainers t2 ON ...?

Options
• Both copies have the same columns, so SQL needs a way to tell which copy a column comes from
• Aliases make the query faster
• Self-joins only work with one-letter aliases
• They are not mandatory

Hint : What would trainers.name mean when trainers appears twice?

Exercise 7 — Roles in a battle

In FROM battles b INNER JOIN trainers t1 ON b.trainer1_id = t1.trainer_id INNER JOIN trainers w ON b.winner_id = w.trainer_id, match each expression with its meaning.

Items to match
• t1.name
• w.name
• b.battle_date
• b.winner_id = w.trainer_id
Definitions
• The name of the first fighter
• The name of the winner
• The date of the battle
• The condition that picks the winner's row

Exercise 8 — No ON at all

5 gyms and 6 battles. How many rows does SELECT * FROM gyms CROSS JOIN battles; return?

Options
• 30
• 11
• 6
• 5

Hint : Every gym paired with every battle.

Exercise 9 — Count once

Complete the query so that each trainer's number of badges is correct even though Codémons are joined too.

Hint : Count distinct values of a column that identifies a badge uniquely.

Exercise 10 — Taylor's open challenges

Taylor has badges from Tide Gym and Boulder Gym. There are 5 gyms. How many rows does this query return for Taylor?

SELECT g.name FROM trainers t CROSS JOIN gyms g LEFT JOIN badges bd ON bd.trainer_id = t.trainer_id AND bd.gym_id = g.gym_id WHERE t.name = 'Taylor' AND bd.badge_id IS NULL;
Options
• 3
• 2
• 5
• 0

Hint : All gyms minus the ones already beaten.

Exercise 11 — Both fighters

Complete the query so that each battle shows both fighters' names.

Hint : The second copy of trainers follows the second fighter's foreign key.

Exercise 12 — Row counts

4 trainers, 5 gyms, 6 battles, 2 pairs of same-type Codémons, 2 trainers from Azure City. Match each query with its number of rows.

Items to match
• trainers CROSS JOIN gyms
• battles b INNER JOIN trainers w ON b.winner_id = w.trainer_id
• codemon c1 INNER JOIN codemon c2 ON c1.type = c2.type AND c1.codemon_id < c2.codemon_id
• trainers t1 INNER JOIN trainers t2 ON t1.hometown = t2.hometown AND t1.trainer_id < t2.trainer_id
Definitions
• 20
• 6
• 2
• 1

Exercise 13 — < or !=?

What changes if you write t1.trainer_id != t2.trainer_id instead of t1.trainer_id < t2.trainer_id in a self-join on trainers from the same hometown?

Options
• Self-pairs are still removed, but each pair appears twice (Morgan–Jordan and Jordan–Morgan)
• Nothing, the two conditions are equivalent
• The self-pairs come back
• The query fails

Hint : != accepts both orders of the same two ids.

Exercise 14 — Badges from Stone Ridge

Complete the query so that it lists the trainers who earned a badge in a gym located in Stone Ridge.

Hint : The location of a gym is a column of gyms.

Exercise 15 — Explain the 6

Alex owns 2 Codémons and holds 3 badges. Why does this query return 6 for Alex?

SELECT t.name, COUNT(*) FROM trainers t INNER JOIN codemon c ON c.trainer_id = t.trainer_id INNER JOIN badges bd ON bd.trainer_id = t.trainer_id GROUP BY t.name;
Options
• Each of the 2 Codémon rows is combined with each of the 3 badge rows: 2 × 3 = 6
• COUNT(*) adds the Codémons and the badges: 2 + 3 + 1
• Alex secretly has 6 badges
• GROUP BY doubles the rows

Hint : Two one-to-many joins multiply.

Exercise 16 — Vocabulary

Match each term with its meaning.

Items to match
• Junction table
• Self-join
• Cross join
• Fan-out
Definitions
• A table of links between two tables, one row per link
• A table joined to itself through two aliases
• Every row of one table paired with every row of another
• Rows multiplied by joining two one-to-many tables at once

Exercise 17 — The open challenges

Complete the query so that it lists every (trainer, gym) pair where the trainer has no badge from that gym.

Hint : Build the full grid first, then keep the pairs with no badge attached.

Exercise 18 — Winner's name

Which query lists each battle date with the winner's name?

Options
• SELECT b.battle_date, w.name FROM battles b INNER JOIN trainers w ON b.winner_id = w.trainer_id;
• SELECT b.battle_date, w.name FROM battles b INNER JOIN trainers w ON b.trainer1_id = w.trainer_id;
• SELECT b.battle_date, b.winner_id AS name FROM battles b;
• SELECT b.battle_date, w.name FROM battles b CROSS JOIN trainers w;

Hint : Follow the winner_id foreign key.

Exercise 19 — Join order

You rewrite FROM trainers t INNER JOIN badges bd ON ... INNER JOIN gyms g ON ... as FROM gyms g INNER JOIN badges bd ON ... INNER JOIN trainers t ON ... (same conditions). What changes?

Options
• Nothing in the rows returned: INNER JOINs can be chained in any order
• The result has more rows
• The result loses the trainers without badges
• The query fails because gyms has no trainer_id

Hint : Only outer joins care about which side is which.

Exercise 20 — Wins per trainer, zero included

Complete the query so that every trainer appears with their number of victories, 0 for those who never won.

Hint : Keep every trainer, and count a battle column so that no match gives 0.

Exercise 21 — Who never fought?

Which query returns the trainers who appear in no battle, neither as fighter 1 nor as fighter 2?

Options
• SELECT t.name FROM trainers t LEFT JOIN battles b ON t.trainer_id = b.trainer1_id OR t.trainer_id = b.trainer2_id WHERE b.battle_id IS NULL;
• SELECT t.name FROM trainers t INNER JOIN battles b ON t.trainer_id = b.trainer1_id OR t.trainer_id = b.trainer2_id WHERE b.battle_id IS NULL;
• SELECT t.name FROM trainers t LEFT JOIN battles b ON b.trainer1_id = t.trainer_id AND b.trainer2_id = t.trainer_id WHERE b.battle_id IS NULL;
• SELECT t.name FROM trainers t CROSS JOIN battles b WHERE b.battle_id IS NULL;

Hint : An anti-join, with an ON that accepts either fighter column (OR, not AND).

Exercise 22 — Which join?

Match each need with the join that answers it.

Items to match
• Every possible trainer / gym combination
• Pairs of trainers from the same hometown
• Trainer names next to gym names via badges
• Every gym with its badge count, zeros included
Definitions
• CROSS JOIN
• A self-join on trainers
• Two INNER JOINs through the junction table
• A LEFT JOIN from gyms to badges

Exercise 23 — Winner and gym

Complete the query so that each battle shows the winner's name and the gym's name.

Hint : Each ON links a foreign key of battles to the primary key of the joined table.

Exercise 24 — Morgan's fan-out

Morgan owns 2 Codémons and holds 5 badges. What does COUNT(*) return for Morgan in a query that joins trainers to both codemon and badges?

Options
• 10
• 7
• 5
• 2

Hint : Multiply, don't add.

Exercise 25 — Still a cross join

Which query produces the same rows as SELECT * FROM trainers CROSS JOIN gyms;?

Options
• SELECT * FROM trainers INNER JOIN gyms;
• SELECT * FROM trainers INNER JOIN gyms ON trainers.trainer_id = gyms.gym_id;
• SELECT * FROM trainers LEFT JOIN gyms ON trainers.hometown = gyms.city;
• SELECT * FROM trainers, badges;

Hint : A join without any condition keeps every combination.

Back to the course