MySQL数据库 - 复杂查询（二）

第1关 查询学生平均分

一、本关任务：

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

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区域内进行代码补充。

三、预期输出：

+------+--------+-----------+
| s_id | s_name | avg_score |
+------+--------+-----------+
| 04  | Lucas | 33.33   |
| 06  | Caden | 32.50   |
| 08  | Jacob | 0.00    |
+------+--------+-----------+

四、代码

########## Begin ##########

select s.s_id,s_name,round(avg(s_score),2) avg_score
from student s,score s1 
where s.s_id = s1.s_id 
group by s_id
having avg_score < 60
union
select a.s_id,a.s_name,0 as avg_score from student a
    where a.s_id not in (select distinct s_id from score);

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

第2关 查询修课相同学生信息

一、本关任务：

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

表格同上

二、编程要求

根据提示，在Begin - End区域内进行代码补充。

三、预期输出：

+------+--------+-------+
| s_id | s_name | s_sex |
+------+--------+-------+
| 02  | Riley | 男  |
| 03  | Aria  | 女  |
| 04  | Lucas | 女  |
+------+--------+-------+

四、代码  

########## Begin ##########
select s_id,s_name,s_sex
from student
where s_id in (
    select s_id
    from score
    group by s_id
    having count(*) = (
        select count(*)
        from score
        where s_id = '01'
    )
)
and s_id != '01'
########## End ##########

第3关 查询各科成绩并排序

一、本关任务：

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

表格同上

二、编程要求

根据提示，在Begin - End区域内进行代码补充。

三、代码

########## Begin ##########
select a.s_id,a.c_id,a.s_score,count(b.s_score)+1 rank
from score a
left join score b on 
(a.c_id = b.c_id and a.s_score<b.s_score)
group by a.s_id,a.c_id
order by a.s_id,a.c_id

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

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

一、本关任务：

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

表格同上

二、编程要求

根据提示，在Begin - End区域内进行代码补充。

三、预期输出：

+------+--------+-------+---------+------+--------+
| s_id | s_name | s_sex | s_score | c_id | c_name |
+------+--------+-------+---------+------+--------+
| 01  | Mia   | 女  | 90   | 02   | Math |
+------+--------+-------+---------+------+--------+

 四、代码

#请在此添加实现代码
########## Begin ##########
select s.*,s_score,sc.c_id,c_name
from score sc,student s,course c
where 
sc.s_id = s.s_id and sc.c_id = c.c_id
and t_id = (select t_id from teacher where t_name = '张三')

group by c_id
having s_score = max(s_score)

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

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

一、本关任务：

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

表格同上

二、编程要求

根据提示，在Begin - End区域内进行代码补充。

三、预期输出：

+------+--------+-----------+
| s_id | s_name | avg_score |
+------+--------+-----------+
| 04  | Lucas  | 33    |
| 06  | Caden  | 33    |
+------+--------+-----------+

 四、代码

########## Begin ##########
select s.s_id,s.s_name,round(avg(s_score),0) avg_score
from student s,score sc
where s.s_id = sc.s_id
and s_score < 60

group by sc.s_id
having count(*) >= 2

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

部分代码参考链接：               

版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.youkuaiyun.com/weixin_51970555/article/details/124548702