sql语句作业2-高级查询附答案

1SQL作业2：高级查询一、连接查询1不同表之间的连接查询例1.37查询每个学生及其选修课程的情况。SELECTStudents.*,Reports.*FROMStudents,ReportsWHEREStudents.Sno=Reports.Sno;例1.38查询每个学生的学号(Sno)、姓名(Sname)、选修的课程名(Cname)及成绩(Grade)。SELECTStudents.Sno,Sname,Cname,GradeFROMStudents,Reports,CoursesWHEREStudents.Sno=Reports.SnoANDReports.Cno=Courses.Cno;补例子：查询选修了C01课程且成绩为70分以上的学生学号姓名和成绩。selectStudents.Sno,Sname,Reports.GradefromStudents,ReportswhereStudents.Sno=Reports.SnoandReports.Cno='C01'andGrade702自身连接例1.39查`询每一门课的间接先修课(即先修课的先修课)。SELECTA.Cname,A.Cno,B.Pre_CnoFROMCoursesA,CoursesBWHEREA.Pre_Cno=B.Cno;3外连接例1.40把例1.37中的等值连接改为左连接。SELECTStudents.*,Reports.Cno,REPORTS.GradeFROMStudents,ReportsWHEREStudents.Sno*=Reports.Sno;二、嵌套查询1带谓词IN的嵌套查询例1.41查询选修了编号为“C02”的课程的学生姓名(Sname)和所在系(Sdept)。SELECTStudents.Sno,Sname表1.1关系StudentsSnoSnameSsexSageSdeptS01S02S03S04S05S06王建平刘华范林军李伟黄河长江男女女男男男211918191820自动化自动化计算机数学数学数学表1.2关系CoursesCnoCnamePre_CnoCreditsC01C02C03C04C05C06C07英语数据结构数据库DB_设计C++网络原理操作系统C05C02C03C07C054223333表1.3关系ReportsSnoCnoGradeS01S01S02S02S02S03S03S04C01C03C01C02C03C01C02C0392849094827290752FROMStudentswhereStudents.Snoin(selectSnofromReportsWHERECnoin(selectCnofromCourseswhereCname='数据结构'))例1.42查询与“李伟”在同一个系学习的学生学号(Sno)、姓名(Sname)和系名(Sdept)。SELECTSno,Sname,SdeptFROMStudentsWHERESdeptIN(SELECTSdeptFROMStudentsWHERESname=’李伟’);例1.43查询选修了课程名为“数据结构”的学生学号(Sno)和姓名(Sname)。SELECTSno,SnameFROMStudentsWHERESnoIN(SELECTSnoFROMReportsWHERECnoIN(SELECTCnoFROMCoursesWHERECname='数据结构'));2带有比较运算符的嵌套查询例1.44将例1.42改为带有比较运算符的嵌套查询。SELECTSno,Sname,SdeptFROMStudentsWHERESdept=(SELECTSdeptFROMStudentsWHERESname='李伟');3带谓词ANY或ALL的嵌套查询例1.45查询非自动化系的不超过自动化系所有学生的年龄的学生姓名(Sname)和年龄(Sage)。SELECTSname,SageFROMStudentsWHERESage=ALL(SELECTSageFROMStudentsWHERESdept='自动化')ANDSdept!='自动化';或者用下面的方法SELECTSname,SageFROMStudentsWHERESage=ALL(SELECTSageFROMStudentsWHERESdept='自动化')ANDSdeptNOTIN('自动化')4带谓词EXISTS的嵌套查询例1.46查询所有选修了编号为“C01”课程的学生姓名(Sname)和所在系(Sdept)。SELECTSname,SdeptFROMStudentsWHEREEXISTS(SELECTSno3FROMReportsWHEREStudents.Sno=Reports.SnoANDCno='C01');例1.47将例1.42改为带谓词EXISTS的查询，SELECTSno,Sname,SdeptFromStudentsWhereEXISTS(SELECT*FromStudentsWhereSname='李伟')例1.48查询选修了所有课程的学生姓名(Sname)和所在系。SELECTSname,SdeptFROMStudentsWHEREEXISTS(SELECT*FROMReportsWHEREStudents.Sno=Reports.Sno);三、集合查询例1.49查询计算机科学系的学生或年龄不大于20岁的学生信息。SELECTStudents.*FROMStudentsWHERESdept='计算机'UNIONSELECTStudents.*FROMStudentsWHERESage=20;例1.50查询数学系的学生且年龄不大于20岁的学生的交集，这实际上就是查询数学系中年龄不大于20岁的学生。SELECTStudents.*FROMStudentsWHERESdept='数学'INTERSECTSELECTStudents.*FROMStudentsWHERESage=20;例1.51查询数学系的学生与年龄不大于20岁的学生的差集。SELECTStudents.*FROMStudentsWHERESdept='数学'MINUSSELECTStudents.*FROMStudentsWHERESage=20;