-- 1、使用SQL分别创建班级表、学生表以及成绩表的表结构，表内数据可以一条一条的插入也可以批量插入
CREATE TABLE class(
  c_id TINYINT UNSIGNED AUTO_INCREMENT KEY COMMENT '编号',
  name VARCHAR(50) NOT NULL COMMENT '班级',
  descrip VARCHAR(50) NOT NULL COMMENT '备注'
);
CREATE TABLE student(
  s_id SMALLINT UNSIGNED AUTO_INCREMENT KEY COMMENT '学生ID',
  name VARCHAR(50) NOT NULL COMMENT '姓名',
  gender VARCHAR(20) NOT NULL COMMENT '性别',
  class TINYINT NOT NULL COMMENT '班级'
);
CREATE TABLE score(
  sc_id TINYINT UNSIGNED AUTO_INCREMENT KEY COMMENT '成绩ID',
  s_id SMALLINT NOT NULL COMMENT '学生ID',
  course VARCHAR(20) NOT NULL COMMENT '课程',
  mark TINYINT NOT NULL COMMENT '分数'
);


INSERT class(c_id,name,descrip) VALUES(001,'一年级一班','重点班'),
 (002,'一年级二班','重点班'),
 (003,'二年级一班','重点班'),
 (004,'二年级二班','普通班');

 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);

INSERT score(sc_id,s_id,course,mark) VALUES(001,1001,'数学',98),
(002,1001,'语文',90),
(003,1001,'英语',97),
(004,1002,'数学',96),
(005,1002,'语文',88),
(006,1003,'语文',88),
(007,1002,'英语',91),
(008,1003,'数学',96),
(009,1003,'英语',86),
(010,1004,'数学',89),
(011,1004,'语文',82),
(012,1004,'英语',83),
(013,1005,'数学',75),
(014,1005,'语文',86),
(015,1005,'英语',77),
(016,1006,'数学',81),
(017,1006,'语文',77),
(018,1006,'英语',60),
(019,1007,'数学',89),
(020,1007,'语文',56),
(021,1007,'英语',70),
(022,1008,'数学',87),
(023,1008,'语文',55),
(024,1008,'英语',66),
(025,1009,'数学',78),
(026,1009,'语文',60),
(027,1009,'英语',52);

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

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

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

-- 5、将李米米的数学成绩修改为88分
UPDATE score 
SET mark=88 WHERE s_id=1005 and course='数学';

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