0
0

Mysql多表练习题30道

根据上一篇文章建立的表，我们来做一些多表练习：

没建立表的可以点击此链接去建立练习用的表：

1.查询“1”号学生的姓名和各科成绩：

进行student表和scores表的id相连接，course表和scores表的id相连接

SELECT
    s.id sid,
    s.`name` sname,
    c.`name` cname,
    sc.score 
FROM
    student s
    LEFT JOIN scores sc ON s.id = sc.s_id
    LEFT JOIN course c ON c.id = sc.c_id 
WHERE
    s.id = 1;

2.查询各个学科的平均成绩和最高成绩：

SELECT
    c.id,
    c.`name`,
    AVG( sc.score ),
    max( sc.score ) 
FROM
    course c
    LEFT JOIN scores sc ON c.id = sc.c_id 
GROUP BY
    c.id,
    c.`name`;

3.查询所有姓张的同学的各科成绩：

SELECT
    s.id,
    s.`name`,
    c.`name` cname,
    sc.score 
FROM
    student s
    LEFT JOIN scores sc ON sc.s_id = s.id
    LEFT JOIN course c ON c.id = sc.c_id 
WHERE
    s.`name` LIKE '张%';

4.查询每个同学的最高成绩和科目名称

SELECT
    t.id,
    t.NAME,
    c.id,
    c.NAME,
    r.score 
FROM
    (
    SELECT
        s.id,
        s.NAME,(
        SELECT
            max( score ) 
        FROM
            scores r 
        WHERE
            r.s_id = s.id 
        ) score 
    FROM
        student s 
    ) t
    LEFT JOIN scores r ON r.s_id = t.id 
    AND r.score = t.score
    LEFT JOIN course c ON r.c_id = c.id;

5.查询每个课程的最高分的学生信息

SELECT
    * 
FROM
    student s 
WHERE
    id IN (
    SELECT DISTINCT
        r.s_id 
    FROM
        (
        SELECT
            c.id,
            c.NAME,
            max( score ) score 
        FROM
            student s
            LEFT JOIN scores r ON r.s_id = s.id
            LEFT JOIN course c ON c.id = r.c_id 
        GROUP BY
            c.id,
            c.NAME 
        ) t
        LEFT JOIN scores r ON r.c_id = t.id 
    AND t.score = r.score 
    );

6.查询名字中含有'张'或'李'字的学生的信息和各科成绩。

SELECT
    s.id,
    s.NAME sname,
    sc.score,
    c.NAME 
FROM
    student s
    LEFT JOIN scores sc ON s.id = sc.s_id
    LEFT JOIN course c ON sc.c_id = c.id 
WHERE
    s.NAME LIKE '%张%' 
    OR s.NAME LIKE '%李%';

7.查询平均成绩大于70的同学的信息。（子查询）

SELECT
    * 
FROM
    student 
WHERE
    id IN (
    SELECT
        sc.s_id 
    FROM
        scores sc 
    GROUP BY
        sc.s_id 
    HAVING
    avg( sc.score ) >= 70 
    );

8.将学生按照总分数进行排名。（从高到低）

SELECT
    s.id,
    s.NAME,
    sum( sc.score ) score 
FROM
    student s
    LEFT JOIN scores sc ON s.id = sc.s_id 
GROUP BY
    s.id,
    s.NAME 
ORDER BY
    score DESC,
    s.id ASC;

9.查询数学成绩的最高分、最低分、平均分。

SELECT
    c.NAME,
    max( sc.score ),
    min( sc.score ),
    avg( sc.score ) 
FROM
    course c
    LEFT JOIN scores sc ON c.id = sc.c_id 
WHERE
    c.NAME = '数学';

10.将各科目按照平均分排序。

SELECT
    c.id,
    c.NAME,
    avg( sc.score ) score 
FROM
    course c
    LEFT JOIN scores sc ON c.id = sc.c_id 
