CREATE DATABASE IF NOT EXISTS sql_homework DEFAULT CHARACTER SET UTF8;

USE sql_homework;

-- 1.1 创建班级表
CREATE TABLE IF NOT EXISTS class(
	c_id TINYINT UNSIGNED ZEROFILL AUTO_INCREMENT KEY COMMENT '班级ID',
	name VARCHAR(20) NOT NULL COMMENT '名称',
	descrip VARCHAR(50) NOT NULL COMMENT '备注'
)ENGINE = INNODB CHARSET = UTF8;

-- 1.2 插入班级表内的数据
INSERT class(c_id, name, descrip) VALUES(NULL, '一年级一班', '重点班');
INSERT class(c_id, name, descrip) VALUES(NULL, '一年级二班', '重点班');
INSERT class(c_id, name, descrip) VALUES(NULL, '二年级一班', '重点班');
INSERT class(c_id, name, descrip) VALUES(NULL, '二年级二班', '普通班');

-- 1.3 创建学生表
CREATE TABLE IF NOT EXISTS student(
	s_id INT UNSIGNED AUTO_INCREMENT KEY COMMENT '学生ID',
	name VARCHAR(20) NOT NULL COMMENT '姓名',
	gender ENUM('男', '女', '保密') NOT NULL DEFAULT '保密' COMMENT '性别',
	class TINYINT UNSIGNED ZEROFILL COMMENT '班级'
)ENGINE = INNODB CHARSET = UTF8;

-- 1.4 插入学生表内的数据
INSERT student(s_id, name, gender, class) VALUES
(1001, '赵晓明', '男', 001),
(1002, '王晓红', '女', 001),
(1003, '张晓晓', '女', 001),
(1004, '孙琪琪', '女', 003),
(1005, '李米米', '女', 004),
(1006, '赵晓刚', '男', 003),
(1007, '张大宝', '男', 002),
(1008, '张兰', '女', 004),
(1009, '孙好', '男', 001);

-- 1.5 创建成绩表
CREATE TABLE IF NOT EXISTS score(
	sc_id TINYINT UNSIGNED ZEROFILL AUTO_INCREMENT KEY COMMENT '成绩ID',
	s_id INT UNSIGNED NOT NULL COMMENT '学生ID',
	course VARCHAR(20) NOT NULL COMMENT '课程',
	mark TINYINT UNSIGNED NOT NULL COMMENT '分数'
)ENGINE = INNODB CHARSET = UTF8;

-- 1.6 插入成绩表内的数据
INSERT score(sc_id, s_id, course, mark) VALUES
(DEFAULT, 1001, '数学', 98),
(DEFAULT, 1001, '语文', 90),
(DEFAULT, 1001, '英语', 97),
(DEFAULT, 1002, '数学', 96),
(DEFAULT, 1002, '语文', 88),
(DEFAULT, 1003, '语文', 88),
(DEFAULT, 1002, '英语', 91),
(DEFAULT, 1003, '数学', 96),
(DEFAULT, 1003, '英语', 86),
(DEFAULT, 1004, '数学', 89),
(DEFAULT, 1004, '语文', 82),
(DEFAULT, 1004, '英语', 83),
(DEFAULT, 1005, '数学', 75),
(DEFAULT, 1005, '语文', 86),
(DEFAULT, 1005, '英语', 77),
(DEFAULT, 1006, '数学', 81),
(DEFAULT, 1006, '语文', 77),
(DEFAULT, 1006, '英语', 60),
(DEFAULT, 1007, '数学', 89),
(DEFAULT, 1007, '语文', 56),
(DEFAULT, 1007, '英语', 70),
(DEFAULT, 1008, '数学', 87),
(DEFAULT, 1008, '语文', 55),
(DEFAULT, 1008, '英语', 66),
(DEFAULT, 1009, '数学', 78),
(DEFAULT, 1009, '语文', 60),
(DEFAULT, 1009, '英语', 52);


-- 2. 查询每个班级中每一科的平均成绩，显示数据包括班级名称，课程以及平均分数，并按照班ID升序排列
SELECT c.name AS '班级名称', sc.course AS '课程名称', AVG(sc.mark) AS avg_score FROM score AS sc
INNER JOIN student AS s
ON sc.s_id = s.s_id
INNER JOIN class AS c
ON s.class = c.c_id
GROUP BY s.class, sc.course
ORDER BY c.c_id ASC;


-- 3. 查询所有同学的学生ID，姓名，性别以及总分，并按照成绩从高到低排序
SELECT s.s_id AS '学生ID', s.name AS '学生姓名', s.gender AS '性别', SUM(sc.mark) AS total_score FROM score AS sc
INNER JOIN student as s
ON sc.s_id = s.s_id
GROUP BY sc.s_id
ORDER BY total_score DESC;


-- 4. 查询课程成绩小于75分的学生ID，姓名，班级，课程以及分数
SELECT s.s_id AS '学生ID', s.name AS '学生姓名', c.name AS '班级名称', sc.course AS '课程名称', sc.mark AS '成绩'
FROM score AS sc
INNER JOIN student AS s
ON sc.s_id = s.s_id
INNER JOIN class AS c
ON s.class = c.c_id
WHERE sc.mark < 75;


-- 5. 将李米米的数学成绩修改为88分
UPDATE score SET mark = 88 WHERE course = '数学' AND s_id = (
	SELECT s_id FROM student WHERE name = '李米米'
);


-- 6. 计算重点班中每一科的平均成绩，显示数据包括：重点班级ID，班级名称，课程，平均分数，按照降序排列
SELECT c.c_id AS '重点班级ID', c.name AS '班级名称', sc.course AS '课程名称', AVG(sc.mark) AS avg_score
FROM score AS sc
INNER JOIN student AS s
ON sc.s_id = s.s_id
INNER JOIN class AS c
ON s.class = c.c_id
WHERE c.c_id < 4
GROUP BY c.c_id, sc.course
ORDER BY c.c_id DESC;