⚡ Pokemon SQL Academy

Master SQL concepts through the world of Pokemon!
Designed for IB Computer Science SL & HL students.

30

Pokemon

5

Tables

20

Moves

15

Abilities

🎯 Learning Objectives

Understand relational database concepts
Write efficient SELECT queries with filtering and sorting
Master JOIN operations to combine tables
Use aggregate functions with GROUP BY and HAVING
Perform data manipulation (INSERT, UPDATE, DELETE)
Analyze query performance and optimization (HL)

🗄️ Database Setup

Run this SQL script to create your Pokemon database with all tables and sample data.

📋 pokemon_database.sql

BEGIN TRANSACTION;

CREATE TABLE types (
    id INTEGER PRIMARY KEY,
    name TEXT UNIQUE
);

INSERT INTO types (id, name) VALUES
(1,'Normal'), (2,'Fire'), (3,'Water'), (4,'Grass'), (5,'Electric'),
(6,'Ice'), (7,'Fighting'), (8,'Poison'), (9,'Ground'), (10,'Flying'),
(11,'Psychic'), (12,'Bug'), (13,'Rock'), (14,'Ghost'), (15,'Dragon');

CREATE TABLE abilities (
    id INTEGER PRIMARY KEY,
    name TEXT,
    description TEXT
);

INSERT INTO abilities (id, name, description) VALUES
(1,'Overgrow','Powers up Grass moves in a pinch.'),
(2,'Blaze','Powers up Fire moves in a pinch.'),
(3,'Torrent','Powers up Water moves in a pinch.'),
(4,'Static','Contact may paralyze foe.'),
(5,'Levitate','Immune to Ground moves.'),
(6,'Intimidate','Lowers foe''s Attack.'),
(7,'Keen Eye','Prevents accuracy loss.'),
(8,'Sturdy','Cannot be KO''d from full HP.'),
(9,'Chlorophyll','Speed doubles in sun.'),
(10,'Swift Swim','Speed doubles in rain.'),
(11,'Flash Fire','Powers up if hit by Fire.'),
(12,'Water Absorb','Heals if hit by Water.'),
(13,'Volt Absorb','Heals if hit by Electric.'),
(14,'Run Away','Easier escape from wild battles.'),
(15,'Swarm','Powers up Bug moves in a pinch.');

CREATE TABLE moves (
    id INTEGER PRIMARY KEY,
    name TEXT,
    type_id INTEGER,
    power INTEGER,
    accuracy INTEGER,
    pp INTEGER,
    FOREIGN KEY (type_id) REFERENCES types(id)
);

INSERT INTO moves (id, name, type_id, power, accuracy, pp) VALUES
(1,'Tackle',1,40,100,35), (2,'Growl',1,0,100,40),
(3,'Vine Whip',4,45,100,25), (4,'Ember',2,40,100,25),
(5,'Bubble',3,40,100,30), (6,'Scratch',1,40,100,35),
(7,'Thunder Shock',5,40,100,30), (8,'Quick Attack',1,40,100,30),
(9,'Peck',10,35,100,35), (10,'Absorb',4,20,100,25),
(11,'Water Gun',3,40,100,25), (12,'Bite',1,60,100,25),
(13,'Acid',8,40,100,30), (14,'Leech Seed',4,0,90,10),
(15,'Razor Leaf',4,55,95,25), (16,'Flamethrower',2,90,100,15),
(17,'Hydro Pump',3,110,80,5), (18,'Thunderbolt',5,90,100,15),
(19,'Psychic',11,90,100,10), (20,'Earthquake',9,100,100,10);

CREATE TABLE pokemon (
    id INTEGER PRIMARY KEY,
    name TEXT UNIQUE,
    type1_id INTEGER,
    type2_id INTEGER,
    hp INTEGER,
    attack INTEGER,
    defense INTEGER,
    sp_attack INTEGER,
    sp_defense INTEGER,
    speed INTEGER,
    FOREIGN KEY (type1_id) REFERENCES types(id),
    FOREIGN KEY (type2_id) REFERENCES types(id)
);

