Retrieving Data with MySQL

  • Introduction to: Relational Database Management Systems (RDBSM) & Structured Query Language (SQL)

  • Creating a table

  • Querying Data

    • SELECT

    • FROM

    • WHERE

Preperation:

MySQL in 10 minutes

What is MySQL

The SELECT statement

WHERE & ORDER BY

How to load data into a database

Choose a connection

image-20220816103831195

Copy the script (below) into the Query window or download from here

image-20220816104142995

Press the left lightning button

image-20220816104228789

Refresh All

image-20220816104435630
DROP DATABASE IF EXISTS pokemon;

CREATE DATABASE pokemon;

USE pokemon;

DROP TABLE IF EXISTS pokemon;

CREATE TABLE pokemon (
    pokedex_number int,
    name VARCHAR(45),
    speed int,
    special_defence int,
    special_attack int,
    defence int,
    attack int,
    hp int, 
    primary_type VARCHAR(45),
    secondary_type VARCHAR(45)
);


INSERT INTO pokemon (pokedex_number, `name`, speed, special_defence, special_attack, defence, attack, hp, primary_type, secondary_type) 
                     VALUES (1, 'Bulbasaur', 45, 65, 65, 49, 49, 45, 'Grass', 'Poison');
INSERT INTO pokemon (pokedex_number, `name`, speed, special_defence, special_attack, defence, attack, hp, primary_type, secondary_type) 
                     VALUES (2, 'Ivysaur', 60, 80, 80, 63, 62, 60, 'Grass', 'Poison');
INSERT INTO pokemon (pokedex_number, `name`, speed, special_defence, special_attack, defence, attack, hp, primary_type, secondary_type) 
                     VALUES (3, 'Venusaur', 80, 100, 100, 83, 82, 80, 'Grass', 'Poison');
INSERT INTO pokemon (pokedex_number, `name`, speed, special_defence, special_attack, defence, attack, hp, primary_type, secondary_type) 
                     VALUES (4, 'Charmander', 65, 50, 60, 43, 52, 39, 'Fire', 'null');
INSERT INTO pokemon (pokedex_number, `name`, speed, special_defence, special_attack, defence, attack, hp, primary_type, secondary_type) 
                     VALUES (5, 'Charmeleon', 80, 65, 80, 58, 64, 58, 'Fire', 'null');
INSERT INTO pokemon (pokedex_number, `name`, speed, special_defence, special_attack, defence, attack, hp, primary_type, secondary_type) 
                     VALUES (6, 'Charizard', 100, 85, 109, 78, 84, 78, 'Fire', 'Flying');
INSERT INTO pokemon (pokedex_number, `name`, speed, special_defence, special_attack, defence, attack, hp, primary_type, secondary_type) 
                     VALUES (7, 'Squirtle', 43, 64, 50, 65, 48, 44, 'Water', 'null');
INSERT INTO pokemon (pokedex_number, `name`, speed, special_defence, special_attack, defence, attack, hp, primary_type, secondary_type) 
                     VALUES (8, 'Wartortle', 58, 80, 65,80, 63, 59, 'Water', 'null');
INSERT INTO pokemon (pokedex_number, `name`, speed, special_defence, special_attack, defence, attack, hp, primary_type, secondary_type) 
                     VALUES (9, 'Blastoise', 78, 105, 85, 100, 83, 79, 'Water', 'null');
INSERT INTO pokemon (pokedex_number, `name`, speed, special_defence, special_attack, defence, attack, hp, primary_type, secondary_type) 
                     VALUES (10, 'Caterpie', 45, 20, 20, 35, 30, 45, 'Bug', 'null');
INSERT INTO pokemon (pokedex_number, `name`, speed, special_defence, special_attack, defence, attack, hp, primary_type, secondary_type) 
                     VALUES (11, 'Metapod', 30, 25, 25, 55, 20, 50, 'Bug', 'null');
INSERT INTO pokemon (pokedex_number, `name`, speed, special_defence, special_attack, defence, attack, hp, primary_type, secondary_type) 
                     VALUES (12, 'Butterfree', 70, 80, 90, 50, 45, 60, 'Bug', 'Flying');
INSERT INTO pokemon (pokedex_number, `name`, speed, special_defence, special_attack, defence, attack, hp, primary_type, secondary_type) 
                     VALUES (13, 'Weedle', 50, 20, 20, 30, 35, 40, 'Bug', 'Poison');
INSERT INTO pokemon (pokedex_number, `name`, speed, special_defence, special_attack, defence, attack, hp, primary_type, secondary_type) 
                     VALUES (14, 'Kakuna', 35, 25, 25, 50, 25, 45, 'Bug', 'Poison');
INSERT INTO pokemon (pokedex_number, `name`, speed, special_defence, special_attack, defence, attack, hp, primary_type, secondary_type) 
                     VALUES (15, 'Beedrill', 75, 80, 45, 40, 90, 65, 'Bug', 'Poison');
INSERT INTO pokemon (pokedex_number, `name`, speed, special_defence, special_attack, defence, attack, hp, primary_type, secondary_type) 
                     VALUES (16, 'Pidgey', 56, 35, 35, 40, 45, 40, 'Normal', 'Flying');
