PokemonSqlLab/pokemon_sql/pokemon_part3.sql

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

/*What is Rufflet's 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 p.name, tr.trainerID
FROM pokemon.pokemons p
JOIN pokemon.pokemon_trainer tr
ON p.id = tr.pokemon_id
WHERE tr.trainerID = 303;

/*How many pokemon have a secondary type `Poison`*/
SELECT COUNT(p.id) AS pokemon_count, t.name
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.id)
FROM pokemon.pokemons p
JOIN pokemon.types t
ON p.primary_type = t.id
GROUP BY t.id;

/*How many pokemon at level 100 does each trainer with at least one level 100 pokemone have?*/
SELECT t.trainerID, COUNT(tr.pokelevel) AS pokemon_count
FROM pokemon.pokemon_trainer tr
JOIN pokemon.trainers t
ON tr.trainerID = t.trainerID
WHERE tr.pokelevel = 100
GROUP BY tr.trainerID;

/*How many pokemon only belong to one trainer and no other?*/
SELECT DISTINCT pokemon_id, COUNT(pokemon_id)
FROM pokemon_trainer
GROUP BY pokemon_id
HAVING COUNT(DISTINCT trainerID) = 1;