作业5

创建3个表格
CREATE TABLE S(
s_id VARCHAR(10), #未给出编号规则，按10字符定义
s_name VARCHAR(25), #姓名按25字符定义
s_birth DATE,
s_gend ENUM(‘男’,’女’),
PRIMARY KEY(s_id)
);
CREATE TABLE T(
t_id VARCHAR(10), #未给出编号规则，按10字符定义
t_name VARCHAR(25),
PRIMARY KEY(t_id)
);
CREATE TABLE C(
c_id VARCHAR(10), #未给出编号规则，按10字符定义
c_name VARCHAR(10),
t_id VARCHAR(10),
PRIMARY KEY(c_id),
CONSTRAINT fk_t_id FOREIGN KEY(t_id) REFERENCES T(t_id)
);
CREATE TABLE Score(
s_id VARCHAR(10),
c_id VARCHAR(10),
s_score TINYINT unsigned,
PRIMARY KEY(s_id,c_id), #学生编号、课程编号共同作为主键，同时各自为外键
CONSTRAINT fk_s_id FOREIGN KEY(s_id) REFERENCES S(s_id),
CONSTRAINT fk_c_id FOREIGN KEY(c_id) REFERENCES C(c_id)
);

插入数据
INSERT INTO S VALUES
(‘01’ , ‘赵雷’ , ‘1990-01-01’ , ‘男’),(‘02’ , ‘钱电’ , ‘1990-12-21’ , ‘男’),
(‘03’ , ‘孙风’ , ‘1990-05-20’ , ‘男’),(‘04’ , ‘李云’ , ‘1990-08-06’ , ‘男’),
(‘05’ , ‘周梅’ , ‘1991-12-01’ , ‘女’),(‘06’ , ‘吴兰’ , ‘1992-03-01’ , ‘女’),
(‘07’ , ‘郑竹’ , ‘1989-07-01’ , ‘女’),(‘08’ , ‘王菊’ , ‘1990-01-20’ , ‘女’);
INSERT INTO T VALUES
(‘01’ , ‘张三’),(‘02’ , ‘李四’),(‘03’ , ‘王五’);
INSERT INTO C VALUES
(‘01’ , ‘语文’ , ‘02’),(‘02’ , ‘数学’ , ‘01’),(‘03’ , ‘英语’ , ‘03’);
INSERT INTO Score VALUES
(‘01’ , ‘01’ , 80),(‘01’ , ‘02’ , 90),(‘01’ , ‘03’ , 99),(‘02’ , ‘01’ , 70),
(‘02’ , ‘02’ , 60),(‘02’ , ‘03’ , 80),(‘03’ , ‘01’ , 80),(‘03’ , ‘02’ , 80),
(‘03’ , ‘03’ , 80),(‘04’ , ‘01’ , 50),(‘04’ , ‘02’ , 30),(‘04’ , ‘03’ , 20),
(‘05’ , ‘01’ , 76),(‘05’ , ‘02’ , 87),(‘06’ , ‘01’ , 31),(‘06’ , ‘03’ , 34),
(‘07’ , ‘02’ , 89),(‘07’ , ‘03’ , 98);

1、查询”李”姓老师的数量 5分
SELECT COUNT(t_name) 李姓老师数量 FROM T WHERE t_name LIKE ‘李%’;

2、查询1990年出生的学生名单 5分
SELECT s_name 1990年出生学生 FROM S WHERE YEAR(s_birth)=1990;

3、查询男生、女生人数 5分
SELECT s_gend 性别,COUNT() 数量 FROM S GROUP BY s_gend;

4、查询学生的总成绩、平均成绩并进行排名 25分
先计算出总成绩、平均成绩，并进行排序
SELECT S.s_id 学号, S.s_name 姓名, sum(Score.s_score) 总成绩, avg(Score.s_score) 平均成绩
FROM (S LEFT JOIN Score ON S.s_id=Score.s_id) LEFT JOIN C ON C.c_id=Score.c_id
GROUP BY S.s_id,S.s_name
ORDER BY avg(Score.s_score) DESC;

为方便后续操作，建立视图~
CREATE VIEW v_avg AS
SELECT S.s_id 学号, S.s_name 姓名, sum(Score.s_score) 总成绩, avg(Score.s_score) 平均成绩
FROM (S LEFT JOIN Score ON S.s_id=Score.s_id) LEFT JOIN C ON C.c_id=Score.c_id
GROUP BY S.s_id,S.s_name
ORDER BY avg(Score.s_score) DESC;
在视图的基础上，添加排名
SELECT (SELECT COUNT(平均成绩) FROM v_avg WHERE v.平均成绩<=平均成绩) as 平均分排名, v.
FROM v_avg as v;

5、查询每门课程被选修的学生数 10分
SELECT C.c_id 课程编号, C.c_name 课程名称, count(Score.s_id) 学生数
FROM C LEFT JOIN Score ON C.c_id=Score.c_id #为显示每门课，使用左连接
GROUP BY C.c_id;

#增加一门没有人选的课，进行测试
INSERT INTO C VALUES(‘04’,’法语’,’03’);
SELECT C.c_id 课程编号, C.c_name 课程名称, count(Score.s_id) 学生数
FROM C,Score
WHERE C.c_id=Score.c_id
GROUP BY C.c_id
DELETE FROM C WHERE c_id=’04’;

6、查询学过编号为”01”并且也学过编号为”02”的课程的同学的信息 10分
SELECT *
FROM S
WHERE s_id in
(SELECT s_id FROM Score WHERE c_id=’01’ AND s_id IN (SELECT s_id FROM Score WHERE c_id=’02’));

7、查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩 10分
SELECT S.s_id 学号, S.s_name 姓名, avg(Score.s_score) 平均成绩
FROM S,C,Score
WHERE S.s_id=Score.s_id and C.c_id=Score.c_id
GROUP BY S.s_id,S.s_name
HAVING avg(Score.s_score)>=85;

8、检索至少选修两门课程的学生学号 10 分
SELECT s_id
FROM Score
GROUP BY s_id
HAVING COUNT(c_id)>=2;

#删除部分数据，进行测试
CREATE TABLE tmp AS SELECT FROM Score WHERE c_id=’01’;
DELETE FROM Score WHERE c_id=’01’;
SELECT s_id FROM Score GROUP BY s_id HAVING COUNT(c_id)>=2;
INSERT INTO Score SELECT FROM tmp;
DROP TABLE tmp;

9、统计各科成绩各分数段[100-85],[85-70],[70-60],[0-60]的人数 20分

SELECT S.c_id AS 课程编号
,C.c_name AS 课程名称
,SUM(ifnull([100-85],0)) AS [100-85]人数
,SUM(ifnull([85-70],0)) AS [85-70]人数
,SUM(ifnull([70-60],0)) AS [70-60]人数
,SUM(ifnull([60-0],0)) AS [60-0]人数
FROM
(SELECT c_id
,CASE WHEN s_score Between 85 and 100 THEN 1 END AS [100-85]
,CASE WHEN s_score Between 70 and 85 THEN 1 END AS [85-70]
,CASE WHEN s_score Between 60 and 70 THEN 1 END AS [70-60]
,CASE WHEN s_score Between 0 and 60 THEN 1 END AS [60-0]
FROM Score) AS S JOIN C on S.c_id=C.c_id
GROUP BY S.c_id;
由于区间有重叠，60和70分被分别记录了2次，导致总数比实际成绩数量多2