PokemonSqlLab/Part 3: Joins & Groups.sql

#What is each pokemons primary type?
SELECT p.name, t.name
FROM pokemon.pokemons p
JOIN pokemon.types t
ON p.primary_type = t.id;

#What is Rufflets secondary type?
SELECT p.name, t.name
FROM pokemon.pokemons p
JOIN pokemon.types t
ON p.secondary_type = t.id
WHERE p.name = 'Rufflet';

#What are the names of the pokemon that belong to the trainer with trainerID 303?
SELECT t.trainername as "Trainer", GROUP_CONCAT(p.name) as "Pokemons"
FROM pokemon.pokemons p
    JOIN pokemon.pokemon_trainer pt
    ON pt.pokemon_id = p.id
JOIN pokemon.trainers t
ON pt.trainerID = t.trainerID
WHERE t.trainerID = 303
GROUP BY t.trainername;

#How many pokemon have a secondary type Poison?
SELECT COUNT(*)
FROM pokemon.pokemons p
JOIN pokemon.types t
ON p.secondary_type = t.id
WHERE t.name = 'Poison';

#What are all the primary types and how many pokemon have that type?
SELECT t.name, COUNT(p.name)
FROM pokemon.types t
JOIN pokemon.pokemons p
ON p.primary_type = t.id
GROUP BY t.name;

#How many pokemon at level 100 does each trainer with at least one level 100 pokemon have?
SELECT t.trainername as "Trainer", pt.pokelevel
FROM pokemon.trainers t
JOIN pokemon.pokemon_trainer pt
ON pt.trainerID = t.trainerID
WHERE pt.pokelevel = 100
GROUP BY t.trainername;

#How many pokemon only belong to one trainer and no other?
SELECT p.name as "Pokemon",
       COUNT(t.trainername) as "Trainers"
FROM pokemon.pokemons p
    JOIN pokemon.pokemon_trainer pt
    ON pt.pokemon_id = p.id
JOIN pokemon.trainers t
ON pt.trainerID = t.trainerID
GROUP BY p.name
HAVING COUNT(t.trainername) = 1;