PokemonSqlLab/Part 3

#What is each pokemon's primary type?

select pokemons.name, pokemons.primary_type from Pokemon.pokemons

select * from pokemon.pokemons p join types t on p.primary = t.id;

#What is Rufflet's secondary type

18

SELECT name, secondary_type from Pokemon.pokemons where name = "Rufflet"

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

Wailord and Vileplume

select distinct (po.name) as Pokemon, pt.trainerID as TrainerID,
pk.trainername as Trainername from pokemon.trainers pk
left join pokemon.pokemon_trainer pt ON pk.trainerID = pt.trainerID
left join Pokemon.pokemons po ON po.id=pt.pokemon_id
where pk.trainerID = 303;

#How many pokemon have a secondary type Poison

31

select count(pokemons.id) from pokemon.pokemons
LEFT JOIN Pokemon.types ON types.id = pokemons.secondary_type
where types.name = "Poison";

#What are all the primary types and how many pokemon have that type?

656 pokemons

select distinct pokemons.name, Pokemon.types.name  from pokemons
 join types on pokemons.primary_type = Pokemon.types.id

 select t.name, count(p.name) from pokemon.pokemons p
 join types t
 on p.primary_type = t.id
 group by t.name


#How many pokemon at level 100 does each trainer with at least one level 100 pokemon have?

963

SELECT count(pp.name) AS numberofpokemon, pt.pokelevel FROM pokemon.pokemon_trainer pt
LEFT JOIN pokemon.pokemons pp ON pt.pokemon_id =  pp.id
WHERE pt.pokelevel = 100 GROUP BY pt.pokelevel;


SELECT trainername, t.trainerID, COUNT(pt.pokelevel) FROM pokemon_trainer pt
JOIN trainers t
ON pt.trainerID = t.trainerID
WHERE pokelevel = 100
GROUP BY 2,1;

// 2 = second field, 1 = first field

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

14

Select COUNT(*) AS "Unique"
FROM (SELECT DISTINCT pokemon_id, COUNT(pokemon_id)
FROM pokemon.pokemon_trainer
GROUP BY pokemon_id
HAVING COUNT(DISTINCT trainerID)=1) alias;