postgres.sql

DataBase:

CREATE TABLE "poke_badge" (
	"id_badge" SERIAL PRIMARY KEY,
	"name_badge" VARCHAR(50),
	"gym" VARCHAR(50),
	"gym_leader" VARCHAR(50),
	"region" VARCHAR(50),
	"badge_img"VARCHAR(50)
);
INSERT INTO "poke_badge" ("name_badge", "gym", "gym_leader", "region", "badge_img")
VALUES ('Boulder Badge', 'Pewter City Gym', 'Brock', 'Kanto', 'boulderBadge.png'),
	('Cascade Badge', 'Cerulean City Gym', 'Misty', 'Kanto','cascadeBadge.png'),
	('Thunder Badge', 'Vermillion City Gym', 'Lt. Surge', 'Kanto', 'thunderBadge.png'),
	('Rainbow Badge', 'Celadon City Gym', 'Erika', 'Kanto', 'rainbowBadge.png'),
	('Soul Badge', 'Fuchsia City Gym', 'Koga', 'Kanto', 'soulBadge.png'),
	('Marsh Badge', 'Saffron City Gym', 'Sabrina', 'Kanto', 'marshBadge.png'),
	('Volcano Badge', 'Cinnabar Island Gym', 'Blaine', 'Kanto', 'volcanoBadge.png'),
	('Earth Badge', 'Viridian City Gym', 'Giovanni', 'Kanto', 'earthBadge.png');

CREATE TABLE "trainer" (
	"id" SERIAL PRIMARY KEY,
	"name" VARCHAR (50)
);
INSERT INTO "trainer" ("name")
VALUES ('Sam'),('Ash'), ('Blue');

CREATE TABLE "earned_badges" (
	"trainer_num" INT REFERENCES "trainer",
	"badge_num" INT REFERENCES "poke_badge"
);
INSERT INTO "earned_badges" ("trainer_num","badge_num")
VALUES (1,1),(1,2),(1,3),(1,4),(1,5),(1,6),(2,1),(2,2),(2,3),(2,7),(2,6),(3,1),(3,4),(3,5),(3,7);

CREATE TABLE "caught_poke"(
    "trainer_num" INT REFERENCES "trainer",
    "poke_num" INT REFERENCES "pokemon"
    );
    INSERT INTO "caught_poke" ("trainer_num", "poke_num")
    VALUES (1,1),(1,151);

