#*+*+*+*+*+*+*+*+*+**++*+*+*+*+*+*
#Part 2: Simple Selects and Counts
#*+*+*+*+*+*+*+*+*+**++*+*+*+*+*+*

# What are all the types of pokemon that a pokemon can have?
select distinct (name) from pokemon.types
# What is the name of the pokemon with id 45?
select name,id from pokemon.pokemons
where id = 45

#How many pokemon are there?
select count(distinct(name)) as pokemon from pokemon.pokemons

#How many types are there?
select count(*) as "Num of Pokemon types"
from pokemon.types

#How many pokemon have a secondary type?
select count(distinct(secondary_type)) as secondary_type from pokemon.pokemons


#*+*+*+*+*+*+*+*+*+**++*+*
#Part 3: Joins and Groups
#*+*+*+*+*+*+*+*+*+**++*+*

#What is each pokemon's primary type?
SELECT x.name as pokemon_name, y.name as pokemon_type
FROM pokemon.pokemons x LEFT JOIN pokemon.types y
ON x.primary_type = y.id;

#What is Rufflet's secondary type?
SELECT x.name, y.name as secondary_type
FROM pokemon.pokemons x LEFT JOIN pokemon.types y
ON x.secondary_type = y.id
WHERE x.name = 'Rufflet';

#What are the names of the pokemon that belong to the trainer with trainerID 303?
SELECT z.trainername as "Trainer", group_concat(x.name) as "Pokemons"
FROM pokemon.pokemons x LEFT JOIN pokemon.pokemon_trainer y
ON y.pokemon_id = x.id LEFT JOIN pokemon.trainers z
ON y.trainerID = z.trainerID
WHERE z.trainerID = 303
GROUP BY z.trainername;

#How many pokemon have a secondary type Poison?
SELECT count(*) as secondary_type_Poison
FROM pokemon.pokemons x LEFT JOIN pokemon.types y
ON x.secondary_type = y.id
WHERE y.name = 'Poison';

#What are all the primary types and how many pokemon have that type?
SELECT x.name as primary_types, count(y.name) as pokemon_count
FROM pokemon.types x LEFT JOIN pokemon.pokemons y
ON x.id = y.primary_type
GROUP BY x.name;

#How many pokemon at level 100 does each trainer with at least one level 100 pokemon have?
select trainerid, count(pokelevel) as level_100_pokemon
from pokemon.pokemon_trainer
where pokelevel = 100
group by trainerid;

#How many pokemon only belong to one trainer and no other?
SELECT count(1) as pokemon_has_one_trainer
FROM (select x.name, count(y.trainerID)
      from pokemon.pokemons x left join pokemon.pokemon_trainer y
      on x.id = y.pokemon_id
      group by x.name
      having count(y.trainerID) = 1) a;


#*+*+*+*+*+*+*+*+*+**++*+*
#Part 4: Final Report
#*+*+*+*+*+*+*+*+*+**++*+*

#Final Report [explain your decision - Pokemon with the highest level is the strongest pokemon and has strongest trainer and the bottom one has the weakest trainer]
SELECT a.name as Pokemon_Name	, b.trainername as Trainer_Name	, c.PokeLevel, d.name as Primary_Type	, e.name as Secondary_Type
from pokemon.pokemon_trainer c     join pokemon.pokemons a
         on a.id = c.pokemon_id    join pokemon.trainers b
         on b.trainerID = c.trainerID join pokemon.types d
         on a.primary_type = d.id     join pokemon.types e
         on a.secondary_type = e.id
order by PokeLevel desc #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.