연습문제

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

-- 5. 박지성이 구매한 도서의 출판사수
SELECT COUNT(DISTINCT publisher) AS `출판사 수`
FROM book
WHERE bookid IN (SELECT bookid
				FROM orders
                WHERE custid IN (SELECT custid
								FROM customer
                                WHERE name = '박지성'));

-- 6. 박지성이 구매한 도서의 이름, 가격, 정가와 판매가격의 차이
SELECT b.bookname AS '책 이름', b.price AS '정가', o.saleprice AS '판매가',
		b.price - o.saleprice AS '정가와 판매가 차이'
FROM orders o 
	JOIN book b
    ON o.bookid = b.bookid
WHERE o.custid IN (SELECT custid
					FROM customer
					WHERE name = '박지성');

-- 7. 박지성이 구매하지 않은 도서의 이름
SELECT *
FROM book b
WHERE NOT EXISTS (SELECT bookname
					FROM orders o
                    WHERE o.orderid = b.bookid -- EXISTS 쓸 땐 WHERE절의 조건식을 잘 맞춰줘야 한다
                    AND o.custid = (SELECT custid
									FROM customer
                                    WHERE name = '박지성'));

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

-- 8. 주문하지 않은 고객의 이름(부속질의 사용)
SELECT name
FROM customer
WHERE custid NOT IN (SELECT custid
					FROM orders);
-- EXISTS 사용 : 동일한 값 출력                    
SELECT name
FROM customer
WHERE NOT EXISTS (
	SELECT custid
    FROM orders
    WHERE customer.custid = orders.custid);                    

-- 9. 주문 금액의 총액과 주문의 평균금액
SELECT SUM(saleprice) AS `주문 총액`, ROUND(AVG(saleprice)) AS '주문 평균금액'
FROM orders;

-- 10. 고객의 이름과 고객별 구매액
SELECT name, SUM(saleprice)
FROM customer
	JOIN orders
    ON customer.custid = orders.custid
group by 1;

-- 11. 고객의 이름과 고객이 구매한 도서 목록
SELECT c.name, b.bookname
FROM orders o 
	JOIN customer c
    ON o.custid = c.custid
    JOIN book b
    ON o.bookid = b.bookid;

-- 12. 도서의 가격(Book 테이블)과 판매가격(Orders 테이블)의 차이가 가장 많은 주문
SELECT *, price - saleprice
FROM orders
	JOIN book
    ON orders.bookid = book.bookid
ORDER BY price - saleprice DESC
LIMIT 1;

-- 다른 사람의 풀이
SELECT *
FROM book 
	JOIN orders 
	ON book.bookid = orders.bookid
WHERE price - saleprice LIKE (
	SELECT MAX(price - saleprice)
    FROM book 
		JOIN orders 
        ON book.bookid = orders.bookid);

-- 13. 도서의 판매액 평균 보다 자신의 구매액 평균이 더 높은 고객의 이름
SELECT name
FROM customer 
	JOIN orders
    ON customer.custid = orders.custid
GROUP BY customer.name, customer.custid
HAVING AVG(orders.saleprice) > (SELECT AVG(saleprice)
								FROM orders);