INSERT INTO pokemon (pokedex_number, `name`, speed, special_defence, special_attack, defence, attack, hp, primary_type, secondary_type) 
                     VALUES (17, 'Pidgeotto', 71, 50, 50, 55, 60, 63, 'Normal', 'Flying');
INSERT INTO pokemon (pokedex_number, `name`, speed, special_defence, special_attack, defence, attack, hp, primary_type, secondary_type) 
                     VALUES (18, 'Pidgeot', 101, 70, 70, 75, 80, 83, 'Normal', 'Flying');
INSERT INTO pokemon (pokedex_number, `name`, speed, special_defence, special_attack, defence, attack, hp, primary_type, secondary_type) 
                     VALUES (19, 'Rattata', 72, 35, 25, 35, 56, 30, 'Normal', 'null');
INSERT INTO pokemon (pokedex_number, `name`, speed, special_defence, special_attack, defence, attack, hp, primary_type, secondary_type) 
                     VALUES (20, 'Raticate', 97, 70, 50, 60, 81, 55, 'Normal', 'null');
INSERT INTO pokemon (pokedex_number, `name`, speed, special_defence, special_attack, defence, attack, hp, primary_type, secondary_type) 
                     VALUES (21, 'Spearow', 70, 31, 31, 30, 60, 40, 'Normal', 'Flying');
INSERT INTO pokemon (pokedex_number, `name`, speed, special_defence, special_attack, defence, attack, hp, primary_type, secondary_type) 
                     VALUES (22, 'Fearow', 100, 61, 61, 65, 90, 65, 'Normal', 'Flying');
INSERT INTO pokemon (pokedex_number, `name`, speed, special_defence, special_attack, defence, attack, hp, primary_type, secondary_type) 
                     VALUES (23, 'Ekans', 55, 54, 40, 44, 60, 35, 'Poison', 'null');
INSERT INTO pokemon (pokedex_number, `name`, speed, special_defence, special_attack, defence, attack, hp, primary_type, secondary_type) 
                     VALUES (24, 'Arbok', 80, 79, 65, 69, 85, 60, 'Poison', 'null');
INSERT INTO pokemon (pokedex_number, `name`, speed, special_defence, special_attack, defence, attack, hp, primary_type, secondary_type) 
                     VALUES (25, 'Pikachu', 90, 50, 50, 40, 55, 35, 'Electric', 'null');
INSERT INTO pokemon (pokedex_number, `name`, speed, special_defence, special_attack, defence, attack, hp, primary_type, secondary_type) 
                     VALUES (26, 'Raichu', 110, 80, 90, 55, 90, 60, 'Electric', 'null');
INSERT INTO pokemon (pokedex_number, `name`, speed, special_defence, special_attack, defence, attack, hp, primary_type, secondary_type) 
                     VALUES (27, 'Sandshrew', 40, 30, 20, 85, 75, 50, 'Ground', 'null');
INSERT INTO pokemon (pokedex_number, `name`, speed, special_defence, special_attack, defence, attack, hp, primary_type, secondary_type) 
                     VALUES (28, 'Sandslash', 65, 55, 45, 110, 100, 75, 'Ground', 'null');
INSERT INTO pokemon (pokedex_number, `name`, speed, special_defence, special_attack, defence, attack, hp, primary_type, secondary_type) 
                     VALUES (29, 'Nidoran-f', 41, 40, 40, 52, 47, 55, 'Poison', 'null');
INSERT INTO pokemon (pokedex_number, `name`, speed, special_defence, special_attack, defence, attack, hp, primary_type, secondary_type) 
                     VALUES (30, 'Nidorina', 56, 55, 55, 67, 62, 70, 'Poison', 'null');
INSERT INTO pokemon (pokedex_number, `name`, speed, special_defence, special_attack, defence, attack, hp, primary_type, secondary_type) 
                     VALUES (31, 'Nidoqueen', 76, 85, 75, 87, 92, 90, 'Poison', 'Ground');
INSERT INTO pokemon (pokedex_number, `name`, speed, special_defence, special_attack, defence, attack, hp, primary_type, secondary_type) 
                     VALUES (32, 'Nidoran-m', 50, 40, 40, 40, 57, 46, 'Poison', 'null');
INSERT INTO pokemon (pokedex_number, `name`, speed, special_defence, special_attack, defence, attack, hp, primary_type, secondary_type) 
                     VALUES (33, 'Nidorino', 65, 55, 55, 57, 72, 61, 'Poison', 'null');
INSERT INTO pokemon (pokedex_number, `name`, speed, special_defence, special_attack, defence, attack, hp, primary_type, secondary_type) 
                     VALUES (34, 'Nidoking', 85, 75, 85, 77, 102, 81, 'Poison', 'Ground');
INSERT INTO pokemon (pokedex_number, `name`, speed, special_defence, special_attack, defence, attack, hp, primary_type, secondary_type) 
                     VALUES (35, 'Clefairy', 35, 65, 60, 48, 45, 70, 'Fairy', 'null');
INSERT INTO pokemon (pokedex_number, `name`, speed, special_defence, special_attack, defence, attack, hp, primary_type, secondary_type) 
                     VALUES (36, 'Clefable', 60, 90, 95, 73, 70, 95, 'Fairy', 'null');
