PokemonSqlLab/.idea/dataSources/Results

Part 2: Simple Selects and Counts

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: Joins and Groups

What is each pokemons primary type?

select p.name, t.name
from pokemons p
join types t on p.primary_type = t.id;

What is Rufflets 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 pt.trainerID, p.name
from pokemon_trainer pt
join pokemons p on pt.pokemon_id = p.id
where pt.trainerID = 303;

How many pokemon have a secondary type Poison

select count(p.secondary_type), t.name
from pokemons p
join types t
on p.secondary_type = t.id
where p.secondary_type = 7;

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

select t.name, count(t.name)
from pokemons p
       join types t
         on p.primary_type = t.id
group by t.name;

How many pokemon at level 100 does each trainer with at least one
level 100 pokemonpoq have? (Hint: your query should not display a
trainer

select count(distinct pt.pokemon_id)
from pokemon_trainer pt
where pt.pokelevel > 99;

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

select count(1) from
(select pt.pokemon_id
 from pokemon_trainer pt
 group by pokemon_id
 having count(1) = 1) x

Part 4: Final Report

Directions: Write a query that returns the following collumns:

Pokemon Name	Trainer Name	Level	Primary Type	Secondary Type
Pokemons name	Trainers name	Current Level	Primary Type Name	Secondary Type Name
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.


select
       tr.trainername as trainer,
       p.name as pokemon,
       pt.pokelevel as pokemon_level,
       pt.hp as hit_points,
       pt.attack,
       t.name as primary_type,
       t2.name as secondary_type
from pokemon_trainer pt
    join pokemons p on pt.pokemon_id = p.id
    join trainers tr on pt.trainerID = tr.trainerID
    join types t on p.primary_type = t.id
    join types t2 on p.secondary_type = t2.id
order by pt.attack desc, pt.hp desc;

The strongest trainers have the Rayquaza pokemon, as Rayquaza has the highest hit point and the strongest attack
Chaser♂ Chotle
Chaser♀ Quelis
Cooltrainer♂ Forgon
Cooltrainer♂ Forgon
Supertrainer♀ Garvon
Myth Trainer Infin
Chaser♂ Chotle
Chaser♀ Quelis