Challenge

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

▶ Do the exercises

Exercise 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.

Starter code
-- Step 1: every Codémon with its trainer

Exercise 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.

Starter code
-- Step 2: Azure City Codémons above level 10

Exercise 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.

Starter code
-- Step 3: one line per trainer
Back to the course