/*How many types of pokemon can a pokemon have?*/
SELECT COUNT(*) FROM pokemon.types;

/*What is the name of the pokemon with id 45?*/
SELECT * FROM pokemon.pokemons WHERE id LIKE '45';

/*How many pokemon are there?*/
SELECT COUNT(*) FROM pokemon.pokemons;

/*How many types are there?*/
SELECT COUNT(DISTINCT primary_type) FROM pokemons;

/*How many pokemon have a secondary type?*/
SELECT COUNT(secondary_type) From pokemons;

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

/*What is Rufflet's secondary type?*/
SELECT t.name
FROM pokemons p
JOIN types t
ON p.primary_type = t.id
WHERE p.name LIKE 'Rufflet';

/*What are the names of the pokemon that belong to the trainer with trainerID 303?*/
SELECT p.name
FROM pokemons p
JOIN pokemon_trainer pt
ON pt.pokemon_id=p.id
JOIN trainers t
ON t.trainerID=pt.trainerID
WHERE t.trainerID = '303';

/*How many pokemon have a secondary type Poison*/
SELECT p.name
FROM pokemons p
JOIN types t
ON p.secondary_type = t.id
Where t.name = 'Poison';

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

/*How many pokemon at level 100 does each trainer with at least one level 100 pokemon have?*/
Select pt.trainerID, pt.pokelevel
FROM pokemon_trainer pt
WHERE pt.pokelevel=100
GROUP BY pt.trainerID;

/* How many pokemon only belong to one trainer and no other?*/
Select COUNT(pokemon_id)
FROM (
SELECT  pt.pokemon_id
FROM pokemon_trainer pt
GROUP BY pt.pokemon_id
HAVING COUNT(*)=1
    ) AS once;

/*Final Report- I sorted the pokemon according to their pokeLevel, then attak level, then defense level to show the trainer with the strogest Pokemon at the top. I know nothing about pokemon except what I've learned in this Lab.*/
SELECT p.name as Name, tr.trainername as Trainer, pt.pokelevel as Level, t.name AS PrimaryType, t2.name AS SecondaryType
FROM pokemon.pokemons p
JOIN types t
ON t.id=p.primary_type
JOIN types t2
ON t2.id=p.secondary_type
JOIN pokemon_trainer pt
ON p.id=pt.pokemon_id
JOIN trainers tr
ON pt.trainerID=tr.trainerID
ORDER BY pt.pokelevel DESC, pt.attack DESC , pt.defense DESC;