PokemonSqlLab/Part 3

# What is each pokemon's primary type?
SELECT pokemon.pokemons.name, pokemon.types.name
FROM pokemon.pokemons
       INNER JOIN pokemon.types
WHERE types.id = primary_type;

# What is Rufflet's secondary type?
SELECT pokemon.types.name
FROM pokemon.pokemons
       JOIN pokemon.types ON pokemon.pokemons.secondary_type = pokemon.types.id
WHERE pokemon.pokemons.name = "Rufflet";

# What are the names of the pokemon that belong to the trainer with trainerID 303?
SELECT pokemon.pokemons.name
FROM pokemon.pokemons
       JOIN pokemon.pokemon_trainer ON pokemon.pokemons.id = pokemon.pokemon_trainer.pokemon_id
WHERE pokemon.pokemon_trainer.trainerID = 303;

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

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

# How many pokemon at level 100 does each trainer with at least one level 100 pokemone have? (Hint: your query should not display a trainer
SELECT COUNT(pokemon.pokemon_trainer.pokemon_id)
FROM pokemon.pokemon_trainer
WHERE pokemon.pokemon_trainer.pokelevel = 100
GROUP BY pokemon.pokemon_trainer.trainerID;

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