SQL Chapter3. 연습문제

CHAPTER3 연습문제


--01. 마당서점의 고객이 요구하는 다음 질문에 대해 SQL문을 작성하시오.
--1 도서번호가 1인 도서의 이름
SELECT bookname FROM Book WHERE bookid = 1;

--2 가격이 20000원 이상인 도서의 이름
SELECT bookname FROM Book WHERE price >= 20000;

--3 박지성의 총 구매액
SELECT SUM(saleprice) FROM Orders WHERE custid = (
    SELECT custid FROM customer WHERE name = '박지성'
);

--4 박지성이 구매한 도서의 수
SELECT COUNT(*) FROM Orders WHERE custid = (
    SELECT custid FROM Customer WHERE name = '박지성'
);

--5 박지성이 구매한 도서의 출판사 수
SELECT DISTINCT COUNT(publisher) FROM Book WHERE bookid IN (SELECT bookid FROM Orders WHERE custid = (SELECT custid FROM Customer WHERE name = '박지성'));

--6 박지성이 구매한 도서의 이름, 가격, 정가와 판매가격의 차이
SELECT book.bookname FROM Orders, Customer, Book 
WHERE Orders.custid = Customer.custid AND Orders.bookid = Book.bookid AND orders.custid = 1;

--7 박지성이 구매하지 않은 도서의 이름
SELECT bookname FROM Book MINUS
SELECT book.bookname FROM Orders, Customer, Book 
WHERE Orders.custid = Customer.custid AND Orders.bookid = Book.bookid AND orders.custid = 1;

--02. 마당서점의 운영자와 경영자가 요구하는 다음 질문에 대해 SQL 문을 작성하시오.
--1 마당서점 도서의 총수
SELECT COUNT(*) FROM Book;

--2 마당서점에 도서를 출고하는 출판사의 총수
SELECT COUNT(DISTINCT publisher) FROM Book;

--3 모든 고객의 이름, 주소
SELECT name, address FROM Customer;

--4 2020년 7월 4일- 7월 7일 사이에 주문받은 도서의 주문번호
SELECT orderid FROM Orders WHERE Orderdate between '2014-07-04' and '2014-07-07';

--5 2014년 7월 4일 - 7일 제외한 도서의 주문번호 
SELECT orderid FROM Orders
MINUS SELECT orderid FROM Orders WHERE Orderdate between '2014-07-04' and '2014-07-07';

SELECT * FROM Customer;
--6 성이 '김'씨인 고객의 이름과 주소
SELECT name, address FROM Customer where name LIKE '김%';

--7 성이 '김'씨이고 이름이'아'로 끝나는 고객의 이름과 주소
SELECT name, address FROM Customer where name LIKE '김_아';

--8 주문하지 않은 고객의 이름(서브쿼리 사용)
SELECT name FROM Customer
MINUS
SELECT name FROM Customer where custid IN (SELECT custid FROM Orders);

--9 주문 금액의 총액과 주문의 평균 금액
SELECT SUM(saleprice) 주문총액, AVG(saleprice) "주문 평균금액" FROM Orders;

--10 고객의 이름과 고객별 구매액
SELECT customer.name, SUM(saleprice) FROM Orders, Customer WHERE Orders.custid = Customer.custid GROUP BY customer.name;

--11 고객의 이름과 고객이 구매한 도서 목록
SELECT customer.name, bookname FROM Orders, Customer, Book WHERE Orders.custid = Customer.custid and orders.bookid= book.bookid;

--12 도서의 가격과 판매가격의 차이가 가장 많은 주문
SELECT * FROM Book, Orders WHERE orders.bookid= book.bookid AND book.price - orders.saleprice = (
    SELECT MAX(book.price - orders.saleprice) FROM Orders, Book WHERE Orders.bookid = Book.bookid);

--13 도서의 판매액 평균보다 자신의 구매액 평균이 더 높은 고객의 이름
SELECT name FROM (SELECT customer.name, AVG(orders.saleprice)sum FROM Orders, Customer WHERE Orders.custid = Customer.custid GROUP BY customer.name) cum WHERE cum.sum >= (select avg(saleprice) from orders);