INSERT INTO pokemon VALUES
(1,'Bulbasaur',4,8,45,49,49,65,65,45),
(2,'Ivysaur',4,8,60,62,63,80,80,60),
(3,'Venusaur',4,8,80,82,83,100,100,80),
(4,'Charmander',2,NULL,39,52,43,60,50,65),
(5,'Charmeleon',2,NULL,58,64,58,80,65,80),
(6,'Charizard',2,10,78,84,78,109,85,100),
(7,'Squirtle',3,NULL,44,48,65,50,64,43),
(8,'Wartortle',3,NULL,59,63,80,65,80,58),
(9,'Blastoise',3,NULL,79,83,100,85,105,78),
(10,'Pikachu',5,NULL,35,55,40,50,50,90),
(11,'Raichu',5,NULL,60,90,55,90,80,110),
(12,'Pidgey',1,10,40,45,40,35,35,56),
(13,'Pidgeotto',1,10,63,60,55,50,50,71),
(14,'Pidgeot',1,10,83,80,75,70,70,101),
(15,'Ekans',8,NULL,35,60,44,40,54,55),
(16,'Arbok',8,NULL,60,95,69,65,79,80),
(17,'Sandshrew',9,NULL,50,75,85,20,30,40),
(18,'Sandslash',9,NULL,75,100,110,45,55,65),
(19,'Vulpix',2,NULL,38,41,40,50,65,65),
(20,'Ninetales',2,NULL,73,76,75,81,100,100),
(21,'Jigglypuff',1,14,115,45,20,45,25,20),
(22,'Wigglytuff',1,14,140,70,45,85,50,45),
(23,'Zubat',8,10,40,45,35,30,40,55),
(24,'Golbat',8,10,75,80,70,65,75,90),
(25,'Oddish',4,8,45,50,55,75,65,30),
(26,'Gloom',4,8,60,65,70,85,75,40),
(27,'Vileplume',4,8,75,80,85,110,90,50),
(28,'Meowth',1,NULL,40,45,35,40,40,90),
(29,'Persian',1,NULL,65,70,60,65,65,115),
(30,'Psyduck',3,NULL,50,52,48,65,50,55);

CREATE TABLE pokemon_abilities (
    pokemon_id INTEGER,
    ability_id INTEGER,
    PRIMARY KEY (pokemon_id, ability_id),
    FOREIGN KEY (pokemon_id) REFERENCES pokemon(id),
    FOREIGN KEY (ability_id) REFERENCES abilities(id)
);

INSERT INTO pokemon_abilities VALUES
(1,1),(2,1),(3,1),(4,2),(5,2),(6,2),
(7,3),(8,3),(9,3),(10,4),(11,4),(12,7),
(13,7),(14,7),(15,6),(16,6),(17,8),(18,8),
(19,11),(20,11),(21,6),(22,6),(23,5),(24,5),
(25,9),(26,9),(27,9),(28,14),(29,14),(30,10);

CREATE TABLE pokemon_moves (
    pokemon_id INTEGER,
    move_id INTEGER,
    PRIMARY KEY (pokemon_id, move_id),
    FOREIGN KEY (pokemon_id) REFERENCES pokemon(id),
    FOREIGN KEY (move_id) REFERENCES moves(id)
);

INSERT INTO pokemon_moves VALUES
(1,1),(1,2),(1,3),(1,10),(1,14),(1,15),
(2,1),(2,2),(2,3),(2,10),(2,14),(2,15),
(3,1),(3,2),(3,3),(3,10),(3,14),(3,15),
(4,1),(4,2),(4,4),(4,6),(4,16),
(5,1),(5,2),(5,4),(5,6),(5,16),
(6,1),(6,2),(6,4),(6,6),(6,16),
(7,1),(7,2),(7,5),(7,11),(7,12),(7,17),
(8,1),(8,2),(8,5),(8,11),(8,12),(8,17),
(9,1),(9,2),(9,5),(9,11),(9,12),(9,17),
(10,1),(10,2),(10,7),(10,8),(10,18),
(11,1),(11,2),(11,7),(11,8),(11,18),
(12,1),(12,8),(12,9),
(13,1),(13,8),(13,9),
(14,1),(14,8),(14,9),
(15,1),(15,12),(15,13),
(16,1),(16,12),(16,13),
(17,1),(17,6),(17,20),
(18,1),(18,6),(18,20),
(19,1),(19,4),(19,16),
(20,1),(20,4),(20,16),
(21,1),(21,2),
(22,1),(22,2),
(23,1),(23,9),(23,12),
(24,1),(24,9),(24,12),
(25,1),(25,10),(25,13),(25,14),
(26,1),(26,10),(26,13),(26,14),
(27,1),(27,10),(27,13),(27,14),
(28,1),(28,6),(28,12),
(29,1),(29,6),(29,12),
(30,1),(30,5),(30,11);

COMMIT;

📖 SL Section 1: Basic SELECT Queries

Practice retrieving data with filtering, sorting, and limiting results.

1

List all Pokemon names and their HP stats, ordered by HP from highest to lowest. Show only the top 10. (Use LIMIT 10)

SELECT ORDER BY LIMIT

SELECT name, hp
FROM Pokemon
ORDER BY hp DESC
LIMIT 10;

