SELECT number
FROM ability
WHERE EXISTS(SELECT * FROM ability WHERE type='bug');
mission 1)
SELECT number
FROM ability
WHERE weight=(SELECT MAX(weight) FROM ability);
mission 2)
SELECT number
FROM ability
WHERE speed < ANY( SELECT attack FROM ability WHERE type='electric');
mission 3)
SELECT name
FROM mypokemon
WHERE number IN (SELECT number FROM ability WHERE attack>defense);
mission 3)
SELECT name
FROM mypokemon
WHERE EXISTS (SELECT number FROM ability WHERE attack>defense);
mission 1)
SELECT height, weight, (SELECT name FROM mypokemon WHERE number=133) AS name
FROM ability
WHERE number=133;
mission 2)
SELECT number, speed
FROM ability
WHERE (SELECT RANK() OVER (ORDER BY speed DESC) AS rank_speed FROM ability)=2;
SELECT number, speed
FROM (SELECT number, speed, RANK() OVER(ORDER BY speed DESC) AS speed_rank FROM ability ) AS t1
WHERE speed_rank=2;
mission 3) **틀림! 어렵다!
SELECT name
FROM mypokemon
WHERE number IN
(SELECT number
FROM ability
WHERE defense > ALL(SELECT defense FROM ability type='electric') );
테이블만들기
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) ;
CREATE TABLE ability(
number INT,
attack INT
defense INT)
INSERT INTO ability(number, attack, defense)
VALUES(1, 20, 20);
'SQL' 카테고리의 다른 글
CREATE TABLE , 제약조건(NOT NULL, UNIQUE, DEFAULT, PK, FK) (0) | 2022.06.27 |
---|---|
DELETE, UPDATE Error CODE 1175 (0) | 2022.06.27 |
RANK, DENSE_RANK, ROW_NUMBER (0) | 2022.06.27 |
서브쿼리(select, from, where) (0) | 2022.06.27 |
UNION, UNION ALL, 교집합 , 차집합 (0) | 2022.06.27 |