数据库sql高级查询教务_答案

设教学数据库Education有三个关系：学生关系S（SNO，SNAME，AGE，SEX，SDEPT）；学习关系SC（SNO，CNO，GRADE）；课程关系C（CNO，CNAME，CDEPT，TNAME）查询问题：检索计算机系的全体学生的学号，姓名和性别；检索学习课程号为C2的学生学号与姓名；检索选修课程名为“DS”的学生学号与姓名；（4）检索选修课程号为C2或C4的学生学号；（5）检索至少选修课程号为C2和C4的学生学号；（6）检索不学C2课的学生姓名和年龄；（7）检索学习全部课程的学生姓名；（8）查询所学课程包含学生S3所学课程的学生学号。（1）检索计算机系的全体学生的学号，姓名和性别；SELECTSno，Sname，SexFROMSWHERESdept=’CS’；（2）检索学习课程号为C2的学生学号与姓名；（3）检索选修课程名为“DS”的学生学号与姓名本查询涉及到学号、姓名和课程名三个属性，分别存放在S和C表中，但S和C表没有直接联系，必须通过SC表建立它们二者的联系。C→SC→S基本思路：（1）首先在C表中找出“DS”课程的课程号Cno；（2）然后在SC表中找出Cno等于第一步给出的Cno集合中的某个元素Cno；（3）最后在S关系中选出Sno等于第二步中Sno集合中某个元素的元组，取出Sno和Sname送入结果表列。SELECTSno，SnameFROMSWHERESnoIN（SELECTSnoFROMSCWHERECnoIN（SELECTCnoFROMCWHERECname=‘DS’））；检索选修课程号为C2或C4的学生学号；2.SELECTS.Sno，SnameFROMS，SCWHERES.Sno=SC.SnoANDSC.Cno=‘C2’;1.SELECTSno，SnameFROMSWHERESnoIN（SELECTSnoFROMSCWHERECno=‘C2’)SELECTSnoFROMSCWHERECno=‘C2’ORCno=‘C4’；检索至少选修课程号为C2和C4的学生学号；SELECTSnoFROMSCX，SCYWHEREX.Sno=Y.SnoANDX.Cno=‘C2’ANDY.Cno=‘C4’；检索不学C2课的学生姓名和年龄；检索学习全部课程的学生姓名；在表S中找学生，要求这个学生学了全部课程。换言之，在S表中找学生，在C中不存在一门课程，这个学生没有学。SELECTSnameFROMSWHERENOTEXISTS（SELECT*FROMCWHERENOTEXISTS（SELECT*FROMSCWHERESC.Sno=S.SnoANDSC.Cno=C.Cno）)；查询所学课程包含学生S3所学课程的学生学号。分析：不存在这样的课程Y，学生S3选了Y，而其他学生没有选。SELECTDISTINCTSnoFROMSCASXWHERENOTEXISTS（SELECT*FROMSCASYWHEREY.Sno=‘S3’ANDNOTEXISTS（SELECT*FROMSCASZWHEREZ.Sno=X.SnoANDZ.Cno=Y.Cno）)；1.SELECTSnameFROMSWHERESnoNOTIN（SELECTSnoFROMSCWHERECno=‘C2’)；2.SELECTSnameFROMSWHERENOTEXISTS（SELECT*FROMSCWHERESC.Sno=S.SnoANDCno=‘C2’)；设教学数据库Education有三个关系：学生关系S（SNO，SNAME，AGE，SEX，SDEPT）；学习关系SC（SNO，CNO，GRADE）；课程关系C（CNO，CNAME，CDEPT，TNAME）查询问题：1：查所有年龄在20岁以下的学生姓名及年龄。2：查考试成绩有不及格的学生的学号3：查所年龄在20至23岁之间的学生姓名、系别及年龄。4：查计算机系、数学系、信息系的学生姓名、性别。5：查既不是计算机系、数学系、又不是信息系的学生姓名、性别6：查所有姓“刘”的学生的姓名、学号和性别。7：查姓“上官”且全名为3个汉字的学生姓名。8：查所有不姓“张”的学生的姓名。9：查DB_Design课程的课程号。10：查缺考的学生的学号和课程号。11：查年龄为空值的学生的学号和姓名。12：查计算机系20岁以下的学生的学号和姓名。13：查计算机系、数学系、信息系的学生姓名、性别。14：查询选修了C3课程的学生的学号和成绩，其结果按分数的降序排列。15：查询全体学生的情况，查询结果按所在系升序排列，对同一系中的学生按年龄降序排列。16：查询学生总人数。17：查询选修了课程的学生人数。18：计算选修了C1课程的学生平均成绩。19：查询学习C3课程的学生最高分数。20：查询各个课程号与相应的选课人数。21：查询计算机系选修了3门以上课程的学生的学号。22：求基本表S中男同学的每一年龄组（超过50人）有多少人？要求查询结果按人数升序排列，人数相同按年龄降序排列。23：查询每个学生及其选修课程的情况。24：查询选修了C2课程且成绩在90分以上的所有学生。25：查询每个学生选修的课程名及其成绩。26：统计每一年龄选修课程的学生人数。27：查询选修了C2课程的学生姓名。28：查询与“张三”在同一个系学习的学生学号、姓名和系别。29：查询选修课程名为“数据库”的学生学号和姓名。30：查询与“张三”在同一个系学习的学生学号、姓名和系别。31：查询选修课程名为“数据库”的学生学号和姓名。32：查询选修了C2课程的学生姓名。33：查询所有未选修C2课程的学生姓名。34：查询与“张三”在同一个系学习的学生学号、姓名和系别。35：查询选修了全部课程的学生姓名。36：查询所学课程包含学生S3所学课程的学生学号（1）比较例1：查所有年龄在20岁以下的学生姓名及年龄。SELECTSname，SageFROMSWHERESage20;(NOTage=20)例2：查考试成绩有不及格的学生的学号SELECTDISTINCTSnoFROMSCWHEREgrade60；(2)确定范围例3：查所年龄在20至23岁之间的学生姓名、系别及年龄。SELECTSname，Sdept，SageFROMSWHERESageBETWEEN20AND23；（3）确定集合例4：查计算机系、数学系、信息系的学生姓名、性别。SELECTSname，SsexFROMSWHERESdeptIN（’CS’,‘IS’,‘MATH’）；例5：查既不是计算机系、数学系、又不是信息系的学生姓名、性别SELECTSname，SsexFROMSWHERESdeptNOTIN（’CS’,‘IS’,‘MATH’）；（4）字符匹配例6：查所有姓“刘”的学生的姓名、学号和性别。SELECTSname，Sno，SsexFROMSWHERESnameLIKE‘刘%’；例7：查姓“上官”且全名为3个汉字的学生姓名。SELECTSnameFROMSWHERESnameLIKE‘上官__’；例8：查所有不姓“张”的学生的姓名。SELECTSname，Sno，SsexFROMSWHERESnameNOTLIKE‘张%’；例9：查DB_Design课程的课程号。SELECTCnoFROMCWHERECnameLIKE‘DB\_Design’ESCAPE‘\’；(5)涉及空值的查询例10：查缺考的学生的学号和课程号。SELECTSno，CnoFROMSCWHEREGradeISNULL；（不能用=代替）{有成绩的WHEREGradeISNOTNULLL；}例11：查年龄为空值的学生的学号和姓名。SELECTSno，SnameFROMSWHERESageISNULL；（6）多重条件查询例12：查计算机系20岁以下的学生的学号和姓名。SELECTSno，SnameFROMSWHERESdept=‘CS’ANDSage20;例13：查计算机系、数学系、信息系的学生姓名、性别。SELECTSname，SsexFROMSWHERESdept=’CS’ORSdept=‘IS’ORSdept=’MATH’）；3、对查询结果排序例14：查询选修了C3课程的学生的学号和成绩，其结果按分数的降序排列。SELECTSno，GradeFROMSCWHERECno=‘C3’ORDERBYGradeDESC;例15：查询全体学生的情况，查询结果按所在系升序排列，对同一系中的学生按年龄降序排列。SELECT*FROMSORDERBYSdep，SageDESC；4.聚合函数的使用例16：查询学生总人数。SELECTCOUNT（*）FROMS例17：查询选修了课程的学生人数。SELECTCOUNT（DISTINCTSno）FROMSC例18：计算选修了C1课程的学生平均成绩。SELECTAVG（Grade）FROMSCWHERECno=‘C1’；例19：查询学习C3课程的学生最高分数。SELECTMAX（Grade）FROMSCWHERECno=‘C3’；5、对查询结果分组例20：查询各个课程号与相应的选课人数。SELECTCno，COUNT（Sno）FROMSCGROUPBYCno；该SELECT语句对SC表按Cno的取值进行分组，所有具有相同Cno值的元组为一组，然后对每一组作用聚合函数COUNT以求得该组的学生人数。如果分组后还要求按一定的条件对这些组进行筛选，最终只输出满足指定条件组，则可以使用HAVING短语指定筛选条件。例21：查询计算机系选修了3门以上课程的学生的学号。SELECTSnoFROMSCWHERESdept=‘CS’GROUPBYSnoHAVINGCOUNT（*）3；WHERE子句与HAVING短语的根本区别在于作用对象不同。WHERE子句作用于基本表或视图，从中选择满足条件的元组。HAVING短语作用于组，从中选择满足条件的组。例22：求基本表S中男同学的每一年龄组（超过50人）有多少人？要求查询结果按人数升序排列，人数相同按年龄降序排列。SELECTSage，COUNT（Sno）FROMSWHERESsex='M'GROUPBYSageHAVINGCOUNT（*）50ORDERBY2，SageDESC；二、多表查询1、联接查询例23：查询每个学生及其选修课程的情况。SELECTS.Sno,Sname,Sage,Ssex,Sdept,Cno,GradeFROMS,SCWHERES.Sno=SC.Sno；例24：查询选修了C2课程且成绩在90分以上的所有学生。SELECTS.Sno，SnameFROMS，SCWHERES.Sno=SC.SnoANDSC.Cno=‘C2’ANDSC.Grade90;例25：查询每个学生选修的课程名及其成绩。SELECTS.Sno，Sname，Cname，SC.GradeFROMS，SC，CWHERES.Sno=SC.SnoANDSC.Cno=C.Cno例26：统计每一年龄选修课程的学生人数。SELECTSage，COUNT（DISTINCTS.Sno）FROMS，SCWHERES.Sno=SC.SnoGROUPBYS；由于要统计每一个年龄的学生人数，因此要把满足WHERE子句中条件的查询结果按年龄分组，在每一组中的学生年龄相同。此时的SELECT子句应对每一组分开进行操作，在每一组中，年龄只有一个值，统计的人数是这一组中的学生人数。1、嵌套查询(1)带有IN谓词的子查询指父查询与子查询之间用IN进行联接，判断某个属性列值是否在子查询的结果中。例27：查询选修了C2课程的学生姓名。SELECTSnameFROMSWHERESnoIN（SELECTSnoFROMSCWHERECno=‘C2’);例28：查询与“张三”在同一个系学习的学生学号、姓名和系别。分析：（1）确定“张三”所在的系；（2）查找所有在X系学习的学生。SELECTSdeptFROMSWHERESname=‘张三’；SELECTSno，Sname，SdeptFROMSWHERESdept=‘X’把第一步查询嵌入到