DAY.04

-- Q-21. 가격이 8,000원 이상인 도서를 구매한 고객에 대하여 고객별 주문
--       도서의 총수량을 구하시오. 단, 2권 이상 구매한 고객에 대해서만 구하시오.

--  (1)8,000원 이상인 도서를 구매한 고객 정보를 가져온다.
SELECT *
FROM orders
WHERE saleprice >= 8000;

-- (2) (1)에서 구한 정보에서 고객별 구매 수량을 구한다.
SELECT custid, COUNT(custid)
FROM orders
WHERE saleprice >= 8000
GROUP BY custid;

-- (3) (2)에서 구한 정보에서 구매 수량이 2권 이상인 고객만 구한다.
SELECT custid, COUNT(custid)
FROM orders
WHERE saleprice >= 8000
GROUP BY custid
HAVING COUNT(custid) >= 2;




SELECT custid, COUNT(custid) AS cnt
FROM orders
WHERE saleprice >= 8000
GROUP BY custid
HAVING cnt >= 2;

/*
p.165 조인 (join)

*/

SELECT *
FROM orders
WHERE custid = 1;


SELECT *
FROM orders A
JOIN customer B
ON A.custid = B.custid;

-- 주문정보에서 고객 정보 출력
SELECT A.orderid, A.bookid, A.saleprice, A.orderdate
	  , A.custid
     , B.custid 
	  , B.name, B.address, B.phone
  FROM orders A
  JOIN customer B
    ON A.custid = B.custid;
    
-- p.166 질의 3-21   
SELECT *
FROM customer A, orders B
WHERE A.custid = B.custid;
    

-- 주문정보에서 고객 정보, 도서 정보 출력
SELECT A.orderid, A.bookid, A.saleprice, A.orderdate
	  , A.custid
     , B.custid 
	  , B.name, B.address, B.phone
	  , C.bookid, C.bookname, C.publisher, C.price
  FROM orders A
  JOIN customer B
    ON A.custid = B.custid
  JOIN book C
    ON A.bookid = C.bookid;

-- 질의 3-22. 고객과 고객의 주문에 관한 데이터를 custid 컬럼 기준으로 
--           오름차순으로 정렬하여 나타내시오.
SELECT *
  FROM customer C
  JOIN orders O
    ON C.custid = O.custid
 ORDER BY C.custid;

-- 질의 3-23. 고객의 이름과 고객이 주문한 도서의 판매가격을 검색하시오.
SELECT C.`name`, O.saleprice
  FROM customer C
  JOIN orders O
    ON C.custid = O.custid;

-- 질의 3-24. 고객별로 주문한 모든 도서의 총판매액을 구하고 고객별로 정렬하시오.
SELECT C.`name`, SUM(saleprice)
  FROM customer C
  JOIN orders O
    ON C.custid = O.custid
 GROUP BY C.name
 ORDER BY C.name;
 
-- 질의 3-25. 고객의 이름과 고객이 주문한 도서의 이름을 구하시오.
SELECT C.`name`, B.bookname
FROM orders O
JOIN book B
ON B.bookid = O.bookid
JOIN customer C
ON O.custid = C.custid; 

SELECT C.`name`, B.bookname
FROM book B
JOIN orders O
ON O.bookid = B.bookid
JOIN customer C
ON O.custid = C.custid; 


-- 질의 3-26. 가격이 20,000원인 도서를 주문한 고객의 이름과 도서의 이름을 구하시오.

-- (1) 책가격이 20,000인 도서 정보를 구한다.
SELECT *
FROM book 
WHERE price = 20000;

-- (2) (1)의 정보에서 구매 정보를 연결한다.
SELECT *
FROM book B
INNER JOIN orders O
ON O.bookid = B.bookid
WHERE B.price = 20000;

-- (3) (2)의 정보에서 고객 정보를 연결하고, 표시해야되는 컬럼만 선택한다.
SELECT C.`name`, B.bookname
FROM book B
INNER JOIN orders O
ON O.bookid = B.bookid
INNER JOIN customer C
ON C.custid = O.custid
WHERE B.price = 20000;



/*
LEFT JOIN
질의 3-27. 도서를 구매하지 않은 고객을 포함해 고객의 이름과 고객이 주문한 도서의
            판매가격을 구하시오.
*/

SELECT C.`name`, IFNULL(O.saleprice, 0) AS saleprice
FROM customer C
LEFT JOIN orders O
ON O.custid = C.custid;

/*
서브 쿼리 (Sub Query)
SELECT IN SELECT, 서브쿼리는 소괄호로 감싸주어야 한다.
서브 쿼리를 사용할 수 있는 부분 

스칼라 서브 쿼리라는 개념이 있다.

(1) SELECT - FROM 사이에 작성, 꼭 스칼라 서브 쿼리만 가능
(2) FROM 테이블명 적는 부분 - (호칭: 인라인 뷰, INLINE VIEW)
(3) WHERE 절에서도 사용 가능, equal 비교였을 때는 스칼라 서브 쿼리여야 한다. 
*/

SELECT *, ( SELECT MAX(price) FROM book ) FROM customer;

-- 질의 3-28. 가장 비싼 도서의 이름을 나타내시오.


-- 책 중에 가장 비싼 가격을 알아내봅시다.
SELECT MAX(price)
FROM book;

-- 비싼 책의 이름을 나타내시오.
SELECT bookname
FROM book
WHERE price = 35000;

-- 비싼 책의 이름을 나타내시오.
SELECT bookname
FROM book
WHERE price = ( SELECT MAX(price) FROM book );


SELECT bookname
FROM book
WHERE price IN ( SELECT price FROM book WHERE price >= 20000 );