Madang연습문제 모음

 

1. 마당 서점의 고객측에서 요구할 수 있는 질의

- 출판사가 '굿스프츠' ghrdms '대한미디어'인 도서를 검색하시오(두 개 이상의 값을 비교할 때 in이 쓰인다)

select *
from book
where publisher in ('굿스포츠','대한미디어')

-박지성의 총 구매액

//sol)조인문이용

select sum(saleprice) as 총액
from orders inner join customer
on orders.custid=customer.custid
where customer.name="박지성";

//혹은
select sum(saleprice) as 총액
from orders, customer
where orders.custid=customer.custid and customer.name='박지성';

//sol2)부속질의 이용

select sum(saleprice)
from orders
where custid=(select custid from customer where name='박지성');

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

-- 조인문이용
select count(orderid)
from orders inner join customer
on orders.custid=customer.custid
where customer.name='박지성'

-- 조인문이용
select count(orderid)
from orders, customer
where orders.custid=customer.custid and customer.name="박지성";
-- 부속질의 이용
select count(orderid)
from orders
where custid=(select custid from customer where name="박지성");

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

SELECT COUNT(DISTINCT publisher)
FROM book
WHERE bookid IN (
	SELECT bookid
	FROM orders
	WHERE custid = (
		SELECT custid
		FROM customer
		WHERE name = '박지성'
	)
);

-- 혹은 아래와 같이 조인문과 부속질의를 섞어서 쓸수도 있음

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

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

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

- 박지성이 구매한 도서의 이름

select bookname
from book, orders
where book.bookid=orders.bookid and orders.custid=(select custid from customer where customer.name='박지성')

혹은

select bookname
from book
where book.bookid in (select bookid from orders where custid =(select custid from customer where name='박지성'))

 

-- 고객의 이름과 고객이 주문한 도서의 이름을 구하시오(3개의 테이블을 조인하여 구할 수 있다) (이와같은 3개의 테이블을 조인하면서 원하는 바를 얻으면서 하는 생각은 왜 굳이 편하게 하나의 테이블에 모든 정보를 몽땅 넣어 조인할 필요없이 그냥 답을 구하게 하나의 테이블에 모든 것을 넣어 버리면 되는거 아냐? 라는 생각이다. 하지만 정규화 과정에서 이상현상의 원인은 기본키가 아닌 결정자 속성때문에 발생한다는 것을 생각하면 테이블에 적정수준 이상의 정보를 넣을 시에는 반드시 문제가 된다는 사실을 생각해야 한다. 테이블을 크게 만들면 삽입, 삭제, 수정이상의 문제가 발생한다)

select customer.name, book.bookname
from customer, book, orders
where customer.custid=orders.custid and orders.bookid=book.bookid;

-- 가격이 20,000원인 도서를 주문한 고객의 이름과 도서의 이름을 구하시오

select customer.name as "고객이름", book.bookname as "도서이름"
from customer, orders, book 
where orders.bookid=book.bookid and orders.custid=customer.custid and book.price=20000;

 

 

2. 마당서점의 운영자와 경영자 측에서 요구할 수 있는 질의

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

select orderid
from orders
where orderdate  BETWEEN '2014-07-04' AND '2014-07-07'

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

select orderid from orders where orderid not in (
select orderid
from orders
where orderdate  BETWEEN '2014-07-04' AND '2014-07-07')

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

select name, address
from customer
where customer.name like '김%';

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

select name, address
from customer
where name like '김_아';

 

-  주문하지 않은 고객의 이름(서브쿼리 사용)

select name
from customer
where custid not in (select custid from orders)

 

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

select name, sum(saleprice)
from customer inner join orders
on customer.custid=orders.custid
group by customer.name;

- 도서를 가격순으로 검색하고, 가격이 같으면 이름순으로 검색하시오

select *
from book
ORDER BY price, bookname

- 도서를 가격의 내림차순으로 검색하시오. 만약 가격이 같다면 출판사의 오름차순으로 출력하시오

select *
from book
ORDER BY price DESC, publisher ASC;

 

--도서를 구매하지 않은 고객을 포함하여 고객의 이름과 고객이 주문한 도서의 판매가격을 구하시오(외부조인을 사용해야 한다는 것을 "도서를 구매하지 않은 고객을 포함"이라는 문구에서 눈치채야 한다)

select customer.name as "고객이름", orders.saleprice as "판매가"
from customer left outer join orders
on orders.custid=customer.custid;

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

-- 1.3개의 테이블을 조인하여 해결
select customer.name
from orders, customer, book
where orders.custid=customer.custid and orders.bookid=book.bookid and book.publisher like '대한미디어';

-- 2. 세 개의 부속질의를 이용하여 해결 
select customer.name
from customer
where customer.custid in (select custid from orders where bookid in (select bookid from book where publisher='대한미디어'));

- 출판사별로 출판사의 평균 도서 가격보다 비싼 도서를 구하시오(상관 부속질의를 이용해야 하는 문제다. "~별로"라는 말이 문제에 있어 GROUP BY를 생각할 수도 있지만 SQL의 쿼리 실행 순서를 생각하면 GROUP BY로는 해결할 수 없다. 어쩔수 없이 BOOK테이블에 있는 각각의 책들을 모두 평균가와비교해 봐야 해결이 가능하다. 상관부속질의의 외관상 특징은 튜플변수(테이블의 별칭)가 등장하고 상위 부속질의의 튜플변수가 하위 부속질의에 등장한다는 것이다)

SELECT b1.bookname as "책이름"
from Book b1
where b1.price>(select avg(b2.price) from Book b2 where b1.publisher=b2.publisher);