急急急急急急急急急急急!SQL问题出.100要答案

[epaib]

create database abc
use abc
create table stu
(
sno char(30) primary key ,
sname char(30) not null,
sdep char(30)
)

create table book
(
bno char(30) primary key ,
bname char(30) not null,
cate char(30),
/*zhxm char(30),*/
sun bigint
)

create table jy
(
sno char(30) ,
bno char(30),
jsrq datetime not null,
hsrq datetime,
check(jsrq<hsrq or hsrq is null),
primary key ( sno,bno) ,
foreign key (bno) references book(bno) on delete cascade,
foreign key (sno) references stu(sno) on delete cascade
)
insert into stu values('S0001','张三','D001')
insert into stu values('S0002','李平','D001')
insert into stu values('S0003','王玲','D002')
insert into stu values('S0004','李四','D002')
insert into stu values('S0005','王宁','D003')

alter table book add zhxm char(30)/*建表时忘记了这一列.现在加进来.注意加入的这一列自己加到表的最后列*/

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 jy values ('S0001','B0001','2001/1/1','2001/4/30')
insert into jy values ('S0001','B0002','2002/2/2','2002/6/2')
insert into jy values ('S0001','B0004','2003/12/7',NULL)
insert into jy values ('S0002','B0001','2003/3/1','2003/4/1')
insert into jy values ('S0002','B0002','2003/3/1','2003/5/4')
insert into jy values ('S0003','B0003','2004/1/1','2004/4/1')
insert into jy values ('S0003','B0005','2004/10/26',NULL)
insert into jy values ('S0004','B0002','2004/10/26',NULL)
insert into jy values ('S0005','B0002','2004/10/25',NULL)
1:找出至少借阅过张三同学所借阅过的图书的学生姓名和所属系(不包括张三自己)
2:找出借书最多的学生及其所借的本数
3.今天是2004年11月14日,王玲归还了所借的全部图书,请在数据库中做相应的记录
4.使用游标,定位王玲同学的第二次借阅记录,列出借阅的图书
5.经出被借阅次数排名前2的书名(提示:可以使用游标实现)







本人急求答案.....出分.100....希望不会的人不要来啊....

[txlicenhe]

哦，这是图书馆啊，走错地方了。

[631799]

2

[vinsonshen]

3

[vinsonshen]

1.select * from stu where sno in (select sno from jy where bno in (select bno from jy where sno in (select sno from stu where sname='张三'))) and sname<>'张三'

[feandy]

UP

[vinsonshen]

2.select d.*,c.total from stu as d,(select * from (select sno,count(*) as total from jy group by sno) a where not exists (select * from (select sno,count(*) as total from jy group by sno) b where b.total>a.total)) as c where c.sno=d.sno

[vinsonshen]

3.update a set a.hsrq='2004/11/14' from jy as a,stu b where b.sname='王玲' and a.sno=b.sno

[jiang130]

就當我沒來

[vinsonshen]

4.select a.*,c.sname,b.bname from jy as a,book as b,stu as c where c.sname='王玲' and c.sno=a.sno and a.bno=b.bno and (select count(*) from jy where sno=a.sno and jsrq<=a.jsrq)=2

5.select top 2 * from (select b.bname,count(*) as 次数 from jy as a,book as b where a.bno=b.bno group by b.bname) as c order by c.次数 desc

[vinsonshen]

呵呵，其实我也是不会的人， 乱写的哦~~~~~~~

[jornk]

这么复杂啊！！！明显没有我的问题简单嘛（http://community.csdn.net/Expert/TopicView1.asp?id=3631190）

<我是前来拉客的，对不起楼主了。>

[guanshiyu123]

不会走了

[vinsonshen]

楼主，我觉得你写这句“希望不会的人不要来啊....”有点。。。。。。。其实呢~~就算别人不会，帮你顶也你能顶到最上面让人看嘛~~~~

[epaib]

vinsonshen(猪骨褒咸鱼->味道没得顶)
厉害啊..