-- 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;