GROUP BY
    c.id,
    c.NAME 
ORDER BY
    score DESC;

11.查询老师的信息和他所带的科目的平均分

SELECT
    t.id,
    t.NAME,
    c.id cid,
    c.NAME cname,
    avg( r.score ) 
FROM
    teacher t
    LEFT JOIN course c ON t.id = c.t_id
    LEFT JOIN scores r ON r.c_id = c.id 
GROUP BY
    t.id,
    t.NAME,
    c.id,
    c.NAME;

12.查询被"Tom"和"Jerry"教的课程的最高分和最低分

SELECT
    t.id,
    t.NAME,
    c.id cid,
    c.NAME cname,
    max( r.score ),
    min( r.score ) 
FROM
    teacher t
    LEFT JOIN course c ON t.id = c.t_id
    LEFT JOIN scores r ON r.c_id = c.id 
GROUP BY
    t.id,
    t.NAME,
    c.id,
    c.NAME 
HAVING
    t.NAME IN ( 'Tom', 'Jerry' );

13.查询每个学生的最好成绩的科目名称（子查询）

SELECT
    t.id,
    t.sname,
    r.c_id,
    c.NAME,
    t.score 
FROM
    (
    SELECT
        s.id,
        s.NAME sname,
        max( r.score ) score 
    FROM
        student s
        LEFT JOIN scores r ON r.s_id = s.id 
    GROUP BY
        s.id,
        s.NAME 
    ) t
    LEFT JOIN scores r ON r.s_id = t.id 
    AND r.score = t.score
    LEFT JOIN course c ON r.c_id = c.id;

14.查询所有学生的课程及分数

SELECT
    s.id,
    s.NAME,
    c.id,
    c.NAME,
    r.score 
FROM
    student s
    LEFT JOIN scores r ON s.id = r.s_id
    LEFT JOIN course c ON c.id = r.c_id;

15.查询课程编号为1且课程成绩在60分以上的学生的学号和姓名（子查询）

SELECT
    s.*,
    r.* 
FROM
    student s
    LEFT JOIN scores r ON s.id = r.s_id 
WHERE
    r.c_id = 1 
    AND r.score > 60

16. 查询平均成绩大于等于70的所有学生学号、姓名和平均成绩

SELECT
    s.id,
    s.NAME,
    t.score 
FROM
    student s
    LEFT JOIN ( SELECT r.s_id, avg( r.score ) score FROM scores r GROUP BY r.s_id ) t ON s.id = t.s_id 
WHERE
    t.score >= 70;

17.查询有不及格课程的学生信息

SELECT
    * 
FROM
    student s 
WHERE
    id IN ( SELECT r.s_id FROM scores r GROUP BY r.s_id HAVING min( r.score ) < 60 );

18.查询每门课程有成绩的学生人数

SELECT
    c.id,
    c.NAME,
    count(*) 
FROM
    course c
    LEFT JOIN scores r ON c.id = r.c_id 
GROUP BY
    c.id,
    c.NAME;

19.查询每门课程的平均成绩，结果按照平均成绩降序排列，如果平均成绩相同，再按照课程编号升序排列

SELECT
    c.id,
    c.NAME,
    avg( score ) score 
FROM
    course c
    LEFT JOIN scores r ON c.id = r.c_id 
GROUP BY
    c.id,
    c.NAME 
ORDER BY
    score DESC,
    c.id ASC;

20.查询平均成绩大于60分的同学的学生编号和学生姓名和平均成绩

SELECT
    s.id,
    s.NAME sname,
    avg( r.score ) score 
FROM
    student s
    LEFT JOIN scores r ON r.s_id = s.id
    LEFT JOIN course c ON c.id = r.c_id 
GROUP BY
    s.id,
    s.NAME 
HAVING
    score > 65;

21.查询有且仅有一门课程成绩在80分以上的学生信息

