--What is the name of the pokemon with id 45?
select p.name from pokemons p where p.id = 40

--How many pokemon are there?
select count(*) from pokemons

--How many types are there?
select count(*) from types as PokemonType

--How many pokemon have a secondary type?
select count(p.name) from pokemons p where p.secondary_type is not null

--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 p.name, t.name
from pokemons p
join types t
on p.secondary_type = t.id
where p.name = "Rufflet"

----What are the names of the pokemon that belong to the trainer with trainerID 303?
select p.name
from pokemon_trainer pt
join pokemons p
on pt.pokemon_id = p.id
join trainers t
on pt.trainerID = t.trainerID
where pt.trainerID = 303

--How many pokemon have a secondary type Poison
select p.name, t.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  t.id = p.primary_type
group by t.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
create temporary table amount100
select count(pt.pokelevel) as amount, trainerID
from pokemon_trainer pt
where pt.pokelevel = 100
group by trainerID
having amount > 1;
select amount as "amount of level 100", count(trainerID) as "trainers with this amount"
from amount100
where amount > 0
group by amount;

--How many pokemon only belong to one trainer and no other?
select
  p.name, count(distinct trainerID) as trainerAmt
from
  pokemon_trainer pt
join
  pokemons p
on
    p.id = pt.pokemon_id
group by
    p.name
having
     count(distinct trainerID) = 1;

/*

Sort the data by finding out which trainer has the strongest
pokemon so that this will act as a ranking of strongest to
weakest trainer. You may interpret strongest in whatever way
you want, but you will have to explain your decision.

- I chose to sort the pokemon first by ghost type,
I did this because ghost type is tied with dragon type for the
least amount of vulnerabilities. Dragon is only vulnerable to steel
as where ghost is only vulnerable to dark however there are more
less dark type pokemon in the database then there are steel type
indicating that dragon is more likely to face a challenging opponant.
I then calculated the sum of all of its attributes and divided this
by the amount of attributes total, effectively gather the AVG of its
stats.
 */


select count(ty.name),
       p.name,
       (sum(pt.attack + pt.spatk + pt.defense + pt.spdef + pt.hp + pt.maxhp + pt.speed)/7) as "AVGSTATS",
       pt.pokelevel,
       t.trainerID

from pokemon_trainer pt
join pokemons p
on p.id = pt.pokemon_id
join trainers t
on t.trainerID = pt.trainerID
join types ty
on ty.id = p.primary_type
join types ty2
on ty2.id = p.secondary_type
where ty.name = "Ghost"
group by
         p.name,
         pt.pokelevel,
         t.trainerID
order by AVGSTATS desc