2021-05-13 오라클 데이터베이스(스마트인재개발원)

drop table member cascade constraints;
drop table title cascade constraints;
drop table title_copy cascade constraints;
drop table rental cascade constraints;
drop table reservation cascade constraints;
drop sequence member_id_seq;
drop sequence title_id_seq;

/*테이블 생성*/
create table member(
member_id number(10),
last_name varchar2(25) not null,
first_name varchar2(25),
address varchar2(100),
city varchar2(30),
phone varchar2(15),
join_date date default sysdate not null,
constraint member_member_id_pk primary key(member_id));

create table title(
title_id number(10),
title varchar2(60) not null,
description varchar2(400) not null,
rating varchar2(4),
category varchar2(20),
release_date date,
constraint title_title_id_pk primary key(title_id),
constraint title_rating_ch check(rating in ('G','PG','R','NC17','NR')),
constraint title_category_ch check(category in ('DRAMA', 'COMEDY', 'ACTION', 'CHILD', 'SCIFI', 'DOCUMENTARY')));


create table title_copy(
copy_id number(10),
title_id number(10),
status varchar2(15) not null,
constraint title_copy_copy_id_title_id_pk primary key (copy_id, title_id),
constraint title_copy_title_id_fk foreign key(title_id) references title(title_id),
constraint title_copy_status_ch check(status in ('available','destroyes', 'rented', 'reserved'))
);

create table rental(
book_date date default sysdate,
member_id number(10),
copy_id number(10),
act_ret_date date,
exp_ret_date date default sysdate +2,
title_id number(10),
constraint rental_book_mem_co_ti_id_pk primary key(book_date, member_id, copy_id, title_id),
constraint rental_member_id_fk foreign key(member_id) references member(member_id),
constraint rental_copy_id_title_id_fk foreign key(copy_id, title_id) references title_copy(copy_id, title_id));


create table reservation(
res_date date,
member_id number(10),
title_id number(10),
constraint reservation_res_me_ti_pk primary key(res_date, member_id, title_id),
constraint reservation_member_id_fk foreign key(member_id) references member(member_id),
constraint reservation_title_id_fk foreign key(title_id) references title(title_id)
);

/*3. 시퀀스 생성*/
create sequence member_id_seq  
start with 101 nocache;


create sequence title_id_seq 
start with 92 nocache;


/*
4.데이터를 추가 스크립트 작성* 해당 시퀀스의 값을 증가시키고 싶다면
testSeq.NEXTVAL
현재 시퀀스를 알고 싶다면
testSeq.CURRVAL 
*/

alter session set nls_language='AMERICAN';
alter session set nls_date_format='dd-mon-yy';
insert into title values(title_id_seq.nextval,'WilHeand Christmas Too', 'Willie and a Christmas list for Santa, but Willie has yet to add his own wish list', 'G', 'CHILD', to_date('05-OCT-1995', 'DD-MON-YYYY'));
insert into title values(title_id_seq.nextval, 'Alien Again', 'Yet another installation of science fiction history. Can the heroine save the planet from th alien life from?', 'R', 'SCIFI', to_date('19-MAY-1995', 'DD-MON-YYYY'));
insert into title values(title_id_seq.nextval, 'The Glob', 'A meteor crashes near a small Amerivan town and unleashes carnivorous goo in this classic', 'NR', 'SCIFI', to_date('12-AUG-1995', 'DD-MON-YYYY'));
insert into title values(title_id_seq.nextval, 'My Day off','With a little luck and a lot of ingenuity, a teenager skips school for a day in New York', 'PG', 'COMEDY', to_date('12-JUL-1995', 'DD-MON-YYYY'));
insert into title values(title_id_seq.nextval, 'Miracles on Ice','A six-year-old has doubts about Santa Claus, but she discovers that miracles really do exist.', 'PG', 'DRAMA', to_date('12-SEP-1995', 'DD-MON-YYYY'));
insert into title values(title_id_seq.nextval, 'Soda Gang','After discovering a cache of drugs, a young couple find themsdves pitted against a vicious gang.', 'NR', 'ACTION', to_date('01-JUN-1995', 'DD-MON-YYYY'));