| 1 |
- /*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
|
