头歌实训 MySQL数据库 - 分组选择数据

目录

第1关：对查询结果进行排序

第2关：分组查询

第3关：GROUP BY 与 聚合函数

第4关：使用 HAVING 与 ORDER BY

---

第1关：对查询结果进行排序

任务描述

本关任务：以成绩的降序显示学生成绩表中所有信息。

编程要求

在右侧编辑器Begin-End处补充代码，查询学生成绩表中1班同学的所有信息并以成绩降序的方式显示结果。

我们已经为你提供了学生成绩表：
tb_score表数据：

| stu_id | class_id | name | score |
| :------------: | :------------: | :------------: |
| 1 | 2 | Jack | 81 |
| 2 | 1 | David | 74 |
| 3 | 1 | Mason | 92 |
| 4 | 2 | Ethan | 89 |
| 5 | 1 | Gina | 65 |

USE School;
 
#请在此处添加实现代码
########## Begin ##########
 
########## 查询1班同学的所有信息以成绩降序的方式显示结果 ##########
SELECT*FROM tb_score WHERE class_id = 1 ORDER BY score DESC;
 
########## End ##########

第2关：分组查询

任务描述

本关任务：对班级表中的班级名称进行分组查询。

编程要求

在右侧编辑器Begin-End处补充代码，对班级表中的班级名称进行分组查询。

我们已经为你提供了班级表信息：
tb_class表数据：

stu_id	class_id	name
1	367	Jack
2	366	David
3	366	Mason
4	367	Ethan
5	366	Gina

USE School;
 
#请在此处添加实现代码
########## Begin ##########
 
########## 对班级名称进行分组查询 ##########
 
SELECT*FROM tb_class GROUP BY class_id;
########## End ########## 

第3关：GROUP BY 与 聚合函数

任务描述

本关任务：使用GROUP BY关键字结合聚合函数将数据进行分组。

编程要求

在右侧编辑器补充代码，对年级Id和性别进行分组，分别统计表中2、3、4年级的男生总人数和女生总人数。（注意这里代码中显示的是查询班级中第2名到第5名的学生信息，但实际上要求的是分别统计表中2、3、4年级的男生总人数和女生总人数）

student表数据结构如下：

stuId	gradeId	sex
0201	2	男
0221	2	男
0319	3	女
0508	5	男
0610	6	女
0101	1	男
0224	2	女
0413	4	女

USE School;
 
#请在此处添加实现代码
########## Begin ##########
 
########## 查询班级中第2名到第5名的学生信息 ##########
SELECT gradeId, sex, count(*) 
FROM student where gradeId in (2,3,4)
group by gradeId, sex;
 
########## End ##########

第4关：使用 HAVING 与 ORDER BY

任务描述

本关任务：按照要求编写sql查询语句。

编程要求

根据提示，在右侧编辑器补充代码：

1. 我们要评选三好学生，条件是至少有两门课程在90分以上（包括90分）才能有资格，请列出符合的学生的学号（sno）及其90分以上（包括90分）科目总数；

2. 学校评选先进学生，要求平均成绩大于90分（包括90分）的学生都有资格，并且语文课必须在95分以上（包括95分），请列出有资格的学生的学号（sno）及其科目的平均分。

给定数据表tb_grade格式如下：

sno	pno	score
1	语文	95
1	数学	98
1	英语	90
2	语文	89
2	数学	91
2	英语	92
3	语文	85
3	数学	88
3	英语	96
4	语文	95
4	数学	89
4	英语	88

USE School;

#请在此处添加实现代码
########## Begin ##########

#1.查询表中至少有两门课程在90分及以上（含90分）的学生信息
SELECT sno, count(*) 
FROM tb_grade
WHERE score >= 90
GROUP BY sno
HAVING count(*) >= 2
ORDER BY count(*) DESC
LIMIT 3;


#2.查询表中平均成绩大于等于90分且语文课在95分及以上（含95分）的学生信息
SELECT sno, avg(score) 
FROM tb_grade
GROUP BY sno
HAVING avg(score) >= 90 AND MAX(CASE WHEN pno = '语文' THEN score END) >= 95
ORDER BY avg(score) DESC;

########## End ##########