PokemonSqlLab/PokemonPart3Answers

#Part 3: Joins and Groups

# What is each pokemon's primary type?

SELECT pokemons.name, types.name
FROM pokemons
JOIN types
ON pokemons.primary_type = types.id;

# What is Rufflet's secondary type? --- couln't make it work without using the id :(

SELECT pokemons.name, pokemons.secondary_type, types.name
FROM pokemons
JOIN types
ON pokemons.secondary_type = types.id
  WHERE pokemons.id = 644;

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

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

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

# How many pokemon at lvl 100 does each trainer w/ at least one level 100 pokemone have?(query shouldn't display a trainer)
SELECT COUNT(pokemon_id)      #not sure if this is right because it seems incomplete w/o trainer name
FROM pokemon_trainer
WHERE pokelevel = 100
GROUP BY trainerID
HAVING COUNT(pokelevel) > 1;

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