INSERT INTO pokemon (pokedex_number, `name`, speed, special_defence, special_attack, defence, attack, hp, primary_type, secondary_type) 
                     VALUES (37, 'Vulpix', 65, 65, 50, 40, 41, 38, 'Fire', 'null');
INSERT INTO pokemon (pokedex_number, `name`, speed, special_defence, special_attack, defence, attack, hp, primary_type, secondary_type) 
                     VALUES (38, 'Ninetales', 100, 100, 81, 75, 76, 73, 'Fire', 'null');
INSERT INTO pokemon (pokedex_number, `name`, speed, special_defence, special_attack, defence, attack, hp, primary_type, secondary_type) 
                     VALUES (39, 'Jigglypuff', 20, 25, 45, 20, 45, 115, 'Normal', 'Fairy');
INSERT INTO pokemon (pokedex_number, `name`, speed, special_defence, special_attack, defence, attack, hp, primary_type, secondary_type) 
                     VALUES (40, 'Wigglytuff', 45, 50, 85, 45, 70, 140, 'Normal', 'Fairy');
INSERT INTO pokemon (pokedex_number, `name`, speed, special_defence, special_attack, defence, attack, hp, primary_type, secondary_type) 
                     VALUES (41, 'Zubat', 55, 40, 30, 35, 45, 40, 'Poison', 'Flying');
INSERT INTO pokemon (pokedex_number, `name`, speed, special_defence, special_attack, defence, attack, hp, primary_type, secondary_type) 
                     VALUES (42, 'Golbat', 90, 75, 65, 70, 80, 75, 'Poison', 'Flying');
INSERT INTO pokemon (pokedex_number, `name`, speed, special_defence, special_attack, defence, attack, hp, primary_type, secondary_type) 
                     VALUES (43, 'Oddish', 30, 65, 75, 55, 50, 45, 'Grass', 'Poison');
INSERT INTO pokemon (pokedex_number, `name`, speed, special_defence, special_attack, defence, attack, hp, primary_type, secondary_type) 
                     VALUES (44, 'Gloom', 40, 75, 85, 70, 65, 60, 'Grass', 'Poison');
INSERT INTO pokemon (pokedex_number, `name`, speed, special_defence, special_attack, defence, attack, hp, primary_type, secondary_type) 
                     VALUES (45, 'Vileplume', 50, 90, 110, 85, 80, 75, 'Grass', 'Poison');
INSERT INTO pokemon (pokedex_number, `name`, speed, special_defence, special_attack, defence, attack, hp, primary_type, secondary_type) 
                     VALUES (46, 'Paras', 25, 55, 45, 55, 70, 35, 'Bug', 'Grass');
INSERT INTO pokemon (pokedex_number, `name`, speed, special_defence, special_attack, defence, attack, hp, primary_type, secondary_type) 
                     VALUES (47, 'Parasect', 30, 80, 60, 80, 95, 60, 'Bug', 'Grass');
INSERT INTO pokemon (pokedex_number, `name`, speed, special_defence, special_attack, defence, attack, hp, primary_type, secondary_type) 
                     VALUES (48, 'Venonat', 45, 55, 40, 50, 55, 60, 'Bug', 'Poison');
INSERT INTO pokemon (pokedex_number, `name`, speed, special_defence, special_attack, defence, attack, hp, primary_type, secondary_type) 
                     VALUES (49, 'Venomoth', 90, 75, 90, 60, 65, 70, 'Bug', 'Poison');
INSERT INTO pokemon (pokedex_number, `name`, speed, special_defence, special_attack, defence, attack, hp, primary_type, secondary_type) 
                     VALUES (50, 'Diglett', 95, 45, 35, 25, 55, 10, 'Ground', 'null');
INSERT INTO pokemon (pokedex_number, `name`, speed, special_defence, special_attack, defence, attack, hp, primary_type, secondary_type) 
                     VALUES (51, 'Dugtrio', 120, 70, 50, 50, 80, 35, 'Ground', 'null');
INSERT INTO pokemon (pokedex_number, `name`, speed, special_defence, special_attack, defence, attack, hp, primary_type, secondary_type) 
                     VALUES (52, 'Meowth', 90, 40, 40, 35, 45, 40, 'Normal', 'null');
INSERT INTO pokemon (pokedex_number, `name`, speed, special_defence, special_attack, defence, attack, hp, primary_type, secondary_type) 
                     VALUES (53, 'Persian', 115, 65, 65, 60, 70, 65, 'Normal', 'null');
INSERT INTO pokemon (pokedex_number, `name`, speed, special_defence, special_attack, defence, attack, hp, primary_type, secondary_type) 
                     VALUES (54, 'Psyduck', 55, 50, 65, 48, 52, 50, 'Water', 'null');
INSERT INTO pokemon (pokedex_number, `name`, speed, special_defence, special_attack, defence, attack, hp, primary_type, secondary_type) 
                     VALUES (55, 'Golduck', 85, 80, 95, 78, 82, 80, 'Water', 'null');
INSERT INTO pokemon (pokedex_number, `name`, speed, special_defence, special_attack, defence, attack, hp, primary_type, secondary_type) 
                     VALUES (56, 'Mankey', 70, 45, 35, 35, 80, 40, 'Fighting', 'null');
