PokemonSqlLab/part3.txt

//What is each pokemon's primary type?

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



//What is Rufflet's secondary type?

SELECT pokemon.pokemons.name, pokemon.types.name
FROM pokemon.pokemons
       LEFT JOIN pokemon.types
         ON pokemon.pokemons.secondary_type = pokemon.types.id
WHERE pokemons.name LIKE "Rufflet";


//What are the names of the pokemon that belong to the trainer with trainerID 303?


SELECT pokemon_trainer.trainerID, pokemon.pokemons.name
FROM pokemon.pokemon_trainer
JOIN pokemon.pokemons
  ON pokemon.pokemon_trainer.pokemon_id = pokemon.pokemons.id
WHERE pokemon_trainer.trainerID = 303;


//How many pokemon have a secondary type Poison

SELECT pokemons.name
FROM pokemon.pokemons
JOIN pokemon.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, pokemons.name
FROM pokemon.pokemons
JOIN pokemon.types
  ON pokemons.primary_type = types.id
ORDER BY types.name ASC;


//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 trainerID, pokemon_id, pokelevel
FROM pokemon.pokemon_trainer
WHERE pokelevel >= 100
ORDER BY trainerID ASC;


How many pokemon only belong to one trainer and no other?

SELECT COUNT(pokemon.pokemon_trainer.pokemon_id)
FROM pokemon.pokemon_trainer
GROUP BY pokemon_id
HAVING COUNT(DISTINCT pokemon.pokemon_trainer.trainerID) = 1;