SQL> ed Wrote file afiedt.buf 1 create table member( 2 member_id number(10) NOT NULL unique, 3 last_name varchar2(25) not null, 4 first_name varchar2(25), 5 address varchar2(100), 6 city varchar2(30), 7 phone varchar2(15), 8* join_date date not null ) SQL> r 1 create table member( 2 member_id number(10) NOT NULL unique, 3 last_name varchar2(25) not null, 4 first_name varchar2(25), 5 address varchar2(100), 6 city varchar2(30), 7 phone varchar2(15), 8* join_date date not null ) Table created. SQL> ed Wrote file afiedt.buf 1 create table title( 2 title_id number(10) NOT NULL unique, 3 title varchar2(60) not null, 4 description varchar2(400) not null, 5 rating varchar2(4) constraint dene check(rating ='g'), 6 check(rating= 'pg'),check(rating='r'),check(rating='nc17'), 7 check(rating='nr'), 8 category varchar2(30) constraint dene2 check(category='drama'), 9 check(category='comedy'),check(category='action'), 10 check(category='child'),check(category='scfi'), 11 check(category='document'),check(category='ary'), 12* release_date date) SQL> r 1 create table title( 2 title_id number(10) NOT NULL unique, 3 title varchar2(60) not null, 4 description varchar2(400) not null, 5 rating varchar2(4) constraint dene check(rating ='g'), 6 check(rating= 'pg'),check(rating='r'),check(rating='nc17'), 7 check(rating='nr'), 8 category varchar2(30) constraint dene2 check(category='drama'), 9 check(category='comedy'),check(category='action'), 10 check(category='child'),check(category='scfi'), 11 check(category='document'),check(category='ary'), 12* release_date date) Table created. SQL> ed Wrote file afiedt.buf 1 create table title_copy( 2 copy_id number(10) NOT NULL unique, 3 title_id number(10) not null unique , 4 status varchar2(15) not null, 5 constraint dene4 PRIMARY KEY(title_id,copy_id), 6 constraint dene6 foreign key(title_id) references title(title_id), 7 constraint dene5 check(status ='available'), 8 check(status= 'destroyed'),check(status='rented'), 9* check(status='reserved')) SQL> r 1 create table title_copy( 2 copy_id number(10) NOT NULL unique, 3 title_id number(10) not null unique , 4 status varchar2(15) not null, 5 constraint dene4 PRIMARY KEY(title_id,copy_id), 6 constraint dene6 foreign key(title_id) references title(title_id), 7 constraint dene5 check(status ='available'), 8 check(status= 'destroyed'),check(status='rented'), 9* check(status='reserved')) Table created. 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 dene11 PRIMARY KEY(book_date,member_id,copy_id,title_id), constraint dene8 foreign key(title_id) references title_copy(title_id), constraint dene9 foreign key(copy_id) references title_copy(copy_id), constraint dene10 foreign key(member_id) references member(member_id)) / create table reservation( res_date date not null unique, member_id number(10) not null unique, title_id number(10) not null, constraint dene12 PRIMARY KEY(res_date,member_id,title_id), constraint dene13 foreign key(title_id) references title(title_id), constraint dene14 foreign key(member_id) references member(member_id)) / select table_name from all_tables; TABLE_NAME ------------------------------ MEMBER TITLE TITLE_COPY RENTAL RESERVATION select constraint_name, column_name , table_name from user_cons_columns; soru 3) SQL> create sequence member_id_seq 2 increment by 1 3 start with 101 4 nocache; 1 create sequence title_id_seq 2 increment by 1 3 start with 92 4* nocache SQL> select sequence_name, last_number, increment_by from user_sequences; SEQUENCE_NAME LAST_NUMBER INCREMENT_BY ------------------------------ ----------- ------------ MEMBER_ID_SEQ 101 1 TITLE_ID_SEQ 92 1 2 rows selected. soru 4) insert into title (title_id,title,description,rating,category,release_date) values (TITLE_ID_SEQ.nextval, 'Willi and Christmas Too', 'All of Willies friends make a Christmas list for Santa. but Willlie has yet to add his own wish list' , 'g', 'child' , '05-oct-95'); insert into title (title_id,title,description,rating,category,release_date) values (TITLE_ID_SEQ.nextval, 'Alien Again', 'Yet another installation of science fiction history. Can the heroine save the planet from the alien life form?' , 'r', 'scifi' , '19-may-95'); insert into title (title_id,title,description,rating,category,release_date) values (TITLE_ID_SEQ.nextval, 'The glob', 'A meteor crashes near a small American town and unleashes carnivorous goo in this classic' , 'nr', 'scifi' , '12-aug-95'); insert into title (title_id,title,description,rating,category,release_date) values (TITLE_ID_SEQ.nextval, 'Soda Gang', 'After discovering a cache of drugs a young couple find themselves pitted a vicios gang' , 'nr', 'action' , '01-jun-95'); insert into member (member_id,first_name,Last_name,address,city,phone,join_date) values (MEMBER_ID_SEQ.nextval,'Carmen', 'Valesquez', '283 king street', 'seatle' , '206-899-6666', '08-mar-90'); insert into member (member_id,first_name,Last_name,address,city,phone,join_date) values (MEMBER_ID_SEQ.nextval, 'Ladoris', 'Ngao', '5 Modrany', 'Bratislava', '586-355-8882', '08-mar-90'); insert into member (member_id,first_name,Last_name,address,city,phone,join_date) values (MEMBER_ID_SEQ.nextval, 'Midori', 'nagayama', '68 Via Centrale', 'Sao Paolo' , '254-852-5764', '17-jun-91'); insert into member (member_id,first_name,Last_name,address,city,phone,join_date) values (MEMBER_ID_SEQ.nextval, 'Mark', 'Quick-to-see', '6291 King way', 'Lagos' , '63-559-7777', '07-apr-90'); insert into member (member_id,first_name,Last_name,address,city,phone,join_date) values (MEMBER_ID_SEQ.nextval, 'Audry', 'Ropeburn', '86 chu street', 'hongkong' , '41-559-87', '18-jan-91'); insert into member (member_id,first_name,Last_name,address,city,phone,join_date) values (MEMBER_ID_SEQ.nextval, 'Molly', 'Urguhart', '3035 Laurier', 'quebec' , '418-542-9988', '18-jan-91'); insert into title_copy (title,copy_id,status) values ('willie and christmas too', 1,'Available'); insert into title_copy (title,copy_id,status) values ('Alien Again', 1,'Available'); insert into title_copy (title,copy_id,status) values ('Alien Again', 2,'Rented'); insert into title_copy (title,copy_id,status) values ('The Glob', 1,'Available'); insert into title_copy (title,copy_id,status) values ('My day off', 1,'Available'); insert into title_copy (title,copy_id,status) values ('My Day Off', 2,'Available'); insert into title_copy (title,copy_id,status) values ('My Day Off', 3,'Rented'); insert into title_copy (title,copy_id,status) values ('Miracles on Ice', 1,'Available'); insert into title_copy (title,copy_id,status) values ('Soda Gang', 1,'Available'); insert into rental (title_id,copy_id,Member_id,Book_date,exp_ret_date,act_ret_date) values(92,1,101,'3 days ago','1 day ago','2 day ago'); insert into rental (title_id,copy_id,Member_id,Book_date,exp_ret_date,act_ret_date) values(93,2,101,'1 days ago','1 day ago',NULL); insert into rental (title_id,copy_id,Member_id,Book_date,exp_ret_date,act_ret_date) values(94,3,102,'2 days ago','Today',NULL); insert into rental (title_id,copy_id,Member_id,Book_date,exp_ret_date,act_ret_date) values(95,1,106,'4 days ago','2 day ago','2 day ago'); soru 5) create view title_avail as select a.title,a.copy_id,b.status,a.exp_ret_date from rental a,title_copy b where a.copy_id(+)=b.copy_id order by a.title; soru 6,a) insert into title (title_id,title,description,rating,category,release_date) values (TITLE_ID_SEQ.nextval, 'Interstellar Wars', 'Futuristic interstellar action movie Can the rebels sace the humans form the evil empire' , 'pg', 'scifi' , '07-jul-77'); insert into title_copy (title,copy_id,status) values ('Interstellar Wars', 1,'Available'); insert into title_copy (title,copy_id,status) values ('Interstellar Wars', 2,'Available'); soru 6,b) insert into rental (title_id,copy_id,Member_id,Book_date,exp_ret_date,act_ret_date) values(98,1,101,'3 days ago','1 day ago','2 day ago'); insert into rental (title_id,copy_id,Member_id,Book_date,exp_ret_date,act_ret_date) values(97,1,104,'3 days ago','1 day ago','2 day ago'); soru 7,a) alter table title add (price number(8,2)); desc title; Name Null? Type ----------------------------------------------------- -------- -------------------------- TITLE_ID NOT NULL NUMBER(10) TITLE NOT NULL VARCHAR2(60) DESCRIPTION NOT NULL VARCHAR2(400) RATING VARCHAR2(4) CATEGORY VARCHAR2(30) RELEASE_DATE DATE PRICE NUMBER(8,2) soru 7,b) insert into title (price) values (25) where title_id=92; insert into title (price) values (35) where title_id=93; insert into title (price) values (35) where title_id=94; insert into title (price) values (35) where title_id=95; insert into title (price) values (30) where title_id=96; insert into title (price) values (35) where title_id=97; insert into title (price) values (29) where title_id=98; soru 7,c) alter table title add constraint title_price_nn not null(price); soru 8) set pagesize 30 set linesize 60 set feedback off title 'Customer History Report' column b.title heading 'Title of movie' format A20 select a.member_id,a.first_name,a.last_name,b.title,count(a.member_id) from title b,rental c,member a where a.member_id=c.member_id and b.title_id=c.title_id group by a.member_id order by a.member_id;