[MySQL로 배우는 데이터베이스 개론과 실습] Chapter 3. 실습문제 풀이 - 2 (질의 3-18 ~ 3-33)

 

 

 

🙌 더보기 란을 열기 전 먼저 문제를 풀어보자.

 

 

 

---

 

3-18. 마당서점의 도서 판매 건수를 구하시오

code)

SELECT COUNT(*) AS "도서 판매 건수"
FROM Orders;

 

result)

 

 

3-19. 고객별로 주문한 도서의 총 수량과 총 판매액을 구하시오.

code)

SELECT custid AS "고객 아이디", COUNT(*) AS "도서 수량", SUM(saleprice) AS "총액"
FROM Orders
GROUP BY custid;

 

result)

 

 

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

code)

SELECT custid AS "고객 아이디", COUNT(*) AS "도서 수량"
FROM Orders
WHERE saleprice >= 8000
GROUP BY custid
HAVING COUNT(*) >= 2;

 

result)

 

 

3-21. 고객과 고객의 주문에 관한 데이터를 모두 보이시오.

code)

SELECT *
FROM Customer, Orders
WHERE Customer.custid = Orders.custid;

 

result)

 

 

3-22. 고객과 고객의 주문에 관한 데이터를 고객별로 정렬하여 보이시오.

code)

SELECT *
FROM Customer, Orders
WHERE Customer.custid = Orders.custid
ORDER BY Customer.custid;

 

result)

 

 

3-23. 고객의 이름과 고객이 주문한 도서의 판매가격을 검색하시오.

code)

SELECT name, saleprice
FROM Customer, Orders
WHERE Customer.custid = Orders.custid;

 

result)

 

 

3-24. 고객별로 주문한 모든 도서의 총 판매액을 구하고, 고객별로 정렬하시오.

code)

SELECT Customer.custid, name, SUM(saleprice) AS "총액"
FROM Customer, Orders
WHERE Customer.custid = Orders.custid
GROUP BY Customer.custid
ORDER BY Customer.custid;

 

result)

 

 

3-25. 고객의 이름과 고객이 주문한 도서의 이름을 구하시오.

code)

SELECT Customer.name, Book.bookname
FROM Customer, Orders, Book
WHERE Customer.custid = Orders.custid AND
      Orders.bookid = Book.bookid;

 

result)

 

 

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

code)

SELECT Customer.name, Book.bookname
FROM Customer, Book, Orders
WHERE Customer.custid = Orders.custid AND
      Orders.bookid = Book.bookid AND
      Book.price = 20000;

 

result)

 

 

⭐3-27. 도서를 구매하지 않은 고객을 포함하여 고객의 이름과 고객이 주문한 도서의 판매가격을 구하시오.

code)

SELECT Customer.name, saleprice
FROM Customer LEFT OUTER JOIN Orders
     ON Customer.custid = Orders.custid;

 

result)

 

reff)

A : Customer

B : Orders

 

 

3-28. 가장 비싼 도서의 이름을 보이시오.

code)

SELECT bookname AS "최고가 도서"
FROM Book
WHERE price = (SELECT MAX(price)
               FROM Book);

 

result)

 

 

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

code)

- solution 1 : 축약형

SELECT DISTINCT name
FROM Customer, Orders
WHERE Customer.custid IN (Orders.custid);

- solution 2

SELECT DISTINCT name
FROM Customer, Orders
WHERE Customer.custid IN (SELECT custid
                          FROM Orders);

 

result)

 

 

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

code)

- solution 1 : 축약형

SELECT name
FROM Customer, Orders, Book
WHERE Customer.custid = Orders.custid AND
      Orders.bookid = Book.bookid AND
      Book.publisher LIKE '대한미디어';

-solution 2

SELECT name
FROM Customer
WHERE Customer.custid IN (SELECT custid
                          FROM Orders
                          WHERE bookid IN (SELECT bookid
                                          FROM Book
                                          WHERE publisher LIKE '대한미디어'));

 

result) 

 

 

⭐3-31. 출판사별로 출판사의 평균 도서 가격보다 비싼 도서를 구하시오.

