MySQL经典50题-第36到40题

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