SELECT
    s.id,
    s.NAME,
    s.gender 
FROM
    student s
    LEFT JOIN scores r ON s.id = r.s_id 
WHERE
    r.score > 80 
GROUP BY
    s.id,
    s.NAME,
    s.gender 
HAVING
    count(*) = 1;

22.查询出只有三门课程的学生的学号和姓名

SELECT
    s.id,
    s.NAME,
    s.gender 
FROM
    student s
    LEFT JOIN scores r ON s.id = r.s_id 
GROUP BY
    s.id,
    s.NAME,
    s.gender 
HAVING
    count(*) = 3;

23.查询有不及格课程的课程信息

SELECT
    * 
FROM
    course c 
WHERE
    id IN (
    SELECT
        r.c_id 
    FROM
        scores r 
    GROUP BY
        r.c_id 
    HAVING
    min( r.score ) < 60 
    );

24.查询至少选择4门课程的学生信息

SELECT
    s.id,
    s.NAME 
FROM
    student s
    LEFT JOIN scores r ON s.id = r.s_id 
GROUP BY
    s.id,
    s.NAME 
HAVING
    count(*) >= 4;

25.查询没有选全所有课程的同学的信息

SELECT
    * 
FROM
    student 
WHERE
    id IN (
    SELECT
        r.s_id 
    FROM
        scores r 
    GROUP BY
        r.s_id 
    HAVING
    count(*) != 5
    );

26.查询选全所有课程的同学的信息

SELECT
    s.id,
    s.NAME,
    count(*) number 
FROM
    student s
    LEFT JOIN scores r ON s.id = r.s_id 
GROUP BY
    s.id,
    s.NAME 
HAVING
    number = ( SELECT count(*) FROM course );

27.查询各学生都选了多少门课

SELECT
    s.id,
    s.NAME,
    count(*) number 
FROM
    student s
    LEFT JOIN scores r ON s.id = r.s_id 
GROUP BY
    s.id,
    s.NAME;

28.查询课程名称为"java"，且分数低于60分的学生姓名和分数

SELECT
    s.id,
    s.NAME,
    r.score 
FROM
    student s
    LEFT JOIN scores r ON s.id = r.s_id
    LEFT JOIN course c ON r.c_id = c.id 
WHERE
    c.NAME = 'java' 
    AND r.score < 60;

29.查询学过"Tony"老师授课的同学的信息

SELECT
    s.id,
    s.NAME 
FROM
    student s
    LEFT JOIN scores r ON r.s_id = s.id
    LEFT JOIN course c ON c.id = r.c_id
    LEFT JOIN teacher t ON t.id = c.t_id 
WHERE
    t.NAME = 'Tom';

30.查询没学过"Tony"老师授课的学生信息

SELECT
    * 
FROM
    student 
WHERE
    id NOT IN (
    SELECT DISTINCT
        s.id 
    FROM
        student s
        LEFT JOIN scores r ON r.s_id = s.id
        LEFT JOIN course c ON c.id = r.c_id
        LEFT JOIN teacher t ON t.id = c.t_id 
    WHERE
    t.NAME = 'Tom' 
    )

标签： 数据库 sql oracle
本文转载自: https://blog.csdn.net/weixin_49627122/article/details/126380916
版权归原作者 激进的土豆 所有， 如有侵权，请联系我们删除。
登录后发布评论

“Mysql多表练习题30道”的评论:

还没有评论
关于作者
...
overfit同步小助手
文章同步
相关阅读

网络安全法-网络运行安全

使用selenium/drissionpage时如何阻止chrome自动跳转http到https

docker desktop 里部署的Open WebUI 管理员密码忘记了的处理方法

在ubuntu20.04中搭建onsite比赛运行环境

利用开源的低代码表单设计器FcDesigner高效管理和渲染复杂表单结构

Kafka学习笔记

【前端】浏览器输入url到页面呈现发生了什么？

文章导航