Challenge

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

▶ Do the exercises

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

Starter code
-- Step 1: team size of every trainer

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

Starter code
-- Step 2: Codémons up for adoption

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

Starter code
-- Step 3: the final board
Back to the course