DROP DATABASE IF EXISTS pokemon;
CREATE DATABASE pokemon;
USE pokemon;
CREATE TABLE mypokemon (
number int,
name varchar(20),
type varchar(10),
height float,
weight float
);
INSERT INTO mypokemon (number, name, type, height, weight)
VALUES (10, 'caterpie', 'bug', 0.3, 2.9),
(25, 'pikachu', 'electric', 0.4, 6),
(26, 'raichu', 'electric', 0.8, 30),
(125, 'electabuzz', 'electric', 1.1, 30),
(133, 'eevee', 'normal', 0.3, 6.5),
(137, 'porygon', 'normal', 0.8, 36.5),
(152, 'chikoirita', 'grass', 0.9, 6.4),
(153, 'bayleef', 'grass', 1.2, 15.8),
(172, 'pichu', 'electric', 0.3, 2),
(470, 'leafeon', 'grass', 1, 25.5);
use pokemon;
select * from mypokemon;
select type, avg(weight) as "AVG(weight)"
from mypokemon
where length(name)>5
group by type
having avg(weight)>=20
order by 2 desc;
select type, min(height) as "MIN(height)", max(height) as "MAX(height)"
from mypokemon
where number<200
group by type
having max(weight)>=10 and min(weight)>=2
order by 2 desc, 3 desc;
select type, avg(height)
from mypokemon
group by type;
select type, avg(weight)
from mypokemon
group by type;
select type, avg(height), avg(weight)
from mypokemon
group by type;
select type
from mypokemon
group by type
having avg(height)>=0.5;
select type
from mypokemon
group by type
having avg(weight)>=20;
select type, sum(number)
from mypokemon
group by type;
select type, count(1)
from mypokemon
where height>=0.5
group by type;
select type, min(height)
from mypokemon
group by type;
select type, max(weight)
from mypokemon
group by type;
select type
from mypokemon
group by type
having min(height)>0.5 and max(weight)<30;
