Challenge
The full text of this chapter, to read or revisit. To practise, do the exercises.
▶ Do the exercisesExercise 1 — League census: Step 1
Professor Pi is preparing the league census and needs three queries.
Step 1: list every owned Codémon with its trainer. Return the trainer's name as trainer, the Codémon's name as codemon and its type, sorted by trainer then by Codémon name.
-- Step 1: every Codémon with its trainerExercise 2 — League census: Step 2
Step 2: the Azure City gym wants to know which local Codémons are ready for the tournament.
Return the Codémon's name as codemon, its level and its trainer's name as trainer, for the Codémons whose trainer lives in Azure City and whose level is above 10. Sort by level, highest first.
-- Step 2: Azure City Codémons above level 10Exercise 3 — League census: Step 3
Step 3: one line per trainer for the final report.
Return, for each trainer who owns at least one Codémon: the name as trainer, the number of Codémons as codemon_count and the average level, rounded to 1 decimal, as avg_level. Sort by codemon_count descending, then by trainer name.
-- Step 3: one line per trainer