2

Find all Fire-type Pokemon (type1_id = 2) with attack power greater than 80.

SELECT WHERE AND

SELECT name, attack
FROM Pokemon
WHERE type1_id = 2 AND attack > 80;

3

Display all Pokemon with id between 1 and 10 (inclusive) with speed between 50 and 100 (inclusive), sorted alphabetically by name.

SELECT WHERE BETWEEN ORDER BY

SELECT name, speed
FROM Pokemon
WHERE id BETWEEN 1 AND 10 AND speed BETWEEN 50 AND 100
ORDER BY name ASC;

4

List all Pokemon whose names start with 'P' or end with 'saur'. Include their primary type and defense stats.

SELECT WHERE LIKE OR

SELECT name, type1_id, defense
FROM Pokemon
WHERE name LIKE 'P%' OR name LIKE '%saur';

5

Find all Pokemon with NULL type2_id (single-type Pokemon) that have total base stats (HP + Attack + Defense + Sp. Attack + Sp. Defense + Speed) greater than 300.

SELECT WHERE IS NULL Expressions

SELECT name, (hp + attack + defense + sp_attack + sp_defense + speed) AS total_stats
FROM Pokemon
WHERE type2_id IS NULL
AND (hp + attack + defense + sp_attack + sp_defense + speed) > 300;

🔗 SL Section 2: Joins & Advanced Filtering

Combine tables and use aggregate functions with GROUP BY and HAVING.

6

List all Pokemon with their type1 names (join with Types table). Show Pokemon name and type name.

SELECT INNER JOIN Table Aliases

SELECT p.name, t.name AS type_name
FROM Pokemon p
INNER JOIN Types t ON p.type1_id = t.id;

7

Find all Pokemon that know the ability "Overgrow". Show Pokemon name and ability name.

SELECT Multiple JOINs WHERE

SELECT p.name, a.name AS ability_name
FROM Pokemon p
JOIN pokemon_abilities pa ON p.id = pa.pokemon_id
JOIN abilities a ON pa.ability_id = a.id
WHERE a.name = 'Overgrow';

8

Count how many Pokemon exist for each type1_id. Display type1_id and count, ordered by type1_id.

SELECT COUNT() GROUP BY

SELECT type1_id, COUNT(*) AS pokemon_count
FROM Pokemon
GROUP BY type1_id
ORDER BY type1_id;

9

Find all types that have an average attack stat greater than 75. Show type name and average attack.

SELECT AVG() GROUP BY HAVING

SELECT t.name, AVG(p.attack) AS avg_attack
FROM Pokemon p
JOIN Types t ON p.type1_id = t.id
GROUP BY t.id, t.name
HAVING AVG(p.attack) > 75;

10

List all Pokemon with their type1 AND type2 names (if they have one). Use LEFT JOIN for the secondary type.

SELECT LEFT JOIN COALESCE

SELECT p.name, 
       t1.name AS type1,
       COALESCE(t2.name, 'None') AS type2
FROM Pokemon p
JOIN Types t1 ON p.type1_id = t1.id
LEFT JOIN Types t2 ON p.type2_id = t2.id;

11

Find the maximum, minimum, and average HP for each type1_id. Only show types with more than 3 Pokemon.

SELECT MAX() MIN() AVG() HAVING

SELECT type1_id,
       MAX(hp) AS max_hp,
       MIN(hp) AS min_hp,
       ROUND(AVG(hp), 2) AS avg_hp
FROM Pokemon
GROUP BY type1_id
HAVING COUNT(*) > 3;

12

List all Electric-type moves with power greater than 50. Show move name, power, and type name. (Note: Electric type_id = 5)

SELECT JOIN WHERE

SELECT m.name, m.power, t.name AS type_name
FROM Moves m
JOIN Types t ON m.type_id = t.id
WHERE t.id = 5 AND m.power > 50;

✏️ SL Section 3: Updating Data

Practice INSERT, UPDATE, and DELETE operations.

13

Insert a new Pokemon: "Dratini" with ID 147, Dragon type (type1_id 15), HP 41, Attack 64, Defense 45, Speed 50. (Note: Dragon type has id=15 in the types table)

INSERT INTO VALUES

INSERT INTO Pokemon (id, name, type1_id, hp, attack, defense, speed)
VALUES (147, 'Dratini', 15, 41, 64, 45, 50);

14

Update all Pokemon with id less than 10: increase their speed by 5 points.

UPDATE SET WHERE

UPDATE Pokemon
SET speed = speed + 5
WHERE id <= 10;

15

Delete all Pokemon with HP less than 30. Then write a query to verify how many were deleted.

DELETE WHERE Transaction

-- First, check what will be deleted
SELECT COUNT(*) FROM Pokemon WHERE hp < 30;

-- Then delete
DELETE FROM Pokemon
WHERE hp < 30;

🏗️ HL Section A: DDL vs DML

Understand Data Definition Language vs Data Manipulation Language.

A1

Write a DDL statement to add a new column "catch_rate" (INTEGER) to the Pokemon table. Then write a DML statement to set all catch rates to 45.

ALTER TABLE ADD COLUMN UPDATE DDL vs DML

-- DDL: Data Definition Language
ALTER TABLE Pokemon
ADD COLUMN catch_rate INTEGER;

-- DML: Data Manipulation Language
UPDATE Pokemon
SET catch_rate = 45;

A2

Create a new table "Evolutions" with columns: evolution_id (PK), pokemon_id (FK), evolved_form_name, level_required. Include appropriate constraints.

CREATE TABLE PRIMARY KEY FOREIGN KEY Constraints

CREATE TABLE Evolutions (
    id INT PRIMARY KEY,
    pokemon_id INT NOT NULL,
    evolved_form_name VARCHAR(30) NOT NULL,
    level_required INT CHECK (level_required > 0),
    FOREIGN KEY (pokemon_id) REFERENCES Pokemon(id)
);

🔍 HL Section B: Advanced Queries

Complex joins, subqueries, and advanced SQL techniques.

B1

Find all Pokemon that know 2 or more abilities. Show Pokemon name and ability count. (Note: This is a theoretical query using HAVING. Currently no Pokemon in the dataset have 2+ abilities, but the query demonstrates proper SQL syntax.)

Subquery GROUP BY HAVING

SELECT p.name, COUNT(pa.ability_id) AS ability_count
FROM Pokemon p
JOIN pokemon_abilities pa ON p.id = pa.pokemon_id
GROUP BY p.id, p.name
HAVING COUNT(pa.ability_id) >= 2;

B2

Find the Pokemon with the highest attack stat using a subquery (do not use ORDER BY + LIMIT).

Subquery MAX()

SELECT name, attack
FROM Pokemon
WHERE attack = (SELECT MAX(attack) FROM Pokemon);

B3

List all types that have no Pokemon using them as type1. Use a subquery with NOT IN or NOT EXISTS.

Subquery NOT IN NOT EXISTS

-- Using NOT IN
SELECT name
FROM Types
WHERE id NOT IN (SELECT DISTINCT type1_id FROM Pokemon);

-- Alternative using NOT EXISTS
SELECT name
FROM Types t
WHERE NOT EXISTS (
    SELECT 1 FROM Pokemon p WHERE p.type1_id = t.id
);

⚡ HL Section C: Performance & Optimization

Analyze query performance and optimization strategies.

C1

Write a query to find Pokemon by name 'Pikachu' and explain why an index on the 'name' column would improve performance.

SELECT WHERE INDEX

-- Query
SELECT * FROM Pokemon WHERE name = 'Pikachu';

-- Create index for better performance
CREATE INDEX idx_pokemon_name ON Pokemon(name);

Explanation: Without an index, the database must perform a full table scan (O(n)). With an index, it uses a B-tree structure for O(log n) lookup time, significantly faster for large tables.

C2

Compare the performance of these two queries and explain which is faster:
Query A: SELECT * FROM Pokemon WHERE id = 25
Query B: SELECT name, attack FROM Pokemon WHERE type1_id = 2

Performance Indexing Query Optimization

Query A is faster because:

id is the PRIMARY KEY (automatically indexed)
Uses unique index lookup: O(log n) time
Returns only 1 row

Query B:

type1_id may not be indexed
May require full table scan: O(n) time
Returns multiple rows (all Fire types)

💡 Teaching moment: If you added an index on type1_id (CREATE INDEX idx_type ON Pokemon(type1_id)), Query B would improve dramatically from O(n) to O(log n)!

C3

Explain why denormalization might be useful in a read-heavy Pokemon database, and provide an example of when you would use it.

Denormalization Performance Trade-offs

Denormalization reduces the number of JOINs needed, improving read performance at the cost of:

Increased storage space
Data redundancy
More complex updates (must update multiple places)

Example: Add a redundant "type1_name" column directly to the Pokemon table to avoid JOINing with Types table for every query.

-- Denormalized table structure
ALTER TABLE Pokemon ADD COLUMN type1_name VARCHAR(20);

-- Now this query needs no JOIN
SELECT name, type1_name FROM Pokemon;

🔐 Answer Key

Teachers: Enter the password below to unlock all answers at once.

🔒 Password Protected

Enter password to reveal all answers: