Quizz
The full text of this chapter, to read or revisit. To practise, do the exercises.
▶ Do the exercisesExercise 1 — Junction table
What is a junction table?
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.
badges.trainer_idbadges.gym_idbadges.badge_idbattles.winner_idExercise 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';511020Hint : 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 ...?
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.
t1.namew.nameb.battle_dateb.winner_id = w.trainer_idExercise 8 — No ON at all
5 gyms and 6 battles. How many rows does SELECT * FROM gyms CROSS JOIN battles; return?
301165Hint : 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;3250Hint : 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.
trainers CROSS JOIN gymsbattles b INNER JOIN trainers w ON b.winner_id = w.trainer_idcodemon c1 INNER JOIN codemon c2 ON c1.type = c2.type AND c1.codemon_id < c2.codemon_idtrainers t1 INNER JOIN trainers t2 ON t1.hometown = t2.hometown AND t1.trainer_id < t2.trainer_id20621Exercise 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?
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;Hint : Two one-to-many joins multiply.
Exercise 16 — Vocabulary
Match each term with its meaning.
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?
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?
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?
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.
CROSS JOINExercise 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?
10752Hint : Multiply, don't add.
Exercise 25 — Still a cross join
Which query produces the same rows as SELECT * FROM trainers CROSS JOIN gyms;?
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.