DAY.05

-- 질의 3-29. 도서를 구매한 적이 있는 고객의 이름을 검색하시오.

-- (1) 주문한 고객 id를 구한다. 
SELECT custid FROM orders;

-- (2) 주문한 고객 id를 customer 테이블의 where절에 사용한다. 
SELECT NAME
  FROM customer
 WHERE custid IN ( SELECT custid FROM orders );

SELECT DISTINCT C.name
  FROM customer C
  LEFT JOIN orders O
    ON O.custid = C.custid
 WHERE O.orderid IS NOT NULL;

SELECT DISTINCT C.name
  FROM customer C
 INNER JOIN orders O
    ON O.custid = C.custid;

-- 질의 3-30. 대한미디어에서 출판한 도서를 구매한 고객의 이름을 나타내시오.

-- (1) 대한미디어 출판한 bookid를 구한다.
SELECT bookid FROM book WHERE publisher = '대한미디어';


-- (2) (1) bookid를 구매한 구매 정보를 구한다.
SELECT * FROM orders 
WHERE bookid IN ( SELECT bookid FROM book WHERE publisher = '대한미디어' );

-- (3) (2) 정보에서 고객 테이블과 연결하여 고객 이름을 출력한다.
SELECT name FROM customer
WHERE custid IN ( SELECT custid FROM orders
					   WHERE bookid IN ( SELECT bookid FROM book WHERE publisher = '대한미디어' )
); 
 
SELECT C.name
  FROM customer C
 INNER JOIN orders O
    ON O.custid = C.custid
 INNER JOIN book B
    ON B.bookid = O.bookid
   AND B.publisher = '대한미디어';
   
     
SELECT *
  FROM customer C
 INNER JOIN orders O
    ON O.custid = C.custid
 INNER JOIN book B
    ON B.bookid = O.bookid
 WHERE B.publisher = '대한미디어';

-- 질의 3-31 출판사별로 각 출판사의 평균 도서 가격 초과인 도서를 구하시오.
SELECT DISTINCT publisher
FROM book;

SELECT AVG(price) FROM book
WHERE publisher = '굿스포츠';

-- 미니미션-01. 굿스포츠 출판사의 평균 도서 가격 초과인 굿스포츠 출판사의 도서를 구하시오.
SELECT * FROM book 
WHERE publisher = '굿스포츠'
AND price > ( SELECT AVG(price) FROM book WHERE publisher = '굿스포츠' );


-- 미니미션-02. 대한미디어 출판사의 평균 도서 가격 초과인 대한미디어 출판사의 도서를 구하시오.
SELECT bookname
FROM book
WHERE publisher = '대한미디어'
AND price > ( SELECT AVG(price) FROM book WHERE publisher = '대한미디어' );


-- 미니미션-03.  이상미디어 출판사의 평균 도서 가격 초과인  이상미디어 출판사의 도서를 구하시오.
SELECT bookname
FROM book 
WHERE publisher = '이상미디어'
AND price > (SELECT AVG(price) FROM book WHERE publisher = '이상미디어');

SELECT bookname
FROM book 
WHERE publisher = '삼성당'
AND price > ( SELECT AVG(price) FROM book WHERE publisher = '삼성당' );


-- 풀이
SELECT bookname, publisher
FROM book B1
WHERE B1.price > ( SELECT AVG(price) FROM book B2 WHERE B2.publisher = B1.publisher );

-- 미니미션-04. 대한민국에 거주하는 고객의 이름을 구하시오. (김연아, 김연경, 박세리)
SELECT NAME
FROM customer
WHERE address LIKE '대한민국%';

-- 김연아, 김연경, 박세리, 박지성, 추신수 
-- 질의 3-32. 대한민국에 거주하는 고객의 이름과 도서를 주문한 고객의 이름을 나타내시오. 
--            (중복제거)
SELECT NAME
FROM customer 
WHERE address LIKE '대한민국%'

UNION

SELECT name
FROM customer
WHERE custid IN ( SELECT custid FROM orders );


SELECT NAME
FROM customer 
WHERE address LIKE '대한민국%'

UNION

SELECT C.name
FROM customer C
INNER JOIN orders O
ON O.custid = C.custid;