实验五 聚合查询

第1关：查询统计一

实验完成要求

根据代码区的提示，将查询数据的SQL语句书写在对应的代码区中。 注：请务必将select子句中出现的列名小写，顺序必须和题目要求一致

1、查询‘001’号的学生不及格的课程数，列出不及格课程数（列名为：scnt）

2、查询每个学生不及格的课程数，列出学号和不及格课程数（列名为：scnt）。

3、查询每个系女同学的平均年龄，列出所在系和平均年龄（列名为：sageavg）。

select count(sno) as scnt from SC 
where sno='001'and grade<60
group by sno;

count()是MySQL中用来统计表中记录的一个函数，返回条件的行数

GROUP BY 语句根据一个或多个列对结果集进行分组。

在分组的列上我们可以使用 COUNT, SUM, AVG,等函数。

select sno,count(sno) as scnt from SC
where grade<60
group by sno;

select sdept,avg(sage) as sageavg 
from Student
where ssex='f'
group by sdept;

第2关：查询统计二

实验完成要求

根据代码区的提示，将查询数据的SQL语句书写在对应的代码区中。 注：请务必将select子句中出现的列名小写，顺序必须和题目要求一致

1、查询Niki（姓名）同学的平均分，列出平均分（列名为：savg）

2、查询CS系每个同学的平均分，列出学号和平均分（列名为：savg）。

3、查询学分为2的每门课程的选课人数，列出课程号和选课人数（列名为：scnt）。

select avg(grade) as savg from SC 
where sno in(select sno from Student where sname='Niki');

select sno,avg(grade) as savg from SC 
where sno in (select sno from Student where sdept='CS')
group by sno;

select cno,count(sno) as scnt from SC 
where cno in(select cno from Course where ccredit=2)
group by cno;

第3关：查询统计三

实验完成要求

根据代码区的提示，将查询数据的SQL语句书写在对应的代码区中。

注：请务必将select子句中出现的列名小写，顺序必须和题目要求一致

1、查询平均分最高的学生的姓名。

2、查询不及格人数大于等于2人的课程，列出课程号，课程名，不及格人数(列名为scnt)

select sname 
from Student
 where sno in (select sno from SC group by sno 
 having avg(grade) >= all(select avg(grade) from SC group by sno));

在SELECT语句中使用HAVING子句来指定一组行或聚合的过滤条件。

HAVING子句通常与GROUP BY子句一起使用，以根据指定的条件过滤分组。如果省略GROUP BY子句，则HAVING子句的行为与WHERE子句类似。

请注意，HAVING子句将过滤条件应用于每组分行，而WHERE子句将过滤条件应用于每个单独的行。

select SC.cno,Course.cname,count(sno) as scnt 
from SC,Course 
where SC.cno = Course.cno and grade < 60 group by SC.cno,Course.cname having count(sno) >=2;

第4关：查询统计四

实验完成要求

根据代码区的提示，将查询数据的SQL语句书写在对应的代码区中。 注：请务必将select子句中出现的列名小写，顺序必须和题目要求一致 1、查询E系平均成绩最高的同学的姓名，列出姓名。

select sname
from Student ,SC
where Student.sdept='E' 
and Student.sno=SC.sno
group by sname having avg(grade)>=all(select avg(grade) from SC,Student where Student.sno=SC.sno and sdept='E' group by SC.sno);