USE pokemon;

-- Part 2

-- What are all the types of pokemon that a pokemon can have?
SELECT name
FROM types;
-- What is the name of the pokemon with id 45?
SELECT name
FROM pokemons
WHERE id = 45;
-- How many pokemon are there?
SELECT COUNT(id)
FROM pokemons;
-- How many types are there?
SELECT COUNT(id)
FROM types;
-- How many pokemon have a secondary type?
SELECT COUNT(id)
FROM pokemons
WHERE secondary_type IS NOT null;

-- Part 3

-- What is each pokemon's primary type?
SELECT pokemons.name AS "pokemon species", types.name AS "type"
FROM pokemons
JOIN types ON (types.id = primary_type);
-- What is Rufflet's secondary type?
SELECT pokemons.name AS "Pokemon", types.name AS "Secondary Type"
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 "Trainer Name", pokemons.name AS "Pokemon"
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 "Number of Pokemon", types.name as "Secondary Type"
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 "Primary Type", COUNT(pokemons.primary_type) as "Pokemon Count"
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 "Number of Level l100"
FROM pokemon_trainer
WHERE pokelevel = 100
GROUP BY trainerID;

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

-- PART 4

-- Arranged by highest average stats with slightly more weight in health and speed since you either need to hit first or withstand a hit in order to inflict damage to the enemy pokemon
SELECT pokemons.name AS "Pokemon Name", trainers.trainername AS "Trainer Name", pokemon_trainer.pokelevel as "Level", ANY_VALUE(1st.name) as "Primary Type", ANY_VALUE(2nd.name) as "Secondary Type"
FROM pokemon_trainer
JOIN trainers ON (trainers.trainerID = pokemon_trainer.trainerID)
JOIN pokemons ON (pokemons.id = pokemon_id)
JOIN types 1st ON (1st.id = pokemons.primary_type)
JOIN types 2nd ON (2nd.id = pokemons.secondary_type)
GROUP BY pokemons.name, trainers.trainerID, pokelevel
ORDER BY pokelevel DESC, SUM((pokemon_trainer.maxhp*1.5) + pokemon_trainer.attack + pokemon_trainer.defense + pokemon_trainer.spatk + pokemon_trainer.spdef + (pokemon_trainer.speed*1.5) / 6) DESC, pokemons.name DESC;