MySQL50-10-第36-40题

MySQL50-10-第36-40题

本文中介绍的是第36-40题目，涉及到的知识点都是多表的连接查询，需要指定不同的条件。5个题目分别是：

• 查询任何一门课程成绩在70分以上的姓名、课程名称和分数
• 查询不及格的课程
• 查询课程编号为01且课程成绩大于等于80的学生的学号和姓名
• 每门课程的学生人数
• 查询选修"张三"老师所授课程的学生中，成绩最高的学生信息及其成绩

题目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;

题目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;

题目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;

题目39

题目需求

每门课程的学生人数

分析过程

直接通过Score中的c_id来确定每门课的人数

SQL实现

select
	c_id
	,count(s_id)
from Score
group by c_id;

如果想连接到课程名称：

-- 报错！！！
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

解决的方法是将我们之前的结果作为临时表和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;

题目40

题目需求

查询选修"张三"老师所授课程的学生中，成绩最高的学生信息及其成绩

分析过程

张三老师：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 = '张三';

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 = '张三';

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 = '张三';

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 = '张三');