Challenge
The full text of this chapter, to read or revisit. To practise, do the exercises.
▶ Do the exercisesExercise 1 — New season: Step 1
A new season starts and Professor Pi needs three scripts. Each step starts from the original database (trainers and codemon only).
Step 1: the gyms. Create gyms with gym_id (automatic id), name (required and unique), city (text) and type (required text). Insert Tide Gym (Sunset Valley, water), Ember Gym (Azure City, fire) and Boulder Gym (Stone Ridge, rock), then show name, city and type sorted by name.
-- Step 1: the gyms tableExercise 2 — New season: Step 2
Step 2: the badge book.
Turn foreign key checks on and create badges with badge_id (automatic id), trainer_id (required, references trainers) and gym (required text). Insert two badges: Alex at Tide Gym and Morgan at Ember Gym. Finish by showing the trainer's name as trainer and the gym, sorted by badge_id.
-- Step 2: the badge bookExercise 3 — New season: Step 3
Step 3: the Codémon table gets a makeover.
Add an is_wild column (integer, required, default 0) to codemon, set it to 1 for the Codémons without a trainer, drop the capture_date column, then show name, type, level and is_wild with the wild ones first, then by name.
-- Step 3: the Codémon makeover