1.3 박지성(고객)의 총 구매(주문)액 (박지성의 고객번호는 1번으로 놓고 작성)
select * from customer, orders where customer.custid = orders.custid and customer.custid = 1;
select sum(saleprice) from customer, orders where customer.custid = orders.custid and customer.custid = 1;
1.5 박지성이 구매한 도서의 출판사 수
- join(두개 이상)
- customer, orders, book
select * from customer, orders, book where customer.custid=orders.custid and customer.name='박지성' and book.bookid=orders.bookid;
select count(distinct book.publisher) as "박지성이구매한도서의출판사수" from customer, orders, book where customer.custid=orders.custid and customer.name='박지성' and book.bookid=orders.bookid;
1.6 박지성이 구매한 도서의 이름, 가격, 정가와 판매가격의 차이
select * from customer, orders, book where customer.custid=orders.custid and customer.name='박지성' and book.bookid=orders.bookid;
select book.bookname, book.price from customer, orders, book where customer.custid=orders.custid and customer.name='박지성' and book.bookid=orders.bookid;
select * from customer, orders, book where customer.custid=orders.custid and customer.name='박지성' and book.bookid=orders.bookid;
select 1+2 from customer, orders, book where customer.custid=orders.custid and customer.name='박지성' and book.bookid=orders.bookid;
select book.price - orders.saleprice from customer, orders, book where customer.custid=orders.custid and customer.name='박지성' and book.bookid=orders.bookid;
select book.bookname, book.price, orders.saleprice, book.price - orders.saleprice as "정가와 판매가격의 차이" from customer, orders, book where customer.custid=orders.custid and customer.name='박지성' and book.bookid=orders.bookid;
select 1+2 from dual; --du(dummy) table (al)
1.7 박지성이 구매하지 않은 도서의 이름(서브 쿼리 이용)
select bookname from book where (박지성이 구매하지 않았다.)
- 조인과 서브쿼리 동시에 이용
select bookname from book; -- 조건 구매하지 않음 ;
select bookname from book where bookid not in (1,2,3);
select orders.bookid from customer, orders where customer.custid=orders.custid and customer.name='박지성' ;
select bookname from book where bookid not in (select orders.bookid from customer, orders where customer.custid=orders.custid and customer.name='박지성' );
2.8 주문하지 않은 고객의 이름(부속질의 사용)
-
도서를 구매한 적이 있는 고객( 구매 내역 중 고객이 있냐? orders 테이블 내에 고객 id 가 있으면 구매한 고객)
-
도서를 구매한 적이 있는 고객의 이름을 검색(조인 이용)
1) select * from orders, customer where orders.custid= customer.custid;
select customer.name from orders, customer where orders.custid= customer.custid;
select distinct customer.name from orders, customer where orders.custid= customer.custid;
-
도서를 구매한 적이 있는 고객의 이름을 검색(서브 쿼리 이용)
-
고객의 이름을 검색 쿼리
-
조건 : 도서를 구매한 적이 있는(in)
select name FROM customer;
select * from orders;
select distinct custid from orders;
select name FROM customer where custid in (1,2,3,4);
select name FROM customer where custid in (select distinct custid from orders);
select name FROM customer where custid not in (select distinct custid from orders);
2.9 주문 금액의 총액과 주문의 평균 금액
- book max(price)
select max(price) from book;
select * from book ;
select count(*) from book GROUP by bookname;
select sum(price) from book GROUP by bookname;
select avg(price) from book GROUP by bookname;
select min(price) from book GROUP by bookname;
select max(price) from book GROUP by bookname;
2.10 고객의 이름과 고객별 구매액 (group by - 집계함수(sum, count, avg, max, min)
select * from orders, customer where orders.custid= customer.custid;
select customer.name, sum(orders.saleprice) from orders, customer where orders.custid= customer.custid GROUP by customer.name;
2.11 고객의 이름과 고객이 구매한 도서 목록
select customer.name, book.bookname from orders, customer, book where orders.custid= customer.custid and orders.bookid = book.bookid;
2.12도서의 가격(Book 테이블)과 판매가격(Orders 테이블)의 차이가 가장 많은 주문
select book.bookname, book.price, orders.saleprice, book.price - orders.saleprice FROM book, orders where orders.bookid = book.bookid;
- 차이가 가장 많은 조건
select max(book.price - orders.saleprice) FROM book, orders where orders.bookid = book.bookid;
select *
from book, orders
where orders.bookid = book.bookid
and
book.price - orders.saleprice = (select max(book.price - orders.saleprice)
FROM book, orders
where orders.bookid = book.bookid);
- group by는 집계합수와 사용, 집계함수에 대해에서 조건을 제한한 경우 having 사용
2.13 도서의 판매액 평균보다 자신의 구매액 평균이 더 높은 고객의 이름
- 도서의 판매액 평균 : 11800
select avg(saleprice) from orders;
- 자신의 구매액 평균
select avg(orders.saleprice) from orders;
select customer.name, avg(orders.saleprice) from orders, customer where orders.custid= customer.custid GROUP by customer.name;
select customer.name, round(avg(orders.saleprice)) from orders, customer where orders.custid= customer.custid GROUP by customer.name;
select customer.name, round(avg(orders.saleprice))
from orders, customer
where orders.custid= customer.custid
GROUP by customer.name
having round(avg(orders.saleprice)) > (select avg(orders.saleprice) from orders);
