PokemonSqlLab/Queries/part3.txt

#Part 3: Joins and Groups

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

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

## What are the names of the pokemon that belong to the trainer with trainerID 303?
SELECT trainer.trainername AS "Trainer", GROUP_CONCAT(pok.name) AS "Pokemons"
FROM pokemon.pokemons pok
JOIN pokemon.pokemon_trainer pokTrainer
         ON pokTrainer.pokemon_id = pok.id
JOIN pokemon.trainers trainer
         ON pokTrainer.trainerID = trainer.trainerID
WHERE trainer.trainerID = 303
GROUP BY  trainer.trainername;

## How many pokemon have a secondary type Poison
SELECT COUNT(pok.secondary_type) AS "Poison Pokemons"
FROM pokemon.pokemons pok
JOIN pokemon.types types
          ON pok.secondary_type = types.id
WHERE types.name = "Poison";

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

## How many pokemon at level 100 does each trainer with at least one level 100 pokemon have?
## (Hint: your query should not display a trainer
SELECT COUNT(trainer.pokemon_id) as "Pokemon at Level 100"
FROM pokemon.pokemon_trainer trainer
WHERE trainer.pokelevel = 100
GROUP BY trainer.trainerID;

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