[MySQL로 배우는 데이터베이스 개론과 실습] Chapter 3. 연습문제 풀이 - 1~3

---

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

1-(1). 도서번호가 1인 도서의 이름

📌code

SELECT bookname
FROM Book
WHERE bookid = 1;​

📌result

1-(2). 가격이 20,000원 이상인 도서의 이름

📌code

SELECT bookname
FROM Book
WHERE price >= 20000;​

 

📌result

1-(3). 박지성의 총 구매액

📌code

SELECT SUM(saleprice) AS '총액'
FROM Orders, Customer
WHERE Orders.custid = Customer.custid AND
      Customer.name LIKE '박지성';​

📌result

1-(4). 박지성이 구매한 도서의 수

📌code

SELECT COUNT(Orders.bookid) AS '도서의 수'
FROM Book, Customer, Orders
WHERE Customer.custid = Orders.custid AND
	  Orders.bookid = Book.bookid AND
      Customer.name LIKE '박지성';​

📌reult

1-(5). 박지성이 구매한 도서의 출판사 수

📌code

SELECT COUNT(publisher) AS "출판사 수"
FROM Book, Customer, Orders
WHERE Customer.custid = Orders.custid AND
	  Orders.bookid = Book.bookid AND
      Customer.name LIKE '박지성';​

📌result

1-(6). 박지성이 구매한 도서의 이름, 가격, 정가와 판매가격의 차이

📌code

SELECT bookname, price, (price - saleprice) AS "가격 차이"
FROM Book, Customer, Orders
WHERE Customer.custid = Orders.custid AND
	  Orders.bookid = Book.bookid AND
      Customer.name LIKE '박지성';​

📌result

1-(7). 박지성이 구매하지 않은 도서의 이름

📌code

SELECT DISTINCT bookname AS "박지성이 구매하지 않은 도서"
FROM Book
WHERE Book.bookid NOT IN (SELECT Orders.bookid
                          FROM Orders
                          WHERE Orders.bookid = Book.bookid AND
                                Orders.custid = (SELECT Customer.custid
                                                 FROM Customer
                                                 WHERE name LIKE '박지성'));​

📌result

 

---

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

2-(1). 마당서점 도서의 총 개수

📌code

SELECT COUNT(DISTINCT bookid) AS "총 도서의 수"
FROM Book;​

📌result

2-(2). 마당서점에 도서를 출고하는 출판사의 총 개수

📌code

SELECT COUNT(DISTINCT publisher) AS "총 출판사의 수"
FROM Book;​

📌result

2-(3). 모든 고객의 이름, 주소

📌code

SELECT name, address
FROM Customer;​

📌result

2-(4). 2014년 7월 4일 7월 7일 사이에 주문받은 도서의 주문번호

📌code

SELECT orderid
FROM Orders
WHERE orderdate BETWEEN DATE_FORMAT("2014-07-04", "%Y-%m-%d") AND DATE_FORMAT("2014-07-07", "%Y-%m-%d");​

📌result

2-(5). 2014년 7월 4일 7월 7일 사이에 주문받은 도서를 제외한 도서의 주문번호

📌code

SELECT orderid
FROM Orders
WHERE orderdate NOT BETWEEN DATE_FORMAT("2014-07-04", "%Y-%m-%d") AND DATE_FORMAT("2014-07-07", "%Y-%m-%d");​

📌result

2-(6). 성이 ‘김’ 씨인 고객의 이름과 주소

📌code

SELECT name, address
FROM Customer
WHERE Customer.name LIKE '김%';​

📌result

2-(7). 성이 ‘김’ 씨이고 이름이 ‘아’로 끝나는 고객의 이름과 주소

📌code

FROM Customer
WHERE name LIKE '김%아';​

📌result

2-(8). 주문하지 않은 고객의 이름(부속질의 사용)

📌code

SELECT name
FROM Customer
WHERE custid NOT IN (SELECT custid
					 FROM Orders);​

📌result

2-(9). 주문 금액의 총액과 주문의 평균 금액

📌code

SELECT SUM(saleprice) AS "총액", AVG(saleprice) AS "평균 금액"
FROM Orders;​

📌result

2-(10). 고객의 이름과 고객별 구매액

📌code

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

📌result

2-(11). 고객의 이름과 고객이 구매한 도서 목록

📌code

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

📌result

2-(12). 도서의 가격(Book 테이블)과 판매가격(Orders 테이블)의 차이가 가장 많은 주문

📌code 

SELECT *
FROM Orders, Book
WHERE Orders.bookid = Book.bookid AND
      price - saleprice LIKE (SELECT MAX(price - saleprice)
                              FROM Orders, Book
                              WHERE Orders.bookid = Book.bookid);​

📌result

⭐2-(13). 도서의 판매액 평균보다 자신의 구매액 평균이 더 높은 고객의 이름

📌code

SELECT name
FROM Customer, Orders
WHERE Customer.custid = Orders.custid
GROUP BY name
HAVING AVG(saleprice) >= (SELECT AVG(saleprice)
                          FROM Orders, Customer
                          WHERE Customer.custid = Orders.custid);​

📌result

---

3. 마당서점에서 다음의 심화된 질문에 대해 SQL문을 작성하시오.

⭐3-(1). 박지성이 구매한 도서의 출판사와 같은 출판사에서 도서를 구매한 고객의 이름

📌code

SELECT DISTINCT Customer.name
FROM Customer, Orders, Book
WHERE Customer.custid = Orders.custid AND
      Book.bookid = Orders.bookid AND
	  name NOT IN ('박지성') AND
      Book.publisher IN (SELECT DISTINCT book.publisher
                         FROM Book, Customer, Orders
                         WHERE Customer.custid = Orders.custid AND
                               Book.bookid = Orders.bookid AND
                               Customer.name LIKE '박지성');​

📌result

⭐3-(2). 두 개 이상의 서로 다른 출판사에서 도서를 구매한 고객의 이름

📌code

SELECT DISTINCT name
FROM Customer, Orders, Book
WHERE Customer.custid = Orders.custid AND
      Book.bookid = Orders.bookid AND
      2 <= (SELECT COUNT(DISTINCT publisher)
            FROM Book, Orders
            WHERE Customer.custid = Orders.custid AND
		  Book.bookid = Orders.bookid);

📌result

⭐3-(3). 전체 고객의 30% 이상이 구매한 도서

📌code

SELECT DISTINCT bookname
FROM Book
WHERE (SELECT COUNT(bookid)
       FROM Orders
       WHERE Book.bookid = Orders.bookid) >= 0.3*(SELECT COUNT(*)
		                                  FROM Customer);

📌result