PokemonSqlLab/Queries/Part_3.sql

## PART 3: JOINS AND GROUPS

# What is each pokemons primary type?
SELECT pokemons.name as "Species", types.name as "Type"
FROM pokemons
JOIN types ON (types.id = primary_type);

# What is Rufflets secondary type?
SELECT pokemons.name as "Species", types.name as "SecondType"
FROM pokemons
JOIN types ON (types.id = secondary_type)
WHERE pokemons.name = "Rufflet";

# What are the names of the pokemon that belong to the trainer with trainerID 303?
SELECT trainers.trainername as "TrainerName", pokemons.name as "Species"
FROM pokemon_trainer
JOIN pokemons ON (pokemons.id = pokemon_id)
JOIN trainers ON (trainers.trainerID = pokemon_trainer.trainerID)
WHERE pokemon_trainer.trainerID = 303;

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

# What are all the primary types and how many pokemon have that type?
SELECT types.name as "PrimeType", COUNT(pokemons.primary_type) as "NumOfSpecies"
FROM pokemons
JOIN types ON (types.id = primary_type)
GROUP BY types.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 COUNT(pokelevel) as "NumOfLvl100"
FROM pokemon_trainer
WHERE pokelevel = 100
GROUP BY trainerID;

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