code)

SELECT b1.bookname
FROM Book b1
WHERE b1.price > (SELECT AVG(b2.price)
                  FROM Book b2
                  WHERE b2.publisher = b1.publisher);

 

result)

 

reff)

- 출판사별 평균 도서 가격

- 이보다 비싼 도서

두 가지 데이터 모두 Book 테이블에서 나오게 된다. 💡별칭 사용

 

 

❗Subquery 작성이 어렵다면 하위 부속질의를 먼저 작성해보자.

 

1. 출판사별 평균 도서 가격을 구하는 쿼리

SELECT AVG(b2.price)
FROM Book b2
GROUP BY b2.publisher;

2. 이보다 비싼 도서를 구하는 쿼리

SELECT b1.bookname
FROM Book b1
WHERE b1.price > -- 출판사별 평균 가격 --

3. 부속질의 작성 시 출판사별 결합을 GROUP BY로 둘 경우 1 이상의 행을 반환하기 때문에 오류가 발생한다. 따라서, 하나씩 살펴보기 위해 b1.publisher와 b2.publisher가 같을 때로 조건을 변경한다.

SELECT b1.bookname
FROM Book b1
WHERE b1.price > (SELECT AVG(b2.price)
                  FROM Book b2
                  WHERE b2.publisher = b1.publisher);

 

 

3-32. 대한민국에서 거주하는 고객의 이름과 도서를 주문한 고객의 이름을 보이시오. (UNION 연산)

code)

SELECT name
FROM Customer
WHERE address LIKE '대한민국%'
UNION 
SELECT name
FROM Customer, Orders
WHERE Customer.custid IN (Orders.custid);

 

result)

 

 

3-32-(1). 대한민국에서 거주하는 고객의 이름과 도서를 주문한 고객의 이름을 중복을 포함해 보이시오.

code) 

SELECT name
FROM Customer
WHERE address LIKE '대한민국%'
UNION ALL
SELECT name
FROM Customer, Orders
WHERE Customer.custid IN (Orders.custid);

 

result)

 

 

3-32-(2). 대한민국에서 거주하는 고객의 이름에서 도서를 주문한 고객의 이름을 빼고 보이시오. (MINUS 연산)

code)

- solution 1 : 축약형

SELECT DISTINCT name 
FROM Customer, Orders
WHERE address LIKE '대한민국%' AND
	  Customer.custid NOT IN (SELECT custid
							  FROM Orders
                              WHERE Customer.custid = Orders.custid);

- solution 2

SELECT name 
FROM Customer
WHERE address LIKE '대한민국%' AND
	  name NOT IN (SELECT name
                       FROM Customer
                       WHERE custid IN (SELECT custid
                                        FROM Orders));

 

result)

  

-- 오류 발생 : 이유 미상...
-- SELECT DISTINCT name 
-- FROM Customer, Orders
-- WHERE address LIKE '대한민국%' AND
--       Customer.custid NOT IN (Orders.custid);

 

 

3-32-(3). 대한민국에서 거주하는 고객 중 도서를 주문한 고객의 이름을 보이시오. (INTERSECT 연산)

code)

- solution 1 : 축약형

SELECT DISTINCT name 
FROM Customer, Orders
WHERE address LIKE '대한민국%' AND
      Customer.custid = Orders.custid AND
      Customer.custid IN (Orders.custid);

 - solution 2 

SELECT name
FROM Customer
WHERE address LIKE '대한민국%' AND
      name IN (SELECT name
               FROM Customer
               WHERE custid IN (SELECT custid
                                FROM Orders));

 

result)

 

 

3-33. 주문이 있는 고객의 이름과 주소를 보이시오.

code)

- solution 1 : IN 연산

SELECT DISTINCT name, address
FROM Customer, Orders
WHERE Customer.custid IN (Orders.custid);

-solution 2 : EXISTS 연산

SELECT name, address
FROM Customer
WHERE EXISTS(SELECT *
             FROM Orders
             WHERE Customer.custid = Orders.custid);

 

result)