4 장 서브쿼리와 집합연산(UNION, INTERSECT, EXCEPT)
4.1 서브쿼리(SUB QUERY)
서브쿼리
하나의 SQL문 처리 결과를 다른 SQL문에 전달하여 새로운 결과를 검색하는 기능
이 경우 ORDER BY 절은 사용이 불가
서브쿼리의 검색결과가 여러 행:
IN, EXISTS등 다중행 비교연산자를 사용SELECT * FROM tab1 WHERE col = ( SELECT * FROM tab2 WHERE ... );
예제:
김태희와 같은 학년인 모든 학생의 이름, 키, 몸무게를 검색SELECT name AS "이름", weight AS "몸무게", height AS "키", grade AS "학년" FROM student WHERE grade=(SELECT grade FROM student WHERE name="김태희");학과이름에 문과가 포함된 학과에 소속된 모든 학생의 번호, 이름, 학년, 학과번호를 검색
SELECT id AS 학번, name as 이름, grade as 학년, did as 학과번호 FROM student WHERE did IN (SELECT id FROM depart WHERE name LIKE '%문과%'); SELECT id AS 학번, name as 이름, grade as 학년 FROM (SELECT * FROM student WHERE grade >= 2) AS aa;
4.2 UNION (합집합)
SELECT i1 AS i FROM t1 UNION SELECT i2 AS i FROM t2;
+------+
| i |
+------+
| 1 |
| 2 |
| 3 |
| 4 |
+------+
SELECT * FROM t1 UNION SELECT * FROM t2;
+------+------+
| i1 | c1 |
+------+------+
| 1 | a |
| 2 | b |
| 3 | c |
| 2 | c |
| 3 | b |
| 4 | a |
+------+------+SELECT * FROM t3;
+------+------+
| i3 | c3 |
+------+------+
| 2 | e |
| 6 | a |
+------+------+
2 rows in set (0.00 sec)
SELECT * FROM t1 UNION SELECT * FROM t2 UNION SELECT * FROM t3;
+------+------+
| i1 | c1 |
+------+------+
| 1 | a |
| 2 | b |
| 3 | c |
| 2 | c |
| 3 | b |
| 4 | a |
| 2 | e |
| 6 | a |
+------+------+SELECT * FROM t1 LIMIT 1
UNION SELECT * FROM t2 LIMIT 1
UNION SELECT * FROM t3 LIMIT 1;
+------+------+
| i1 | c1 |
+------+------+
| 1 | a |
+------+------+
(SELECT * FROM t1 LIMIT 1)
UNION (SELECT * FROM t2 LIMIT 1)
UNION (SELECT * FROM t3 LIMIT 1);
+------+------+
| i1 | c1 |
+------+------+
| 1 | a |
| 2 | c |
| 2 | e |
+------+------+4.3 연습문제
아래는 어느 쇼핑몰에서 판매하는 상품들에 대한 고객의 주문 데이터이다. 각 데이터를 DB테이블로 만들어라
item.csv (상품목록): items 테이블
|-------|--------------|------|-----|---------|-------| | Field | Type | Null | Key | Default | Extra | |-------|--------------|------|-----|---------|-------| | pid | int(11) | NO | PRI | NULL |상품ID| | pname | varchar(150) | NO | | NULL |상품명| | price | int(11) | NO | | NULL |판매단가| |-------|--------------|------|-----|---------|-------|customer.csv (고객정보) : customers 테이블
|------------------|-------------|------|-----|---------|-------| | Field | Type | Null | Key | Default | Extra | |------------------|-------------|------|-----|---------|-------| | 회원번호 | char(8) | NO | PRI | NULL | | | 법인유무 | int(1) | YES | | NULL | | | 생년월일 | int(4) | YES | | NULL | | | 성별 | int(1) | YES | | NULL | (1:남, 2:여) | | 직업 | varchar(10) | YES | | NULL | | | 결혼유무 | char(1) | YES | | NULL | | | 회원등급 | varchar(10) | YES | | NULL | | | 가입일 | char(8) | YES | | NULL | | | 2012구매건수 | int(11) | YES | | NULL | | | 2012구매금액 | int(11) | YES | | NULL | | | addr1 | varchar(10) | YES | | NULL | 시도명 | | addr2 | varchar(10) | YES | | NULL | 시군구명 | |------------------|-------------|------|-----|---------|-------|order.csv (주문목록): orders 테이블
|--------------------|-------------|------|-----|---------|----------------| | Field | Type | Null | Key | Default | Extra | |--------------------|-------------|------|-----|---------|----------------| | no | int(11) | NO | PRI | NULL | auto_increment | | 주문일 | int(8) | NO | | NULL | | | 주문번호 | varchar(30) | NO | | NULL | | | 주문일련번호 | int(1) | NO | | NULL | | | Cust_ID | char(8) | NO | MUL | NULL | | | 상품ID | int(11) | NO | MUL | NULL | | | 주문수량 | int(11) | NO | | NULL | | | 판매단가 | int(11) | NO | | NULL | | | 결제금액 | int(11) | NO | | NULL | | |--------------------|-------------|------|-----|---------|----------------|
SQL 보기
# -----------------------
# 상품테이블
# -----------------------
DROP TABLE jskim.items;
CREATE TABLE jskim.items(
# 상품ID,상품명,판매단가
# 1,필립스아벤트 아벤트 전기 보틀워머 / 아기의 우유 / 분유를 따뜻하게,42530
pid INT NOT NULL PRIMARY KEY,
pname VARCHAR(150) NOT NULL,
price INT NOT NULL
) DEFAULT CHARACTER SET utf8
DEFAULT COLLATE utf8_general_ci;
LOAD DATA LOCAL INFILE "~/lectures2/DB/data/item.csv"
INTO TABLE jskim.items
CHARACTER SET utf8
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
IGNORE 1 LINES;
SELECT * from jskim.items limit 20;
# -----------------------
# 고객 테이블
# -----------------------
DROP TABLE jskim.customers;
CREATE TABLE jskim.customers(
# CID,법인유무,생년월일,성별,직업,결혼유무,회원등급,가입일,2012구매건수,2012구매금액,addr1,addr2
# CID_0001,1,1981,1,14,N,다이아몬드,19970709,87,5106270,경기,오산
회원번호 CHAR(8) NOT NULL PRIMARY KEY,
법인유무 INT(1),
생년월일 INT(4),
성별 INT(1), #(1:남, 2:여),
직업 VARCHAR(10),
결혼유무 CHAR(1),
회원등급 VARCHAR(10),
가입일 CHAR(8),
2012구매건수 INT,
2012구매금액 INT,
addr1 VARCHAR(10), #(시도명),
addr2 VARCHAR(10) # (시군구명)
) DEFAULT CHARACTER SET utf8
DEFAULT COLLATE utf8_general_ci;
LOAD DATA LOCAL INFILE "~/lectures2/DB/data/customer.csv"
INTO TABLE jskim.customers
CHARACTER SET utf8
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
IGNORE 1 LINES;
SELECT * from jskim.customers limit 20;
# -----------------------
# 주문 테이블
# -----------------------
DROP TABLE jskim.orders;
CREATE TABLE jskim.orders (
# 주문일,주문번호,주문일련번호,CID,상품ID,주문수량,판매단가,결제금액
# 20130103,20130103105951627810,1,CID_0001,112,1,38480,38480
no int not null auto_increment PRIMARY KEY,
주문일 INT(8) NOT NULL,
주문번호 VARCHAR(30) NOT NULL,
주문일련번호 INT(1) NOT NULL,
Cust_ID CHAR(8) NOT NULL,
상품ID INT NOT NULL,
주문수량 INT NOT NULL,
판매단가 INT NOT NULL,
결제금액 INT NOT NULL,
FOREIGN KEY(Cust_ID) REFERENCES jskim.customers(회원번호),
FOREIGN KEY(상품ID) REFERENCES jskim.items(pid)
) DEFAULT CHARACTER SET utf8
DEFAULT COLLATE utf8_general_ci;
LOAD DATA LOCAL INFILE "~/lectures2/DB/data/order.csv"
INTO TABLE jskim.orders
CHARACTER SET utf8
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(주문일, 주문번호,주문일련번호,Cust_ID, 상품ID, 주문수량,판매단가,결제금액);
SELECT * from jskim.orders limit 20;
SELECT Cust_ID, COUNT(*) AS N from jskim.orders GROUP BY Cust_ID;- 위의 DB테이블에서 20대 여성고객이 구매한 상품들을 추출하시오.
- 고객 테이블에서 20대 여성을 추출 하여 고객번호를 저장 ==> A
- 주문테이블에서 A의 고객번호에 속한 고객들이 주문내역을 추출 ==> B
- B에서 주문 상품을 중복없이 추출
- 결혼한 40대 여성이 구매한 상품들을 추출하시오.
- 경상북도 포항시에 사는 남성이 구매한 상품의 금액은 얼마인가?
- 서울특별시에 사는 고객 중 지역(구별)에 따른 구매한 상품들을 추출하시오.
- 다이아몬드 회원등급인 고객이 구매한 상품 중 일반 회원등급 고객이 구매한 상품과 구매하지 않는 상품을 각각 추출하시오.
- 회원등급별로 2013년 1월1일 ~ 2013년 1월3일까지 구매한 상품들을 추출하시오.
4.4 실전문제
아래는 서울 지역 어느 1개월간 아파트 전월세 거래 데이터이다.
- 서울지역코드.csv
- GID: 서울 구 코드
- GNM: 서울 구 이름
- DID: 동코드
- DNM: 동이름
- 아파트등록정보.csv
- AID: 아파트ID
- ANM_FULL: 아파트단지이름(지역명포함)
- ANM: 아파트단지이름(약어)
- 건축년도: 아파트가 건축 준공된 연도
- 평균면적: 동일 아파트 단지의 아파트 평균 면적
- 아파트거래.csv
- AID: 아파트ID
- 층: 거래된 층수
- 전월세구분: 전세 또는 월세
- 전용면적_제곱미터: 거래 전용면적
- 계약일: 계약일을 월초, 월중, 월말로 구분
- 보증금_만: 전세 또는 월세 보증금
- 월세_만: 월세계약인 경우 월세 금액
- 서울지역코드.csv
구/동별 아파트 전세 및 월세 거래 건수는?
구별 아파트 전세 및 월세 거래 건수는?
구/동별 아파트 전세거래에서 평(= 3.3 제곱미터)당 평균 보증금을 계산하고 보증금이 큰 순으로 나타내라.