INSERT INTO pokemon (pokedex_number, `name`, speed, special_defence, special_attack, defence, attack, hp, primary_type, secondary_type) 
                     VALUES (57, 'Primeape', 95, 70, 60, 60, 105, 65, 'Fighting', 'null');
INSERT INTO pokemon (pokedex_number, `name`, speed, special_defence, special_attack, defence, attack, hp, primary_type, secondary_type) 
                     VALUES (58, 'Growlithe', 60, 50, 70, 45, 70, 55, 'Fire', 'null');
INSERT INTO pokemon (pokedex_number, `name`, speed, special_defence, special_attack, defence, attack, hp, primary_type, secondary_type) 
                     VALUES (59, 'Arcanine', 95, 80, 100, 80, 110, 90, 'Fire', 'null');
INSERT INTO pokemon (pokedex_number, `name`, speed, special_defence, special_attack, defence, attack, hp, primary_type, secondary_type) 
                     VALUES (60, 'Poliwag', 90, 40, 40, 40, 50, 40, 'Water', 'null');
INSERT INTO pokemon (pokedex_number, `name`, speed, special_defence, special_attack, defence, attack, hp, primary_type, secondary_type) 
                     VALUES (61, 'Poliwhirl', 90, 50, 50, 65, 65, 65, 'Water', 'null');
INSERT INTO pokemon (pokedex_number, `name`, speed, special_defence, special_attack, defence, attack, hp, primary_type, secondary_type) 
                     VALUES (62, 'Poliwrath', 70, 90, 70, 95, 95, 90, 'Water', 'Fighting');
INSERT INTO pokemon (pokedex_number, `name`, speed, special_defence, special_attack, defence, attack, hp, primary_type, secondary_type) 
                     VALUES (63, 'Abra', 90, 55, 105, 15, 20, 25, 'Psychic', 'null');
INSERT INTO pokemon (pokedex_number, `name`, speed, special_defence, special_attack, defence, attack, hp, primary_type, secondary_type) 
                     VALUES (64, 'Kadabra', 105, 70, 120, 30, 35, 40, 'Psychic', 'null');
INSERT INTO pokemon (pokedex_number, `name`, speed, special_defence, special_attack, defence, attack, hp, primary_type, secondary_type) 
                     VALUES (65, 'Alakazam', 120, 95, 135, 45, 50, 55, 'Psychic', 'null');
INSERT INTO pokemon (pokedex_number, `name`, speed, special_defence, special_attack, defence, attack, hp, primary_type, secondary_type) 
                     VALUES (66, 'Machop', 35, 35, 35, 50, 80, 70, 'Fighting', 'null');
INSERT INTO pokemon (pokedex_number, `name`, speed, special_defence, special_attack, defence, attack, hp, primary_type, secondary_type) 
                     VALUES (67, 'Machoke', 45, 60, 50, 70, 100, 80, 'Fighting', 'null');
INSERT INTO pokemon (pokedex_number, `name`, speed, special_defence, special_attack, defence, attack, hp, primary_type, secondary_type) 
                     VALUES (68, 'Machamp', 55, 85, 65, 80, 130, 90, 'Fighting', 'null');
INSERT INTO pokemon (pokedex_number, `name`, speed, special_defence, special_attack, defence, attack, hp, primary_type, secondary_type) 
                     VALUES (69, 'Bellsprout', 40, 30, 70, 35, 75, 50, 'Grass', 'Poison');
INSERT INTO pokemon (pokedex_number, `name`, speed, special_defence, special_attack, defence, attack, hp, primary_type, secondary_type) 
                     VALUES (70, 'Weepinbell', 55, 45, 85, 50, 90, 65, 'Grass', 'Poison');
INSERT INTO pokemon (pokedex_number, `name`, speed, special_defence, special_attack, defence, attack, hp, primary_type, secondary_type) 
                     VALUES (71, 'Victreebel', 70, 70, 100, 65, 105, 80, 'Grass', 'Poison');
INSERT INTO pokemon (pokedex_number, `name`, speed, special_defence, special_attack, defence, attack, hp, primary_type, secondary_type) 
                     VALUES (72, 'Tentacool', 70, 100, 50, 35, 40, 40, 'Water', 'Poison');
INSERT INTO pokemon (pokedex_number, `name`, speed, special_defence, special_attack, defence, attack, hp, primary_type, secondary_type) 
                     VALUES (73, 'Tentacruel', 100, 120, 80, 65, 70, 80, 'Water', 'Poison');
INSERT INTO pokemon (pokedex_number, `name`, speed, special_defence, special_attack, defence, attack, hp, primary_type, secondary_type) 
                     VALUES (74, 'Geodude', 20, 30, 30, 100, 80, 40, 'Rock', 'Ground');
INSERT INTO pokemon (pokedex_number, `name`, speed, special_defence, special_attack, defence, attack, hp, primary_type, secondary_type) 
                     VALUES (75, 'Graveler', 35, 45, 45, 115, 95, 55, 'Rock', 'Ground');
