--1. 고객별로 주문된 주문건수와 최고주문금액 검색
-- SELECT ORDERID,MAX(PRICE) FROM ORDERS GROUP BY CUSTOMER(me);
SELECT NAME, COUNT(*), MAX(SALEPRICE)
FROM CUSTOMER C, ORDERS O
WHERE C.CUSTID = O.CUSTID
GROUP BY NAME;
--2. 도서별로 주문된 주문건수와 총 주문액을 검색
--SELECT ORDERID,SALPRICE FROM ORDERS(me);
SELECT BOOKNAME, COUNT(*), SUM(SALEPRICE)
FROM BOOK B NATURAL JOIN ORDERS O
GROUP BY BOOKNAME;
SELECT BOOKNAME, COUNT(*), SUM(SALEPRICE)
FROM BOOK B JOIN ORDERS O USING(BOOKID)
GROUP BY BOOKNAME;
SELECT BOOKNAME,COUNT(*), SUM(SALEPRICE)
FROM BOOK B JOIN ORDERS O ON(B.BOOKID=O.BOOKID)
GROUP BY BOOKNAME;
--3. 도서별로 주문된 주문건수와 총 판매액을 구하되 도서명 순으로 검색
--SELECT * FROM GROUP BY BOOK ORDER BY BOOKNAME;
SELECT BOOKNAME, COUNT(*), SUM(SALEPRICE) "총판매액"
FROM BOOK B, ORDERS O
WHERE B.BOOKID =O.BOOKID
GROUP BY BOOKNAME
ORDER BY BOOKNAME;
--4. 판매가격이 10000원 이하인 도서 중 도서별 주문 총 수량 검색 (단, 두 권 이상 판매된 도서만 구함)
--SELECT COUNT(*) FROM PRICE<=10000
SELECT BOOKID,COUNT(*)
FROM ORDERS
WHERE SALEPRICE<10000
GROUP BY BOOKID
HAVING COUNT(*) > 2;
--4_1. 가격이 10000원 이하인 도서 중 도서별 주문 총 수량 검색 (단, 두 권 이상 판매된 도서만 구함)
SELECT B.BOOKID,COUNT(*)
FROM ORDERS O, BOOK B
WHERE PRICE <=10000 AND O.BOOKID = B.BOOKID
GROUP BY B.BOOKID
HAVING COUNT(*) >=2;
--5. 장미란이 구매한 도서의 총 주문금액 검색
--SELECT SUM(SLAERICE) FROM CUSTOMER WHERE NAME='장미란';
SELECT SUM(SALEPRICE) AS "총 주문금액"
FROM ORDERS O, CUSTOMER C
WHERE O.CUSTID = C.CUSTID AND NAME='장미란';
--6. 가격이 10000원 이상인 도서의 이름과 가격 검색
SELECT BOOKNAME,PRICE
FROM BOOK
WHERE PRICE>=10000;
--7. 도서번호가 3번인 도서의 모든 정보 검색
SELECT *
FROM BOOK
WHERE BOOKID =3;
--8. 고객의 수 검색 // 정확하게 검색 가능
SELECT COUNT(*)
FROM CUSTOMER;
SELECT COUNT(CUSTID)
FROM CUSTOMER; //널일 경우가 들어갔을 때는 제외하는 경우가 있을 수 있음
--9. 주문한 고객의 수 검색
SELECT COUNT(DISTINCT CUSTID) AS 주문고객수
FROM ORDERS;
--10. 주문을 안 한 고객의 수 검색
SELECT COUNT(*)
FROM CUSTOMER
WHERE CUSTID NOT IN (SELECT CUSTID FROM ORDERS);
--11. 주문한 고객의 이름을 검색
SELECT NAME
FROM CUSTOMER
WHERE CUSTID IN(SELECT CUSTID
FROM ORDERS);
--12. 주문을 하지 않은 고객의 이름을 검색
SELECT NAME
FROM CUSTOMER
WHERE CUSTID NOT IN(SELECT CUSTID
FROM ORDERS);
--13. 모든 도서의 이름과 주문받은 판매가격
SELECT BOOKNAME, SALEPRICE
FROM BOOK B,ORDERS O
WHERE B.BOOKID =O.BOOKID;
--14. 2014년 7월 1일 ~ 2014년 7월 5일 사이에 주문 받은 도서의 주문번호 검색
SELECT ORDERID
FROM ORDERS
WHERE ORDERDATE BETWEEN '2014-07-01' AND '2014-07-05';
--15. 도서를 출판사 이름순으로 검색하되 같은 출판사이면 가격이 비싼것부터 검색
SELECT *
FROM BOOK
ORDER BY PUBLISHER, PRICE DESC;
--16. 도서와 도서의 주문에 관한 데이터를 모두 검색
SELECT *
FROM BOOK B,ORDERS O
WHERE B.BOOKID=O.BOOKID;
--17. 도서와 도서의 주문에 관한 데이터를 도서번호 순으로 정렬하여 검색
SELECT *
FROM BOOK B,ORDERS O
WHERE B.BOOKID=O.BOOKID
ORDER BY B.BOOKID;
--18. 도서명과 도서의 판매가격을 검색
SELECT BOOKNAME, SALEPRICE
FROM BOOK B, ORDERS O
WHERE B.BOOKID=O.BOOKID;
--19.도서명과 그 도서를 주문한 고객의 이름과 폰번호를 검색
SELECT B.BOOKNAME, C.NAME, C.PHONE
FROM BOOK B,CUSTOMER C,ORDERS O
WHERE B.BOOKID=O.BOOKID AND C.CUSTID=O.CUSTID;
--20.'축구의 역사'라는 도서를 주문한 고객의 이름과 가격을 검색
SELECT NAME,PRICE
FROM BOOK B,CUSTOMER C,ORDERS O
WHERE B.BOOKID=O.BOOKID AND C.CUSTID=O.CUSTID AND BOOKNAME='축구의 역사';
--21. 팔리지 않은 도서를 포함하여 도서의 이름과 주문한 고객의 이름을 검색
SELECT BOOKNAME, NAME
FROM BOOK B LEFT OUTER JOIN ORDERS O
ON(B.BOOKID =O.BOOKID)
LEFT OUTER JOIN CUSTOMER C
ON(O.CUSTID=C.CUSTID);
--22.가장 비싼 도서의 이름과 가격을 검색
SELECT BOOKNAME, PRICE
FROM BOOK
WHERE PRICE = (SELECT MAX(PRICE)FROM BOOK);
--22_1 :제일 비싼 도서를 사간 고객의 이름
SELECT NAME
FROM CUSTOMER
WHERE CUSTID IN(SELECT CUSTID
FROM ODRERS
WHERE BOOKID IN(SELECT BOOKID
FROM BOOK
WHERE PRICE = (SELECT MAX(PRICE)
FROM BOOK)));
--22_2 : 팔린 것 중에서 가장 비싼 책을 사간 고객 이름
SELECT NAME
FROM ( SELECT NAME , MAX(PRICE)
FROM BOOK B,ORDERS O, CUSTOMER C
WHERE B.BOOKID = O.BOOKID AND C.CUSTID =O.CUSTID
GROUP BY NAME
ORDER BY MAX(PRICE) DESC)
WHERE ROWNUM=1;
--23.고객별 평균 주문 금액
SELECT BOOKID, AVG(SALEPRICE)
FROM ORDERS
GROUP BY BOOKID;
--24.고객번호1번의 고객명, 주문한 도서의 이름과 주문날짜 검색
SELECT NAME,BOOKNAME,ORDERDATE
FROM BOOK B,CUSTOMER C, ORDERS O
WHERE B.BOOKID=O.BOOKID AND C.CUSTID=O.CUSTID
AND C.CUSTID=1;
--25. 도서를 주문한 고객의 고객명과 폰번호 검색(EXISTS사용: 주문한 건이 하나라도 있으면 모든 고객 다 나와! )
SELECT NAME, PHONE
FROM CUSTOMER C
WHERE EXISTS (SELECT * FROM ORDERS O WHERE O.CUSTID = C.CUSTID);
--26. 도서의 판매액 평균보다 자신의 구매액 평균이 더 높은 고객의 이름
SELECT NAME,AVG(SALEPRICE)
FROM CUSTOMER C,ORDERS O
WHERE C.CUSTID=O.CUSTID
GROUP BY NAME
HAVING AVG(SALEPRICE) > (SELECT AVG(SALEPRICE)FROM ORDERS);
--27. 김연아 고객이 구매한 도서의 이름
SELECT BOOKNAME
FROM BOOK B, CUSTOMER C, ORDERS O
WHERE B.BOOKID=O.BOOKID AND C.CUSTID = O.CUSTID
AND NAME='김연아';
--28. 김연아 고객이 구매하지 않은 도서의 이름
SELECT BOOKNAME
FROM BOOK
WHERE BOOKID NOT IN (SELECT BOOKID
FROM CUSTOMER C, ORDERS O
WHERE C.CUSTID = O.CUSTID AND NAME='김연아');
--29.도서의 가격(book 테이블) 과 판매가격(orders 테이블)의 차이가 가장 많은 주문
select O.*, price-saleprice
from ORDERS O, BOOK B
where B.bookid =O.bookid
and (price-saleprice)= (select max(price-saleprice)
from orders o, book b
where o.bookid=b.bookid);
--30.가장 많이 팔린 도서의 이름과 출판사 검색
SELECT BOOKNAME, PUBLISHER
FROM BOOK
WHERE BOOKID IN (SELECT BOOKID
FROM ORDERS
GROUP BY BOOKID
HAVING COUNT(*) = (SELECT MAX(COUNT(*))
FROM ORDERS
GROUP BY BOOKID));
--가장 많이 구매한 고객의 이름과 전화번호
select name,phone
from customer
where custid in (select custid
from orders
group by custid
having count(*) =(select max(count(*))
from orders
group by custid));
