数据库图书查询.doc

三个表：图书信息表、读者信息表、借阅表图书信息（书号、书名、作者、出版日期、出版社、图书存放位置、总数量）读者信息表（图书证号、姓名、所在系、借书上限数）借阅表（图书证号、书号、借出日期、应还日期）数据库表结构如下：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. 查和“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 borrow.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 from 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 readerwhere rno in (select rno from borrowgroup by rno having count(bno)=2)10. 查询有过期未还图书的读者的书号、姓名、所在系select borrow.bno,reader.rn,reader.rde from borrow,reader where borrow.rda 314、求选修了各课程的学生人数。Select cname, count(*) From SC, CWhere C.cno=SC.cnoGroup By cname15、在SC中，求选修课程C01的学生的学号和得分，并将结果按分数降序排序。Select 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.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 Like 王%26、检索和“李军”同性别并同班的同学姓名。Select sname From SWhere sex=(Select sex From S Where sname=李军)and sdepartment=(Select sdepartment From S Where sname=李军)and sno (Select sno From S Where sname=李军)27、统计被学生选修的课程门数。Select Count(distinct cno) From SC28、求选修C04课程的学生的平均年龄。Select AVG(Year(getdate() - Year(Birthday) From SC, SWhere SC.cno=C04 and SC.sno=S.sno29、求LIU老师所授课程的每门课程的学生平均成绩。Select cno, AVG(Grade) From SCWhere cno in(Select cno From C Where Teacher=Liu)Group By cno30、统计每门课程的学生选修人数（超过10人的课程才统计）。要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列。Select cno, Count(sno) as rsFrom SCGroup by cnoHaving Count(sno) 10Order By rs Desc,cno Asc3