부속질의

부속질의

하나의 SQL문 안에 다른 SQL문이 중첩된 질의

다른 테이블에서 가져온 데이터로 현재 테이블에 있는 정보를 찾거나 가공할때 사용

데이터가 대량일 경우 조인보다 성능이 더 좋음

주질의와 부속질의로 구성됨

주질의는 외부질의라고도 하며 부속질의는 내부질의라고 함

 

스칼라 부속질의 - select 부속질의

SELECT절에서 사용되며 단일 값을 반환하기 때문에 스칼라 부속질의라고 한다.

 

인라인 뷰 - FROM 부속질의

FROM절에서 결과를 뷰 형태로 반환하기 때문에 인라인 뷰라고 한다.

 

중첩 질의 - WHERE 부속질의

WHERE절에 술어와 같이 사용되며 결과를 한정시키기 위해 사용된다.

상관 혹은 비 상관 형태이다.

 

스칼라 부속질의

SELECT 절에서 사용되는 부속질의

부속질의의 결과 값을 단일 행, 단일 열의 스칼라 값으로 반환한다.

SELECT문과 UPDATE SET절에 사용된다.

주질의와 부속질의와의 관계는 상관/비상관 모두 가능하다.

 

 

문제1

고객별 판매액을 보이시오. 단, 결과는 고객이름과 고객별 판매액을 출력한다.

select (select cname from cust where cust.cid=orders.cid) as name,
sum(select price from book where book.bid=orders.bid) as price 
from orders group by cid;

 

문제2

orders 테이블에 각 주문에 맞는 도서이름을 입력하시오.

update table orders 
set title = (select title from book where book.bid=orders.bid);

oreders 테이블에 title 속성 추가하기

alter table orders add title varchar(20);

 

문제3

안양사는 고객이 주문한 정보를 출력하시오. 단, 결과는 고객이름, 주문번호, 주문일을 출력한다.

select (select cname from cust where cust.cid=orders.cid), oid, odate 
from orders 
where cid in (select cid from cust where addr = 'anyang');

헷갈릴수도 있는 부분

 

 

인라인 뷰

FROM 절에서 사용되는 부속질의

테이블 이름 대신 인라인 뷰 부속질의를 사용하면 보통의 테이블과 같은 형태로 사용가능

가상의 테이블인 뷰 형태로 제공되기 때문에 상관 부속질의로 사용될 수는 없음

 

 

문제1

고객번호가 2이하인 고객의 판매액을 보이시오. 단, 결과는 고객이름과 고객별 판매액을 출력한다.

select cname, sum(price) 
from (select cid,cname from cust where cid<=2) cs, orders, (select price from book where book.bid=orders.bid) b 
where cs.cid=orders.cid and b.bid=orders.bid  
group by cid;

 

문제2

주문한 고객의 이름과 주문일, 책이름을 보이되, 주문하지 않은 고객의 이름과 주문하지 않은 책 이름도 함께 보여라.

select cname, odate, title
from (select cname from cust left outer join orders on cust.cid=orders.cid) cs
left outer join book on book.bid=orders.bid
union
select cname, odate, title 
from (select cname from cust left outer join orders on cust.cid=orders.cid) cs 
left outer join book on book.bid=orders.bid

 

 

중첩질의

WHERE절에서 사용되는 부속질의

술어 부속질의라고도 함

술어	연산자	반환 행	반환 열	상관
비교	=, ＞, ＜, ＞=, ＜=, ＜＞	단일	단일	가능
집합	IN, NOT IN	다중	단일	가능
한정(quantified)	ALL, SOME(ANY)	다중	단일	가능
존재	EXISTS, NOT EXISTS	다중	다중	필수

 

 

문제1

평균 주문금액 이하의 주문에 대해서 주문번호와 금액

select oid,price
from orders.book
where orders.bid=book.bid and
price <= (select avg(price) from orders,book 
where orders.bid=book.bid);

 

문제2

각 고객별 평균 주문금액보다 큰 금액의 주문 내역에 대해서 주문번호, 고객번호, 금액을 보이시오. (상관 부속질의)

select oid,cid, price 
from (select oid,cid,price from orders natural join book) t1 
where t1.price > (select avg(price) 
from (select oid,cid,price from orders natural join book) t2 
where t1.cid=t2.cid)

 

문제3

1번 고객이 주문한 도서의 최고 금액보다 더 비싼 도서를 구입한 주문의 주문번호와 금액을 보이시오.

select oid, price 
from orders od,book b
where od.bid=b.bid and
price > all(select price from orders od, book b where od.bid=b.bid and cid=1);