INSERT INTO pokemon (pokedex_number, `name`, speed, special_defence, special_attack, defence, attack, hp, primary_type, secondary_type) 
                     VALUES (76, 'Golem', 45, 65, 55, 130, 120, 80, 'Rock', 'Ground');
INSERT INTO pokemon (pokedex_number, `name`, speed, special_defence, special_attack, defence, attack, hp, primary_type, secondary_type) 
                     VALUES (77, 'Ponyta', 90, 65, 65, 55, 85, 50, 'Fire', 'null');
INSERT INTO pokemon (pokedex_number, `name`, speed, special_defence, special_attack, defence, attack, hp, primary_type, secondary_type) 
                     VALUES (78, 'Rapidash', 105, 80, 80, 70, 100, 65, 'Fire', 'null');
INSERT INTO pokemon (pokedex_number, `name`, speed, special_defence, special_attack, defence, attack, hp, primary_type, secondary_type) 
                     VALUES (79, 'Slowpoke', 15, 40, 40, 65, 65, 90, 'Water', 'Psychic');
INSERT INTO pokemon (pokedex_number, `name`, speed, special_defence, special_attack, defence, attack, hp, primary_type, secondary_type) 
                     VALUES (80, 'Slowbro', 30, 80, 100, 110, 75, 95, 'Water', 'Psychic');
INSERT INTO pokemon (pokedex_number, `name`, speed, special_defence, special_attack, defence, attack, hp, primary_type, secondary_type) 
                     VALUES (81, 'Magnemite', 45, 55, 95, 70, 35, 25, 'Electric', 'Steel');
INSERT INTO pokemon (pokedex_number, `name`, speed, special_defence, special_attack, defence, attack, hp, primary_type, secondary_type) 
                     VALUES (82, 'Magneton', 70, 70, 120, 95, 60, 50, 'Electric', 'Steel');
INSERT INTO pokemon (pokedex_number, `name`, speed, special_defence, special_attack, defence, attack, hp, primary_type, secondary_type) 
                     VALUES (83, 'Farfetchd', 60, 62, 58, 55, 65, 52, 'Normal', 'Flying');
INSERT INTO pokemon (pokedex_number, `name`, speed, special_defence, special_attack, defence, attack, hp, primary_type, secondary_type) 
                     VALUES (84, 'Doduo', 75, 35, 35, 45, 85, 35, 'Normal', 'Flying');
INSERT INTO pokemon (pokedex_number, `name`, speed, special_defence, special_attack, defence, attack, hp, primary_type, secondary_type) 
                     VALUES (85, 'Dodrio', 100, 60, 60, 70, 110, 60, 'Normal', 'Flying');
INSERT INTO pokemon (pokedex_number, `name`, speed, special_defence, special_attack, defence, attack, hp, primary_type, secondary_type) 
                     VALUES (86, 'Seel', 45, 70, 45, 55, 45, 65, 'Water', 'null');
INSERT INTO pokemon (pokedex_number, `name`, speed, special_defence, special_attack, defence, attack, hp, primary_type, secondary_type) 
                     VALUES (87, 'Dewgong', 70, 95, 70, 80, 70, 90, 'Water', 'Ice');
INSERT INTO pokemon (pokedex_number, `name`, speed, special_defence, special_attack, defence, attack, hp, primary_type, secondary_type) 
                     VALUES (88, 'Grimer', 25, 50, 40, 50, 80, 80, 'Poison', 'null');
INSERT INTO pokemon (pokedex_number, `name`, speed, special_defence, special_attack, defence, attack, hp, primary_type, secondary_type) 
                     VALUES (89, 'Muk', 50, 100, 65, 75, 105, 105, 'Poison', 'null');
INSERT INTO pokemon (pokedex_number, `name`, speed, special_defence, special_attack, defence, attack, hp, primary_type, secondary_type) 
                     VALUES (90, 'Shellder', 40, 25, 45, 100, 65, 30, 'Water', 'null');
INSERT INTO pokemon (pokedex_number, `name`, speed, special_defence, special_attack, defence, attack, hp, primary_type, secondary_type) 
                     VALUES (91, 'Cloyster', 70, 45, 85, 180, 95, 50, 'Water', 'Ice');
INSERT INTO pokemon (pokedex_number, `name`, speed, special_defence, special_attack, defence, attack, hp, primary_type, secondary_type) 
                     VALUES (92, 'Gastly', 80, 35, 100, 30, 35, 30, 'Ghost', 'Poison');
INSERT INTO pokemon (pokedex_number, `name`, speed, special_defence, special_attack, defence, attack, hp, primary_type, secondary_type) 
                     VALUES (93, 'Haunter', 95, 55, 115, 45, 50, 45, 'Ghost', 'Poison');
INSERT INTO pokemon (pokedex_number, `name`, speed, special_defence, special_attack, defence, attack, hp, primary_type, secondary_type) 
                     VALUES (94, 'Gengar', 110, 75, 130, 60, 65, 60, 'Ghost', 'Poison');
INSERT INTO pokemon (pokedex_number, `name`, speed, special_defence, special_attack, defence, attack, hp, primary_type, secondary_type) 
                     VALUES (95, 'Onix', 70, 45, 30, 160, 45, 35, 'Rock', 'Ground');
