PokemonSqlLab/part3-JoinsAndGroup.txt

#What is each pokemon's primary type?
SELECT p.name, p.primary_type, 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, p.secondary_type, t.name
from pokemons p
    join types t
      on p.secondary_type = t.id
where p.name = 'Rufflet'
18 Flying


#What are the names of the pokemon that belong to the trainer with trainerID 303?
select p.name, pt.trainerID,pt.pokemon_id
    from pokemons p
    join pokemon_trainer pt
    on p.id = pt.pokemon_id
    where trainerID = 303
Wailord, Vileplume

#How many pokemon have a secondary type Poison
SELECT COUNT(*)
FROM pokemons
join    types
         on types.id = pokemons.secondary_type
where types.name = 'poison'
31



#What are all the primary types and how many pokemon have that type?
select  t.name, Count(*)
from pokemons p
join types t
  on t.id = p.primary_type
group by t.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 pt.trainerID, count(*)
from pokemon_trainer pt
where pt.pokelevel =100
group by pt.trainerID

#How many pokemon only belong to one trainer and no other?
select pt.pokemon_id, count(*)
from pokemon_trainer pt
group by pt.pokemon_id
HAVING COUNT(*)=1