项目5学生成绩查询

SQLServer2005数据库基础项目五学生成绩查询模块5-1统计查询学习目标工作任务任务1各类职称的教师人数的统计【任务分析】根据任务要求，此查询要用到的数据库为student，用到的表为teacher。最后要显示的信息为两列，一列是职称的名称，一列为该类职称的教师人数。职称列在表中对应的字段为ttitle，而人数是需要统计的信息，这就需要用到聚合函数COUNT了。任务1各类职称的教师人数的统计【程序代码】USEstudentGOSELECTttitleAS职称,COUNT(*)AS人数FROMteacherGROUPBYttitle任务1各类职称的教师人数的统计【执行结果】任务2统计各课程的最高分、最低分和平均成绩【任务分析】根据任务要求，此查询要用到的数据库为student，用到的表为choice。最后要显示的信息为四列，即课程编号、最高分、最低分和平均分。其中最高分、最低分和平均分都不是表中的列，要利用聚合函数MAX、MIN和AVG显示信息。【程序代码】USEstudentGOSELECTcnoAS课程编号,MAX(grade)AS最高分,MIN(grade)AS最低分,AVG(grade)AS平均分FROMchoiceGROUPBYcno任务2统计各课程的最高分、最低分和平均成绩【执行结果】任务2统计各课程的最高分、最低分和平均成绩任务3查询选修了4门以上（包括4门）选修课的学生【任务分析】根据任务要求，此查询要用到的数据库为student，用到的表为choice。最后要显示学生学号和课程门数，这里要筛选出的学生是选修了四门课程及以上的学生学号。要完成这个任务，首先可以统计出每个学生选修的课程门数，然后将选修课程门数大于等于4的学生筛选出来。这样就要用到GROUPBY和HAVING子句，HAVING子句可以对分类汇总的结果进行筛选【任务代码】USEstudentGOSELECTsnoAS学号,COUNT(*)AS课程门数FROMchoiceGROUPBYsnoHAVINGCOUNT(*)=4任务3查询选修了4门以上（包括4门）选修课的学生【执行结果】任务3查询选修了4门以上（包括4门）选修课的学生知识说明聚合函数聚合函数属于系统内置函数之一，它与前面介绍的数学函数、字符串函数等内置函数不同，它能够对一组值执行计算并返回单一的值。聚合函数经常与SELECT语句的GROUPBY子句一同使用。除COUNT函数之外，聚合函数一般忽略空值。知识说明聚合函数的种类表5-1常用聚合函数的功能聚合函数功能AVG返回组中值的平均值COUNT返回组中项目的数量MAX返回表达式的最大值MIN返回表达式的最小值SUM返回表达式中所有值的和STDEV返回表达式中所有值的统计标准偏差VAR返回表达式中所有值的统计标准方差知识说明GROUPBY子句GROUPBY语句从英文的字面意义上理解就是“根据(BY)一定的规则进行分组(GROUP)”。它的作用是通过一定的规则将一个数据集划分成若干个小的区域，然后针对若干个小区域进行数据处理。HAVING子句用于在包含GROUPBY子句的SELECT语句中指定显示哪些分组记录。在GROUPBY对记录进行组合之后，将显示满足HAVING子句条件的GROUPBY子句进行分组的任何记录。模块5-2多表查询——连接查询学习目标工作任务任务1查询孙晓龙的所有选修课的成绩【任务分析】此查询要用到的数据库为student，用到的表为student和choice。最后要显示的信息为两列，一列是课程编号，一列为课程成绩。虽然这两列在choice表中都有，但是本例中要显示的是名叫孙晓龙的学生的选课信息。解决这个问题可以将student和choice两表进行内连接操作，然后再筛选出满足条件的记录，即姓名为孙晓龙的学生的选课信息。【程序代码】USEstudentGOSELECTcno,gradeFROMstudentINNERJOINchoiceONstudent.sno=choice.snoWHEREsname='孙晓龙'任务1查询孙晓龙的所有选修课的成绩【执行结果】任务1查询孙晓龙的所有选修课的成绩任务2查询选修了课程编号为“0101001”的学生的姓名和成绩【任务分析】根据任务要求，此查询要用到的数据库为student，用到的表为student和choice。最后要显示的信息为两列，一列是学生姓名，一列为课程成绩。解决这个问题可以将student和choice两表进行内连接操作，然后再筛选出满足条件的记录，即课程编号为0101001的课程成绩。【程序代码】USEstudentGOSELECTsnameAS姓名,gradeAS成绩FROMstudentINNERJOINchoiceONstudent.sno=choice.snoWHEREcno='0101001'任务2查询选修了课程编号为“0101001”的学生的姓名和成绩【执行结果】任务2查询选修了课程编号为“0101001”的学生的姓名和成绩任务3查询选修了“Illustrator平面设计”课程的学生的姓名和课程成绩，并按成绩降序排列【任务分析】根据任务要求，此查询要用到的数据库为student，用到的表为student、choice和course。最后要显示的信息为两列，一列是学生姓名，一列为课程成绩。学生姓名在student表中，而课程成绩则在choice表中。解决这个问题可以将student、choice和course三表进行内连接操作，然后再筛选出满足条件的记录，即课程名称为“Illustrator平面设计”的课程成绩。【任务代码】USEstudentGOSELECTA.snameAS姓名,B.gradeAS成绩FROMstudentASAJOINchoiceASBONA.sno=B.snoJOINcourseASCONB.cno=C.cnoWHEREC.cname='Illustrator平面设计'ORDERBYB.gradeDESC任务3查询选修了“Illustrator平面设计”课程的学生的姓名和课程成绩，并按成绩降序排列【执行结果】任务3查询选修了“Illustrator平面设计”课程的学生的姓名和课程成绩，并按成绩降序排列知识说明连接查询连接查询是关系型数据库中重要的查询类型之一，通过表间的相关字段，可以追踪各个表之间的逻辑关系，从而实现跨表间的查询。知识说明连接查询的种类表5-2JOIN关键字的含义连接类型连接符号备注左外连接LEFTJOIN外连接右外连接RIGHTJOIN全外连接FULLJOIN交叉连接CROSSJOIN交叉连接内连接INNERJOININNER可省略知识说明内连接的种类等值连接在连接条件中使用等号(=)运算符比较被连接列的列值，其查询结果中列出被连接表中的任何列，包括其中的重复列。自然连接自然连接是等值连接的一种特殊情况，即在连接条件中使用等于(=)运算符比较被连接列的列值，但它使用选择列表指出查询结果集合中所包括的列，并删除连接表中的重复列。不等值连接在连接条件使用除等于运算符以外的其他比较运算符比较被连接的列的列值。这些运算符包括、=、=、、!、!和。模块5-3多表查询——子查询学习目标工作任务任务1查询和“孙晓龙”同班的学生信息【任务分析】根据任务要求，此查询要用到的数据库为student，用到的表为student。最后要显示的信息为与孙晓龙同班的学生的所有信息。解决这个问题可以分两步走，首先查出孙晓龙所在的班级编码，然后再以班级编码作为查询条件找出该班所有学生的信息。子查询的本质就是嵌套查询，内层查询的结果作为外层查询的条件来进行查询。【程序代码】USEstudentGOSELECT*FROMstudentWHEREclassno=(SELECTclassnoFROMstudentWHEREsname='孙晓龙')任务1查询和“孙晓龙”同班的学生信息【执行结果】任务1查询和“孙晓龙”同班的学生信息任务2查询比“07010111”班学生入学成绩都高的其他班的学生的学号和姓名【任务分析】根据任务要求，此查询要用到的数据库为student，用到的表为student。最后要显示的信息为三列，分别是学生学号、姓名和班级编码。解决这个问题可以利用带ALL的子查询，首先从student表中找出“07010111”班的所有学生的入学成绩，然后再将此作为父查询的条件，从表student找出比该班所有学生的入学成绩均高且不在该班的学生信息。【程序代码】USEstudentGOSELECTsno,sname,classnoFROMstudentWHEREsscoreALL(SELECTsscoreFROMstudentWHEREclassno='07010111')ANDclassno'07010111'任务2查询比“07010111”班学生入学成绩都高的其他班的学生的学号和姓名【执行结果】任务2查询比“07010111”班学生入学成绩都高的其他班的学生的学号和姓名任务3查询选修课考试不及格的学生的学号和姓名【任务分析】根据任务要求，此查询要用到的数据库为student，用到的表为student和choice。最后要显示的信息为两列，一列是学生学号，一列为学生姓名。解决这个问题可以利用子查询，首先从choice表中找出考试不及格的学生的学号，然后再将此作为父查询的条件，从表student找出学生的姓名。任务4查询选修了课程编号为“0101001”的学生的学号和姓名【任务代码】USEstudentGOSELECTsno,snameFROMstudentWHEREEXISTS(SELECT*FROMchoiceWHEREstudent.sno=choice.snoANDcno='0101001')【执行结果】任务4查询选修了课程编号为“0101001”的学生的学号和姓名知识说明子查询子查询也称为内部查询，而包含子查询的语句也称为外部查询或父查询。子查询是一个SELECT语句，它嵌套在一个SELECT语句、SELECT...INTO语句、INSERT...INTO语句、DELETE语句、或UPDATE语句或嵌套在另一子查询中。子查询的SELECT查询总是使用圆括号括起来。它不能包含COMPUTE或者FORBROWSE子句，如果同时指定了TOP子句，则只能包含ORDERBY子句。知识说明子查询的种类带有比较运算符的子查询带有比较运算符的子查询，子查询的结果是一个单一的值。常用的比较运算符包括、=、=、、!、!和等。ANY或ALL子查询如果子查询返回的值不是单一的值而是一个结果集，则可以使用带有ANY或ALL的子查询，但是运用该类查询时必须同时使用比较运算符。ANY的含义表示父查询与子查询结果中的某个值进行比较运算，而ALL的含义表示父查询与子查询结果中的所有值进行比较运算。