Challenge
The full text of this chapter, to read or revisit. To practise, do the exercises.
βΆ Do the exercisesExercise 1 β League directory: Step 1
Professor Pi is printing the league directory and needs three queries from you.
Step 1: one list of every name in the league. Return name and kind ('trainer', 'gym' or 'codemon') for all three tables, sorted by kind then name.
-- Step 1: every name, with its kindExercise 2 β League directory: Step 2
Step 2: the quiet gym of March.
Return the name of the gyms that hosted a battle in March 2023 (battle_date LIKE '2023-03%') but handed out no badge in March 2023 (earned_on LIKE '2023-03%'). Write two joins β gyms with their March battles, gyms with their March badges β and subtract the second from the first with EXCEPT.
-- Step 2: battles in March, but no badge in MarchExercise 3 β League directory: Step 3
Step 3: the Azure City double.
Return the name of the trainers who hold a badge from both Azure City gyms: 'Ember Gym' and 'Spark Gym'. Write one join per gym (trainers β badges β gyms, filtered on the gym's name) and keep the trainers present in both with INTERSECT. Sort by name.
-- Step 3: a badge from both Azure City gyms