SQL 代码实操题

 题一

        有四张表，学生表student有学生id、学生名字、学生年龄；老师表teacher有老师id、老师名字；课程表subject有课程id、课程名字、老师id；成绩表transcript有id、学生id、课程id、成绩，根据要求查询数据。

1、根据一个同学的id，找到其他和学习科目完全一样的同学

-- 查询与指定名字学习相同课程的同学
SELECT s.id, s.name
FROM student s
WHERE s.id != 1 -- 排除指定学生
  AND NOT EXISTS (
    -- 该子查询检查是否存在不同课程的情况
    SELECT 1
    FROM transcript t
    WHERE t.student_id = s.id
      AND t.subject_id NOT IN (
        -- 获取指定学生所学的所有课程
        SELECT subject_id
        FROM transcript
        WHERE student_id = 1))
  AND NOT EXISTS (
    -- 该子查询检查课程数量是否相同
    SELECT 1
    FROM transcript t
    WHERE t.student_id = s.id
-- HAVING 子句是用来过滤分组后的结果的。
-- 通常 HAVING 是和 GROUP BY 一起使用的，虽然在这里没有显式的 GROUP BY，但由于查询本身是针对每个学生的选课记录，因此它也起到了“分组”的作用。
-- HAVING 后面的条件，实际上是比较当前学生（s）的选课数量与学生 1 (student_id = 1) 的选课数量是否相同。
    HAVING COUNT(DISTINCT t.subject_id) != (
        -- 该子查询查询指定学生的课程数量
        SELECT COUNT(DISTINCT subject_id)
        FROM transcript
        WHERE student_id = 1));

2、查询每科成绩的老师id，名字，以及每个科目下每个分段的60分以下，60-70，70-80，80-90，90-100的人数

SELECT *
FROM transcript t,
     student s
WHERE t.student_id = s.id;
SELECT t.id                                 AS 教师id,
       t.name                               AS 教师名称,
       s.name                               AS 科目名称,
       SUM(IF(tr.score < 60, 1, 0))         AS "60分以下",
       SUM(IF(60 <= tr.score < 70, 1, 0))   AS "60-70分",
       SUM(IF(70 <= tr.score < 80, 1, 0))   AS "70-80分",
       SUM(IF(80 <= tr.score < 90, 1, 0))   AS "80-90分",
       SUM(IF(90 <= tr.score <= 100, 1, 0)) AS "90-100分"
FROM teacher t
         JOIN
     subject s ON t.id = s.teacher_id
         JOIN
     transcript tr ON s.id = tr.subject_id
GROUP BY t.id, t.name, s.name
ORDER BY t.id, s.name;

3、删除王平老师相关的所有成绩

DELETE t
FROM transcript t
         JOIN subject s ON t.subject_id = s.id
         JOIN teacher te ON s.teacher_id = te.id
WHERE te.name = '王平';

4、建表语句及数据

-- 创建数据库
CREATE DATABASE study;

-- 学生表
create table student
(
    id   int auto_increment primary key,
    name varchar(20) not null,
    age  tinyint     not null
);
-- 学生表数据
insert into study.student (id, name, age)
values  (1, '张三', 18),
        (2, '李四', 29),
        (3, '王五', 23),
        (4, '赵六', 32),
        (5, '钱七', 15),
        (6, '陈八', 36),
        (7, '田九', 26),
        (8, '张三', 18),
        (9, '李四', 28);

-- 老师表
create table teacher
(
    id   int auto_increment primary key,
    name varchar(20) not null
);
-- 老师表数据
insert into study.teacher (id, name)
values  (1, '王平'),
        (2, '赵方'),
        (3, '李圆'),
        (4, '陈角'),
        (5, '王平');

-- 课程表
create table subject
(
    id         int auto_increment primary key,
    teacher_id int         not null,
    name       varchar(20) not null
);
-- 课程表数据
insert into study.subject (id, teacher_id, name)
values  (1, 1, '语文'),
        (2, 2, '数学'),
        (3, 3, '英语'),
        (4, 4, '体育'),
        (5, 5, '政治');

-- 成绩表
create table transcript
(
    id         int auto_increment primary key,
    subject_id int     not null,
    student_id int     not null,
    score      tinyint not null
);
-- 成绩表数据
insert into study.transcript (id, subject_id, student_id, score)
values  (1, 1, 1, 50),
        (2, 2, 1, 60),
        (3, 3, 1, 70),
        (4, 1, 2, 80),
        (5, 2, 2, 90),
        (6, 3, 2, 100),
        (7, 1, 3, 89),
        (8, 2, 3, 73),
        (10, 1, 4, 24),
        (11, 2, 4, 77),
        (12, 3, 4, 68),
        (13, 4, 4, 78),
        (14, 5, 4, 65),
        (15, 1, 5, 45),
        (16, 2, 5, 56),
        (17, 3, 5, 77),
        (18, 4, 5, 53),
        (19, 5, 5, 74),
        (20, 1, 6, 95),
        (21, 2, 6, 88),
        (22, 3, 6, 65),
        (23, 4, 6, 66),
        (24, 5, 6, 78),
        (25, 1, 7, 76),
        (26, 2, 7, 67),
        (27, 3, 7, 69),
        (28, 4, 7, 78),
        (29, 5, 7, 89),
        (30, 1, 10, 99),
        (31, 1, 1, 60);