题目：
数据库原理练习1
/*学校图书馆有如下的数据库，其中存放了如下表： 学生（学号, 姓名, 所属系号）
图书（书号，书名，类别，作者姓名，总册数）注：同一种书具有同一书号 借阅（学号，书号，借书日期，还书日期） 使用SQL语句完成如下功能：
1. 建表，要求在定义中做如下声明： a) 必要的主码外码
b) 学生的姓名和图书名称不能为空
c) 借书日期不能为空, 还书日期必须迟于借书日期或者为空，数据类型均为datetime。
2. 往表中插入数据（注意给定数据中的日期插入时必须转换为合适的格式） 学生（S0001,张三,D001； S0002,李平,D001； S0003,王玲,D002； S0004,李四,D002； S0005,王宁,D003）
图书（B0001,平凡的世界,现代小说,路遥,2； B0002,王朔文集,现代小说,王朔,2； B0003,小李飞刀,武侠小说,古龙,2；
B0004,数据库系统概念,计算机,杨冬青,2； B0005,数据结构,计算机,张铭,2）
借阅（S0001,B0001,2001年1月1日,2001年4月30日； S0001,B0002,2002年2月2日,2002年6月2日； S0001,B0004,2003年12月7日,null）
S0002,B0001,2003年3月1日,2003年4月1日； S0002,B0002,2003年3月1日,2003年5月4日； S0003,B0003,2004年1月1日,2004年4月1日； S0003,B0005,2004年10月26日,null； S0004,B0004,2004年10月26日,null； S0005,B0005,2004年10月25日,null） 3. 用SQL语句完成如下查询：
a) 找出借书日期超过30天的所有学生的姓名。
b) 找出至少借阅过张三同学所借阅过的图书的学生姓名和所属系（不包括张三自己）。 c) 找出借书最多的学生及其所借书的本数。
4. 今天是2004年11月14日，王玲归还了所借的全部图书，请在数据库中做相应的记录。 5. 使用游标，定位王玲同学的第二次借阅记录，列出借阅的图书。 6. 给出被借阅次数排名前2的书名。（提示：可以使用游标实现）*/
网上的参考答案：
create table e032study( s_id char(20),
s_name char(20) not null, x_id char(20), primary key (s_id) )
create table e032book( b_id char(20) UNIQUE, b_name char(20) not null, b_type char(20), b_writer char(20), b_sl char(20), primary key (b_id) )
create table e032brrow( s_id char(20), b_id char(20),
j_datedatetime not null, h_datedatetime,
primary key (s_id,b_id),
foreign key (s_id) references e032study(s_id), foreign key (b_id) references e032book(b_id), check (h_date is null or h_date>j_date) )
insert into e032study values('S0001','张三','D001') insert into e032study values('S0002','李平','D001') insert into e032study values('S0003','王玲','D002') insert into e032study values('S0004','李四','D002') insert into e032study values('S0005','王宁','D003')
select * from e032study
insert into e032book values('B0001','平凡的世界','现代小说','路遥',2) insert into e032book values('B0002','王朔文集','现代小说','王朔',2)
insert into e032book values('B0003','小李飞刀','武侠小说','古龙',2)
insert into e032book values('B0004','数据库系统概念','计算机','杨冬青',2) insert into e032book values('B0005','数据结构','计算机','张铭',2)
select * from e032book
insert into e032brrow values('S0001','B0001','2001-1-1','2001-4-30') insert into e032brrow values('S0001','B0002','2002/2/2','2002/6/2') insert into e032brrow values('S0001','B0004','2003/12/7',null)
insert into e032brrow values('S0002','B0001','2003/3/1','2003/4/1') insert into e032brrow values('S0002','B0002','2003/3/1','2003/5/4') insert into e032brrow values('S0003','B0003','2004/1/1','2004/4/1') insert into e032brrow values('S0003','B0005','2004/10/26',null) insert into e032brrow values('S0004','B0004','2004/10/26',null) insert into e032brrow values('S0005','B0005','2004/10/25',null)
select * from e032brrow
--找出借书期超过30天的所有学生的姓名。
select s_name借书期超过30天的所有学生的姓名 from e032study wheres_id in( selects_id
from e032brrow
whereh_date-j_date>30 )
--找出至少借阅过张三同学所借阅过的图书的学生姓名和所属系（不包括张三自己） selectz.s_name,z.x_id from e032study z
where z.s_name<>'张三'and z.s_id in( selectx.s_id
from e032brrow x, e032brrow y
wherex.b_id=y.b_id and y.s_id='S0001' and x.s_id<>'S0001' )
--找出借书最多的学生及其所借书的本数。 selects_id,count(s_id) from e032brrow group by s_id
having count(s_id)>=all( select count(s_id) from e032brrow group by s_id
)
--今天是2004/11/14，王玲归还了所借的全部图书，请在数据库中做相应的记录。 update e032brrow
seth_date='2004/11/14'
where s_id=all(select s_id from e032study where s_name='王玲') and h_date is null select * from e032brrow
--使用游标，定位王玲同学的第二次借阅记录，列出借阅的图书。 print \王玲同学的第二次借阅记录\
declare @i char(20),@b_id char(20),@s_id char(20),@j_datedatetime,@h_datedatetime set @i=1
declarett cursor
for select * from e032brrow where s_id=all(select s_id from e032study where s_name='王玲') opentt
fetch from tt
into @s_id,@b_id,@j_date,@h_date if (@@fetch_status<>0)
print\王玲同学无借阅记录\fetch from tt
into @s_id,@b_id,@j_date,@h_date if (@@fetch_status<>0)
print\王玲同学无第二次借阅记录\IF(@@fetch_status=0) print @s_id print @b_id print @j_date print @h_date closett
deallocatett
--给出被借阅次数排名前2的书名。（提示：可以使用游标实现） print \被借阅次数排名前2的书名\
declare @bid char(20),@j int,@b_name char(20),@s int set @j=1
declarettt cursor
for select b_id,count(b_id) from e032brrow group by b_id openttt
while @j<=2 begin
fetch next from ttt into @bid,@s
select @b_name=b_name from e032book where b_id=@bid print @b_name
set @j=@j+1 end closettt
deallocatettt
自己做的参考答案：
create table stu (
studentno char(10) not null, studentname char(15) not null, departmentno char(10), primary key (studentno) )
create table book (
bookno char(10) not null, bookname char(20) not null , sort char(15), author char(10), numsmallint,
primary key (bookno) )
create table borrow (
studentno char(10) not null, bookno char(10) not null, borrowdatedatetime not null, retuendatedatetime
primary key (studentno,bookno),
foreign key (studentno) references stu(studentno), foreign key (bookno) references book(bookno),
check (retuendate>borrowdate or retuendate is null) )
insert into stu values('S0001','张三','D001'); insert into stu values('S0002','李平','D001');
北大数据库原理上机考题练习及参考答案 练习三(学生、书、借阅)