INSERT INTO pokemon (pokedex_number, `name`, speed, special_defence, special_attack, defence, attack, hp, primary_type, secondary_type) 
                     VALUES (96, 'Drowzee', 42, 90, 43, 45, 48, 60, 'Psychic', 'null');
INSERT INTO pokemon (pokedex_number, `name`, speed, special_defence, special_attack, defence, attack, hp, primary_type, secondary_type) 
                     VALUES (97, 'Hypno', 67, 115, 73, 70, 73, 85, 'Psychic', 'null');
INSERT INTO pokemon (pokedex_number, `name`, speed, special_defence, special_attack, defence, attack, hp, primary_type, secondary_type) 
                     VALUES (98, 'Krabby', 50, 25, 25, 90, 105, 30, 'Water', 'null');
INSERT INTO pokemon (pokedex_number, `name`, speed, special_defence, special_attack, defence, attack, hp, primary_type, secondary_type) 
                     VALUES (99, 'Kingler', 75, 50, 50, 115, 130, 55, 'Water', 'null');
INSERT INTO pokemon (pokedex_number, `name`, speed, special_defence, special_attack, defence, attack, hp, primary_type, secondary_type) 
                     VALUES (100, 'Voltorb', 100, 55, 55, 50, 30, 40, 'Electric', 'null');
INSERT INTO pokemon (pokedex_number, `name`, speed, special_defence, special_attack, defence, attack, hp, primary_type, secondary_type) 
                     VALUES (101, 'Electrode', 140, 80, 80, 70, 50, 60, 'Electric', 'null');
INSERT INTO pokemon (pokedex_number, `name`, speed, special_defence, special_attack, defence, attack, hp, primary_type, secondary_type) 
                     VALUES (102, 'Exeggcute', 40, 45, 60, 80, 40, 60, 'Grass', 'Psychic');
INSERT INTO pokemon (pokedex_number, `name`, speed, special_defence, special_attack, defence, attack, hp, primary_type, secondary_type) 
                     VALUES (103, 'Exeggutor', 55, 65, 125, 85, 95, 95, 'Grass', 'Psychic');
INSERT INTO pokemon (pokedex_number, `name`, speed, special_defence, special_attack, defence, attack, hp, primary_type, secondary_type) 
                     VALUES (104, 'Cubone', 35, 50, 40, 95, 50, 50, 'Ground', 'null');
INSERT INTO pokemon (pokedex_number, `name`, speed, special_defence, special_attack, defence, attack, hp, primary_type, secondary_type) 
                     VALUES (105, 'Marowak', 45, 80, 50, 110, 80, 60, 'Ground', 'null');
INSERT INTO pokemon (pokedex_number, `name`, speed, special_defence, special_attack, defence, attack, hp, primary_type, secondary_type) 
                     VALUES (106, 'Hitmonlee', 87, 110, 35, 53, 120, 50, 'Fighting', 'null');
INSERT INTO pokemon (pokedex_number, `name`, speed, special_defence, special_attack, defence, attack, hp, primary_type, secondary_type) 
                     VALUES (107, 'Hitmonchan', 76, 110, 35, 79, 105, 50, 'Fighting', 'null');
INSERT INTO pokemon (pokedex_number, `name`, speed, special_defence, special_attack, defence, attack, hp, primary_type, secondary_type) 
                     VALUES (108, 'Lickitung', 30, 75, 60, 75, 55, 90, 'Normal', 'null');
INSERT INTO pokemon (pokedex_number, `name`, speed, special_defence, special_attack, defence, attack, hp, primary_type, secondary_type) 
                     VALUES (109, 'Koffing', 35, 45, 60, 95, 65, 40, 'Poison', 'null');
INSERT INTO pokemon (pokedex_number, `name`, speed, special_defence, special_attack, defence, attack, hp, primary_type, secondary_type) 
                     VALUES (110, 'Weezing', 60, 70, 85, 120, 90, 65, 'Poison', 'null');
INSERT INTO pokemon (pokedex_number, `name`, speed, special_defence, special_attack, defence, attack, hp, primary_type, secondary_type) 
                     VALUES (111, 'Rhyhorn', 25, 30, 30, 95, 85, 80, 'Ground', 'Rock');
INSERT INTO pokemon (pokedex_number, `name`, speed, special_defence, special_attack, defence, attack, hp, primary_type, secondary_type) 
                     VALUES (112, 'Rhydon', 40, 45, 45, 120, 130, 105, 'Ground', 'Rock');
INSERT INTO pokemon (pokedex_number, `name`, speed, special_defence, special_attack, defence, attack, hp, primary_type, secondary_type) 
                     VALUES (113, 'Chansey', 50, 105, 35, 5, 5, 250, 'Normal', 'null');
INSERT INTO pokemon (pokedex_number, `name`, speed, special_defence, special_attack, defence, attack, hp, primary_type, secondary_type) 
                     VALUES (114, 'Tangela', 60, 40, 100, 115, 55, 65, 'Grass', 'null');
INSERT INTO pokemon (pokedex_number, `name`, speed, special_defence, special_attack, defence, attack, hp, primary_type, secondary_type) 
                     VALUES (115, 'Kangaskhan', 90, 80, 40, 80, 95, 105, 'Normal', 'null');
