[오라클로 배우는 데이터베이스 개론과 실습] 3장 연습문제

오라클로 배우는 데이터베이스 개론과 실습 3장 연습문제 풀이입니다.

오류가 있다면 댓글로 알려주세요 :) 

 

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

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

SELECT bookname FROM Book WHERE bookid=1;

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

SELECT bookname FROM Book WHERE price>=20000;

(3) 박지성의 총 구매액 (박지성의 고객 번호는 1번으로 놓고 작성)

SELECT sum(saleprice) FROM Orders WHERE custid=1;

(4) 박지성이 구매한 도서의 수 (박지성의 고객 번호는 1번으로 놓고 작성)

SELECT count(*) FROM Orders WHERE custid=1;

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

SELECT count(DISTINCT publisher) FROM Customer, Orders, Book WHERE (Customer.custid=Orders.custid) AND (Book.bookid=Orders.bookid) AND (Customer.name='박지성');

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

SELECT bookname, price, price-saleprice FROM Customer, Orders, Book WHERE (Customer.custid=Orders.custid) AND (Book.bookid=Orders.bookid) AND (Customer.name='박지성');

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

SELECT bookname FROM Book, Orders, Customer

MINUS

SLEECT booknmae FROM Book, Orders, Customer

WHERE (Customer.custid=Orders.custid) AND (Book.bookid=Orders.bookid) AND (Customer.name='박지성');

 

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

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

SELECT count(*) FROM Book;

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

SELECT count(DISTINCT publisher) FROM Book;

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

SELECT name, address FROM Customer;

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

SELECT * FROM Orders WHERE orderdate BETWEEN to_date('2014-07-04','YYYY-MM-DD') AND to_date('2014-07-07','YYYY-MM-DD');

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

SELECT * FROM Orders WHERE orderdate NOT BETWEEN to_date('2014-07-04','YYYY-MM-DD') AND to_date('2014-07-07','YYYY-MM-DD');

(6) 성이 '김'씨인 고객의 이름과 주소

SELECT name, address FROM Customer WHERE name like '김%';

(7) 성이 '김'씨이고 이름이 '아'로 끝나는 고객의 이름과 주소

SELECT name, address FROM Customer WHERE name like '김%아';

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

SELECT name FROM Book, Orders, Customer

MINUS

SELECT name FROM Book, Orders, Customer

WHERE (Customer.custid=Orders.custid) AND (Orders.bookid=Book.bookid);

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

SELECT sum(saleprice), avg(saleprice) FROM Orders;

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

SELECT name, sum(saleprice) FROM Orders, Customer WHERE Orders.custid=Customer.custid GROUP BY name;

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

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

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

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

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

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

 

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

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

select name from customer where custid

in (select custid from orders where bookid

in (select bookid from book where publisher 

in (select publisher from book, orders, customer where customer.custid=orders.custid and book.bookid=orders.bookid and name like '박지성')));

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

select name from customer where custid

in (select custid from orders where bookid

in (select bookid from book where publisher 

in (select publisher from book, orders, customer where customer.custid=orders.custid and book.bookid=orders.bookid and name like '박지성')));

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

 

4. 다음 질의에 대해 DDL문과 DML문을 작성하시오.

(1) 새로운 도서 ('스포츠 세계', '대한미디어', 10000원)이 마당서점에 입고되었다. 삽입이 안될 경우 필요한 데이터가 더 있는지 찾아보시오.

INSERT INTO Book VALUES(11, '스포츠세계', '대한미디어', 10000);

SELECT * FROM Book;

(2) '삼성당'에서 출판한 도서를 삭제하시오.

DELETE FROM Book WHERE publisher='삼성당';

(3) ‘이상미디어’에서 출판한 도서를 삭제해야 하시오. 삭제가 안될 경우 원인을 생각해보시오.

(DELETE FROM Book WHERE publisher='이상미디어';)

다른 table이 publisher 속성을 외래키로 참조하고 있기 때문에 무결성 제약조건에 위배됨.

(4) 출판사 ‘대한미디어’를 ‘대한출판사’로 이름을 바꾸시오.

UPDATE Book SET publisher='대한출판사' WHERE publisher like '대한미디어';

SELECT * FROM BOOK;

(5), (6), (7) 

 

5. 다음 EXISTS 질의의 결과를 보이시오.

SELECT *
FROM Customer c1
WHERE NOT EXISTS (SELECT *
                            FROM Orders c2
                            WHERE c1.custid=c2.custid);

(1) 질의의 결과는 무엇인가?

Orders 테이블에 주문건이 없는 고객

(2) NOT을 지우면 질의의 결과는 무엇인가?

EXISTS는 조건이 맞는 투플이 존재하면 결과에 포함시키므로 

Orders 테이블에 주문건이 있는 고객