PokemonSqlLab/pokemon_sql/PART3

#*****************************************
##PART 3: JOINS AND GROUPS###

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


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

#Flying

#What are the names of the pokemon that belong to the trainer with trainerID 303?
SELECT DISTINCT pokemons.name, pokemon.pokemon_trainer.trainerID
FROM pokemon.pokemons
 JOIN pokemon.pokemon_trainer on trainerID
WHERE trainerID LIKE  303;
#500


#How many pokemon have a secondary type Poison

SELECT COUNT(pokemons.secondary_type)
FROM pokemon.pokemons
JOIN pokemon.types on types.id = secondary_type
WHERE types.name LIKE 'Poison';
#31

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

#Normal	  90
#Water	  95
#Grass	  59
#Rock	  38
#Fire     38
#Ground   29
#Poison	  27
#Bug	  61
#Electric 35
#Dragon	  22
#Steel	  19
#Dark	  24
#Fighting 25
#Psychic  38
#Ghost	  19
#Fairy	  14
#Ice	  22
#Flying	  1


#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 pokemon_trainer.trainerID, COUNT(pokemon_trainer.pokelevel)
FROM pokemon.pokemon_trainer
WHERE pokelevel LIKE 100
GROUP BY trainerID;
#194

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