INSERT INTO pokemon (pokedex_number, `name`, speed, special_defence, special_attack, defence, attack, hp, primary_type, secondary_type) 
                     VALUES (116, 'Horsea', 60, 25, 70, 70, 40, 30, 'Water', 'null');
INSERT INTO pokemon (pokedex_number, `name`, speed, special_defence, special_attack, defence, attack, hp, primary_type, secondary_type) 
                     VALUES (117, 'Seadra', 85, 45, 95, 95, 65, 55, 'Water', 'null');
INSERT INTO pokemon (pokedex_number, `name`, speed, special_defence, special_attack, defence, attack, hp, primary_type, secondary_type) 
                     VALUES (118, 'Goldeen', 63, 50, 35, 60, 67, 45, 'Water', 'null');
INSERT INTO pokemon (pokedex_number, `name`, speed, special_defence, special_attack, defence, attack, hp, primary_type, secondary_type) 
                     VALUES (119, 'Seaking', 68, 80, 65, 65, 92, 80, 'Water', 'null');
INSERT INTO pokemon (pokedex_number, `name`, speed, special_defence, special_attack, defence, attack, hp, primary_type, secondary_type) 
                     VALUES (120, 'Staryu', 85, 55, 70, 55, 45, 30, 'Water', 'null');
INSERT INTO pokemon (pokedex_number, `name`, speed, special_defence, special_attack, defence, attack, hp, primary_type, secondary_type) 
                     VALUES (121, 'Starmie', 115, 85, 100, 85, 75, 60, 'Water', 'Psychic');
INSERT INTO pokemon (pokedex_number, `name`, speed, special_defence, special_attack, defence, attack, hp, primary_type, secondary_type) 
                     VALUES (122, 'Mr-mime', 90, 120, 100, 65, 45, 40, 'Psychic', 'Fairy');
INSERT INTO pokemon (pokedex_number, `name`, speed, special_defence, special_attack, defence, attack, hp, primary_type, secondary_type) 
                     VALUES (123, 'Scyther', 105, 80, 55, 80, 110, 70, 'Bug', 'Flying');
INSERT INTO pokemon (pokedex_number, `name`, speed, special_defence, special_attack, defence, attack, hp, primary_type, secondary_type) 
                     VALUES (124, 'Jynx', 95, 95, 115, 35, 50, 65, 'Ice', 'Psychic');
INSERT INTO pokemon (pokedex_number, `name`, speed, special_defence, special_attack, defence, attack, hp, primary_type, secondary_type) 
                     VALUES (125, 'Electabuzz', 105, 85, 95, 57, 83, 65, 'Electric', 'null');
INSERT INTO pokemon (pokedex_number, `name`, speed, special_defence, special_attack, defence, attack, hp, primary_type, secondary_type) 
                     VALUES (126, 'Magmar', 93, 85, 100, 57, 95, 65, 'Fire', 'null');
INSERT INTO pokemon (pokedex_number, `name`, speed, special_defence, special_attack, defence, attack, hp, primary_type, secondary_type) 
                     VALUES (127, 'Pinsir', 85, 70, 55, 100, 125, 65, 'Bug', 'null');
INSERT INTO pokemon (pokedex_number, `name`, speed, special_defence, special_attack, defence, attack, hp, primary_type, secondary_type) 
                     VALUES (128, 'Tauros', 110, 70, 40, 95, 100, 75, 'Normal', 'null');
INSERT INTO pokemon (pokedex_number, `name`, speed, special_defence, special_attack, defence, attack, hp, primary_type, secondary_type) 
                     VALUES (129, 'Magikarp', 80, 20, 15, 55, 10, 20, 'Water', 'null');
INSERT INTO pokemon (pokedex_number, `name`, speed, special_defence, special_attack, defence, attack, hp, primary_type, secondary_type) 
                     VALUES (130, 'Gyarados', 81, 100, 60, 79, 125, 95, 'Water', 'Flying');
INSERT INTO pokemon (pokedex_number, `name`, speed, special_defence, special_attack, defence, attack, hp, primary_type, secondary_type) 
                     VALUES (131, 'Lapras', 60, 95, 85, 80, 85, 130, 'Water', 'Ice');
INSERT INTO pokemon (pokedex_number, `name`, speed, special_defence, special_attack, defence, attack, hp, primary_type, secondary_type) 
                     VALUES (132, 'Ditto', 48, 48, 48, 48, 48, 48, 'Normal', 'null');
INSERT INTO pokemon (pokedex_number, `name`, speed, special_defence, special_attack, defence, attack, hp, primary_type, secondary_type) 
                     VALUES (133, 'Eevee', 55, 65, 45, 50, 55, 55, 'Normal', 'null');
INSERT INTO pokemon (pokedex_number, `name`, speed, special_defence, special_attack, defence, attack, hp, primary_type, secondary_type) 
                     VALUES (134, 'Vaporeon', 65, 95, 110, 60, 65, 130, 'Water', 'null');
INSERT INTO pokemon (pokedex_number, `name`, speed, special_defence, special_attack, defence, attack, hp, primary_type, secondary_type) 
                     VALUES (135, 'Jolteon', 130, 95, 110, 60, 65, 65, 'Electric', 'null');