CREATE TABLE "pokemon" (
"poke_id" SERIAL PRIMARY KEY,
"poke_name" VARCHAR (40),
"type_1" VARCHAR (40),
"type_2" VARCHAR (40) DEFAULT NULL
);
## "pokemon" has a fourth column "region" that was entered in later##
INSERT INTO "pokemon" ("poke_name", "type_1", "type_2")
VALUES ('Bulbasaur', 'Grass', 'Poison'),
    ('Ivysaur', 'Grass', 'Poison'),
    ('Venusaur', 'Grass', 'Poison'),
    ('Charmander', 'Fire', ''),
    ('Charmeleon', 'Fire', ''),
    ('Charizard', 'Fire', 'Flying'),
    ('Squirtle', 'Water',''),
    ('Wartortle', 'Water',''),
    ('Blastoise', 'Water',''),
    ('Caterpie', 'Bug',''),
    ('Metapod', 'Bug',''),
    ('Butterfree', 'Bug', 'Flying'),
    ('Weedle', 'Bug', 'Poison'),
    ('Kakuna', 'Bug', 'Poison'),
    ('Beedrill', 'Bug', 'Poison'),
    ('Pidgey', 'Normal', 'Flying'),
    ('Pidgeotto', 'Normal', 'Flying'),
    ('Pidgeot', 'Normal', 'Flying'),
    ('Rattata', 'Normal',''),
    ('Raticate', 'Normal',''),
    ('Spearow', 'Normal', 'Flying'),
    ('Fearow', 'Normal', 'Flying'),
    ('Ekans', 'Poison',''),
    ('Arbok', 'Poison',''),
    ('Pikachu', 'Electric',''),
    ('Raichu', 'Electric',''),
    ('Sandshrew', 'Ground',''),
    ('Sandslash', 'Ground',''),
    ('Nidoran♀', 'Poison',''),
    ('Nidorina', 'Poison',''),
    ('Nidoqueen', 'Poison', 'Ground'),
    ('Nidoran♂', 'Poison',''),
    ('Nidorino', 'Poison',''),
    ('Nidoking', 'Poison', 'Ground'),
    ('Clefairy', 'Fairy',''),
    ('Clefable', 'Fairy',''),
    ('Vulpix', 'Fire',''),
    ('Ninetales', 'Fire',''),
    ('Jigglypuff', 'Normal', 'Fairy'),
    ('Wigglytuff', 'Normal', 'Fairy'),
    ('Zubat', 'Poison', 'Flying'),
    ('Golbat', 'Poison', 'Flying'),
    ('Oddish', 'Grass', 'Poison'),
    ('Gloom', 'Grass', 'Poison'),
    ('Vileplume', 'Grass', 'Poison'),
    ('Paras', 'Bug', 'Grass'),
    ('Parasect', 'Bug', 'Grass'),
    ('Venonat', 'Bug', 'Poison'),
    ('Venomoth', 'Bug', 'Poison'),
    ('Diglett', 'Ground',''),
    ('Dugtrio', 'Ground',''),
    ('Meowth', 'Normal',''),
    ('Persian', 'Normal',''),
    ('Psyduck', 'Water',''),
    ('Golduck', 'Water',''),
    ('Mankey', 'Fighting',''),
    ('Primeape', 'Fighting',''),
    ('Growlithe', 'Fire',''),
    ('Arcanine', 'Fire',''),
    ('Poliwag', 'Water',''),
    ('Poliwhirl', 'Water',''),
    ('Poliwrath', 'Water', 'Fighting'),
    ('Abra', 'Psychic',''),
    ('Kadabra', 'Psychic',''),
    ('Alakazam', 'Psychic',''),
    ('Machop', 'Fighting',''),
    ('Machoke', 'Fighting',''),
    ('Machamp', 'Fighting',''),
    ('Bellsprout', 'Grass', 'Poison'),
    ('Weepinbell', 'Grass', 'Poison'),
    ('Victreebel', 'Grass', 'Poison'),
    ('Tentacool', 'Water', 'Poison'),
    ('Tentacruel', 'Water', 'Poison'),
    ('Geodude', 'Rock', 'Ground'),
    ('Graveler', 'Rock', 'Ground'),
    ('Golem', 'Rock', 'Ground'),
    ('Ponyta', 'Fire',''),
    ('Rapidash', 'Fire',''),
    ('Slowpoke', 'Water', 'Psychic'),
    ('Slowbro', 'Water', 'Psychic'),
    ('Magnemite', 'Electric', 'Steel'),
    ('Magneton', 'Electric', 'Steel'),
    ('Farfetch d', 'Normal', 'Flying'),
    ('Doduo', 'Normal', 'Flying'),
    ('Dodrio', 'Normal', 'Flying'),
    ('Seel', 'Water',''),
    ('Dewgong', 'Water', 'Ice'),
    ('Grimer', 'Poison',''),
    ('Muk', 'Poison',''),
    ('Shellder', 'Water',''),
    ('Cloyster', 'Water', 'Ice'),
    ('Gastly', 'Ghost', 'Poison'),
    ('Haunter', 'Ghost', 'Poison'),
    ('Gengar', 'Ghost', 'Poison'),
    ('Onix', 'Rock', 'Ground'),
    ('Drowzee', 'Psychic',''),
    ('Hypno', 'Psychic',''),
    ('Krabby', 'Water',''),
    ('Kingler', 'Water',''),
    ('Voltorb', 'Electric',''),
    ('Electrode', 'Electric',''),
    ('Exeggcute', 'Grass', 'Psychic'),
    ('Exeggutor', 'Grass', 'Psychic'),
    ('Cubone', 'Ground',''),
    ('Marowak', 'Ground',''),
    ('Hitmonlee', 'Fighting',''),
    ('Hitmonchan', 'Fighting',''),
    ('Lickitung', 'Normal',''),
    ('Koffing', 'Poison',''),
    ('Weezing', 'Poison',''),
    ('Rhyhorn', 'Ground', 'Rock'),
    ('Rhydon', 'Ground', 'Rock'),
    ('Chansey', 'Normal',''),
    ('Tangela', 'Grass',''),
    ('Kangaskhan', 'Normal',''),
    ('Horsea', 'Water',''),
    ('Seadra', 'Water',''),
    ('Goldeen', 'Water',''),
    ('Seaking', 'Water',''),
    ('Staryu', 'Water',''),
    ('Starmie', 'Water', 'Psychic'),
    ('Mr. Mime', 'Psychic', 'Fairy'),
    ('Scyther', 'Bug', 'Flying'),
    ('Jynx', 'Ice', 'Psychic'),
    ('Electabuzz', 'Electric',''),
    ('Magmar', 'Fire',''),
    ('Pinsir', 'Bug',''),
    ('Tauros', 'Normal',''),
    ('Magikarp', 'Water',''),
    ('Gyarados', 'Water', 'Flying'),
    ('Lapras', 'Water', 'Ice'),
    ('Ditto', 'Normal',''),
    ('Eevee', 'Normal',''),
    ('Vaporeon', 'Water',''),
    ('Jolteon', 'Electric',''),
    ('Flareon', 'Fire',''),
    ('Porygon', 'Normal',''),
    ('Omanyte', 'Rock', 'Water'),
    ('Omastar', 'Rock', 'Water'),
    ('Kabuto', 'Rock', 'Water'),
    ('Kabutops', 'Rock', 'Water'),
    ('Aerodactyl', 'Rock', 'Flying'),
    ('Snorlax', 'Normal',''),
    ('Articuno', 'Ice', 'Flying'),
    ('Zapdos', 'Electric', 'Flying'),
    ('Moltres', 'Fire', 'Flying'),
    ('Dratini', 'Dragon',''),
    ('Dragonair', 'Dragon',''),
    ('Dragonite', 'Dragon', 'Flying'),
    ('Mewtwo', 'Psychic',''),
    ('Mew', 'Psychic',''),
    ('Chikorita', 'Grass',''),
    ('Bayleef', 'Grass',''),
    ('Meganium', 'Grass',''),
    ('Cyndaquil', 'Fire',''),
    ('Quilava', 'Fire',''),
    ('Typhlosion', 'Fire',''),
    ('Totodile', 'Water',''),
    ('Croconaw', 'Water',''),
    ('Feraligatr', 'Water',''),
    ('Sentret', 'Normal',''),
    ('Furret', 'Normal',''),
    ('Hoothoot', 'Normal', 'Flying'),
    ('Noctowl', 'Normal', 'Flying'),
    ('Ledyba', 'Bug', 'Flying'),
    ('Ledian', 'Bug', 'Flying'),
    ('Spinarak', 'Bug', 'Poison'),
    ('Ariados', 'Bug', 'Poison'),
    ('Crobat', 'Poison', 'Flying'),
    ('Chinchou', 'Water', 'Electric'),
    ('Lanturn', 'Water', 'Electric'),
    ('Pichu', 'Electric',''),
    ('Cleffa', 'Fairy',''),
    ('Igglybuff', 'Normal', 'Fairy'),
    ('Togepi', 'Fairy',''),
    ('Togetic', 'Fairy', 'Flying'),
    ('Natu', 'Psychic', 'Flying'),
    ('Xatu', 'Psychic', 'Flying'),
    ('Mareep', 'Electric',''),
    ('Flaaffy', 'Electric',''),
    ('Ampharos', 'Electric',''),
    ('Bellossom', 'Grass',''),
    ('Marill', 'Water', 'Fairy'),
    ('Azumarill', 'Water', 'Fairy'),
    ('Sudowoodo', 'Rock',''),
    ('Politoed', 'Water',''),
    ('Hoppip', 'Grass', 'Flying'),
    ('Skiploom', 'Grass', 'Flying'),
    ('Jumpluff', 'Grass', 'Flying'),
    ('Aipom', 'Normal',''),
    ('Sunkern', 'Grass',''),
    ('Sunflora', 'Grass',''),
    ('Yanma', 'Bug', 'Flying'),
    ('Wooper', 'Water', 'Ground'),
    ('Quagsire', 'Water', 'Ground'),
    ('Espeon', 'Psychic',''),
    ('Umbreon', 'Dark',''),
    ('Murkrow', 'Dark', 'Flying'),
    ('Slowking', 'Water', 'Psychic'),
    ('Misdreavus', 'Ghost',''),
    ('Unown', 'Psychic',''),
    ('Wobbuffet', 'Psychic',''),
    ('Girafarig', 'Normal', 'Psychic'),
    ('Pineco', 'Bug',''),
    ('Forretress', 'Bug', 'Steel'),
    ('Dunsparce', 'Normal',''),
    ('Gligar', 'Ground', 'Flying'),
    ('Steelix', 'Steel', 'Ground'),
    ('Snubbull', 'Fairy',''),
    ('Granbull', 'Fairy',''),
    ('Qwilfish', 'Water', 'Poison'),
    ('Scizor', 'Bug', 'Steel'),
    ('Shuckle', 'Bug', 'Rock'),
    ('Heracross', 'Bug', 'Fighting'),
    ('Sneasel', 'Dark', 'Ice'),
    ('Teddiursa', 'Normal',''),
    ('Ursaring', 'Normal',''),
    ('Slugma', 'Fire',''),
    ('Magcargo', 'Fire', 'Rock'),
    ('Swinub', 'Ice', 'Ground'),
    ('Piloswine', 'Ice', 'Ground'),
    ('Corsola', 'Water', 'Rock'),
    ('Remoraid', 'Water',''),
    ('Octillery', 'Water',''),
    ('Delibird', 'Ice', 'Flying'),
    ('Mantine', 'Water', 'Flying'),
    ('Skarmory', 'Steel', 'Flying'),
    ('Houndour', 'Dark', 'Fire'),
    ('Houndoom', 'Dark', 'Fire'),
    ('Kingdra', 'Water', 'Dragon'),
    ('Phanpy', 'Ground',''),
    ('Donphan', 'Ground',''),
    ('Porygon2', 'Normal',''),
    ('Stantler', 'Normal',''),
    ('Smeargle', 'Normal',''),
    ('Tyrogue', 'Fighting',''),
    ('Hitmontop', 'Fighting',''),
    ('Smoochum', 'Ice', 'Psychic'),
    ('Elekid', 'Electric',''),
    ('Magby', 'Fire',''),
    ('Miltank', 'Normal',''),
    ('Blissey', 'Normal',''),
    ('Raikou', 'Electric',''),
    ('Entei', 'Fire',''),
    ('Suicune', 'Water',''),
    ('Larvitar', 'Rock', 'Ground'),
    ('Pupitar', 'Rock', 'Ground'),
    ('Tyranitar', 'Rock', 'Dark'),
    ('Lugia', 'Psychic', 'Flying'),
    ('Ho-Oh', 'Fire', 'Flying'),
    ('Celebi', 'Psychic', 'Grass'),
    ('Treecko', 'Grass',''),
    ('Grovyle', 'Grass',''),
    ('Sceptile', 'Grass',''),
    ('Torchic', 'Fire',''),
    ('Combusken', 'Fire', 'Fighting'),
    ('Blaziken', 'Fire', 'Fighting'),
    ('Mudkip', 'Water',''),
    ('Marshtomp', 'Water', 'Ground'),
    ('Swampert', 'Water', 'Ground'),
    ('Poochyena', 'Dark',''),
    ('Mightyena', 'Dark',''),
    ('Zigzagoon', 'Normal',''),
    ('Linoone', 'Normal',''),
    ('Wurmple', 'Bug',''),
    ('Silcoon', 'Bug',''),
    ('Beautifly', 'Bug', 'Flying'),
    ('Cascoon', 'Bug',''),
    ('Dustox', 'Bug', 'Poison'),
    ('Lotad', 'Water', 'Grass'),
    ('Lombre', 'Water', 'Grass'),
    ('Ludicolo', 'Water', 'Grass'),
    ('Seedot', 'Grass',''),
    ('Nuzleaf', 'Grass', 'Dark'),
    ('Shiftry', 'Grass', 'Dark'),
    ('Taillow', 'Normal', 'Flying'),
    ('Swellow', 'Normal', 'Flying'),
    ('Wingull', 'Water', 'Flying'),
    ('Pelipper', 'Water', 'Flying'),
    ('Ralts', 'Psychic', 'Fairy'),
    ('Kirlia', 'Psychic', 'Fairy'),
    ('Gardevoir', 'Psychic', 'Fairy'),
    ('Surskit', 'Bug', 'Water'),
    ('Masquerain', 'Bug', 'Flying'),
    ('Shroomish', 'Grass',''),
    ('Breloom', 'Grass', 'Fighting'),
    ('Slakoth', 'Normal',''),
    ('Vigoroth', 'Normal',''),
    ('Slaking', 'Normal',''),
    ('Nincada', 'Bug', 'Ground'),
    ('Ninjask', 'Bug', 'Flying'),
    ('Shedinja', 'Bug', 'Ghost'),
    ('Whismur', 'Normal',''),
    ('Loudred', 'Normal',''),
    ('Exploud', 'Normal',''),
    ('Makuhita', 'Fighting',''),
    ('Hariyama', 'Fighting',''),
    ('Azurill', 'Normal', 'Fairy'),
    ('Nosepass', 'Rock',''),
    ('Skitty', 'Normal',''),
    ('Delcatty', 'Normal',''),
    ('Sableye', 'Dark', 'Ghost'),
    ('Mawile', 'Steel', 'Fairy'),
    ('Aron', 'Steel', 'Rock'),
    ('Lairon', 'Steel', 'Rock'),
    ('Aggron', 'Steel', 'Rock'),
    ('Meditite', 'Fighting', 'Psychic'),
    ('Medicham', 'Fighting', 'Psychic'),
    ('Electrike', 'Electric',''),
    ('Manectric', 'Electric',''),
    ('Plusle', 'Electric',''),
    ('Minun', 'Electric',''),
    ('Volbeat', 'Bug',''),
    ('Illumise', 'Bug',''),
    ('Roselia', 'Grass', 'Poison'),
    ('Gulpin', 'Poison',''),
    ('Swalot', 'Poison',''),
    ('Carvanha', 'Water', 'Dark'),
    ('Sharpedo', 'Water', 'Dark'),
    ('Wailmer', 'Water',''),
    ('Wailord', 'Water',''),
    ('Numel', 'Fire', 'Ground'),
    ('Camerupt', 'Fire', 'Ground'),
    ('Torkoal', 'Fire',''),
    ('Spoink', 'Psychic',''),
    ('Grumpig', 'Psychic',''),
    ('Spinda', 'Normal',''),
    ('Trapinch', 'Ground',''),
    ('Vibrava', 'Ground', 'Dragon'),
    ('Flygon', 'Ground', 'Dragon'),
    ('Cacnea', 'Grass',''),
    ('Cacturne', 'Grass', 'Dark'),
    ('Swablu', 'Normal', 'Flying'),
    ('Altaria', 'Dragon', 'Flying'),
    ('Zangoose', 'Normal',''),
    ('Seviper', 'Poison',''),
    ('Lunatone', 'Rock', 'Psychic'),
    ('Solrock', 'Rock', 'Psychic'),
    ('Barboach', 'Water', 'Ground'),
    ('Whiscash', 'Water', 'Ground'),
    ('Corphish', 'Water',''),
    ('Crawdaunt', 'Water', 'Dark'),
    ('Baltoy', 'Ground', 'Psychic'),
    ('Claydol', 'Ground', 'Psychic'),
    ('Lileep', 'Rock', 'Grass'),
    ('Cradily', 'Rock', 'Grass'),
    ('Anorith', 'Rock', 'Bug'),
    ('Armaldo', 'Rock', 'Bug'),
    ('Feebas', 'Water',''),
    ('Milotic', 'Water',''),
    ('Castform', 'Normal',''),
    ('Kecleon', 'Normal',''),
    ('Shuppet', 'Ghost',''),
    ('Banette', 'Ghost',''),
    ('Duskull', 'Ghost',''),
    ('Dusclops', 'Ghost',''),
    ('Tropius', 'Grass', 'Flying'),
    ('Chimecho', 'Psychic',''),
    ('Absol', 'Dark',''),
    ('Wynaut', 'Psychic',''),
    ('Snorunt', 'Ice',''),
    ('Glalie', 'Ice',''),
    ('Spheal', 'Ice', 'Water'),
    ('Sealeo', 'Ice', 'Water'),
    ('Walrein', 'Ice', 'Water'),
    ('Clamperl', 'Water',''),
    ('Huntail', 'Water',''),
    ('Gorebyss', 'Water',''),
    ('Relicanth', 'Water', 'Rock'),
    ('Luvdisc', 'Water',''),
    ('Bagon', 'Dragon',''),
    ('Shelgon', 'Dragon',''),
    ('Salamence', 'Dragon', 'Flying'),
    ('Beldum', 'Steel', 'Psychic'),
    ('Metang', 'Steel', 'Psychic'),
    ('Metagross', 'Steel', 'Psychic'),
    ('Regirock', 'Rock',''),
    ('Regice', 'Ice',''),
    ('Registeel', 'Steel',''),
    ('Latias', 'Dragon', 'Psychic'),
    ('Latios', 'Dragon', 'Psychic'),
    ('Kyogre', 'Water',''),
    ('Groudon', 'Ground',''),
    ('Rayquaza', 'Dragon', 'Flying'),
    ('Jirachi', 'Steel', 'Psychic'),
    ('Deoxys', 'Psychic','');