CREATE TABLE PokemonSpecies (
pokedex_number INTEGER PRIMARY KEY,
name TEXT NOT NULL,
description TEXT,
height REAL, /* cm */
weight REAL, /* kg */
type1 TEXT NOT NULL, /* must have at least one type */
type2 TEXT,
base_hp INTEGER,
base_attack INTEGER,
base_defense INTEGER,
base_special_attack INTEGER,
base_special_defense INTEGER,
base_speed INTEGER,
evolution_stage INTEGER, /* 0 for base, 1 for stage 1, 2 for stage 2 */
evolves_from INTEGER REFERENCES PokemonSpecies(pokedex_number)
);
CREATE TABLE Abilities (
ability_id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL,
effect TEXT
);
CREATE TABLE PokemonAbilities (
pokedex_number INTEGER NOT NULL,
ability_id INTEGER NOT NULL,
is_hidden BOOLEAN,
PRIMARY KEY (pokedex_number, ability_id),
FOREIGN KEY (pokedex_number) REFERENCES PokemonSpecies(pokedex_number),
FOREIGN KEY (ability_id) REFERENCES Abilities(ability_id)
);
CREATE TABLE Moves (
move_id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL,
type TEXT NOT NULL,
category TEXT, /* physical, special, or status */
power INTEGER,
accuracy INTEGER,
pp INTEGER,
effect TEXT
);
CREATE TABLE PokemonMoves (
pokedex_number INTEGER NOT NULL,
move_id INTEGER NOT NULL,
method TEXT, /* from levelling up, TM, breeding, etc. */
level INTEGER, /* if applicable */
PRIMARY KEY (pokedex_number, move_id),
FOREIGN KEY (pokedex_number) REFERENCES PokemonSpecies(pokedex_number),
FOREIGN KEY (move_id) REFERENCES Moves(move_id)
);
CREATE TABLE Trainers (
trainer_id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL,
money INTEGER DEFAULT 0,
current_region TEXT,
pokeballs INTEGER DEFAULT 5 /* default 5 at start of game */
);
/* trainer's Pokemon party */
CREATE TABLE TrainerPokemon (
trainer_id INTEGER NOT NULL,
pokedex_number INTEGER NOT NULL,
nickname TEXT,
level INTEGER DEFAULT 5,
current_hp INTEGER,
status_condition TEXT, /* poisoned, burned, etc. */
PRIMARY KEY (trainer_id, pokedex_number),
FOREIGN KEY (trainer_id) REFERENCES Trainers(trainer_id),
FOREIGN KEY (pokedex_number) REFERENCES PokemonSpecies(pokedex_number)
);
/* gym badges */
CREATE TABLE Badges (
badge_id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL,
region TEXT NOT NULL
);
/* badges trainer has earned */
CREATE TABLE TrainerBadges (
trainer_id INTEGER NOT NULL,
badge_id INTEGER NOT NULL,
date_earned DATE,
PRIMARY KEY (trainer_id, badge_id),
FOREIGN KEY (trainer_id) REFERENCES Trainers(trainer_id),
FOREIGN KEY (badge_id) REFERENCES Badges(badge_id)
);
CREATE TABLE Items (
item_id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL,
effect TEXT,
cost INTEGER
);
/* items owned by trainers */
CREATE TABLE TrainerItems (
trainer_id INTEGER NOT NULL,
item_id INTEGER NOT NULL,
quantity INTEGER DEFAULT 0,
PRIMARY KEY (trainer_id, item_id),
FOREIGN KEY (trainer_id) REFERENCES Trainers(trainer_id),
FOREIGN KEY (item_id) REFERENCES Items(item_id)
);
/* gym battle scenarios
*/
CREATE TABLE GymBattles (
battle_id INTEGER PRIMARY KEY AUTOINCREMENT,
trainer_id INTEGER NOT NULL,
gym_leader_id INTEGER NOT NULL, /* gym leader considered trainer */
battle_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
result TEXT, /* Win or Los */
FOREIGN KEY (trainer_id) REFERENCES Trainers(trainer_id),
FOREIGN KEY (gym_leader_id) REFERENCES Trainers(trainer_id)
);
INSERT INTO PokemonSpecies (pokedex_number, name, description, height, weight, type1, type2, base_hp, base_attack, base_defense, base_special_attack, base_special_defense, base_speed, evolution_stage, evolves_from)
VALUES
(1, 'Bulbasaur', 'Seed Pokémon', 7.0, 6.9, 'Grass', 'Poison', 45, 49, 49, 65, 65, 45, 0, NULL),
(2, 'Ivysaur', 'Seed Pokémon', 10.0, 13.0, 'Grass', 'Poison', 60, 62, 63, 80, 80, 60, 1, 1),
(3, 'Venusaur', 'Seed Pokémon', 20.0, 100.0, 'Grass', 'Poison', 80, 82, 83, 100, 100, 80, 2, 2),
(4, 'Charmander', 'Lizard Pokémon', 6.0, 8.5, 'Fire', 'Flying', 39, 52, 43, 60, 50, 65, 0, NULL),
(5, 'Charmeleon', 'Flame Pokémon', 11.0, 19.0, 'Fire', NULL, 58, 64, 58, 80, 65, 80, 1, 4),
(6, 'Charizard', 'Fire Pokémon', 17.0, 90.5, 'Fire', 'Flying', 78, 84, 78, 109, 85, 100, 2, 5),
(7, 'Squirtle', 'Tiny Turtle Pokémon', 5.0, 9.0, 'Water', 'Bug', 44, 48, 65, 50, 64, 43, 0, NULL),
(8, 'Wartortle', 'Turtle Pokémon', 10.0, 22.5, 'Water', NULL, 59, 63, 80, 65, 80, 58, 1, 7),
(9, 'Blastoise', 'Shellfish Pokémon', 16.0, 85.5, 'Water', NULL, 79, 83, 100, 85, 105, 78, 2, 8),
(10, 'Caterpie', 'Worm Pokémon', 3.0, 2.9, 'Bug', NULL, 45, 30, 35, 20, 20, 45, 0, NULL),
(11, 'Metapod', 'Cocoon Pokémon', 7.0, 9.9, 'Bug', NULL, 50, 20, 55, 25, 25, 30, 1, 10),
(12, 'Butterfree', 'Butterfly Pokémon', 11.0, 32.0, 'Bug', 'Flying', 60, 45, 50, 90, 80, 70, 2, 11),
(13, 'Weedle', 'Hairy Pokémon', 3.0, 3.2, 'Bug', 'Poison', 40, 35, 30, 20, 20, 50, 0, NULL),
(14, 'Kakuna', 'Cocoon Pokémon', 6.0, 10.0, 'Bug', 'Poison', 45, 25, 50, 25, 25, 35, 1, 13),
(15, 'Beedrill', 'Poison Bee Pokémon', 10.0, 29.5, 'Bug', 'Poison', 65, 90, 40, 45, 80, 75, 2, 14),
(16, 'Pidgey', 'Tiny Bird Pokémon', 3.0, 1.8, 'Normal', 'Flying', 40, 45, 40, 35, 35, 56, 0, NULL),
(17, 'Pidgeotto', 'Bird Pokémon', 11.0, 30.0, 'Normal', 'Flying', 63, 60, 55, 50, 50, 71, 1, 16),
(18, 'Pidgeot', 'Bird Pokémon', 15.0, 39.5, 'Normal', 'Flying', 83, 80, 75, 70, 70, 101, 2, 17),
(19, 'Rattata', 'Mouse Pokémon', 3.0, 3.5, 'Normal', NULL, 30, 56, 35, 25, 35, 72, 0, NULL),
(20, 'Raticate', 'Mouse Pokémon', 7.0, 18.5, 'Normal', NULL, 55, 81, 60, 50, 70, 97, 1, 19),
(21, 'Spearow', 'Tiny Bird Pokémon', 3.0, 2.0, 'Normal', 'Flying', 40, 60, 30, 31, 31, 70, 0, NULL),
(22, 'Fearow', 'Beak Pokémon', 12.0, 38.0, 'Normal', 'Flying', 65, 90, 65, 61, 61, 100, 1, 21);
INSERT INTO Abilities (name, effect)
VALUES
('Overgrow', 'Increases Grass-type moves when HP is low.'),
('Chlorophyll', 'Raises Speed in sunshine.'),
('Blaze', 'Increases Fire-type moves when HP is low.'),
('Torrent', 'Increases Water-type moves when HP is low.'),
('Run Away', 'Can always flee from wild Pokémon.'),
('Keen Eye', 'Prevents loss of accuracy.'),
('Intimidate', 'Lowers the opponent''s Attack.'),
('Shell Armor', 'Protects against critical hits.'),
('Shield Dust', 'Protects against extra effects.'),
('Compound Eyes', 'Raises accuracy.'),
('Illusion', 'Takes the appearance of a Pokémon it was sent out with.'),
('Levitate', 'Gives immunity to Ground-type moves and effects.'),
('Swarm', 'Increases Bug-type moves when HP is low.'),
('Rivalry', 'Increases damage against Pokémon of the same gender.'),
('Guts', 'Raises Attack if there is a status problem.'),
('Huge Power', 'Doubles Attack stat.'),
('Poison Point', 'May poison attacker on contact.'),
('Early Bird', 'Reduces duration of sleep.'),
('Rock Head', 'Prevents recoil damage.'),
('Sturdy', 'Prevents one-hit KO moves from taking effect.'),
('Clear Body', 'Prevents other Pokémon from lowering stats.'),
('Lightning Rod', 'Draws Electric moves to this Pokémon.'),
('Serene Grace', 'Doubles likelihood of extra effects occurring.'),
('Hyper Cutter', 'Prevents Attack reduction.'),
('Unaware', 'Ignores opponent''s Attack and Special Attack boosts.'),
('Insomnia', 'Prevents sleep.'),
('Cute Charm', 'May infatuate attacker on contact.'),
('Limber', 'Prevents paralysis.'),
('Pickup', 'May pick up items.'),
('Truant', 'Can only attack every other turn.'),
('Hustle', 'Raises Attack, but lowers accuracy.'),
('Cute Charm', 'May infatuate attacker on contact.');
INSERT INTO PokemonAbilities (pokedex_number, ability_id, is_hidden)
VALUES
(1, 1, 0),
(1, 20, 1),
(2, 1, 0),
(2, 20, 1),
(3, 1, 0),
(3, 20, 1),
(4, 3, 0),
(4, 21, 1),
(5, 3, 0),
(5, 21, 1),
(6, 3, 0),
(6, 21, 1),
(7, 4, 0),
(7, 22, 1),
(8, 4, 0),
(8, 22, 1),
(9, 4, 0),
(9, 22, 1),
(10, 5, 0),
(10, 6, 1),
(11, 5, 0),
(11, 6, 1),
(12, 5, 0),
(12, 6, 1),
(13, 7, 0),
(13, 8, 1),
(14, 7, 0),
(14, 8, 1),
(15, 7, 0),
(15, 8, 1),
(16, 9, 0),
(16, 10, 1),
(17, 9, 0),
(17, 10, 1),
(18, 9, 0),
(18, 10, 1),
(19, 11, 0),
(19, 12, 1),
(20, 11, 0),
(20, 12, 1),
(21, 13, 0),
(21, 14, 1),
(22, 13, 0),
(22, 14, 1);
INSERT INTO Moves (name, type, category, power, accuracy, pp, effect)
VALUES
('Tackle', 'Normal', 'Physical', 50, 100, 35, 'No additional effect.'),
('Growl', 'Normal', 'Status', 0, 100, 40, 'Lowers the opponent''s Attack.'),
('Vine Whip', 'Grass', 'Physical', 45, 100, 25, 'No additional effect.'),
('Razor Leaf', 'Grass', 'Physical', 55, 95, 25, 'High critical hit ratio.'),
('Take Down', 'Normal', 'Physical', 90, 85, 20, 'User recoils 1/4 of the damage inflicted.'),
('Double-Edge', 'Normal', 'Physical', 120, 100, 15, 'User recoils 1/3 of the damage inflicted.'),
('Ember', 'Fire', 'Special', 40, 100, 25, '10% chance to burn the target.'),
('Flamethrower', 'Fire', 'Special', 95, 100, 15, '10% chance to burn the target.'),
('Water Gun', 'Water', 'Special', 40, 100, 25, 'No additional effect.'),
('Water Pulse', 'Water', 'Special', 60, 100, 20, '10% chance to confuse the target.'),
('Bubble', 'Water', 'Special', 20, 100, 30, '10% chance to lower the target''s Speed.'),
('String Shot', 'Bug', 'Status', 0, 95, 40, 'Lowers the target''s Speed.'),
('Poison Sting', 'Poison', 'Physical', 15, 100, 35, '10% chance to poison the target.'),
('Bug Bite', 'Bug', 'Physical', 60, 100, 20, '30% chance to make the target flinch.'),
('Leech Life', 'Bug', 'Physical', 20, 100, 15, 'User recovers 1/2 the damage dealt.'),
('Gust', 'Flying', 'Special', 40, 100, 35, '10% chance to make the target flinch.'),
('Wing Attack', 'Flying', 'Physical', 60, 100, 35, 'No additional effect.'),
('Peck', 'Flying', 'Physical', 35, 100, 35, 'No additional effect.'),
('Quick Attack', 'Normal', 'Physical', 40, 100, 30, 'Always goes first.'),
('Pursuit', 'Dark', 'Physical', 40, 100, 20, 'If the target switches out, inflicts double damage.'),
('Scratch', 'Normal', 'Physical', 40, 100, 35, 'No additional effect.'),
('Bite', 'Dark', 'Physical', 60, 100, 25, '30% chance to make the target flinch.'),
('Poison Tail', 'Poison', 'Physical', 50, 100, 25, '50% chance to poison the target.'),
('Thunder Shock', 'Electric', 'Special', 40, 100, 30, '10% chance to paralyze the target.'),
('Thunderbolt', 'Electric', 'Special', 95, 100, 15, '10% chance to paralyze the target.'),
('Drill Peck', 'Flying', 'Physical', 80, 100, 20, 'No additional effect.'),
('Submission', 'Fighting', 'Physical', 80, 85, 25, 'User takes 1/4 of the damage inflicted.'),
('Low Kick', 'Fighting', 'Physical', 50, 100, 20, 'Power doubles if the target is heavier.'),
('Karate Chop', 'Fighting', 'Physical', 50, 100, 25, 'High critical hit ratio.'),
('Confusion', 'Psychic', 'Special', 50, 100, 25, '10% chance to confuse the target.'),
('Psybeam', 'Psychic', 'Special', 65, 100, 20, '10% chance to confuse the target.'),
('Barrier', 'Psychic', 'Status', 0, 100, 25, 'Raises the user''s Defense.'),
('Agility', 'Psychic', 'Status', 0, 100, 30, 'Maximizes the user''s Speed.'),
('Teleport', 'Psychic', 'Status', 0, 100, 20, 'User escapes from battle.'),
('Night Shade', 'Ghost', 'Special', 0, 100, 15, 'Damages target based on the user''s level.'),
('Leer', 'Normal', 'Status', 0, 100, 30, 'Lowers the target''s Defense.'),
('Focus Energy', 'Normal', 'Status', 0, 100, 30, 'Raises the user''s critical hit ratio.'),
('Horn Attack', 'Normal', 'Physical', 65, 100, 25, 'No additional effect.'),
('Fury Attack', 'Normal', 'Physical', 15, 85, 20, 'Hits 2-5 times.'),
('Endure', 'Normal', 'Status', 0, 100, 10, 'User survives with at least 1 HP if it would be a KO.'),
('Attract', 'Normal', 'Status', 0, 100, 15, 'Infatuates the target if it is the opposite gender.'),
('Rest', 'Psychic', 'Status', 0, 100, 10, 'User sleeps for 2 turns and restores HP and status.'),
('Rock Slide', 'Rock', 'Physical', 75, 90, 10, '10% chance to make the target flinch.'),
('Rock Throw', 'Rock', 'Physical', 50, 90, 15, 'High critical hit ratio.'),
('Rock Tomb', 'Rock', 'Physical', 60, 95, 15, 'Lowers the target''s Speed.'),
('Ancient Power', 'Rock', 'Special', 60, 100, 5, '10% chance to raise all of the user''s stats.'),
('Earthquake', 'Ground', 'Physical', 100, 100, 10, 'No additional effect.'),
('Dig', 'Ground', 'Physical', 80, 100, 10, 'User goes underground on first turn, then attacks.'),
('Cut', 'Normal', 'Physical', 50, 95, 30, 'No additional effect.'),
('Slash', 'Normal', 'Physical', 70, 100, 20, 'High critical hit ratio.'),
('Sand Attack', 'Ground', 'Status', 0, 100, 15, 'Lowers the target''s accuracy.'),
('Poison Gas', 'Poison', 'Status', 0, 90, 40, 'Badly poisons the target.'),
('Bind', 'Normal', 'Physical', 15, 85, 20, 'Prevents the target from moving for 2-5 turns.'),
('Slam', 'Normal', 'Physical', 80, 75, 20, 'No additional effect.'),
('Constrict', 'Normal', 'Physical', 10, 100, 35, '33% chance to lower the target''s Speed.'),
('Wrap', 'Normal', 'Physical', 15, 90, 20, 'Prevents the target from moving for 2-5 turns.'),
('Swift', 'Normal', 'Special', 60, 0, 20, 'Never misses.'),
('Skull Bash', 'Normal', 'Physical', 100, 100, 15, 'User raises Defense on first turn, then attacks.'),
('Tackle', 'Normal', 'Physical', 50, 100, 35, 'No additional effect.');
INSERT INTO PokemonMoves (pokedex_number, move_id, method, level)
VALUES
(1, 1, 'Level Up', 1),
(1, 2, 'Level Up', 1),
(1, 3, 'Level Up', 7),
(1, 4, 'Level Up', 20),
(1, 5, 'Level Up', 32),
(2, 1, 'Level Up', 1),
(2, 2, 'Level Up', 1),
(2, 3, 'Level Up', 1),
(2, 4, 'Level Up', 1),
(2, 5, 'Level Up', 24),
(2, 6, 'Level Up', 36),
(3, 1, 'Level Up', 1),
(3, 2, 'Level Up', 1),
(3, 3, 'Level Up', 1),
(3, 4, 'Level Up', 1),
(3, 5, 'Level Up', 27),
(3, 6, 'Level Up', 40),
(4, 7, 'Level Up', 1),
(4, 8, 'Level Up', 7),
(4, 9, 'Level Up', 13),
(4, 10, 'Level Up', 19),
(4, 11, 'Level Up', 25),
(5, 7, 'Level Up', 1),
(5, 8, 'Level Up', 1),
(5, 9, 'Level Up', 1),
(5, 10, 'Level Up', 1),
(5, 11, 'Level Up', 17),
(5, 12, 'Level Up', 29),
(6, 7, 'Level Up', 1),
(6, 8, 'Level Up', 1),
(6, 9, 'Level Up', 1),
(6, 10, 'Level Up', 1),
(6, 11, 'Level Up', 19),
(6, 12, 'Level Up', 37),
(7, 13, 'Level Up', 1),
(7, 14, 'Level Up', 7),
(7, 15, 'Level Up', 13),
(7, 16, 'Level Up', 19),
(7, 17, 'Level Up', 25),
(8, 13, 'Level Up', 1),
(8, 14, 'Level Up', 1),
(8, 15, 'Level Up', 1),
(8, 16, 'Level Up', 1),
(8, 17, 'Level Up', 25),
(8, 18, 'Level Up', 37),
(9, 13, 'Level Up', 1),
(9, 14, 'Level Up', 1),
(9, 15, 'Level Up', 1),
(9, 16, 'Level Up', 1),
(9, 17, 'Level Up', 31),
(9, 18, 'Level Up', 43),
(10, 19, 'Level Up', 1),
(10, 20, 'Level Up', 7),
(10, 21, 'Level Up', 13),
(11, 22, 'Level Up', 1),
(11, 23, 'Level Up', 1),
(12, 24, 'Level Up', 1),
(12, 25, 'Level Up', 7),
(12, 26, 'Level Up', 13),
(12, 27, 'Level Up', 19),
(13, 28, 'Level Up', 1),
(13, 29, 'Level Up', 7),
(13, 30, 'Level Up', 13),
(14, 31, 'Level Up', 1),
(14, 32, 'Level Up', 1),
(15, 33, 'Level Up', 1),
(15, 34, 'Level Up', 7),
(15, 35, 'Level Up', 13),
(15, 36, 'Level Up', 19),
(16, 37, 'Level Up', 1),
(16, 38, 'Level Up', 7),
(16, 39, 'Level Up', 13),
(16, 40, 'Level Up', 19),
(17, 37, 'Level Up', 1),
(17, 38, 'Level Up', 1),
(17, 39, 'Level Up', 1),
(17, 40, 'Level Up', 27),
(17, 41, 'Level Up', 41),
(18, 37, 'Level Up', 1),
(18, 38, 'Level Up', 1),
(18, 39, 'Level Up', 1),
(18, 40, 'Level Up', 35),
(18, 41, 'Level Up', 53),
(19, 42, 'Level Up', 1),
(19, 43, 'Level Up', 5),
(19, 44, 'Level Up', 9),
(20, 42, 'Level Up', 1),
(20, 43, 'Level Up', 1),
(20, 44, 'Level Up', 1),
(20, 45, 'Level Up', 29),
(21, 46, 'Level Up', 1),
(21, 47, 'Level Up', 7),
(21, 48, 'Level Up', 13),
(22, 46, 'Level Up', 1),
(22, 47, 'Level Up', 1),
(22, 48, 'Level Up', 1),
(22, 49, 'Level Up', 27),
(22, 50, 'Level Up', 41);
INSERT INTO Trainers (name, money, current_region, pokeballs)
VALUES
('Ash Ketchum', 5000, 'Kanto', 10),
('Misty', 3000, 'Kanto', 8),
('Brock', 4000, 'Kanto', 6),
('Gary Oak', 6000, 'Kanto', 12),
('Lance', 7000, 'Johto', 9),
('Cynthia', 8000, 'Sinnoh', 11),
('Iris', 4500, 'Unova', 7),
('Alain', 5500, 'Kalos', 10),
('Serena', 4800, 'Kalos', 8),
('Steven Stone', 6500, 'Hoenn', 12),
('May', 4200, 'Hoenn', 6),
('Drew', 4600, 'Hoenn', 9);
INSERT INTO TrainerPokemon (trainer_id, pokedex_number, nickname, level, current_hp, status_condition)
VALUES
(1, 4, 'Charmander', 10, 40, 'Healthy'),
(1, 7, 'Squirtle', 8, 35, 'Poisoned'),
(1, 10, 'Caterpie', 6, 25, 'Burned'),
(1, 13, 'Weedle', 7, 30, 'Healthy'),
(1, 16, 'Pidgey', 9, 42, 'Paralyzed'),
(2, 1, 'Bulbasaur', 5, 28, 'Asleep'),
(2, 19, 'Rattata', 4, 18, 'Frozen'),
(3, 6, 'Charmeleon', 12, 55, 'Healthy'),
(3, 9, 'Blastoise', 11, 68, 'Burned'),
(4, 15, 'Beedrill', 10, 50, 'Poisoned'),
(4, 18, 'Pidgeot', 9, 65, 'Paralyzed'),
(5, 2, 'Ivysaur', 8, 45, 'Healthy'),
(5, 5, 'Charmeleon', 7, 48, 'Asleep'),
(5, 8, 'Wartortle', 6, 40, 'Frozen'),
(6, 11, 'Metapod', 5, 22, 'Healthy'),
(7, 12, 'Butterfree', 10, 50, 'Poisoned'),
(7, 17, 'Pidgeotto', 9, 58, 'Paralyzed'),
(8, 14, 'Kakuna', 7, 35, 'Healthy'),
(8, 21, 'Spearow', 6, 30, 'Burned'),
(9, 3, 'Venusaur', 12, 75, 'Healthy'),
(9, 20, 'Raticate', 11, 60, 'Paralyzed'),
(10, 1, 'Bulbasaur', 9, 40, 'Asleep'),
(10, 13, 'Weedle', 8, 32, 'Frozen'),
(11, 6, 'Charizard', 10, 70, 'Healthy'),
(11, 9, 'Blastoise', 9, 65, 'Burned');
INSERT INTO Badges (name, region)
VALUES
('Boulder Badge', 'Kanto'),
('Cascade Badge', 'Kanto'),
('Thunder Badge', 'Kanto'),
('Rainbow Badge', 'Kanto'),
('Soul Badge', 'Johto'),
('Marsh Badge', 'Johto'),
('Mineral Badge', 'Johto'),
('Glacier Badge', 'Johto'),
('Zephyr Badge', 'Hoenn'),
('Heat Badge', 'Hoenn'),
('Balance Badge', 'Hoenn'),
('Feather Badge', 'Sinnoh'),
('Mine Badge', 'Sinnoh'),
('Icicle Badge', 'Sinnoh'),
('Relic Badge', 'Unova'),
('Legend Badge', 'Kalos'),
('Hive Badge', 'Alola'),
('Volcano Badge', 'Alola');
INSERT INTO TrainerBadges (trainer_id, badge_id, date_earned)
VALUES
(1, 1, '2023-04-10'),
(1, 2, '2023-04-12'),
(1, 3, '2023-04-15'),
(1, 4, '2023-04-20'),
(2, 1, '2023-03-15'),
(3, 2, '2023-02-20'),
(4, 1, '2023-04-05'),
(4, 2, '2023-04-10'),
(4, 3, '2023-04-18'),
(4, 4, '2023-04-25'),
(4, 5, '2023-05-02'),
(5, 6, '2023-03-10'),
(5, 7, '2023-03-20'),
(6, 8, '2023-02-15'),
(6, 9, '2023-02-25'),
(7, 15, '2023-04-10'),
(8, 16, '2023-03-20'),
(9, 17, '2023-04-05'),
(10, 10, '2023-02-10'),
(10, 11, '2023-02-20'),
(10, 12, '2023-03-05'),
(11, 13, '2023-04-15'),
(12, 14, '2023-03-25');
INSERT INTO Items (name, effect, cost)
VALUES
('Potion', 'Restores 20 HP', 300),
('Super Potion', 'Restores 50 HP', 700),
('Hyper Potion', 'Restores 200 HP', 1200),
('Full Restore', 'Fully restores HP and heals status conditions', 3000),
('Antidote', 'Cures poison', 100),
('Paralyze Heal', 'Cures paralysis', 200),
('Burn Heal', 'Cures burn', 250),
('Ice Heal', 'Cures freeze', 250),
('Repel', 'Repels wild Pokémon for a while', 350),
('Super Repel', 'Repels wild Pokémon for a longer duration', 500),
('Max Repel', 'Repels wild Pokémon for an even longer duration', 700),
('Escape Rope', 'Escapes from a cave or dungeon instantly', 550),
('Bike', 'Speeds up travel on land', 1000),
('Surfboard', 'Allows surfing on water', 1500),
('Dive Goggles', 'Allows diving underwater', 800),
('Poké Flute', 'Wakes up sleeping Pokémon', 900);
INSERT INTO TrainerItems (trainer_id, item_id, quantity)
VALUES
(1, 1, 5),
(1, 2, 3),
(1, 3, 1),
(1, 4, 1),
(1, 5, 2),
(1, 6, 1),
(1, 7, 1),
(1, 8, 3),
(1, 9, 2),
(2, 1, 3),
(2, 4, 1),
(2, 5, 1),
(2, 8, 2),
(3, 2, 2),
(3, 6, 1),
(3, 9, 1),
(3, 11, 1),
(4, 1, 10),
(4, 3, 2),
(4, 4, 1),
(4, 7, 2),
(4, 10, 3),
(4, 12, 1),
(4, 13, 1),
(5, 2, 5),
(5, 5, 2),
(5, 8, 1),
(5, 11, 1),
(6, 3, 3),
(6, 6, 2),
(6, 9, 2),
(6, 14, 1),
(7, 1, 2),
(7, 4, 1),
(7, 10, 2),
(8, 2, 4),
(8, 7, 1),
(8, 11, 1),
(8, 13, 1),
(9, 1, 3),
(9, 5, 2),
(9, 8, 1),
(10, 3, 2),
(10, 7, 1),
(10, 12, 1),
(10, 14, 1),
(11, 1, 4),
(11, 6, 2),
(11, 10, 3),
(12, 2, 3),
(12, 5, 1),
(12, 9, 2),
(12, 13, 1);
INSERT INTO GymBattles (trainer_id, gym_leader_id, battle_date, result)
VALUES
(1, 3, '2023-04-11 15:00:00', 'Win'),
(1, 2, '2023-04-15 09:30:00', 'Win'),
(1, 12, '2023-04-20 14:00:00', 'Win'),
(2, 3, '2023-03-10 10:00:00', 'Loss'),
(2, 5, '2023-03-25 16:00:00', 'Win'),
(3, 2, '2023-02-18 11:30:00', 'Loss'),
(4, 3, '2023-04-08 08:00:00', 'Win'),
(4, 2, '2023-04-12 14:30:00', 'Win'),
(8, 12, '2023-03-20 14:30:00', 'Win'),
(10, 11, '2023-02-08 12:00:00', 'Win'),
(12, 11, '2023-03-20 11:30:00', 'Win'),
(12, 10, '2023-03-25 15:00:00', 'Win');
-- Assuming gym leaders can be identified if they have hosted a gym battle
-- WITH GymLeaderInfo AS (
-- SELECT
-- G.gym_leader_id AS trainer_id,
-- AVG(TP.level) AS avg_level
-- FROM
-- TrainerPokemon TP
-- JOIN
-- GymBattles G ON TP.trainer_id = G.gym_leader_id
-- GROUP BY
-- G.gym_leader_id
-- ),
-- TrainerInfo AS (
-- SELECT
-- T.trainer_id,
-- T.name,
-- AVG(TP.level) AS avg_level
-- FROM
-- Trainers T
-- JOIN
-- TrainerPokemon TP ON T.trainer_id = TP.trainer_id
-- WHERE
-- T.trainer_id NOT IN (SELECT gym_leader_id FROM GymBattles) -- Ensure trainers are not gym leaders themselves
-- GROUP BY
-- T.trainer_id
-- )
-- SELECT
-- TI.name AS Trainer_Name,
-- TI.avg_level AS Trainer_Avg_Level,
-- GLI.avg_level AS Gym_Leader_Avg_Level
-- FROM
-- TrainerInfo TI
-- JOIN
-- GymLeaderInfo GLI ON TI.avg_level >= GLI.avg_level + 5;
-- WITH MoveTypeVariety AS (
-- SELECT
-- PS.name AS Pokemon_Name,
-- PS.pokedex_number,
-- COUNT(DISTINCT M.type) AS Unique_Move_Types
-- FROM
-- PokemonSpecies PS
-- JOIN
-- PokemonMoves PM ON PS.pokedex_number = PM.pokedex_number
-- JOIN
-- Moves M ON PM.move_id = M.move_id
-- GROUP BY
-- PS.pokedex_number
-- )
-- SELECT
-- Pokemon_Name,
-- Unique_Move_Types
-- FROM
-- MoveTypeVariety
-- WHERE
-- Unique_Move_Types = (SELECT MAX(Unique_Move_Types) FROM MoveTypeVariety)
-- ORDER BY Pokemon_Name;
-- WITH EvolutionsTrainer AS (
-- SELECT
-- T.name AS Trainer_Name,
-- PS1.name AS Base_Form,
-- PS2.name AS Evolved_Form,
-- TP.level AS Level_At_Evolution
-- FROM
-- Trainers T
-- JOIN
-- TrainerPokemon TP ON T.trainer_id = TP.trainer_id
-- JOIN
-- PokemonSpecies PS1 ON TP.pokedex_number = PS1.pokedex_number
-- JOIN
-- PokemonSpecies PS2 ON PS1.pokedex_number = PS2.evolves_from
-- WHERE
-- TP.level >= (SELECT MIN(level) FROM PokemonMoves WHERE pokedex_number = PS2.pokedex_number AND method = 'Level Up')
-- ),
-- TrainerAppearances AS (
-- SELECT
-- Trainer_Name,
-- COUNT(*) AS Appearance_Count
-- FROM
-- EvolutionsTrainer
-- GROUP BY
-- Trainer_Name
-- ),
-- MaxAppearance AS (
-- SELECT
-- MAX(Appearance_Count) AS Max_Appearance
-- FROM
-- TrainerAppearances
-- )
-- SELECT
-- ET.Trainer_Name,
-- ET.Base_Form,
-- ET.Evolved_Form,
-- ET.Level_At_Evolution
-- FROM
-- EvolutionsTrainer ET
-- JOIN
-- TrainerAppearances TA ON ET.Trainer_Name = TA.Trainer_Name
-- JOIN
-- MaxAppearance MA ON TA.Appearance_Count = MA.Max_Appearance
-- ORDER BY
-- ET.Trainer_Name, ET.Level_At_Evolution;
-- WITH TrainerTypes AS (
-- SELECT
-- T.trainer_id,
-- T.name,
-- PS.type1,
-- PS.type2
-- FROM
-- Trainers T
-- JOIN
-- TrainerPokemon TP ON T.trainer_id = TP.trainer_id
-- JOIN
-- PokemonSpecies PS ON TP.pokedex_number = PS.pokedex_number
-- GROUP BY
-- T.trainer_id, PS.type1, PS.type2
-- ),
-- UniqueTypes AS (
-- SELECT
-- trainer_id,
-- name,
-- type1 AS type
-- FROM
-- TrainerTypes
-- UNION
-- SELECT
-- trainer_id,
-- name,
-- type2 AS type
-- FROM
-- TrainerTypes
-- WHERE
-- type2 IS NOT NULL
-- ),
-- TypeCount AS (
-- SELECT
-- trainer_id,
-- name,
-- COUNT(DISTINCT type) AS type_coverage
-- FROM
-- UniqueTypes
-- GROUP BY
-- trainer_id
-- ),
-- RequiredTypeCount AS (
-- SELECT
-- COUNT(DISTINCT type) AS total_types
-- FROM
-- (SELECT type1 AS type FROM PokemonSpecies
-- UNION
-- SELECT type2 FROM PokemonSpecies WHERE type2 IS NOT NULL)
-- )
-- SELECT
-- TC.trainer_id,
-- TC.name,
-- TC.type_coverage
-- FROM
-- TypeCount TC,
-- RequiredTypeCount RTC
-- WHERE
-- TC.type_coverage = RTC.total_types;
WITH RECURSIVE EvolutionChain AS (
SELECT
pokedex_number,
name AS base_form,
name AS current_form,
0 AS evolution_count -- Start with zero evolutions for the base form
FROM
PokemonSpecies
WHERE
evolves_from IS NULL -- Base forms do not evolve from any other Pokémon
UNION ALL
-- Recursively join to find all subsequent evolutions
SELECT
ps.pokedex_number,
ec.base_form,
ps.name AS current_form,
ec.evolution_count + 1 AS evolution_count -- Increment the evolution count
FROM
PokemonSpecies ps
JOIN
EvolutionChain ec ON ps.evolves_from = ec.pokedex_number
)
-- Select only the final forms of each evolutionary chain by finding the max evolution count
, FinalForms AS (
SELECT
base_form,
MAX(evolution_count) AS max_evolutions
FROM
EvolutionChain
GROUP BY
base_form
)
-- Query to output the result
SELECT
base_form AS Pokemon_Name,
max_evolutions AS Number_of_Evolutions
FROM
FinalForms
ORDER BY
Pokemon_Name;
To embed this program on your website, copy the following code and paste it into your website's HTML: