MySQL50-10-第36-40题
本文中介绍的是第36-40题目，涉及到的知识点都是多表的连接查询，需要指定不同的条件。5个题目分别是：
- 查询任何一门课程成绩在70分以上的姓名、课程名称和分数
- 查询不及格的课程
- 查询课程编号为01且课程成绩大于等于80的学生的学号和姓名
- 每门课程的学生人数
- 查询选修"张三"老师所授课程的学生中，成绩最高的学生信息及其成绩
image
题目36
题目需求
查询任何一门课程成绩在70分以上的姓名、课程名称和分数
分析过程
课程成绩：Score
课程名称：Course
姓名：Student
SQL实现
select
s.s_name
,c.c_name
,sc.s_score
from Score sc -- 成绩表
join Student s -- 学生信息表
on sc.s_id = s.s_id
join Course c -- 课程表
on sc.c_id = c.c_id
where sc.s_score > 70
group by s.s_name, c.c_name, sc.s_score;
image
题目37
题目需求
查询不及格的课程
分析过程
- 不及格：s_score小于60分，对应Score表的s_score
- 课程：Course表
SQL实现
select
sc.c_id
,c.c_name
,sc.s_score
from Score sc
join Course c
on sc.c_id = c.c_id
where sc.s_score < 60;
image
题目38
题目需求
查询课程编号为01且课程成绩大于等于80的学生的学号和姓名
分析过程
- 课程编号：Score，c_id
- 课程成绩：Score，s_score
- 学号和姓名：Student，s_id，s_name
SQL实现
select
sc.s_id
,s.s_name
,sc.s_score
from Score sc -- 成绩表
join Student s -- 学生信息表
on sc.s_id = s.s_id
join Course c -- 课程表
on sc.c_id = c.c_id
where c.c_id = 01
and sc.s_score >= 80;
image
题目39
题目需求
每门课程的学生人数
分析过程
直接通过Score中的c_id来确定每门课的人数
SQL实现
select
c_id
,count(s_id)
from Score
group by c_id;
image
如果想连接到课程名称：
-- 报错！！！
select
sc.c_id
,c.c_name
,count(sc.s_id)
from Score sc
join Course c
group by sc.c_id;
ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'test.c.c_name' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
image
解决的方法是将我们之前的结果作为临时表和Course表来连接查询：
select
c.c_name 课程名称
,c.c_id 课程编号
,t.num 人数
from Course c
join(select
c_id
,count(s_id) num
from Score
group by c_id)t -- 上面结果的临时表
on c.c_id = t.c_id;
image
题目40
image
题目需求
查询选修"张三"老师所授课程的学生中，成绩最高的学生信息及其成绩
分析过程
张三老师：Teacher
课程：Course
成绩：Score
学生信息：Student
这个题目中涉及到了4个表，我们需要多表连接查询
SQL实现
1、我们先找出张三老师教了哪些课程
select
c.c_id
,c.c_name
from Course c
join Teacher t
on c.t_id = t.t_id
where t.t_name = '张三';
image
2、找出哪些人修了数学
select
sc.s_id
,sc.s_score
from Score sc
left join Course c
on sc.c_id = c.c_id
left join Teacher t
on c.t_id = t.t_id
where t.t_name = '张三';
image
3、通过max函数找出成绩的最高分
select
max(sc.s_score)
from Score sc
left join Course c
on sc.c_id = c.c_id
left join Teacher t
on c.t_id = t.t_id
where t.t_name = '张三';
image
4、连接Student表，找出学生信息
select
s.*
,sc.s_score
,sc.c_id
,c.c_name
from Student s
left join Score sc
on s.s_id = sc.s_id
left join Course c
on sc.c_id = c.c_id
where sc.s_score in (select max(sc.s_score) -- 最大值90分的确定
from Score sc
left join Course c
on sc.c_id = c.c_id
left join Teacher t
on c.t_id = t.t_id
where t.t_name = '张三');
image
网友评论