复杂查询（二）

本文主要针对连表查询和分组进行展开练习

查询学生平均分

任务描述

本关任务：根据提供的表和数据，查询平均成绩小于60分的同学的学生编号（s_id）、学生姓名（s_name）和平均成绩（avg_score），要求平均成绩保留2位小数点。（注意：包括有成绩的和无成绩的，无成绩的 score = 0.00）

student表数据：

s_id s_name s_sex
01 Mia 女
02 Riley 男
03 Aria 女
04 Lucas 女
05 Oliver 男
06 Caden 男
07 Lily 女
08 Jacob 男

course表数据：

c_id c_name t_id
01 Chinese 02
02 Math 01
03 English 03

teacher表数据：

t_id t_name
01 张三
02 李四
03 王五

score表部分数据：

s_id c_id s_score
01 01 80
01 02 90
01 03 99
02 01 70
… … …

相关知识

略

编程要求

请仔细阅读右侧代码，根据方法内的提示，在Begin - End区域内进行代码补充。

测试说明
补充完代码后，点击测评，平台会对你编写的代码进行测试，当你的结果与预期输出一致时，即为通过。

预期输出：

1
2
3
4
5
6
7
+------+--------+-----------+
| s_id | s_name | avg_score |
+------+--------+-----------+
| 04   | Lucas  |     33.33 |
| 06   | Caden  |     32.50 |
| 08   | Jacob  |      0.00 |
+------+--------+-----------+

我的代码

1
2
3
4
5
6
7
8
9
10
11
#请在此添加实现代码
########## Begin ##########
select student.s_id as s_id,
s_name,(case when avg(s_score) is not null 
then round(avg(s_score),2) 
else 0.00 end )as avg_score 
from student left join score on student.s_id=score.s_id 
group by s_id having (avg(s_score)<60 or avg(s_score) is null) 
order by avg_score desc;

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

查询修课相同学生信息

任务描述

本关任务：根据提供的表和数据，查询与s_id=01号同学学习的课程完全相同的其他同学的信息（学号s_id，姓名s_name，性别s_sex）。

student表数据：

s_id s_name s_sex
01 Mia 女
02 Riley 男
03 Aria 女
04 Lucas 女
05 Oliver 男
06 Caden 男
07 Lily 女
08 Jacob 男

course表数据：

c_id c_name t_id
01 Chinese 02
02 Math 01
03 English 03

teacher表数据：

t_id t_name
01 张三
02 李四
03 王五

score表部分数据：

s_id c_id s_score
01 01 80
01 02 90
01 03 99
02 01 70
… … …

相关知识

略

编程要求

请仔细阅读右侧代码，根据方法内的提示，在Begin - End区域内进行代码补充。

测试说明
补充完代码后，点击测评，平台会对你编写的代码进行测试，当你的结果与预期输出一致时，即为通过。

预期输出：

1
2
3
4
5
6
7
+------+--------+-------+
| s_id | s_name | s_sex |
+------+--------+-------+
| 02   | Riley  | 男    |
| 03   | Aria   | 女    |
| 04   | Lucas  | 女    |
+------+--------+-------+

我的代码

1
2
3
4
5
6
7
8
9
10
11

#请在此添加实现代码
########## Begin ##########
select * from student s where s.s_id in 
    (select s_id from score where c_id in 
        (select c_id from score where s_id='01')
        group by s_id having count(*) = (select count(c_id) from score where s_id='01')
    ) and s.s_id!='01' and
(select count(sc.c_id) from score sc where sc.s_id=s.s_id group by sc.s_id)=(select count(c_id) from score where s_id='02');

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

查询各科成绩并排序

任务描述

本关任务：根据提供的表和数据，查询各科成绩，进行排序并显示排名，按学生编号（s_id）、课程编号（c_id）、学生成绩（s_score）和排名（rank）进行输出，具体效果请查看测试集。

student表数据：

s_id s_name s_sex
01 Mia 女
02 Riley 男
03 Aria 女
04 Lucas 女
05 Oliver 男
06 Caden 男
07 Lily 女
08 Jacob 男

course表数据：

