[DB] 부속 질의(sub query) 연습문제

1. 마당서점의 고객이 요구하는 다음 질문에 대해 SQL 문을 작성하시오.

  (5) 박지성이 구매한 도서의 출판사 수

  (6) 박지성이 구매한 도서의 이름, 가격, 정가와 판매가격의 차이

  (7) 박지성이 구매하지 않은 도서의 이름

-- 5.
SELECT PUBLISHER
FROM BOOK
WHERE BOOKID IN (SELECT BOOKID
                 FROM ORDERS
                 WHERE CUSTID=1);

-- 6.
SELECT CS.NAME, BK.BOOKNAME, BK.PRICE, BK.PRICE - OD.SALEPRICE "차이"
FROM CUSTOMER CS, ORDERS OD, BOOK BK
WHERE CS.CUSTID = OD.CUSTID AND
                OD.BOOKID = BK.BOOKID AND CS.CUSTID = 1;

-- 7.
SELECT BOOKNAME
FROM BOOK
MINUS
SELECT BOOKNAME
FROM BOOK
WHERE BOOKID IN (SELECT BOOKID
                 FROM ORDERS
                 WHERE CUSTID=1);

 

 

2. 마당서점의 운영자와 경영자가 요구하는 다음 질문에 대해 SQL 문을 작성하시오.

  (8) 주문하지 않은 고객의 이름(부속질의 사용)

  (9) 주문 금액의 총액과 주문의 평균 금액

  (10) 고객의 이름과 고객별 구매액

  (11) 고객의 이름과 고객이 구매한 도서 목록

  (12) 도서의 가격(Book 테이블)과 판매가격(Orders 테이블)의 차이가 가장 많은 주문

  (13) 도서의 판매액 평균보다 자신의 구매액 평균이 더 높은 고객의 이름

-- 8.
SELECT NAME
FROM CUSTOMER
WHERE CUSTID NOT IN (SELECT CUSTID
                     FROM ORDERS);

-- 9.
SELECT SUM(SALEPRICE) "주문총액" ,  AVG(SALEPRICE) "주문평균"
FROM ORDERS;

-- 10.
SELECT CUSTID, NAME, (SELECT SUM(SALEPRICE)
                     FROM ORDERS OD
                     WHERE OD.CUSTID = CS.CUSTID) "구매액"
FROM CUSTOMER CS
GROUP BY CUSTID, NAME
ORDER BY CUSTID;

-- 11.
SELECT CS.NAME, BK.BOOKNAME
FROM CUSTOMER CS, ORDERS, BOOK BK
WHERE CS.CUSTID = ORDERS.CUSTID AND
                ORDERS.BOOKID = BK.BOOKID
ORDER BY NAME;

-- 12.
SELECT OD.ORDERID, MAX(BK.PRICE-OD.SALEPRICE) "큰차이"
FROM ORDERS OD, BOOK BK
WHERE OD.BOOKID = BK.BOOKID AND 
                BK.PRICE-OD.SALEPRICE = (SELECT MAX(BK.PRICE-OD.SALEPRICE)  
                                      FROM ORDERS OD , BOOK BK 
                                      WHERE OD.BOOKID = BK.BOOKID)
GROUP BY  OD.ORDERID;

-- 13.
SELECT CS.NAME, AVG(OD.SALEPRICE)
FROM CUSTOMER CS, ORDERS OD
WHERE CS.CUSTID = OD.CUSTID
GROUP BY CS.NAME
HAVING AVG(OD.SALEPRICE) > (SELECT AVG(SALEPRICE) FROM ORDERS);