[SQL] LEFT JOIN, RIGHT JOIN, FULL JOIN, CROSS JOIN, INNER JOIN

DROP DATABASE IF EXISTS pokemon
;

CREATE DATABASE pokemon
;

USE pokemon
;

CREATE TABLE mypokemon
(

number INT,
name VARCHAR(20),
type VARCHAR(10)
);
INSERT INTO mypokemon (number, name, type)
VALUES (10, 'caterpie', 'bug'),
(25, 'pikachu', 'electric'),
(26, 'raichu', 'electric'),
(133, 'eevee', 'normal'),
(152, 'chikoirita', 'grass');


CREATE TABLE ability (
number INT,
height FLOAT,
weight FLOAT,
attack INT,
defense INT,
speed int
);
INSERT INTO ability (number, height, weight, attack, defense, speed)
VALUES (10, 0.3, 2.9, 30, 35, 45),
(25, 0.4, 6, 55, 40, 90),
(125, 1.1, 30, 83, 57, 105),
(133, 0.3, 6.5, 55, 50, 55),
(137, 0.8, 36.5, 60, 70, 40),
(152, 0.9, 6.4, 49, 65, 45),
(153, 1.2, 15.8, 62, 80, 60),
(172, 0.3, 2, 40, 15, 60),
(470, 1, 25.5, 110, 130, 95);

select * from mypokemon;
select * from ability;

select name, attack, defense
from mypokemon
left join ability
on mypokemon.number=ability.number ;

select ability.number, name
from ability
left join mypokemon
on mypokemon.number=ability.number ;

select type, avg(height)
from mypokemon
left join ability
on mypokemon.number=ability.number 
group by type;

select type, avg(weight)
from mypokemon
left join ability
on mypokemon.number=ability.number 
group by type;

select type, avg(height), avg(weight)
from mypokemon
left join ability
on mypokemon.number=ability.number 
group by type;

select mypokemon.number, name, attack, defense
from mypokemon
left join ability
on mypokemon.number=ability.number 
where mypokemon.number>=100 ;

select name
from mypokemon
left join ability
on mypokemon.number=ability.number 
order by attack+defense desc;

select *
from mypokemon
left join ability
on mypokemon.number=ability.number ;

INSERT INTO ability (number, height, weight, attack, defense, speed)
VALUES (26, 0.4, 6, 55, 40, 90);

select name
from mypokemon
left join ability
on mypokemon.number=ability.number 
order by attack+defense desc;

select name
from mypokemon
left join ability
on mypokemon.number=ability.number 
order by speed desc
limit 1 ;