Oracle小练兵之映像租赁系统
映像租赁系统：
在美国，由于知识产权得到了很好的保护，所以一般不会出现盗版的问题。美国人除去在电影院看电影之外就是在影像店租碟回家去看。在这种情况下，影相出租的店铺就比较多。在信息化的背景下，设计一套系统管理影像出租业务成了当务之急，于是通过和店铺经理沟通，获得如下业务信息：
1.凡是想要到该影像店租赁影碟的被称为会员（Member）,只有成为该店的会员才能够租借该店的影像资料(Title)。
2.影像店中不定期会购买一些影像资料(Title)，并且获得了复制该影像资料的权力，每一份影像制品可以有多个拷贝，每一份拷贝称为一个复制条目(TitleCopy)
3.对客户而言，原始的影像资料（Title）只能被预定并且可以同时接受多份预定(Reservation)，客户可以租赁的是影像拷贝（TitleCopy）,
4.任何一个客户的租赁信息（Rental）都必须存档以便将来店铺对客户的租赁进行评估。
过和租赁店经理详细面谈，了解更详细的业务的信息：
1.每一个会员必须被记录的信息包括会员编号id,而且每个会员的编号是唯一的，姓名name, 成为会员的日期join_date,以及其他需要被记录的信息包括（住宅地址address, 所在城市city, 住宅电话phone.
2.影像制品(Title)必须被记录的信息包括编号id，而且所有影像制品的编号都是唯一的，标题title，影片说明description，价格price，和其他如下信息（分
级rating，种类category, 发行日期release_date）.
3.每一份影像拷贝（TitleCopy）都有各自的编号id，同一个影像的若干拷贝编号不同，但是不同的影像拷贝的编号可能会重复。必须记录下是否已经出租，以便于店员随时能够查询拷贝资料的当前状态(status)。
4.系统中允许会员预定某项影像资料，但必须提前预约租赁时间（res_date）。
5.每一项出租事项需要记录下来（租借日期book_date，实际归还日期act_ret_date,期待归还日期/过期日期(exp_ret_date)。
创表语句
id number(7) ,
title varchar2(15) constraint title_title_nn not null,
description varchar2(15) constraint title_description_nn not null,
rating char(1),
category varchar2(15),
release_date date,
price number(7,2) constraint title_price_nn not null,
constraint title_id_pk primary key(id)
);
id number(7),
last_name varchar2(15) constraint member_last_name_nn not null,
first_name varchar2(15),
address varchar2(15),
city varchar2(10),
phone varchar2(11),
join_date date constraint member_join_date_nn not null,
constraint member_id_pk primary key(id)
);
id number(7),
status varchar2(8) constraint title_copy_status_nn not null,
title_id number(7) constraint title_copy_title_id_nn not null constraint title_copy_title_id_fk references title(id),
constraint title_copy_id_pk2 primary key(id,title_id)
);
----------预约表-----------
create table reservation(
res_date date,
title_id number(7) constraint res_title_id_nn not null
constraint res_title_id_fk references title(id),
member_id number(7) constraint res_member_id_nn not null
constraint res_member_id_fk references member(id),
constraint res_res_date_pk3 primary key(res_date,title_id,member_id)
);
-------租赁表----------
create table rental(
book_date date,
act_ret_date date,
exp_ret_date date,
title_copy_id number(7),
member_id number(7) constraint rental_member_id_nn not null
constraint rental_member_id_fk references member(id),
title_copy_title_id number(7),
constraint rental_book_date_pk3 primary key(book_date,title_copy_id,title_copy_title_id),
constraint rental_title_copy_id_fk2 FOREIGN KEY(title_copy_id,title_copy_title_id) references title_copy(id,title_id)
);
insert into title values(1,'西虹市首富','反转人生','A','comedy','01-1月-18',36.5);
insert into title values(2,'摩天大楼','警察破案','A','suspense','01-9月-20',15);
insert into title values(3,'魁拔','走向巅峰','A','cartoon','01-8月-16',80);
insert into title values(4,'舌尖中国','中国美食','B','documentary','05-6月-18',20);
insert into title values(5,'中国好声音','选拔精英','B','documentary','06-9月-20',30);
insert into title values(6,'釜山行2','丧尸横行','B','disaster','03-9月-20',50.2);
insert into title values(7,'流浪地球','保护地球','C','science','01-1月-19',60);
insert into title values(8,'奥特曼','保护日本','C','cartoon','01-8月-06',30.5);
insert into title values(9,'疯狂动物','动物的本性','C','cartoon','01-1月-18',30);
insert into title values(10,'死亡迷宫','危险的迷宫','A','suspense','01-10月-19',36.5);
------------member---------------
insert into member(id,last_name,first_name,address,city,join_date)
values(1,'jack',null,'学院路','南京','05-3月-19');
insert into member(id,last_name,first_name,address,city,join_date)
values(2,'mary','lisa','解放路','苏州','29-2月-08');
insert into member(id,last_name,first_name,address,city,join_date)
values(3,'lili','luck','学府路','太原','09-8月-13');
insert into member(id,last_name,first_name,address,city,join_date)
values(4,'shi','zt','牧马路','忻州','06-9月-20');
insert into member(id,last_name,first_name,address,city,join_date)
values(5,'rui','sj','花园小区','南京','03-3月-19');
insert into member(id,last_name,first_name,address,city,join_date)
values(6,'liu','ll','榴莲小区','忻州','06-9月-20');
insert into member(id,last_name,first_name,address,city,join_date)
values(7,'ren','zhe','星星小区','太原','01-10月-19');
insert into member(id,last_name,first_name,address,city,join_date)
values(8,'ren','nan','学府街','太原','15-4月-20');
insert into member(id,last_name,first_name,address,city,join_date)
values(9,'zhang','san','学院路','苏州','01-9月-17');
insert into member(id,last_name,first_name,address,city,join_date)
values(10,'tom','len','学院路','昆山','04-5月-16');
-------------title_copy------------
insert into title_copy values(1,'T',1);
insert into title_copy values(2,'F',1);
insert into title_copy values(1,'T',3);
insert into title_copy values(2,'T',3);
insert into title_copy values(3,'T',3);
insert into title_copy values(1,'F',7);
insert into title_copy values(1,'F',5);
insert into title_copy values(2,'T',7);
insert into title_copy values(1,'F',9);
insert into title_copy values(1,'T',8);
insert into title_copy values(1,'T',6);
insert into title_copy values(1,'F',4);
insert into title_copy values(1,'T',2);
--------------reservation预约表----------
insert into reservation values('10-9月-20',1,3);
insert into reservation values('11-9月-20',3,3);
insert into reservation values('11-9月-20',3,5);
insert into reservation values('13-9月-20',4,6);
insert into reservation values('13-9月-20',6,3);
insert into reservation values('14-9月-20',3,5);
insert into reservation values('16-9月-20',9,5);
insert into reservation values('16-9月-20',6,5);
insert into reservation values('16-9月-20',2,7);
insert into reservation values('16-9月-20',8,5);
insert into reservation values('20-9月-20',7,6);
insert into reservation values('21-9月-20',7,2);
insert into reservation values('22-9月-20',7,1);
------------------rental租赁表----------
insert into rental values('20-9月-18','20-10月-18','20-12月-18',1,2,1);
insert into rental values('05-3月-19','05-7月-19','05-6月-19',1,5,7);
insert into rental values('05-8月-20',null,'05-11月-20',1,5,3);
insert into rental values('03-5月-20','01-7月-20','03-8月-20',2,4,3);
insert into rental values('05-9月-20','09-12月-20','05-12月-20',3,1,3);
insert into rental values('07-3月-20','01-4月-20','07-6月-20',1,6,2);
insert into rental values('07-9月-20','07-10月-20','07-12月-20',1,7,9);
insert into rental values('05-3月-19',null,'05-6月-19',1,8,6);
insert into rental values('07-6月-19','07-9月-19','20-12月-18',2,8,7);
insert into rental values('01-10月-18',null,'01-1月-19',1,1,4);
1.以及用户所借阅的影像资料名字和借阅的日期
select m.last_name||m.first_name,t.title,ren.book_date
from rental ren,title t,member m
where ren.member_id=m.id and t.id=ren.title_copy_title_id;
2.查询出最近一周订阅影像资料的用户和相应的影像资料名字及借阅日期
select last_name,title,book_date
from member m,title t,reservation res,rental ren
where m.id=res.member_id and t.id=res.title_id and ren.member_id=m.id and ren.title_copy_title_id=t.id
and (months_between(sysdate,res_date))*30<=7
and (months_between(res_date,sysdate))*30<=7;
3.查询出本周日应该归还的影像资料和借阅者的姓名，地址
select title_copy_id,title,last_name||first_name name,address
from rental ren,title_copy tc,member m,title t
where ren.title_copy_id=tc.id and ren.title_copy_title_id=tc.title_id
and ren.member_id=m.id and t.id=tc.title_id
and next_day(sysdate,'星期日')>=exp_ret_date;
4.查询出已经超期还未归还的影像资料和借阅者的姓名，地址
select title_copy_id,title,last_name||first_name name,address
from rental ren,title_copy tc,member m,title t
where ren.title_copy_id=tc.id and ren.title_copy_title_id=tc.title_id and ren.member_id=m.id and t.id=tc.title_id
and sysdate>=exp_ret_date;
5.查询出最近一月借阅次数最多的影像资料
select title
from title
where id in (select title_copy_title_id id
from rental
where months_between(sysdate,book_date)<=1
group by title_copy_title_id
having count(title_copy_title_id)=(select max(count(title_copy_title_id))
from rental
where months_between(sysdate,book_date)<=1
group by title_copy_title_id));
6.查询出已经登记但是还没有拷贝的影像资料
select title
from title
where id in (select title_id
from title_copy
group by title_id
having count(title_id)=1);
7.查询出本周预定最多的影像资料
select title
from title
where id in (select max(count(title_id))
from reservation
where res_date between (next_day(sysdate,'星期一'))-7 and (next_day(sysdate,'星期一'))
group by title_id);
