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) ;
'SQL' 카테고리의 다른 글
서브쿼리(select, from, where) (0) | 2022.06.27 |
---|---|
UNION, UNION ALL, 교집합 , 차집합 (0) | 2022.06.27 |
LEFT JOIN, INNER JOIN , 차집합 (0) | 2022.06.27 |
UDF(User Define Function), Delimiter (0) | 2022.06.27 |
IF, SUM IF, IFNULL, CASE WHEN (0) | 2022.06.27 |