서브쿼리 위치에 따른 용어

select 절 : 스칼라 서브쿼리

• select 절에 컬럼 이름이 오는 곳에 사용되는 쿼리를 말한다.
• 단일 행이 오도록 표현해야 한다. 따라서 반드시 조건 식이 필요하다
• 메인 쿼리와 서브 쿼리의 조건이 항상 필요하므로 상관서브쿼리(서로 상관이 있음을 표현)라고도 부른다.
• 예제

-- 고객별 고객 아이디, 이름, 총주문 금액 출력
select custid,(
    -- 앞의 custid 와 관련이 있는 name의 단일 행만 가져온다.
    select name from customer c
    where c.custid = o.custid
) name, sum(saleprice)
from orders o
group by custid;

-- 스칼라서브쿼리(= 상관 서브쿼리)를 이용하여
-- 도서명별 판매 건수를 출력한다.
select (
	--  bookid 와 관련이 있는 bookname 의 단일 행만 가져온다.
    select bookname from book b
    where b.bookid = o.bookid
) bookname,count(*) from orders o
group by bookid;

-- 스칼라 서브 쿼리를 이용하여
-- 부서별로 근무자 수, 평균 급여, 최고 급여를 출력한다.
select (
        select dname from dept d2
        where d.dno = d2.dno
    ) 부서명, (
        select count(eno) from emp e
        where d.dno = e.dno
    ) 인원수,
    (
        select avg(nvl(salary, 0)) from emp e
        where d.dno = e.dno
    ) 평균급여,
    (
        select max(nvl(salary, 0)) from emp e
        where d.dno = e.dno
    ) 최고급여
from dept d
group by dno;

• update 문에도 사용되는 스칼라 서브쿼리
  • updatwe 테이블명 set 컬럼명 = 값 [where ~]
  • updatwe 테이블명 set 컬럼명 = (select ~) [where ~]
  • 예제

-- 실습을 위하여 orders 테이블에 bookname 칼럼을 추가한다.
alter table orders add bookname varchar2(50);

-- update 에 스칼라 서브 쿼리를 이용하여 
-- book 테이블에 있는 책 제목을 orders table
-- 칼럼에 내용을 추가한다.
select * from orders;

update orders o set bookname = (
    select bookname from book b
    where o.bookid = b.bookid
);

select * from orders;

/*
    데이터의 삭제
    - delete 테이블 [where 조건]

    컬럼의 삭제(DDL)
    -- alter table 테이블이름 drop column 컬럼이름

    테이블 삭제(DDL)
    -- drop 테이블명
*/
alter table orders drop column bookname;

 

from 절 : 인라인 뷰(뷰 = 존재하지 않는 가상의 테이블)

• 서브쿼리가 from 절에 오는 경우를 말한다.
• 실제로는 존재하지 않는 select 한 결과를 가상의 테이블(View) 이라고 한다.

--실습을 위해 추가
insert into book values(14, '재미있는 자바', 34000, '삼성당');
insert into book values(15, '신나는 자바', 34000, '삼성당');
				
insert into orders values(16,5,14,34000,sysdate);
insert into orders values(17,1,14,34000,sysdate);
insert into orders values(18,2,14,34000,sysdate);
insert into orders values(19,3,14,34000,sysdate);
insert into orders values(20,4,14,34000,sysdate);
insert into orders values(21,1,15,34000,sysdate);
insert into orders values(22,2,15,34000,sysdate);
insert into orders values(23,2,14,34000,sysdate);
insert into orders values(24,2,1,34000,sysdate);
insert into orders values(25,2,2,34000,sysdate);
insert into orders values(26,2,3,34000,sysdate);
insert into orders values(27,3,2,34000,sysdate);
insert into orders values(28,3,15,34000,sysdate);

-- 급여가 가장 낮은 5명의 직원에게 특별상여금을 지급하려고 한다.
--대상자의 사원번호, 사원명, 상여금을 출력한다.
-- 상여금은 급여의 100% 이다
select * from (
    select rownum no, a.* from (
        select eno, ename, salary bonus from emp
        order by salary, comm
    ) a
) where no <= 5;

-- 오늘 날짜에 가장 많이 판매된 도서의 2권의 정보를 출력해봅시다
select * from book
where bookid in (
    select bookid from(   
        select rownum, a.* from 
        (
            select bookid, count(*) cnt 
            from orders
            where to_char(orderdate,'yy/mm/dd') = to_char(sysdate,'yy/mm/dd')
            -- sysdate으로 추가하게 되면 년월일시분초에대한 정보가 함께 추가가된다.
            group by bookid    
            order by cnt desc, bookid desc
        ) a
    ) where rownum <=2
);

 

where 절 : 중첩쿼리(= 중첩 질의)

