20200409 JOIN 서브쿼리 집합연산

join 

테이블 두개, 세개를 하나로 합치는 것

눈에 보이지 않는 그걸 view라고 한다. 

 

stack 구조

가장 안에 있는 서브쿼리 가장 안에 있는 함수를 먼저 해서 순서대로

 

상관 서브쿼리

같은 테이블 끼리 붙이는데 한 테이블의 다른 컬럼 별칭 줘야한다.

 

집합연산

 

Exists 랑 in이랑 비교해서 알자 차이점을

EXISTS 한 리코드에서 (상관쿼리랑 같이 잘 쓴다.) 단항연산 앞에 컬럼 안붙음.

IN을 쓸 수 있는것은 EXISTS를 쓸 수 있음.

 

집합연산(MINUS..) 

 

JOIN

 

--1.5 박지성이 구매한 도서의 출판사 수
SELECT COUNT(DISTINCT PUBLISHER)
FROM BOOK B, customer C, ORDERS O
WHERE O.CUSTID = C.CUSTID AND O.BOOKID = B.BOOKID AND C.NAME = '박지성';

--1.6 박지성이 구매한 도서의 이름, 가격, 정가와 판매가격의 차이
SELECT B.BOOKNAME, B.PRICE 판매가, B.PRICE - O.SALEPRICE
FROM BOOK B, customer C, ORDERS O
WHERE O.CUSTID = C.CUSTID AND O.BOOKID = B.BOOKID AND C.NAME = '박지성';

--1.7 박지성이 구매하지 않은 도서의 이름
----1 MINUS
SELECT BOOK.BOOKNAME
FROM BOOK 
MINUS
SELECT BOOKNAME
FROM BOOK B, customer C, ORDERS O
WHERE O.CUSTID = C.CUSTID AND O.BOOKID = B.BOOKID AND C.NAME = '박지성';
----2 NOT IN
SELECT BOOKNAME
FROM BOOK
WHERE BOOKNAME NOT IN
(SELECT BOOKNAME 
FROM CUSTOMER, ORDERS, BOOK 
WHERE CUSTOMER.CUSTID = ORDERS.CUSTID 
AND ORDERS.BOOKID = BOOK.BOOKID
AND NAME LIKE '박지성');
----3 EXISTS 상관 서브쿼리
SELECT BOOKNAME
FROM BOOK
WHERE NOT EXISTS
(SELECT BOOKNAME 
FROM CUSTOMER, ORDERS
WHERE CUSTOMER.CUSTID = ORDERS.CUSTID 
AND ORDERS.BOOKID = BOOK.BOOKID 
AND NAME LIKE '박지성');

--2.8 NOT IN 주문하지 않은 고객의 이름 NULL말고 0값일 수 도있는 위험있다. 비추
SELECT NAME, ORDERID
FROM customer C, ORDERS O
WHERE O.CUSTID(+) = C.CUSTID AND O.ORDERID IS NULL;

----MINUS
select name
    from customer
    minus
    select distinct name
    from customer, orders, book
    where customer.custid = orders.custid
      and orders.bookid = book.bookid;
----NOT IN
SELECT C.NAME
FROM CUSTOMER C
WHERE C.NAME NOT IN (SELECT DISTICT NAME FROM CUSTOMER ORDERS WHERE CUSTOMER.CUSTID = ORDERS.CUSTID);

----not exists
select name
    from customer
    where  not exists (select distinct name
                        from orders
                        where customer.custid = orders.custid);

--2.9 주문 금액의 총액과 주문의 평균 금액
SELECT SUM(SALEPRICE) 총액, AVG(SALEPRICE) "평균 금액"
FROM ORDERS;

--2.10 고객의 이름과 고객별 구매액
SELECT C.NAME, SUM(SALEPRICE) "고객별 구매액"
FROM CUSTOMER C, ORDERS O
WHERE C.CUSTID = O.CUSTID
GROUP BY C.NAME; 

--2.11 고객의 이름과 고객이 구매한 도서 목룍
SELECT C.NAME, B.BOOKNAME
FROM CUSTOMER C, ORDERS O, BOOK B
WHERE O.CUSTID = C.CUSTID AND O.BOOKID = B.BOOKID;

--2.12 도서의 가격(BOOK 테이블)과 판매가격(ORDERS 테이블)의 차이가 가장 많은 주문
SELECT * 
FROM ORDERS O, BOOK B
WHERE O.BOOKID = B.BOOKID 
AND B.PRICE - O.SALEPRICE = (SELECT MAX(B.PRICE - O.SALEPRICE) FROM ORDERS O, BOOK B WHERE O.BOOKID = B.BOOKID);

--2.13 도서의 판매액 평균보다 자신의 구매액 평균이 더 높은 고객의 이름
SELECT NAME, AVG(SALEPRICE)
FROM ORDERS O, CUSTOMER C
WHERE O.CUSTID = C.CUSTID
GROUP BY C.NAME
HAVING AVG(SALEPRICE) >(SELECT AVG(SALEPRICE)
FROM ORDERS);