서브쿼리 예제

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);