/*Part 1*/
/*What are all the types of pokemon that a pokemon can have?*/
select distinct name from pokemon.pokemons order by name;

/*What is the name of the pokemon with id 45?*/
select * from pokemon.pokemons where id = 45;

/*How many pokemon are there?*/
select count(ID) from pokemon.pokemons;

/*How many types are there?*/
select * from pokemon.types;

/*How many pokemon have a secondary type?*/
select count(distinct (secondary_type))
from pokemon.pokemons;


/*Part 3*/
/*What is each pokemon's primary type?*/
select t.name as name_type, pk.name as pokemon_name, pk.primary_type  from pokemon.pokemons  pk
                left join pokemon.types t
                  on pk.primary_type = t.id;

/*What is Rufflet's secondary type?*/

select t.name as name_type, pk.name as pokemon_name, pk.secondary_type
from pokemon.pokemons  pk
left join pokemon.types t
on pk.secondary_type = t.id
    where pk.name = "Rufflet";

/*What are the names of the pokemon that belong to the
trainer with trainerID 303?*/

select distinct(pok.name) as pokemon_name,
       t.trainerID as trainer_id,
       pk.trainername as trainer_name
from pokemon.trainers  pk
       left join pokemon.pokemon_trainer t
         on pk.trainerID = t.trainerID
      left join pokemon.pokemons pok
          on pok.id =t.pokemon_id
          where pk.trainerID = 303;

/*How many pokemon have a secondary type Poison*/
select count(pk.name) as num_of_pokemon,
       t.name as name_type,
       pk.secondary_type
from pokemon.pokemons  pk
       left join pokemon.types t
         on pk.secondary_type = t.id
where t.name in ("poison")
group by t.name,pk.secondary_type;


/*What are all the primary types and how many
pokemon have that type? */

select count(pk.name) as num_of_pokm_prim_typ,
       t.name as prim_name_type,
       pk.primary_type as primary_type_id
from pokemon.pokemons  pk
left join pokemon.types t
on pk.primary_type = t.id
where pk.primary_type is not null
group by t.name, pk.primary_type
order by num_of_pokm_prim_typ desc;


/*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(pok.name) as num_of_pokmn,
               t.pokelevel
from pokemon.pokemon_trainer t
       left join pokemon.pokemons pok
         on pok.id =t.pokemon_id
where t.pokelevel = 100
group by t.pokelevel;


/*How many pokemon only belong to one trainer
and no other?*/

select * from (
select pok.name as pokemon_name,
               count(pk.trainername) as trainer_name
from pokemon.trainers  pk
       left join pokemon.pokemon_trainer t
         on pk.trainerID = t.trainerID
       left join pokemon.pokemons pok
         on pok.id =t.pokemon_id group by pok.name) a
where a.trainer_name = 1;

# Part 4: Final Report

select pok.name as "Pokemon Name",
pk.trainername as "Trainer Name",
t.pokelevel as "Level",
ptyp.name as "Primary Type",
styp.name as "Secondary Type"
from pokemon.trainers  pk
left join pokemon.pokemon_trainer t
on pk.trainerID = t.trainerID
left join pokemon.pokemons pok
on pok.id =t.pokemon_id
left join pokemon.types ptyp
on pok.primary_type = ptyp.id
left join pokemon.types styp
 on pok.secondary_type = styp.id
order by t.pokelevel desc, pok.secondary_type desc