[oracle] 대출내역, 도서내역, 그리고 JOIN

-- create 권한 부여
grant create table, create seqeunce to system;

-- 대출 정보 테이블 생성
create table reservation_tbl (
  lentno varchar2(6),
  custname varchar2(20),
  bookno varchar2(3),
  outdate date,
  indate date,
  status char(1),
  primary key (lentno)
);

-- CRUD 권한 부여
grant select, update, delete, insert on reservation_tbl to system;

-- sequence 생성
create sequence res_seq
start with 1
increment by 1
cache 10;

-- seqeunce 권한 부여
grant select, alter on res_seq to system;

-- 도서정보 테이블 생성
create table bookinfo_tbl (
  bookno number(4) primary key,
  author char(20),
  bookname char(20)
);

-- crud 권한 부여
grant select, update, delete, insert on bookinfo_tbl to system;

-- 커밋
commit;


-- 도서정보 추가
insert into bookinfo_tbl values(101, '박작가', '시스템분석');
insert into bookinfo_tbl values(102, '김작가', '전산개론');
insert into bookinfo_tbl values(103, '이작가', '마케팅개론');
insert into bookinfo_tbl values(105, '황작가', '사회학');
insert into bookinfo_tbl values(201, '최작가', '역사학');
insert into bookinfo_tbl values(301, '오작가', '전산영어');

commit;

-- 대출내역 추가
insert into reservation_tbl values(res_seq.nextval, '김한국', '101', '20171201', NULL, 1);
insert into reservation_tbl values(res_seq.nextval, '진선미', '102', '20171204', '20171206', 2);
insert into reservation_tbl values(res_seq.nextval, '장소미', '201', '20171001', NULL, 1);
insert into reservation_tbl values(res_seq.nextval, '최소망', '103', '20171109', NULL, 1);
insert into reservation_tbl values(res_seq.nextval, '이동국', '301', '20171224', '20171225', 2);
insert into reservation_tbl values(res_seq.nextval, '이소라', '105', '20171208', '20171211', 2);

commit;

-- JOIN 하여 조회
select r.lentno as 대출번호,
       r.custname as 대출자,
       b.bookname as 도서명,
       to_char(r.outdate, 'YYYY-MM-DD') as 대출일,
       to_char(r.indate, 'YYYY-MM-DD') as 반납일
from   reservation_tbl r
join   bookinfo_tbl b on r.bookno = b.bookno
order by r.lentno asc;