INSERT INTO pokemon (pokedex_number, `name`, speed, special_defence, special_attack, defence, attack, hp, primary_type, secondary_type) 
                     VALUES (136, 'Flareon', 65, 110, 95, 60, 130, 65, 'Fire', 'null');
INSERT INTO pokemon (pokedex_number, `name`, speed, special_defence, special_attack, defence, attack, hp, primary_type, secondary_type) 
                     VALUES (137, 'Porygon', 40, 75, 85, 70, 60, 65, 'Normal', 'null');
INSERT INTO pokemon (pokedex_number, `name`, speed, special_defence, special_attack, defence, attack, hp, primary_type, secondary_type) 
                     VALUES (138, 'Omanyte', 35, 55, 90, 100, 40, 35, 'Rock', 'Water');
INSERT INTO pokemon (pokedex_number, `name`, speed, special_defence, special_attack, defence, attack, hp, primary_type, secondary_type) 
                     VALUES (139, 'Omastar', 55, 70, 115, 125, 60, 70, 'Rock', 'Water');
INSERT INTO pokemon (pokedex_number, `name`, speed, special_defence, special_attack, defence, attack, hp, primary_type, secondary_type) 
                     VALUES (140, 'Kabuto', 55, 45, 55, 90, 80, 30, 'Rock', 'Water');
INSERT INTO pokemon (pokedex_number, `name`, speed, special_defence, special_attack, defence, attack, hp, primary_type, secondary_type) 
                     VALUES (141, 'Kabutops', 80, 70, 65, 105, 115, 60, 'Rock', 'Water');
INSERT INTO pokemon (pokedex_number, `name`, speed, special_defence, special_attack, defence, attack, hp, primary_type, secondary_type) 
                     VALUES (142, 'Aerodactyl', 130, 75, 60, 65, 105, 80, 'Rock', 'Flying');
INSERT INTO pokemon (pokedex_number, `name`, speed, special_defence, special_attack, defence, attack, hp, primary_type, secondary_type) 
                     VALUES (143, 'Snorlax', 30, 110, 65, 65, 110, 160, 'Normal', 'null');
INSERT INTO pokemon (pokedex_number, `name`, speed, special_defence, special_attack, defence, attack, hp, primary_type, secondary_type) 
                     VALUES (144, 'Articuno', 85, 125, 95, 100, 85, 90, 'Ice', 'Flying');
INSERT INTO pokemon (pokedex_number, `name`, speed, special_defence, special_attack, defence, attack, hp, primary_type, secondary_type) 
                     VALUES (145, 'Zapdos', 100, 90, 125, 85, 90, 90, 'Electric', 'Flying');
INSERT INTO pokemon (pokedex_number, `name`, speed, special_defence, special_attack, defence, attack, hp, primary_type, secondary_type) 
                     VALUES (146, 'Moltres', 90, 85, 125, 90, 100, 90, 'Fire', 'Flying');
INSERT INTO pokemon (pokedex_number, `name`, speed, special_defence, special_attack, defence, attack, hp, primary_type, secondary_type) 
                     VALUES (147, 'Dratini', 50, 50, 50, 45, 64, 41, 'Dragon', 'null');
INSERT INTO pokemon (pokedex_number, `name`, speed, special_defence, special_attack, defence, attack, hp, primary_type, secondary_type) 
                     VALUES (148, 'Dragonair', 70, 70, 70, 65, 84, 61, 'Dragon', 'null');
INSERT INTO pokemon (pokedex_number, `name`, speed, special_defence, special_attack, defence, attack, hp, primary_type, secondary_type) 
                     VALUES (149, 'Dragonite', 80, 100, 100, 95, 134, 91, 'Dragon', 'Flying');
INSERT INTO pokemon (pokedex_number, `name`, speed, special_defence, special_attack, defence, attack, hp, primary_type, secondary_type) 
                     VALUES (150, 'Mewtwo', 130, 90, 154, 90, 110, 106, 'Psychic', 'null');
INSERT INTO pokemon (pokedex_number, `name`, speed, special_defence, special_attack, defence, attack, hp, primary_type, secondary_type) 
                     VALUES (151, 'Mew', 100, 100, 100, 100, 100, 100, 'Psychic', 'null');

Exercises 1

  • Explain: What does the dataset contain?

Retrieve the following data by querying:

  • All pokemon with all their columns

  • Only the name of all pokemon

  • All grass type pokemon

  • All pokemon with a speed above 56

  • All pokemon with a secondary type

  • Is MySQL case sensitive? Explain with an example

Exercises 2

Retrieve the following data by querying:

  • The name of all pokemon with a special defence less than 56 and a special attack higher than 65

  • All grass type pokemon starting with the letter 'B'

  • All fire or ground pokemon with an attack higher than 50

  • The first 10 pokemon by pokedex number ordered by their speed

Advanced queries (Optional)

  • All pokemon without a secondary type, starting with 'P' with an attack or defence above 40

  • The 10 pokemon with the highest attack

  • All pokemon that contains the latter 'C' in their name - ordered alphabetically

PreviousData types & ScalesNextData Quality & Cleaning

Last updated