• 단일 행 연산자, 다중행 연산자를 구분하여 사용해야 한다.
  • 단일행 연산자 (=, >, <, >=, <=) : 서브 쿼리의 건 수가 한 건(단일 행) 일 때 사용할 수 있다.
  • 다중 행 연산자 (in) : 서브 쿼리의 건수가 여러 건(다중 행)일 때, 사용할 수 있다.
    • >, < , >=, <= 는 any(some) - 아무거나, all- 모두 과 같이 사용해야 한다.
    • 또, 서브쿼리의 건 수가 있는지 없는지만 판단하려면 exist, not exist 를 사용할 수 있다.

-- 평균 주문 금액 이하의 주문번호, 주문 금액 출력
-- 단일 행 연산자 사용
select orderid, saleprice from ORDERS
where saleprice <= 
(select avg(saleprice) avg from orders);

-- 대한민국에 거주하는 고객의 아이디 출력
select custid from customer
where address like '대한민국%';

-- 대한민국에 거주하는 고객의 총 주문 금액을 출력
select sum(saleprice)
from orders
where custid in (
    select custid from customer
    where address like '대한민국%'
);

-- 4번 고객이 주문한 모든 주문 금액보다 더 비싼 도서를
-- 구입한 주문의 주문번호와 금액을 출력하라
select orderid, saleprice from orders
where saleprice > (
    select max(saleprice) from orders
    where custid = 4
);

- '박지성'에게 어떤 도서를 추천 해 주면 좋을까요?
- '박지성'이 구매한 도서와 
	동일한 도서를 가장 많이 구매한 고객 번호 2명을 출력

- '박지성'이 구매한 도서번호를 출력
select bookid from 
orders where custid = (select custid from customer
						where name = '박지성');

    14     1     3     2    15
    
- 고객번호별로 위의 도서를 구매한 건수를 출력   
select custid,count(custid)
from orders 
where bookid in (select bookid from 
orders where custid = (select custid from customer
						where name = '박지성'))  
and custid <> 
(select custid from customer where name = '박지성')
group by custid
order by count(custid) desc;

CUSTID COUNT(CUSTID)
------ -------------
     2             6
     3             3
     5             1


- 위의 결과에 행번호를 붙여서 상위 2개의 고객번호를 출력

select custid 
from ()
where rownum <= 2;

select custid 
from (select custid,count(custid)
from orders 
where bookid in (select bookid from 
orders where custid = (select custid from customer
						where name = '박지성'))  
and custid <> 
(select custid from customer where name = '박지성')
group by custid
order by count(custid) desc)
where rownum <= 2;


CUSTID
------
     2
     3


- 위의 고객들이 주문한 도서번호를 출력

select bookid 
from orders 
where custid in ();


select distinct bookid 
from orders 
where custid in (select custid 
from (select custid,count(custid)
from orders 
where bookid in (select bookid from 
orders where custid = (select custid from customer
						where name = '박지성'))  
and custid <> 
(select custid from customer where name = '박지성')
group by custid
order by count(custid) desc)
where rownum <= 2);


    14     5    6    10     8    15     1     2     3

- 위의 결과에서 박지성이 구매한 도서목록를 빼 줍니다.
()
minus
()
    
(select distinct bookid 
from orders 
where custid in (select custid 
from (select custid,count(custid)
from orders 
where bookid in (select bookid from 
orders where custid = (select custid from customer
						where name = '박지성'))  
and custid <> 
(select custid from customer where name = '박지성')
group by custid
order by count(custid) desc)
where rownum <= 2))
minus
(select bookid from 
orders where custid = (select custid from customer
						where name = '박지성'));


==> 도서번호가 위의 결과에 해당하는 도서정보를 출력
select * from 
book
where bookid in ();


select * from 
book
where bookid in ((select distinct bookid 
from orders 
where custid in (select custid 
from (select custid,count(custid)
from orders 
where bookid in (select bookid from 
orders where custid = (select custid from customer
						where name = '박지성'))  
and custid <> 
(select custid from customer where name = '박지성')
group by custid
order by count(custid) desc)
where rownum <= 2))
minus
(select bookid from 
orders where custid = (select custid from customer
						where name = '박지성')));



select * from 
book
where bookid in ((select distinct bookid 
from orders 
where custid in (select custid 
from (select custid,count(custid)
from orders 
where bookid in (select bookid from 
orders where custid = (select custid from customer
						where name = '임유나'))  
and custid <> 
(select custid from customer where name = '임유나')
group by custid
order by count(custid) desc)
where rownum <= 2))
minus
(select bookid from 
orders where custid = (select custid from customer
						where name = '임유나')));

	
SQL> select * from
  2  book
  3  where bookid in ((select distinct bookid
  4  from orders
  5  where custid in (select custid
  6  from (select custid,count(custid)
  7  from orders
  8  where bookid in (select bookid from
  9  orders where custid = (select custid from customer
 10  where name = '임유나'))
 11  and custid <>
 12  (select custid from customer where name = '임유나')
 13  group by custid
 14  order by count(custid) desc)
 15  where rownum <= 2))
 16  minus
 17  (select bookid from
 18  orders where custid = (select custid from customer
 19  where name = '임유나')));

선택된 레코드가 없습니다.
==> 구매이력이 없는 사람은 추천도서가 없어요!