Challenge

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

▶ Do the exercises

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

Starter code
-- Step 1: the elite

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

Starter code
-- Step 2: gyms without a local champion

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

Starter code
-- Step 3: the ranking table
Back to the course