DB 놀이 5

--1.
create table member(
  id int,
  name varchar2(10),
  sal int,
  birthday date
);

drop table member;

insert into member (id, name, sal, birthday) values (1,'사랑해', 4000, '1994/07/18');
insert into member (id, name, sal, birthday) values (2,'최보람', 2500, '1994/07/19');
insert into member (id, name, sal, birthday) values (3,'고마와', 2200, '1994/07/24');
insert into member (id, name, sal, birthday) values (4,'오감사', 2000, '1992/06/27');
insert into member (id, name, sal, birthday) values (5,'이가을', 3000, '1993/10/18');
insert into member (id, name, sal, birthday) values (6,'배우자', null, '1995/09/02');

delete from member where name='오감사';

select * from member;

select to_char(sysdate,'MM')from dual;

--2.
select sum(sal), avg(sal), count(*), count(sal) from member;
--3.
select * from member where sal is null;
--4.
select * from member where sal='';
--5. member테이블에서 생일이 1994년 7월 8일에서 7월 9일 사이에 태어난 회원의 회원번호, 이름, 봉급, 생일검색명령.
--   단, 생일은 '1994년 07월 09일 수요일' 형태로 표시
select  id, name,sal,to_char('YYYY"년",MM"월",DD"일"') as birthday
from member
where birthday between to_date('1994/07/08') and to_date('1994/07/09');

--6.dbms 서버에 설정된 현재 시간과 오늘 날짜를 확인 예)2018년 09월 17일 10:20:22 형태로
select to_char(sysdate, 'YYYY"년"MM"월"DD"일" HH24:MI:SS')today from dual;

--7.member 테이블에서 모든 정보를 앞의 두명만 검색 
select *
from member
where rownum <= 2; 

--8. 아래 두 쿼리 차이가 무엇일까?
--정렬먼저 후에 rownum을 실행하라는 쿼리
select * from (select * from member order by sal) where rownum <=3;

--위의 3명을 잘라서 정렬하라는 의미
select * from (select * from member where rownum<=3) order by sal;

--9. sal이 가장 많은 두 명의 모든 정보를 검색 
select * from (select sal from member order by sal desc) where rownum <=2 and sal is not null;

--10. member 테이블에서 party라는 속성을 추가하고 생일에서 각각 100일 지난날로 입력하는 명령
alter table member add party date;
--
update member set party = birthday+100;

-- 스칼라 부속질의를 사용하여 마당서점의 도서별 도서명과 판매약 합계를 검색
select (select bookname 
        from book b
        where b.bookid = o.bookid) as "도서명",
        sum(saleprice)
from orders o
group by bookid;

select bookname, sum(saleprice) 
from book b join orders o using (bookid)
group by bookname;

select bookname, sum(saleprice)
from book b join orders o
on (o.BOOKID = b.BOOKID)
group by bookname;



--12 도서번호가 4이하인 도서의 판매액을 보이는 명령 (결과는 도서명과 도서별 판매액 출력, 인라인 뷰 이용!) 
select b.bookname,sum(od.saleprice)"total"
from (select bookid, bookname
      from book
      where bookid<=4) b,
      orders od
where b.bookid =od.bookid
group by bookname;


--13. 대한미디어에서 출판한 도서의 총 판매액을 구하는 명령(where 부속 질의 사용!)
select sum(saleprice) "total"
from orders
where bookid in(select bookid 
                from book
                 where publisher like '%대한미디어%');
                 
--join 사용
select sum(saleprice) "total"
from orders o, book b
where publisher like '%대한미디어%' and o.bookid = b.bookid;
                 



--14. 2번 도서의 최고 가격보다 더 비싼 금액의 도서명과 가격 검색
select bookid, price
from book
where price > all (select price
                   from book
                   where bookid=2);
                   
select bookid, price
from book
where price >  (select max(price)
                   from book
                   where bookid=2);
                   
--15. 고객번호가 3번 이하인 고객들에게 판매한 판매액 합계(exists사용) 검색
select sum(saleprice) 
from orders o
where exists(select * from customer c
            where custid<=3 and c.custid= o.custid);

select sum(saleprice)
from orders
where custid <=3;

--16. EXISTS 연산자로 대한민국에 거주하지 않는 고객에게 판매한 도서의 총 판매액을 구하는 명령
select  sum(saleprice) "total"
from orders od
where  exists     (select *
                  from customer cs
                  where address not like '%대한민국%'
                  and cs.custid = od.custid);

--17. 대한미디어에서 출판한 도서의 모든 정보를 보여주는 뷰인vw_b_d생성하는 명령
create view vw_b_d
as select *
from book
where publisher like '대한미디어';

--18. 대한미디어에서 출판한 도서의 모든 정보 중 가격이 2만원대인 도서정보를 검색하는 명령(17번에서 생성한 뷰이용)
select *
from vw_b_d
where price >= 20000 and price <30000;

--19. vw_b_d부ㅠ를 삭제하는 명령
drop view vw_b_d;

--20. 판매가격이 15000원 이상인 도서의 도서번호, 도서이름, 고객이름, 출판사, 판매가격을 보여주는 vw_o를 생성
create view vw_o
as select b.bookid, bookname, name, publisher, saleprice
from book b, customer c, orders o
where saleprice >= 15000 and 
b.bookid=o.bookid and c.custid = o.custid;

--21. 생성한 뷰를 이용하여 판매된 도서의 이름과 고객의 이름을 출력하는 문을 작성하기
select bookname, name from vw_o;

--22. vw_o뷰를 변경하고자 한다. 판매가격 속성을 삭제하는 명령, 삭제 후 21번 sql 문을 다시 수행한 결과
create or replace view vw_o
as select b.bookid, b.bookname, c.name, b.publisher
from book b, orders o,customer c
where b.bookid=o.bookid and o.custid=c.custid and
saleprice = 15000;

select * from vw_o;