数据库-自由编程

现有班级、学生以及成绩三张表：

备注：表名称和字段名称可以参考表格内单词设置

根据表格信息，按要求完成下面SQL语句的编写：

1、使用SQL分别创建班级表、学生表以及成绩表的表结构，表内数据可以一条一条的插入也可以批量插入

2、查询每个班级中每一科的平均成绩，显示数据包括班级名称，课程以及平均分数，并按照班ID升序排列

3、查询所有同学的学生ID，姓名，性别以及总分，并按照成绩从高到低排序

4、查询课程成绩小于75分的学生ID，姓名，班级，课程以及分数

5、将李米米的数学成绩修改为88分

6、计算重点班中每一科的平均成绩，显示数据包括：重点班级ID，班级名称，课程，平均分数，按照降序排列

1、
-- 班级表
CREATE TABLE class(
  c_id TINYINT(3) UNSIGNED ZEROFILL AUTO_INCREMENT KEY COMMENT '班级ID',
  name VARCHAR(50) NOT NULL UNIQUE COMMENT '名称',
  descrip VARCHAR(200) DEFAULT '' COMMENT '备注'
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT class(name,descrip) VALUES('一年级一班','重点班'),
('一年级二班','重点班'),
('二年级一班','重点班'),
('二年级二班','普通班');


-- 学生表
CREATE TABLE student(
  s_id INT UNSIGNED AUTO_INCREMENT KEY COMMENT '学生ID',
  name VARCHAR(50) NOT NULL COMMENT '姓名',
  gender ENUM('男','女') NOT NULL DEFAULT '男' COMMENT '性别',
  class_id TINYINT UNSIGNED NOT NULL COMMENT '班级',
  CONSTRAINT `fk_class_id` FOREIGN KEY(class_id) REFERENCES class(c_id)
)ENGINE=InnoDB AUTO_INCREMENT=1001 DEFAULT CHARSET=utf8;

INSERT student(name,gender,class_id) VALUES ('赵晓明','男','001'),
('王晓红','女','001'),
('张晓晓','女','001'),
('孙琪琪','女','003'),
('李米米','女','004'),
('赵晓刚','男','003'),
('张大宝','男','002'),
('张兰','女','004'),
('孙好','男','001');


-- 成绩表
CREATE TABLE score(
  sc_id INT(3) UNSIGNED ZEROFILL AUTO_INCREMENT KEY COMMENT '成绩ID',
  s_id INT UNSIGNED NOT NULL COMMENT '学生ID',
  course VARCHAR(50) NOT NULL COMMENT '课程',
  mark TINYINT UNSIGNED NOT NULL COMMENT '分数',
  CONSTRAINT `fk_s_id` FOREIGN KEY(s_id) REFERENCES student(s_id)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT score(s_id,course,mark) VALUES ('1001','数学','98'),
('1001','语文','90'),
('1001','英语','97'),
('1002','数学','96'),
('1002','语文','88'),
('1003','语文','88'),
('1002','英语','91'),
('1003','数学','96'),
('1003','英语','86'),
('1004','数学','89'),
('1004','语文','82'),
('1004','英语','83'),
('1005','数学','75'),
('1005','语文','86'),
('1005','英语','77'),
('1006','数学','81'),
('1006','语文','77'),
('1006','英语','60'),
('1007','数学','89'),
('1007','语文','56'),
('1007','英语','70'),
('1008','数学','87'),
('1008','语文','55'),
('1008','英语','66'),
('1009','数学','78'),
('1009','语文','60'),
('1009','英语','52');



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

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

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

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

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