作业 数据库SQL练习题

SQL测验题

BUAA 软件工程大二数据管理技术作业

目录

• SQL测验题
  • schema
  • data
  • SQL

schema

CREATE TABLE student (
    id INT AUTO_INCREMENT,
    name VARCHAR(64),
    sex ENUM("男", "女"),
    age INT,
    PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=UTF8MB4;

CREATE TABLE course (
    id INT AUTO_INCREMENT,
    name VARCHAR(64),
    teacher_name VARCHAR(64),
    PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=UTF8MB4;

CREATE TABLE selection (
    id INT AUTO_INCREMENT,
    cos_id INT,
    stu_id INT,
    grade INT,
    PRIMARY KEY (id),
    FOREIGN KEY (cos_id) REFERENCES course(id)
        ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY (stu_id) REFERENCES student(id)
        ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=UTF8MB4;

data

INSERT INTO student (id, name, sex, age) VALUES
(1, '李想', '男', 23),
(2, '李莉', '女', 19),
(3, '李刚', '男', 20),
(4, '小丽', '女', 21),
(5, '小虹', '女', 19),
(6, '赵刚', '男', 22),
(7, '李鑫', '女', 21),
(8, '李强', '男', 20);

INSERT INTO course (id, name, teacher_name) VALUES
(1, '物理', '刘刚'),
(2, '数学', '刘小堆'),
(3, '英语', '李四'),
(4, '化学', '张小栈'),
(5, '生物', '张三');

INSERT INTO selection (id, cos_id, stu_id, grade) VALUES
(1, 1, 1, 59),
(2, 1, 2, 59),
(3, 1, 3, 80),
(4, 1, 4, 81),
(5, 1, 1, 40),  -- 1号学生重修，没有过
(6, 1, 2, 80),  -- 2号学生重修后通过
(7, 1, 1, 70);  -- 1号学生再次重修后通过

INSERT INTO selection (id, cos_id, stu_id, grade) VALUES
(8, 2, 1, 60),
(9, 2, 2, 90),
(10, 2, 7, 100);

INSERT INTO selection (id, cos_id, stu_id, grade) VALUES
(11, 3, 8, 0),
(12, 3, 8, 85);

SQL

-- 1.检索年龄大于20岁的男生的学号和姓名
SELECT id, name FROM student WHERE age > 20 AND sex = '男';


-- 2.检索选修了姓刘的老师所教授的课程的女学生的姓名
SELECT DISTINCT(student.name)
FROM selection INNER JOIN student ON selection.stu_id = student.id
INNER JOIN course ON selection.cos_id = course.id
WHERE course.teacher_name LIKE '刘%' AND student.sex = '女';


-- 3.检索李想同学不学的课程的课程号和课程名
SELECT course.id, course.name
FROM course LEFT OUTER JOIN (
    -- 先查找到'李想'上的所有课的课程id
    SELECT DISTINCT(selection.cos_id)
    FROM student INNER JOIN selection ON student.id = selection.stu_id
    WHERE student.name = '李想'
) AS tb1 ON course.id = tb1.cos_id
WHERE tb1.cos_id IS NULL;


-- 4.检索至少选修了两门课程的学生的学号，需要去掉重修
SELECT student.id
FROM student INNER JOIN selection ON student.id = selection.stu_id
GROUP BY student.id
HAVING COUNT(DISTINCT(selection.cos_id)) >= 2;  --去掉重修


-- 5.求刘老师所教授课程的每门课的平均成绩
SELECT course.name, AVG(selection.grade)
FROM course INNER JOIN selection ON course.id = selection.cos_id
WHERE course.teacher_name LIKE '刘%'
GROUP BY course.id;


-- 6.假设不存在重修的情况，统计每门课的选修人数(选课人数超过两人的课程才统计)要求显示课程号和人数，查询结果按人数降序排列，若人数相同，按课程号升序排列
SELECT course.id, COUNT(DISTINCT(selection.stu_id)) AS course_amounts
FROM course INNER JOIN selection ON course.id = selection.cos_id
GROUP BY course.id
HAVING course_amounts > 2
ORDER BY course_amounts DESC, course.id;


-- 7.求年龄大于所有女生年龄的男生的姓名和年龄
SELECT name, age FROM student
WHERE sex = '男' AND age > (
    SELECT MAX(age) FROM student WHERE sex = '女'
);


-- 8.假定不存在重修的情况，求选修了所有课程的学生的学号姓名
SELECT student.id, student.name
FROM student INNER JOIN selection ON student.id = selection.stu_id
GROUP BY selection.stu_id
HAVING COUNT(DISTINCT(selection.cos_id)) = (
    SELECT COUNT(DISTINCT(course.id)) FROM course
);


-- 9.统计每门课最高分、最低分、平均分和不及格（小于 60 分）人数占总人数百分比

-- 10.所有女生都选修物理课(已知课程号为001)，将她们的物理选课记录插入选课表
INSERT INTO selection (cos_id, stu_id) VALUES
(001, (SELECT student.id FROM student WHERE sex = '女'));


-- 11.将物理课的成绩都加20分，但不能超过100分
UPDATE selection SET grade = grade + 20
WHERE grade <= 80 AND selection.cos_id = (SELECT id FROM course WHERE name = '物理');


-- 12.将张老师教的学生都从学生表里删除
DELETE FROM selection WHERE selection.cos_id = (SELECT id FROM course WHERE teacher_name = '张老师')


-- 13.查询重修次数在2次以上的学生学号，课程号，重修次数
SELECT stu_id, cos_id, COUNT(*) AS amounts
FROM selection
GROUP BY stu_id, cos_id
HAVING COUNT(*) >= 2;


-- 14.将学生的重修课程成绩都改成60分
-- You can't specify target table 'selection' for update in FROM clause
-- UPDATE selection SET grade = 60
-- WHERE stu_id = tb1.id (
--     SELECT stu_id AS id FROM selection
--     GROUP BY stu_id, cos_id
--     HAVING COUNT(*) > 2
-- ) AS tb1;

UPDATE selection SET grade = 60
WHERE stu_id IN (
    SELECT stu_id FROM selection WHERE selection.stu_id = tb1.stu_id IN (
        SELECT stu_id FROM selection
        GROUP BY stu_id, cos_id
        HAVING COUNT(*) > 2
    ) AS tb1
);


-- 15.查询重修学生人数最多的课程号，课程名，教师姓名
SELECT cos_id, course.name, course.teacher_name, COUNT(cos_id) AS restudy_count FROM (
    SELECT stu_id, cos_id FROM selection
    GROUP BY stu_id, cos_id HAVING COUNT(*) > 1
) AS tb1 INNER JOIN course ON tb1.cos_id = course.id
GROUP BY tb1.cos_id ORDER BY restudy_count DESC;