데베 9주차

뷰 이후 실기 : 프린트물은 가능 : 전자기기 사용 금지

실기때는 다른 데이터를 준다.

 

inner join

-- 책을 주문한 고객의 이름과 주무을 책을 출력
select name, saleprice
from customer c inner join orders o on c.custid = o.custid;

left outer join / right outer join : 기준은 join을 기준으로 왼쪽이냐 오른 쪽이냐 차이

-- 책을 주문한 고객의 이름과 주무을 책을 출력
select name, saleprice
from customer c left outer join orders o on c.custid = o.custid;

서브쿼리

use madang;


-- inner join / outer join (letf outer join / right outer join)
-- 책을 주문한 고객의 이름과 주무을 책을 출력
select name, saleprice
from customer c inner join orders o on c.custid = o.custid;

-- 가장 비싼 책의 이름과 출판사 가격
select bookname, publisher, price from book where price = (select max(price) from book);

-- 도서 평균 가격보다 비싼 책의 이름 출판사 가격을 검색
select bookname, publisher, price from book where price >= (select avg(price) from book);

-- 책 제목이 '축구'가 있는 책들의 출판사와 책들의 이름과 출판사 출력
select bookname, publisher from book
where publisher in (select publisher from book where bookname like '%축구%') order by publisher;

-- 김연아가 주문한 책의 총 합계를 부질의를 이용하여 총 합계를 출력
select count(custid) from orders where custid = (select custid from customer where name = '김연아');

select sum(saleprice) from orders o inner join customer c on name = '김연아';

-- 고객번호가 3번인 고객과 같은 책을 주문한 고객의 이름, 책 제목 검색
select name, bookname from orders o, customer c, book b where o.custid = c.custid and b.bookid = o.bookid and b.bookid in (select bookid from orders where custid = 3);

-- union : 합집합 / in : 교집합 / not in : 차집합

-- 도서를 구매한 적이 있는 고객을 검색(교집합)
select distinct name from customer c, orders o where c.custid = o.custid; 
select name from customer where custid in (select custid from orders);

-- 도서를 구해한 적이 없는 고객을 검색
select name from customer where custid not in (select custid from orders);

-- 영국에 거주하는 고객의 이름과 도서를 매한 적이 없는 고객의 이름을 검색
select name from customer where custid not in (select custid from orders) or address like ('%영국%');

select name from customer where custid not in (select custid from orders)
union select name from customer where address like ('%영국%');

-- Exists 연산자 : 조건절에 만족하는 모든값을 일치하는 값을 검색
-- 책을 주문한 고객의 이름과 주소를 검색
select name, address from customer c where exists (select * from orders o where c.custid = o.custid);

-- 트랜잭션 : 백업 모든 작업들을 복구 : rollback 수락 : commit
start transaction;

 

예제

-- 1.도서 테이블에서 가장 책가격이 싼 책의 도서이름과 책가격을 검색하시오
select bookname, price from book where price = (select min(price) from book);
-- 2.주문테이블의 팔린 책 가격의 평균보다 적은 책들의 책이름,책번호와 가격을 검색하시오
select bookname,b.bookid, saleprice from orders o, book b where b.bookid = o.bookid and saleprice <= (select avg(saleprice) from orders);
-- 3. 도서 테이블에서 책제목이 ‘피겨 교본’ 과 같은 출판사인 책이름과 출판사이름검색
select bookname,publisher from book where publisher = (select publisher from book where bookname like '%피겨 교본%');
-- 4. 주문테이블에서 주문 금액의 평균 금액보다 큰 금액의 책제목과 주문 금액 검색
select distinct bookname, saleprice from book b, orders o where b.bookid = o.bookid and saleprice >= (select avg(saleprice) from orders);
-- 5. 도서를 주문한 적이 한 번도 없는 고객의 이름 과 주소를 검색하시오(부속질의를 사용)
select name, address from customer where custid not in (select custid from orders);
-- 6.고객 테이블에서 고객 번호가 2번인 고객의 주소를 ‘경기도 시흥시’ 로 수정하시오.
update customer set address = '경기도 시흥시', phone = '010-0011-0000' where custid = 2; -- 조건은 최대한 기본키로 줘라
-- 7.도서테이블에서 새로운 도서 ‘데이터베이스 개론’을 삽입하시오(출판사와 가격은 원하는되로 정해도 됩니다)
insert into book (bookid,bookname) values (11,'데이터베이스 개론');
insert into book (bookid,bookname) values (12,'자바프로그래밍');
-- 8. 7번에서 삽입한 ‘데이터베이스 개론’을 삭제하시오.
delete from book where bookid = 11;
-- 사용은 가능 워크밴치에서 왜 안되는지 모르겠다. mysql에서는 update시 자기 테이블을 바로 호출 불가 해결법은 따로 없음
delete from book where bookid = (select bookid from book where bookname ='자바프로그래밍');
-- 9. 도서테이블에서 도서번호가 3번인 도서의 출판사명을 경기출판사 가격은 25000원으로 수정하시오.
update book set publisher = '경기출판사', price = 25000 where bookid = 3;
-- 10. 주문테이블에서 2번고객이 5번 도서를 주문하였습니다. 삽입해주세요(가격은 임의로 정하고 주문날짜는 형식에 맞게 삽입하세요)
insert into orders values(11, 2, 5, 20000, '2014-07-15');
-- 11. 고객번호가 3번인 고객과 같은 책을 주문한 고객의 이름 책제목을 검색하시오
select name,bookname from customer c, book b, orders o where b.bookid = o.bookid and o.custid = c.custid and o.bookid in (select bookid from orders where custid = 3);

-- 출판사 별로 출판사의 평균 도서 가격보다 비싼 도서의 이름을 검색
select bookname, publisher from book b1 where price > (select avg(price) from book b2 where b1.publisher = b2.publisher);
-- 같은걸 2개인 척 가능
-- 불 필요한 테이블을 일부로 만들 필요가 없음
-- 예시 직원 테이블 만들때 사수테이블을 만들지 않고 자체조인으로 해결함