北大数据库原理上机考题练习及参考答案 练习三(学生、书、借阅)

insert into stu values('S0003','王玲','D002'); insert into stu values('S0004','李四','D002'); insert into stu values('S0005','王宁','D003');

insert into book values ('B0001','平凡的世界','现代小说','路遥',2); insert into book values ('B0002','王朔文集','现代小说','王朔',2); insert into book values ('B0003','小李飞刀','武侠小说','古龙',2);

insert into book values ('B0004','数据库系统概念','计算机','杨冬青',2); insert into book values ('B0005','数据结构','计算机','张铭',2);

insert into borrow values('S0001','B0001','2001-1-1','2001-4-30'); insert into borrow values('S0001','B0002','2002-2-2','2002-6-2'); insert into borrow values('S0001','B0004','2003-12-7',null);

insert into borrow values('S0002','B0001','2003-3-1','2003-4-1'); insert into borrow values('S0002','B0002','2003-3-1','2003-5-4'); insert into borrow values('S0003','B0003','2004-1-1','2004-4-1'); insert into borrow values('S0003','B0005','2004-10-26',null); insert into borrow values('S0004','B0004','2004-10-26',null); insert into borrow values('S0005','B0005','2004-10-25',null);

3. 用SQL语句完成如下查询：

a) 找出借书日期超过30天的所有学生的姓名。

select distinct studentname from stus,borrow b where s.studentno = b.studentno and retuendate - borrowdate> 30;

b) 找出至少借阅过张三同学所借阅过的图书的学生姓名和所属系（不包括张三自己）。

我理解：这里应该不是包括张三借过的所有图书。

select studentname,departmentno from stus,borrow b where s.studentno = b.studentno and s.studentname not like '%张三%'and

b.bookno in (select bookno from stus,borrow b where s.studentno = b.studentno and s.studentname = '张三')

c) 找出借书最多的学生及其所借书的本数。

selects.studentname,count(bookno) from borrow b,stu s wheres.studentno = b.studentno group by s.studentname

having count(bookno) > =all (

select count(bookno) from borrow group by studentno )

4. 今天是2004年11月14日，王玲归还了所借的全部图书，请在数据库中做相应的记录。

update borrow set retuendate = '2004-11-14'

where studentno in (select studentno from stu where studentname = '王玲' ) and retuendate is null

5. 使用游标，定位王玲同学的第二次借阅记录，列出借阅的图书。

declare c1 cursor scroll for

selectbookname from stus,borrow b ,book k where s.studentno = b.studentno and k.bookno = b.bookno and

studentname = '王玲' order by borrowdateasc

open c1

declare @x char(10)

fetchabsolute 2from c1 into @x print @x close c1

deallocate c1

6. 给出被借阅次数排名前2的书名。（提示：可以使用游标实现）*/

declare @bookno char(10),@j int,@s int,@bookname char(15) set @j =1

declare tt cursor

for select bookno,count(bookno) from borrow group by bookno order by count(bookno) desc open tt

while @j<=2 begin

fetch next from tt into @bookno,@s

select @bookname= bookname from book where bookno = @bookno print @bookname set @j = @j+1 end close tt deallocatett