Challenge
The full text of this chapter, to read or revisit. To practise, do the exercises.
▶ Do the exercisesExercise 1 — Adoption day: Step 1
Professor Pi organizes an adoption day for wild Codémons and needs three lists.
Step 1: every trainer with the size of their team. Return the trainer's name as trainer and the number of Codémons as team_size (0 for trainers who own nothing), sorted by team_size ascending, then by trainer.
-- Step 1: team size of every trainerExercise 2 — Adoption day: Step 2
Step 2: the Codémons up for adoption are the ones without a trainer.
Write a query that returns the name, type and level of every Codémon that has no trainer, using a LEFT JOIN on trainers.
-- Step 2: Codémons up for adoptionExercise 3 — Adoption day: Step 3
Step 3: the final board shows everyone, trainers and Codémons alike.
Write a query that returns trainer (the trainer's name, or wild for a Codémon without trainer) and codemon (the Codémon's name, or looking... for a trainer without Codémon). Use a FULL OUTER JOIN and sort by trainer, then codemon.
-- Step 3: the final board