实验五 聚合查询

注：此内容仅仅只是记录成长与分享学习，不能保证绝对正确

实验五 聚合查询

实验日期：  2022 年 5 月 6 日  星期  五    实验地点：   宿舍 

一 实验目的

1. 熟练掌握SQL聚合函数的使用。

2. 熟练使用GROUP BY、HAVING、ORDER BY 等对查询结果进行数据分组和排序。

二 实验要求

1. 请大家务必动手完成实验，实验过程中，会随机抽查提问，作为实验成绩重要参考。

2. 请将数据库studb的两个基本文件自行保留，下次实验在此基础上进行。

3. 请在本实验报告指定方框认真填写对应的实验代码或实验结果。

三 实验内容

1. 使用AVG、COUNT、SUM、MIN、MAX等SQL聚合函数，对数据表S、C、SC中的数据进行统计。

2. 使用GROUP BY子语句，对数据表S、C、SC中的数据进行分组统计，结合HAVING子语句对分组进行过滤。

3. 使用ORDER BY子语句，对统计查询结果进行排序。

四 实验步骤

附加前述数据库，按下述要求完成实验：

1. SQL聚合函数的使用

1）查询SC表中的平均成绩（AVG）。

SQL命令为：

select AVG(SCORE) 平均分 from SC;

2）用DISTINCT统计SC中选修了课程的学生人数（COUNT(DISTINCT SNO)）。

SQL命令为：

select COUNT(DISTINCT SNO)人数 from SC;

2. GROUP BY数据分组子语句的使用

1）用GROUP BY查询所有开设的课程号及对应选修了该课程的学生人数。

SQL命令为：

select CNO,count(SNO)选课人数 from SC GROUP BY CNO;

2）查询选修课程超过3门的学生的学号,结合GROUP BY,HAVING予以实现。

SQL命令为：

select SNO from SC GROUP BY SNO HAVING COUNT(*)>3;

3. ORDER BY数据排序子语句的使用

1）查询指定学号（如S1）的同学所选课程的成绩，并按成绩降序排列。

SQL命令为：

select * from SC where SNO='S1' order by SCORE desc;

2）查询课程名称，课程的最高成绩、最低成绩和平均成绩，列名分别取为“课程名称”，“最高成绩”，“最低成绩”，“平均成绩”，查询结果按课程名称升序排列。此处要连接C与SC进行查询。

SQL命令为：

select C.CNAME as 课程名称,MAX(SCORE)最高成绩,MIN(SCORE)最低成绩,AVG(SCORE)平均成绩 from SC,C where C.CNO=SC.CNO group by CNAME order by CNAME;

4. 小综合

查询年龄最小的学生选课信息（要求含SNO、SNAME、CNAME、SCORE五个字段并分别以学号、姓名、课程名、成绩作为查询结果的列名）。

SQL命令为：

select S.SNO as 学号,SNAME as 姓名, CNAME as 课程名,SCORE as 成绩 from SC,C,S where S.SNO=SC.SNO and C.CNO=SC.CNO and S.AGE in (     select MIN(AGE)     from S );

五 实验总结

在3. 2)的那个查询和小综合这两实验的时候我一开始都忘记添加选择条件了，也就是在3.2）中没有写“where C.CNO=SC.CNO”这条语句，导致最高成绩都是98，最低成绩都是74，平均分都是85，一开始我都没仔细看数据是错的，直到小综合也没写“S.SNO=SC.SNO and C.CNO=SC.CNO and”这个限制，查询出来的数据有44条，我才发现数据有问题，回去看3.2）的数据果然也有问题，这才发现做错了，然后我反别都加了“C.CNO=SC.CNO”这个限制，第3.2）是对了，可是小综合还是不对，居然还有11行，明显还是错的，后来想了很久，才发现原来是漏了学号，也就是“S.SNO=SC.SNO”这条语句，加上之后再看，两行数据，再人工验证后，终于确实无误了。 本次实验还算是比较简单的，基本上没有啥大的难点，就是逻辑上理解起来可能不太容易。

                                                                                                                 成绩