PokemonSqlLab/Answers.rtf

{\rtf1\ansi\ansicpg1252\cocoartf1561\cocoasubrtf600
{\fonttbl\f0\fswiss\fcharset0 Helvetica;}
{\colortbl;\red255\green255\blue255;}
{\*\expandedcolortbl;;}
\margl1440\margr1440\vieww14220\viewh11000\viewkind0
\pard\tx720\tx1440\tx2160\tx2880\tx3600\tx4320\tx5040\tx5760\tx6480\tx7200\tx7920\tx8640\pardirnatural\partightenfactor0

\f0\fs24 \cf0 Part 2:\
\
What are all the types of pokemon that a pokemon can have?\

\b SELECT name FROM types;\
\

\b0 What is the name of the pokemon with id 45?\

\b SELECT name FROM pokemons WHERE id=45;
\b0 \
\
How many pokemon are there?\

\b SELECT COUNT(id) from pokemons;
\b0 \
\
How many types are there?\

\b SELECT COUNT(id) from types;
\b0 \
\
How many pokemon have a secondary type?\

\b SELECT COUNT(secondary_type) FROM pokemons;
\b0 \
\
\
Part 3:\
\
What is each pokemon's primary type?\

\b SELECT pokemons.name, types.name FROM pokemons INNER JOIN types ON pokemons.primary_type=types.id;
\b0 \
\
What is Rufflet's secondary type?\

\b SELECT pokemons.name, types.name FROM pokemons INNER JOIN types ON pokemons.primary_type=types.id WHERE pokemons.name='Rufflet';
\b0 \
\
What are the names of the pokemon that belong to the trainer with trainerID 303?\

\b SELECT pokemons.name FROM pokemons INNER JOIN pokemon_trainer ON pokemon_trainer.pokemon_id=pokemons.id WHERE trainerID=303;
\b0 \
\
How many pokemon have a secondary type Poison\

\b SELECT COUNT(secondary_type) FROM pokemons INNER JOIN types ON pokemons.secondary_type=types.id WHERE types.name='poison';
\b0 \
\
What are all the primary types and how many pokemon have that type?\

\b SELECT COUNT(pokemons.primary_type)  FROM pokemons JOIN types ON pokemons.primary_type=types.id GROUP BY types.name; 
\b0 \
[[[Can only get the counts to display in order by name, but does not display names of Types. having trouble subquerying for the names of the types]]]\
\
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\

\b SELECT COUNT(pokemon_trainer.pokelevel) FROM pokemon_trainer JOIN trainers ON pokemon_trainer.pokelevel=trainers.trainerID WHERE pokemon_trainer.pokelevel=100;\
\

\b0 How many pokemon only belong to one trainer and no other?
\b \
SELECT COUNT(DISTINCT pokemon_trainer.trainerID) FROM pokemon_trainer;\
\

\b0 Part 4 (Final Report)\
\

\b \
}