Oracle로 배우는 데이터베이스 개론과 실습 3장 연습문제 답

3장 sql기초 연습문제 1, 2

--도서번호 1인 도서 이름

select bookname

from book

where bookid=1;

 

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

select bookname

from book

where price>20000;

 

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

select sum(saleprice)

from orders

where custid=1;

 

-- 박지성이 구매한 도서 수

select count(*) AS 구매도서수

from orders

where custid=1;

 

-- 마당 서점 도서의 총 개수

select count(*) AS 총도서개수

from book;

 

-- 모든 고객의 이름, 주소

select name, address

from customer;

 

--2014년 7월4일-7월7일 사이 주문받은 도서 주문번호

select orderid

from orders

where orderdate between '2014-07-04' AND '2014-07-07';

 

-- 앞의 예제 날짜 제외 주문번호

select orderid

from orders

where not orderdate between '2014-07-04' AND '2014-07-07';

 

-- 두번째 방법, 차집합사용 Minus

select orderid

from orders

Minus

select orderid

from orders

where orderdate between '2014-07-04' AND '2014-07-07';

 

-- 성이 '김'씨인 고객의 이름과 주소

select name, address

from customer

where name like '김%';

 

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

select name, address

from customer

where name like '김%아'; -- '김_아'

 

-- 고객의 이름과 고객이 주문한 도서의 이름을 구하시오

select c.name, b.bookname

from customer c, book b, orders o

where c.custid=o.custid AND b.bookid=o.bookid;

 

select c.name, b.bookname

from customer c, book b

where b.price=20000;

 

select customer.name, saleprice

from customer left outer join orders on customer.custid = orders.custid;

 

select bookname

from book

where price = (select max(price) from book);

 

-- 도서를 구매한적 있는 고객 이름

select name

from customer

where custid in (select o.custid from orders o); -- 집합으로 나오니 in 써주기

 

-- 대한미디어에서 출판한 도서를 구매한 고객의 이름

select name

from customer

where custid in

(select o.custid from orders o where o.bookid in (select b.bookid from book b where publisher like '대한미디어'));

 

-- 출판사별로 출판사의 평균 도서 가격보다 비싼 도서를 구하시오

select b.bookname

from book b

where b.price>

(select avg(b2.price)

from book b2

where b2.publisher = b.publisher);

 

-- 도서를 주문하지 않은 고객 이름

select name

from customer

Minus

select name

from customer, orders

where customer.custid = orders.custid;

 

-- 주문이 있는 고객의 이름과 주소

select name, address

from customer c

where exists (select * from orders where c.custid=custid);

 

-- 박지성이 구매한 도서출판사 수

select count(b.publisher) as 구매한도서출판사수

from book b

where b.bookid in (select o.bookid

from orders o, customer

where customer.name='박지성' and o.custid = customer.custid);

 

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

select b.bookname, b.price, b.price-o.saleprice

from book b, orders o

where b.bookid = o.bookid and o.custid in

(select c.custid

from customer c, orders o2

where c.custid=o2.custid and c.name='박지성');

 

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

select bookname

from book

Minus

select bookname

from book b, orders o

where b.bookid=o.bookid and o.custid in

(select c.custid

from customer c

where o.custid = c.custid);

 

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

select name

from customer

Minus

select c.name

from customer c, orders o

where c.custid=o.custid;

 

select distinct c.name

from customer c, orders o

where c.custid not in (select c2.custid

from customer c2, orders o2

where c2.custid = o2.custid);

 

-- 주문 금액의 총액과 주문의 평균 금액

select sum(saleprice), avg(saleprice)

from orders;

 

-- 고객의 이름과 고객별 구매액

select name, sum(saleprice)

from customer, orders

where customer.custid=orders.custid

group by name;

 

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

select name, bookname

from customer c, book b, orders o

where c.custid=o.custid And o.bookid = b.bookid;

 

-- 도서의 가격(북테이블)과 판매가격(오더테이블)의 차이가 가장 많은 주문

select *

from orders o1, book b1

where b1.price-o1.saleprice = (

select max(b.price-o.saleprice)

from orders o, book b, customer c

where o.bookid=b.bookid and c.custid = o.custid);

 

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

select c.name

from customer c, orders o

where c.custid = o.custid

group by name

having avg(o.saleprice)>

(select avg(saleprice)

from orders);

 

select name,avg(saleprice)

from customer c, orders o

where c.custid = o.custid

group by name;

 

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

select distinct customer.name

from customer, orders, book

where customer.name != '박지성' and customer.custid=orders.custid and orders.bookid = book.bookid and publisher in (select publisher

from book b, customer c, orders o

where c.name='박지성' and b.bookid=o.bookid and c.custid=o.custid);

 

select customer.name, publisher

from customer, orders, book

where customer.custid=orders.custid and orders.bookid = book.bookid ;

 

 

 

insert into book(bookid, bookname, publisher, price) values(13, '스포츠 세계', '대한미디어', 1000); --2-(1)

delete from book where publisher='삼성당';--(2)

delete from book where publisher='이상미디어'; --(3)

update book

set publisher = (select publisher from book where publisher='대한출판사')

where publisher='대한미디어';

 

---

데이터베이스 3장 연습문제풀이 2번

(1) SELECT count(*) from book;

(2) SELECT count(distinct publisher) from book;

(3) SELECT name, address from customer;

 

(4) SELECT orderid FROM orders

WHERE orderdate

BETWEEN '20140704' AND '20140707';

 

(5) SELECT orderid from orders

Minus

SELECT orderid

FROM Orders

WHERE orderdate BETWEEN '20140704' AND '20140707';

 

SELECT *

FROM Orders

WHERE orderdate NOT BETWEEN To_date('20140704' , 'yyyymmdd')

and To_date('20140707' , 'yyyymmdd')

 

( 답 두 개가 가능합니다. )

 

(6) SELECT name, address from custoemr where name like ‘김%’;

SELECT custid, address from customer where '김' = substr(name,1,1);

 

( 답 두 개가 가능합니다. )

 

(7) SELECT custid, address

FROM customer

WHERE name like '김%' AND name like '%아';

 

 

(8) SELECT name

from customer

where custid NOT IN

(SELECT custid

from orders

where orders.custid = customer.custid);

 

select name from customer

minus

select name

from customer, orders

where customer.custid = orders.custid;

 

(9) SELECT Sum(saleprice), Avg(saleprice) from orders;

 

(10) SELECT c.name as 이름, sum(saleprice) as 총구매액

from customer c, orders o

where c.custid = o.custid

group by c.name

order by sum(saleprice) desc;

 

(11) SELECT c.name as 이름, b.bookname as 구매도서목록

from customer c, book b, orders o

where c.custid = o.custid AND b.bookid = o.bookid;

 

(12) SELECT *

from orders , book

where orders.bookid = book.bookid

AND book.price-orders.saleprice

= (Select max(price-saleprice)

from orders, book

where orders.bookid=book.bookid);

 

 

 

(13) select name

from customer c, orders o

where o.custid = c.custid

group by name

having avg(saleprice) > (select avg(o1.saleprice) from orders o1);