Challenge
The full text of this chapter, to read or revisit. To practise, do the exercises.
▶ Do the exercisesExercise 1 — League report: Step 1
Professor Pi is writing the league report and needs three queries from you.
Step 1: the badge book. Return trainer (the trainer's name), gym (the gym's name), the gym's city and earned_on, from the oldest badge to the most recent.
-- Step 1: the badge bookExercise 2 — League report: Step 2
Step 2: the battle log, with names instead of ids.
Return battle_date, fighter_1, fighter_2 and winner (all three as trainer names), from the oldest battle to the most recent.
-- Step 2: the battle logExercise 3 — League report: Step 3
Step 3: one row per trainer with their badges and their victories — every trainer, even Jordan, and no fan-out mistakes.
Return trainer, badge_count (badges held) and wins (battles won), 0 when there are none. Sort by wins descending, then by badge_count descending, then by trainer.
-- Step 3: badges and wins per trainer