实验四复杂查询.docx
实验四复杂查询一、实验目的掌握两个表以上的连接查询的应用，包括嵌套查询。二、实验内容(1)查询比“林红”年纪大的男学生信息。se1ect*fromStudentwhereSex=，男，andYEAR(Birth)-(se1ectYEAR(Birth)fromStudentwhereSname='林红')<0ZQYCStudentjnfo-dbo.StudentSQ1Query1sqI-ZQ一(Zqy-Pezqy(52)*日se1ect*fromStudentwhereSex='男'andYEAR(Birth)-(se1ectYEAR(Birth)-IfrOmStudentwhereSname='林红')<0国结果I消息IISnOISnameSeXBirthCiaSsnoEntrar>cedateHOmeaddrSdePtPOStCOde(2)检索所有学生的选课信息，包括学号、姓名、课号、课程名、成绩。se1ectSC.Sno,Sname,Sex,C1assno,CnamezGradefromStudents,SC,Coursecwheres.Sno-SC.SnoandSC.cno-oSQ1Query1sqI-ZQ_(zqy-PCzqy(52)*日se1ectSC.SnozSname,SexrC1assno,Cname,GradeIfromStudents,SCrCoursec1Wheres.Sno=SC-SnoandSC.cno=o口结果I3消息ISnoISnameSexQassnoCname|Grade|120110001!张虹里051高数89220110001张虹里051C语言程序设计78320110001张虹男051JAVA语言程序设计89420110002林红女051C语言程序设计60520110103赵青男061高数80(3)查询己选课学生的学号、姓名、课程名、成绩。se1ectSC.Sno,Sname,Cname,GradefromStudents,coursec,SCwheres.sno=SCo=SC.cnoSQ1Query1sqI-ZQ一(Zqy-PCzqy(52)*se1ectSC.Sno,Sname,Cname,GradeIfromStudents,coursecrSC1wheres.sno=SCo=SC.cno口结果IJ消息ISnoSname|CnameGrade|120110001张虹高数89220110001张虹C语言程序设计78320110001张虹JAVA语言程序设计89420110002林红C语言程序设计60520110103赵青高数80(4)查询选修了“C语言程序设计”的学生的学号和姓名。se1ectsc.Sno,SnamefromStudents,courseczscwherec.Cname-'C语言程序设计'ands.Sno-sc.Snoandsc.Cno=c.CnoSQ1Query1sqI-ZQ.(zqy-PCzqy(52)*se1ectsc.Sno,SnameIfromStudent3,coursec,sc1wherec.Cname='C语言程序设计'ands.Sno=sc.Snoandsc.Cno=c.Cno里结果I包消息!SnoISname120110001ia2f1oiioooi-z林红(5)查询与“张虹”在同一个班级的学生学号、姓名、家庭住址。a.用子查询se1ectSno,SnamezHome_addrfromStudentwhereC1assno='051,andSrIame!='张虹'SQ1QueryZsqI-ZQ一(Zqy-Pezqy(54)*SQ1Query1.sq日se1ectSno,Sname,Home_addrIfromStudent1whereC1assno=1051,andSname!='张虹N结果®消息ISnoISnameHome-addr20110002j林红北京b.用连接查询se1ectSno,SnamezHome_addrfromStudentwhereC1assno=(se1ectC1assnofromStudentwhereSname=张虹)andSname!='张虹'SQ1Query2,sq1ZQ.(zqy-PCzqy(54)*SQ1Query1sqI-ZQ(Zqy-PCzqy(52)*日se1ectSnozSname,Home_addrfromStudentwhereC1assno=(se1ectC1assnofromStudentwhereSna1ne='张虹')-andSname!=,张虹,口结果必消另ISnoSnameHome-addrD20110002j林红北京（6）查询其他班级中比“051”班所有学生年龄大的学生的学号、姓名。se1ectSno,SnamefromStudentwhereC1assno<>,051,andBirth<a11(se1ectBirthfromStudentwhereC1assno=,051,)SQ1Query1sqI-ZQ.(zqy-PCzqy(52)*ZQY-PC.Studentjnfo-dbo.St曰se1ectSnorSnamefromStudentwhereC1assno<>051andBirth<a11(se1ectBirthfromStudent1whereC1assno,051)E产果IBIISnoISnafne(7)(选作)查询选修了全部课程的学生姓名。本题使用除运算的方法。由题意可得另一种语言，没有一个选了课的学生没有选CoUrSe表里的课程。那么，我们需要两个NOTEXISTS表示双重否定；另一种思路可详见书例4.52se1ectSnamefromStudentwherenotexists(se1ect*fromCoursewherenotexists(se1ect*fromSCwhereSno=Student.snoandcno=Co)ZQY-PC.Studentnfo-dbo.SCSQ1Query1sqI-ZQ.(zqy曰se1ectSnamefromStudentwherenotexists(se1ectKfromCoursewherenotexists(se1ect*fromSCwhereSno=Student.sno1andcno=Co)<四F果也消息IISname(8)(选作)查询至少选修了学生“20110002”选修的全部课程的学生的学号，姓名。se1ectSnozSnamefromStudentwhereSnoin(se1ectdistinctSnofromSCasSC1wherenotexists(se1ect*fromSCasSC2whereSC2.Sno=,20110002,andnotexists(se1ect*fromSCasSC3whereSC3.Sno=SC1.SnoandSCo=SCo)ZQY-PC.Studentjnfo-dbo.SCSQ1Query1sqI-ZQ.(zqy-PCzqse1ectSno,SnamefromStudentwhereSnoin(se1ectdistinctSnofromSCasSC1wherenotexists(se1ect*fromSCasSC2whereSC2.Sno=20110002,andnotexists(se1ect*fromSCasSC3whereSC3.Sno=SC1.SnoandSCo=SCo)1JJ国结果®消F1SnoISname120110001:张虹21而而应一林红(9)检索学生的学号、姓名、学习课程名及课程成绩。se1ects.SnozSname,CnamezGradefromStudents,Coursec,SCwheres.Sno=sc.Snoandsc.Cno=c.CnoZQY-PC-StudentJnfo-dbo.SCSQ1Query1sqI-ZQ.Qse1ects.SnorSname,Cname,GradeIfromStudents,Coursec,SC1wheres.Sno=sc.Snoandsc.Cno=c.CnoSnoSnameCname|Grade|120110001*题高数89220110001张虹C语言程序设计78320110001张虹JAVA语言程序设计89420110002林红C语言程序设计60520110103赵青高数80(10)检索选修了“高数”课且成绩至少高于选修课程号为“002”课程的学生的学号、课程号、成绩，并按成绩从高到低次序排列。由题意得，选修了高数课的学生的成绩要高于选修002课号课程的学生的成绩se1ectdistinctSno,CnozGradefromSCwhereCnoin(se1ectCnofromCoursewhereCname='高数')andGrade>(se1ectMAX(Grade)fromSCwherecno=,002,)orderbyGradedescZQY-PC-Studentjnfo-dbo.SC1SQ1Query1sqI-ZQ(NqyBse1ectdistinctSnorCnorGradefromSCwhereCnoin(se1ectCnofromCoursewhereCname=，高数，)andGrade>(se1ectMAX(Grade)fromSCwherecno=002,)1orderbyGradedesc(H)检索选修3门以上课程的学生的学号、总成绩(不统计不及格的课程)，并要求按总成绩的降序排列出来。se1ectSnozsum(grade)as总成绩fromSCwhereSnoin(se1ectSnofromSCgroupbySnohavingcount(*)>3)andGrade>=60groupbySnoorderby总成绩desc/ZQYgCude1indbo>SQgury1Jse1ectSno,sum(grade)as总成绩fromSCwhereSnoin(se1ectSnofromSCgroupbySnohavingcount(*)>3)andGrade>=60groupbySno1Orderby总成绩desc口结果口消息ISn。I总成绩I(12)检索多于3名学生选修的并以3结尾的课程号的平均成绩。se1ectavg(Grade)as平均成绩fromSCwhereCno1ike'%3,groupbyCnohavingcount(Cno)>3(ZQY-PGStudentiJnfo-dboSjQ1¾
