Challenge
The full text of this chapter, to read or revisit. To practise, do the exercises.
▶ Do the exercisesExercise 1 — Annual review: Step 1
Professor Pi is writing the league's annual review and needs three queries from you.
Step 1: the elite. Return name, type and level of the Codémons whose level is above the average level of all Codémons and whose trainer holds at least one badge. Two subqueries, no JOIN. Sort by level descending.
-- Step 1: the eliteExercise 2 — Annual review: Step 2
Step 2: gyms without a local champion.
Return name and city of the gyms where no trainer from the gym's own city has ever earned a badge. Use NOT EXISTS with a correlated subquery (it may contain a JOIN).
-- Step 2: gyms without a local championExercise 3 — Annual review: Step 3
Step 3: the ranking table — every trainer, Jordan included.
Return trainer (the name), badge_count and wins (both 0 when none) and best_level (the level of the trainer's strongest Codémon, NULL for Jordan). Use derived tables for the two counts and a correlated subquery for best_level. Sort by wins descending, then badge_count descending, then trainer.
-- Step 3: the ranking table