Challenge

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

β–Ά Do the exercises

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

Starter code
-- Step 1: every name, with its kind

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

Starter code
-- Step 2: battles in March, but no badge in March

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

Starter code
-- Step 3: a badge from both Azure City gyms
Back to the course