데베 7주차

select 연습문제

customer 릴레이션

custid	name	address	phone
1	박지성	영국 맨체스타	000-5000-0001
2	김연아	대한민국 서울	000-6000-0001
3	장미란	대한민국 강원도	000-7000-0001
4	추신수	미국 클리블랜드	000-8000-0001
5	박세리	대한민국 대전

book 릴레이션

bookid	bookname	publisher	price
1	축구의 역사	굿스포츠	7000
2	축구아는 여자	나무수	13000
3	축구의 이해	대한미디어	22000
4	골프 바이블	대한미디어	35000
5	피겨 교본	굿스포츠	8000
6	역도 단계별기술	굿스포츠	6000
7	야구의 추억	이상미디어	20000
8	야구를 부탁해	이상미디어	13000
9	올림픽 이야기	삼성당	7500
10	Olympic Champions	Pearson	13000

orders 릴레이션

orderid	custid	bookid	saleprice	orderdate
1	1	1	6000	2014-07-01
2	1	3	21000	2014-07-03
3	2	5	8000	2014-07-03
4	3	6	6000	2014-07-04
5	4	7	20000	2014-07-05
6	1	2	12000	2014-07-07
7	4	8	13000	2014-07-07
8	3	10	12000	2014-07-08
9	2	10	7000	2014-07-09
10	3	8	13000	2014-07-10

조건 문에서 in 과 like 는 같이 못 씀

use madang;
show tables;
select * from customer;
select * from book;
select * from orders;
-- 고객 번호가 3번인 선수의 이름과 주소를 검색하시오
select name, address from customer where custid = 3;
-- 2014년 7월 4일~7월 7일 사이에 주문받은 도서의 주문번호와 주문날짜를 검색하시오
select orderid, orderdate from orders where orderdate between '2014-07-04' and '2014-07-07';
-- 고객의 주소가 서울이거나 대전에 사는 고객의 이름과 주소를 검색하시오
select name, address from customer where address like '%서울%' or address like '%대전%';
-- 출판사가 이상미디어 중 가격이 20000원 이상인 도서의 책제목, 출판사, 가격을 검색
select bookname, publisher, price from book where publisher in ('이상미디어') and price >= 20000;
-- 도서를 주문한 회원의 번호를 검색하시오 단 중복된 회원의 번호는 한번만 출력
select distinct custid from orders; 
-- 고객 중 전화번호가 없는 고객의 이름과 주소를 검색하시오
select name, address from customer where phone is null;
-- 출판사가 굿스포츠 인 도서 중 축구가 포함된 도서의 이름과 출판사 가격을 검색
select bookname, price from book where publisher = '굿스포츠' and bookname like '%축구%';
-- 2014년 7월 4일~7월 7일 사이에 주문받은 도서를 제외한 도서의 주문번호 주문날짜를 검색하시오
select orderid, orderdate from orders where orderdate not between '2014-07-04' and '2014-07-07';
-- 도서 테이블에서 출판사별로 금액의 합계와 평균을 출력해주세요 -> 팔린가격 합계 평균
select publisher, sum(price), avg(price) from book group by publisher;
-- 도서테이블의 도서 중 가격이 가장 비싼 책과 가장 저렴한 가격을 검색하시오 -> 가격의 차이를 구해라
select max(price) - min(price) as '가격의 차이' from book;
-- 도서테이블에서 정가와 10%할인된 가격을 출력
select price as '정가', price * 0.9 as '할인 가격' from book;
-- 주문 테이블에서 고객번호 가 1번인 고객의 총 구매한 책의 가격을 출력하시오 -> 이름으로
select sum(saleprice) from orders where custid = 1;
-- 주문 테이블에서 고개번호 별로 책 구입 금액이 비싼 책 부터 정렬해서 고객번호 판매금액을 검색하시오 -> 이름별로
select custid,saleprice from orders order by custid, saleprice  desc;
-- 주문 테이블에서 책 번호 별로 판매금액의 합계를 구하되 판매금액의 합계가 10000원 이상 인 것만 검색하시오 -> 책이름
select bookid , sum(saleprice) from orders group by bookid having sum(saleprice) > 10000;
-- 도서테이블의 도서의 총 개수를 출력해주세요
select count(bookid) from book;
-- 도서 테이블의 총 출판사의 개수를 출력해 주세요
select count(distinct publisher) from book; 

-- 주문 테이블에서 한번이라도 주문한 고객의 개수를 출력해주세요
select count(distinct custid) from orders;

-- 고객별로 주문한 도서의 총 수량과 총 판매액을 구하시오
select count(orderid), sum(saleprice) from orders group by custid;

그냥 불러오면 기본적으로 곱해서 가져온다

customer 5개 * orders 10개 즉 50개를 가져옴.

-- 고객과 고객의 주문한 관한 테이블을 모두 검색하시오

select * from customer, orders;

-- 고객과 고객의 주문한 관한 테이블을 모두 검색하시오
select * from customer c, orders o where c.custid = o.custid
--  책을 주문한 고객의 이름과 책번호 책이름 가격을 검색하시오
select name, bookname, b.bookid, saleprice from customer c,book b,orders o where b.bookid = o.bookid and c.custid = o.custid;
-- 박지성이 주문한 책의 총가격을 검색하시오
select name, sum(saleprice) from customer c, orders o where c.custid = o.custid and c.name ='박지성'; -- 일치하는 것 부터 찾아
-- 고객의 이름과 고객이 주문한 도서의 판매가격을 검색하시오
select name, saleprice from customer c, orders o where c.custid = o.custid; 
-- 고객의 이름과 고객이 주문한 도서의 판매가격을 검색하되 고객별로 금액이 비싼거별로 정렬하시오
select name, saleprice from customer c, orders o where c.custid = o.custid order by name, saleprice desc;
-- 도서명별로 책을 판매한 금액의 합계가 20000 이상 검색하시오
select bookname, sum(saleprice) from book b, orders o where b.bookid = o.bookid group by bookname having sum(saleprice) >= 20000 order by sum(saleprice) desc;
-- 책을 구매한 고객의 이름과  구매한 책의 이름과 판매금액을 검색하시오
select name, count(o.custid), sum(saleprice) from customer c, orders o where c.custid = o.custid group by name order by count(o.custid) desc, sum(saleprice) desc;
 -- 주문 테이블에서 책 이름 별로 판매금액의 합계를 구하되 판매금액의 합계가 10000 원 이상인 것만 검색
select bookname, sum(saleprice) from book b, orders o where b.bookid = o.bookid group by bookname having sum(saleprice) >= 10000;
-- 가격이 20000 원 이상인 도서를 주문한 고객의 이름과 도서의 이름을 검색하시오
select name, bookname, saleprice from book b, orders o, customer c where o.bookid = b.bookid and o.custid = c.custid and saleprice >= 20000;
-- 고객별로 주문한 도서의 총 수량과 총 판매액을 구하시오(이름으로 출력)
select name, count(orderid), sum(saleprice) from orders o, customer c where o.custid = c.custid group by name;

-- 도서를 주문한 고객의 이름과 책가격을 검색 inner join 조건절 은 on 
select name, saleprice
from customer c inner join orders o on c.custid = o.custid;
-- 도서를 구매하지 않은 고객을 포함하여 고객의 이름과 고객이 주문한 도서의 판매가격을 구하시오.
select name, saleprice
from customer c left outer join orders o on c.custid = o.custid;