4实验四-复杂查询

1实验四复杂查询一、实验目的掌握两个表以上的连接查询的应用，包括嵌套查询。二、实验内容（1）查询比“林红”年纪大的男学生信息。select*fromStudentwhereSex='男'andYEAR(Birth)-(selectYEAR(Birth)fromStudentwhereSname='林红')0（2）检索所有学生的选课信息，包括学号、姓名、课号、课程名、成绩。selectSC.Sno,Sname,Sex,Classno,Cname,GradefromStudents,SC,Coursecwheres.Sno=SC.SnoandSC.cno=c.cno2（3）查询已选课学生的学号、姓名、课程名、成绩。selectSC.Sno,Sname,Cname,GradefromStudents,coursec,SCwheres.sno=SC.snoandc.cno=SC.cno（4）查询选修了“C语言程序设计”的学生的学号和姓名。selectsc.Sno,SnamefromStudents,coursec,scwherec.Cname='C语言程序设计'ands.Sno=sc.Snoandsc.Cno=c.Cno3（5）查询与“张虹”在同一个班级的学生学号、姓名、家庭住址。a.用子查询selectSno,Sname,Home_addrfromStudentwhereClassno='051'andSname!='张虹'b.用连接查询selectSno,Sname,Home_addrfromStudentwhereClassno=(selectClassnofromStudentwhereSname='张虹')andSname!='张虹'（6）查询其他班级中比“051”班所有学生年龄大的学生的学号、姓名。4selectSno,SnamefromStudentwhereClassno'051'andBirthall(selectBirthfromStudentwhereClassno='051')（7）(选作)查询选修了全部课程的学生姓名。本题使用除运算的方法。由题意可得另一种语言，没有一个选了课的学生没有选course表里的课程。那么，我们需要两个NOTEXISTS表示双重否定；另一种思路可详见书例4.52selectSnamefromStudentwherenotexists(select*fromCoursewherenotexists(select*fromSCwhereSno=Student.snoandcno=Course.cno))（8）(选作)查询至少选修了学生“20110002”选修的全部课程的学生的学号，姓名。selectSno,SnamefromStudent5whereSnoin(selectdistinctSnofromSCasSC1wherenotexists(select*fromSCasSC2whereSC2.Sno='20110002'andnotexists(select*fromSCasSC3whereSC3.Sno=SC1.SnoandSC3.cno=SC2.cno)))（9）检索学生的学号、姓名、学习课程名及课程成绩。selects.Sno,Sname,Cname,GradefromStudents,Coursec,SCwheres.Sno=sc.Snoandsc.Cno=c.Cno6（10）检索选修了“高数”课且成绩至少高于选修课程号为“002”课程的学生的学号、课程号、成绩，并按成绩从高到低次序排列。由题意得，选修了高数课的学生的成绩要高于选修002课号课程的学生的成绩selectdistinctSno,Cno,GradefromSCwhereCnoin(selectCnofromCoursewhereCname='高数')andGrade(selectMAX(Grade)fromSCwherecno='002')orderbyGradedesc（11）检索选修3门以上课程的学生的学号、总成绩（不统计不及格的课程），并要求按总成绩的降序排列出来。selectSno,sum(grade)as总成绩7fromSCwhereSnoin(selectSnofromSCgroupbySnohavingcount(*)3)andGrade=60groupbySnoorderby总成绩desc（12）检索多于3名学生选修的并以3结尾的课程号的平均成绩。selectavg(Grade)as平均成绩fromSCwhereCnolike'%3'groupbyCnohavingcount(Cno)3（13）检索最高分与最低分之差大于5分的学生的学号、姓名、最高分、最底分。selectdistinctSC.Sno学号,Sname姓名,max(grade)as最高分,min(grade)as最低分fromStudent,SC8whereSC.Sno=Student.SnogroupbySC.Sno,Snamehavingmax(grade)-min(grade)5（14）外连接对实验二中的表6和表7做一个外连接查询，显示每门课程的课号、课名、选修该门课的学号、成绩，没有同学选修的课程（如Visual_Basic）也要在查询结果中。selectc.Cno课号,Cname课名,Sno学号,Grade成绩fromCoursecleftouterjoinSCon(c.Cno=SC.Cno)（15）创建一个表Student_other，结构同Student,输入若干记录，部分记录和Student表中的相同。创建过程：createtableStudent_other(Snochar(8)primarykey,Snamevarchar(8)notnull,Sexchar(2)notnull,Birthsmalldatetimenotnull,9Classnochar(3)notnull,Entrance_datesmalldatetimenotnull,Home_addrvarchar(40),Sdeptchar(2)notnull,Postcodechar(6))随意输入几条Student表中没有的信息，完成创建a.查询同时出现在Student表和Student_other表中的记录select*fromstudent_otherso,Studentswhereso.Sno=s.Sno10b.查询Student表和Student_other表中的全部记录select*fromstudentunionselect*fromstudent_other（16）（选作）创建一个数据库Student_info_other,参数自定。创建过程：新建数据库11名称确定，参数自定义，然后“确定”即可a.当前数据库为Student_info，将Student_info数据库中的Student_other复制到Student_info_other中。select*intoStudent_info_other.dbo.Student_otherfromStudent_info.dbo.Student_otherb.查询同时出现在Student表和Student_info_other数据库Student_other表中的记录。select*fromStudent_info_other.dbo.student_otherso,Student_info.dbo.Studentswhereso.sno=s.sno