1、三个表：图书信息表、读者信息表、借阅表图书信息（书号、书名、作者、出版日期、出版社、图书存放位置、总数量）读者信息表（图书证号、姓名、所在系、借书上限数）借阅表（图书证号、书号、借出日期、应还日期）数据库表结构如下：BOOK(BNO, BN, BAU, BDA, BPU, BPL, BNU)READER(RNO, RN, RDE, RUP)BORROW(RNO, BNO, BDA, RDA)1. 查询被借阅的图书号以TP开头的图书信息select * from book where bno in (select bno from borrow where bno like TP%)2. 查和“
2、S0701026”读者借了相同图书的读者的图书证号和姓名Select rno,rn from readerWhere rno in(select rno from borrowWhere bno in(select bno from borrowWhere rno= S0701026)3. 检索至少借阅了 “数据结构”和“操作系统教程”的读者图书证号select borrow.rno from borrow,bookwhere borrow.bno=book.bnoand bn=数据结构and rno in (select borrow.rno from borrow,bookwhere bo
3、rrow.bno=book.bnoand bn=操作系统教程)4. 查询没有借阅 “C 程序设计”的读者姓名select rn from reader where rno not in(select rno from borrow where bno=(select bno from book where bn=C 程序设计)5查询张朝阳和李丽都借阅的图书书号select A.bno from borrow as A, borrow as Bwhere A.rno in(select rno from reader where rn=李丽 )and B.rno in(select rno fr
4、om reader where rn=张朝阳)and A.bno=B.bno6.查询借阅了图书的读者信息Select *From readerWhere rno in(Select rno from borrow)7.查没有被借阅的图书信息select * from bookwhere bno not in(select bno from borrow)8.查没有借书的读者的图书证号和姓名select rno,rn from readerwhere rno not in(select rno from borrow)9. 查询借阅图书数量达到 2 本的读者信息select * from rea
5、derwhere rno in (select rno from borrowgroup by rno having count(bno)=2)10. 查询有过期未还图书的读者的书号、姓名、所在系select borrow.bno,reader.rn,reader.rde from borrow,readerwhere borrow.rda 314、求选修了各课程的学生人数。Select cname, count(*) From SC, CWhere C.cno=SC.cnoGroup By cname15、在 SC 中，求选修课程 C01 的学生的学号和得分，并将结果按分数降序排序。Sele
6、ct sno, grade From SC Where cno=C01 order by Grade Desc16、查找每个同学的学号及选修课程的平均成绩情况。Select sno, AVG(Grade) From SC Group By sno17、列出学生所有可能的选课情况。Select S.sno, sname, cname From S, SC, CWhere S.sno=Sc.sno and SC.cno=C.cno18、列出每个同学的学号及选修课程的平均成绩情况，没有选修的同学也列出。Select s.sno,AVG(Grade)From SC right Join S ON sc
7、.sno=s.snoGroup By s.sno19、列出每个同学的学号及选修课程号，没有选修的同学也列出。Select s.sno,cnoFrom SC right Join S ON sc.sno=s.sno24、检索至少有两名男生选修的课程名。Select cname From C Where cno in(Select cno From SC, SWhere SC.sno=S.sno and sex=男Group by SC.cnoHaving Count(SC.sno) 1)25、检索 S 中不姓 “王”同学记录。Select * From S Where sname not Lik
8、e 王%26、检索和“李军” 同性别并同班的同学姓名。Select sname From SWhere sex=(Select sex From S Where sname=李军)and sdepartment=(Select sdepartment From S Where sname=李军)and sno 10Order By rs Desc,cno Asc31、求年龄大于所有女同学年龄的男同学姓名和年龄。Select sname,Year(getdate() - Year(Birthday) From SWhere Year(Birthday) (Select Min(Year(Birthday) From S Where sex=女)
