JOIN 실습

mission 1)
SELECT t1.name, t2.attack, t2.defense
FROM mypokemon AS t1
LEFT JOIN ability AS t2
ON t1.number= t2.number ;

 

mission 2)
SELECT t1.number, t2.name
FROM ability AS t1
LEFT JOIN mypokemon AS t2
ON t1.number = t2.number ;

 

mission 1)
SELECT t1.type, t2.AVG(height)
FROM mypokemon AS t1
INNER JOIN ability AS t2
ON t1.number=t2.number
GROUP BY t1.type;

mission 2)
SELECT t1.type, t2.AVG(weight)
FROM mypokemon AS t1
INNER JOIN ability AS t2
ON t1.number=t2.number
GROUP BY t1.type;

mission 3)
SELECT t1.type, t2.AVG(height), t2.AVG(weight)
FROM mypokemon AS t1
INNER JOIN ability AS t2
ON t1.number=t2.number
GROUP BY t1.type;

mission 4)
SELECT t1.number, t1.name, t2.attack, t2.defense
FROM mypokemon AS t1
INNER JOIN ability AS t2
ON t1.number=t2.number
WHERE t1.number>=100;

mission 5)
SELECT t1.name
FROM mypokemon AS t1
INNER JOIN ability AS t2
ON t1.number=t2.number
ORDER BY 2 DESC ;

mission 6)
SELECT t1.name, t2.speed
FROM mypokemon AS t1
INNER JOIN ability AS t2
ON t1.number=t2.number
ORDER BY t2.speed DESC
LIMIT 1 ;

 

 

테이블 만들기

DROP DATABASE IF EXISTS pokemon;
CREATE DATABASE pokemon;
USE pokemon;
CREATE TABLE mypokemon(
name VARCHAR(20),
number INT,
);
INSERT INTO mypokemon(name, number)
VALUES ('pikachu', 1)
VALUES ('raichu',2) ;