2020.11.30 시험문제 풀이 (sql 질의 작성)

기본 테이블

BOOK - -BOOKID, BOOKNAME, PUBLISHER, PRICE

CUSTOMER - CUSTID, NAME, ADDRESS

ORDERS - ORDERSID, CUSTID, BOOKID, SALEPRICE, ORDERDATE

 

 

//1-1 도서번호가 1인 도서의 이름

SELECT BOOKNAME FROM BOOK WHERE BOOKID = 1;

 

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

SELECT BOOKNAME FROM BOOK

WHERE price > 20000
ORDER BY BOOKNAME;

 

//1-3 박지성의 총 구매액 (1번 회원)

SELECT SUM(SALEPRICE) FROM orders WHERE CUSTID = 1;

 

//1-4 박지성이 구매한 도서의 수

SELECT COUNT(*) FROM orders WHERE CUSTID = 1;

 

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

SELECT COUNT(*) PUBLISHER
FROM BOOK, ORDERS
WHERE book.BOOKID = orders.BOOKID
AND ORDERS.CUSTID = 1;

 

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

SELECT book.BOOKNAME, orders.SALEPRICE, book.PRICE-orders.SALEPRICE
FROM BOOK, ORDERS
WHERE book.BOOKID = orders.BOOKID
AND ORDERS.CUSTID = 1;

 

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

SELECT DISTINCT book.BOOKNAME
FROM BOOK, ORDERS
WHERE book.BOOKID = orders.BOOKID
AND ORDERS.CUSTID NOT IN (1);

 

//2-1 마당서점 도서의 총 개수

SELECT COUNT(BOOKID) FROM BOOK;

 

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

SELECT COUNT(DISTINCT publisher) FROM BOOK;

 

//2-3 모든 고객의 이름, 주소

SELECT NAME, ADDRESS FROM customer
ORDER BY NAME;

 

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

SELECT BOOKID FROM orders
WHERE orderdate BETWEEN '2014/07/04' AND '2014/07/07'
ORDER BY BOOKID;

 

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

SELECT DISTINCT BOOKID
FROM orders
WHERE orderdate NOT BETWEEN '2014/07/04' AND '2014/07/07'
ORDER BY BOOKID;

 

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

SELECT name, ADDRESS
FROM customer
WHERE NAME LIKE '김%' ;

 

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

SELECT NAME, ADDRESS
FROM customer
WHERE NAME LIKE '김__아%' ;

 

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

SELECT customer.NAME
FROM CUSTOMER
WHERE custid NOT IN (
SELECT DISTINCT orders.custid
FROM ORDERS
);

 

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

SELECT sum(orders.SALEPRICE), avg(orders.SALEPRICE)
FROM ORDERS;

 

//2-10 고객의 이름과 고객별 구매액

SELECT DISTINCT customer.name, orders.SALEPRICE
FROM CUSTOMER, ORDERS
WHERE CUSTOMER.CUSTID = ORDERS.CUSTID
ORDER BY NAME;

 

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

SELECT DISTINCT customer.name, book.BOOKNAME
FROM CUSTOMER, book, ORDERS
WHERE CUSTOMER.CUSTID = ORDERS.CUSTID
AND book.BOOKID = orders.BOOKID
ORDER BY NAME;

 

//2-12 도서 가격(prcie)와 판매가격(saleprice)의 차이가 가장 많은 주문

SELECT *
FROM book, ORDERS
WHERE book.BOOKID = orders.BOOKID
AND book.PRICE-orders.SALEPRICE = (
SELECT max(book.PRICE-orders.SALEPRICE)
FROM book, ORDERS
WHERE book.BOOKID = orders.BOOKID
);

 

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

SELECT customer.NAME
FROM CUSTOMER, ORDERS
WHERE CUSTOMER.CUSTID = ORDERS.CUSTID
GROUP BY customer.NAME
HAVING avg(orders.SALEPRICE) > (
SELECT avg(orders.SALEPRICE)
FROM ORDERS
);

 

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

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, ORDERS

WHERE BOOK.BOOKID = ORDERS.BOOKID

AND ORDERS.CUSTID = 1

);

 

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

 

SELECT DISTINCT CUSTOMER.NAME

FROM CUSTOMER, ORDERS, BOOK

WHERE CUSTOMER.CUSTID =ORDERS.CUSTID

AND BOOK.BOOKID = ORDERS.BOOKID

GROUP BY CUSTOMER.NAME

HAVING COUNT(DISTINCT PUBLISHER) >= 2;

 

//3-3 전체 고객의 30% 이상 구매한 도서
// 퍼센트 구하는 공식 (전체값*구할퍼센트/100)

SELECT DISTINCT book.BOOKNAME
FROM book, ORDERS
WHERE ORDERS.BOOKID = BOOK.BOOKID
GROUP BY BOOKNAME
HAVING COUNT() >= (SELECT count() custid FROM customer)*30/100;