c_id c_name t_id
01 Chinese 02
02 Math 01
03 English 03

teacher表数据：

t_id t_name
01 张三
02 李四
03 王五

score表部分数据：

s_id c_id s_score
01 01 80
01 02 90
01 03 99
02 01 70
… … …

相关知识

略

编程要求

请仔细阅读右侧代码，根据方法内的提示，在Begin - End区域内进行代码补充。

测试说明
补充完代码后，点击测评，平台会对你编写的代码进行测试，当你的结果与预期输出一致时，即为通过。

我的代码

1
2
3
4
5
6
7
8
9
#请在此添加实现代码
########## Begin ##########
select a.s_id,a.c_id,a.s_score,
(select count(*) from score b 
where a.s_score < b.s_score and a.c_id=b.c_id)+1 as rank 
from score a 
order by c_id,s_score desc,rank,s_id desc;

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

查询张老师课程成绩最高的学生信息

任务描述

本关任务：根据提供的表和数据，查询选修“张三”老师所授课程的学生中，成绩最高的学生信息（具体输出信息请查看测试说明）及其成绩。

student表数据：

s_id s_name s_sex
01 Mia 女
02 Riley 男
03 Aria 女
04 Lucas 女
05 Oliver 男
06 Caden 男
07 Lily 女
08 Jacob 男

course表数据：

c_id c_name t_id
01 Chinese 02
02 Math 01
03 English 03

teacher表数据：

t_id t_name
01 张三
02 李四
03 王五

score表部分数据：

s_id c_id s_score
01 01 80
01 02 90
01 03 99
02 01 70
… … …

相关知识

略

编程要求

请仔细阅读右侧代码，根据方法内的提示，在Begin - End区域内进行代码补充。

测试说明
补充完代码后，点击测评，平台会对你编写的代码进行测试，当你的结果与预期输出一致时，即为通过。

预期输出：

1
2
3
4
5
+------+--------+-------+---------+------+--------+
| s_id | s_name | s_sex | s_score | c_id | c_name |
+------+--------+-------+---------+------+--------+
| 01   | Mia    | 女    |      90 | 02   | Math   |
+------+--------+-------+---------+------+--------+

我的代码

1
2
3
4
5
6
7
8
9
#请在此添加实现代码
########## Begin ##########
select student.s_id,s_name,s_sex,max(s_score) as s_score,score.c_id,c_name from student
join score on student.s_id = score.s_id
join course on score.c_id = course.c_id
join teacher on course.t_id = teacher.t_id
where teacher.t_name='张三';

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

查询两门课程不及格同学信息

任务描述

本关任务：根据提供的表和数据，查询两门及其以上不及格课程的同学的学号（s_id）、姓名（s_name）及其平均成绩（avg_score），要求计算平均成绩后为整数。

student表数据：

s_id s_name s_sex
01 Mia 女
02 Riley 男
03 Aria 女
04 Lucas 女
05 Oliver 男
06 Caden 男
07 Lily 女
08 Jacob 男

course表数据：

c_id c_name t_id
01 Chinese 02
02 Math 01
03 English 03

teacher表数据：

t_id t_name
01 张三
02 李四
03 王五

score表部分数据：

s_id c_id s_score
01 01 80
01 02 90
01 03 99
02 01 70
… … …

相关知识

略

编程要求

请仔细阅读右侧代码，根据方法内的提示，在Begin - End区域内进行代码补充。

测试说明

补充完代码后，点击测评，平台会对你编写的代码进行测试，当你的结果与预期输出一致时，即为通过。

预期输出：

1
2
3
4
5
6
+------+--------+-----------+
| s_id | s_name | avg_score |
+------+--------+-----------+
| 04   | Lucas  |        33 |
| 06   | Caden  |        33 |
+------+--------+-----------+

我的代码

1
2
3
4
5
6
7
8
#请在此添加实现代码
########## Begin ##########
select a.s_id,s_name,round(avg(s_score),0) as avg_score 
from score a join student on a.s_id=student.s_id 
where (select count(*) from score b where a.s_id = b.s_id and b.s_score < 60 )>=2
group by a.s_id;

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