请查询某一科目分数大于85分的所有学生的名单

这是一道面试题

题目照片--

CREATE DATABASE interview;


USE interview;


--学生表字段分别表示：学生ID，学生所属班级ID，学生名称
CREATE TABLE student (
std_id VARCHAR(30) PRIMARY KEY,
cls_id VARCHAR(30),
std_name VARCHAR(30),
CONSTRAINT cls_id_fk1 FOREIGN KEY(cls_id) REFERENCES class(cls_id)
)


--班级表字段分别表示：班级ID，班级名称
CREATE TABLE class (
cls_id VARCHAR(30) PRIMARY KEY,
cls_name VARCHAR(30)
)


--分数表字段分别表示：分数ID，学生ID，课程ID，
CREATE TABLE score (
s_id VARCHAR(30),
std_id VARCHAR(30),
t_id VARCHAR(30),
s_value INT,
CONSTRAINT std_id_fk1 FOREIGN KEY(std_id) REFERENCES student(std_id),
CONSTRAINT t_id_fk1 FOREIGN KEY(t_id) REFERENCES timetable(t_id)
)


--课程表字段分别表示：课程ID，课程名称
CREATE TABLE timetable (
t_id VARCHAR(30) PRIMARY KEY,
t_name VARCHAR(30)
)


DROP TABLE class;
DROP TABLE timetable;
DROP TABLE score;


INSERT INTO class (cls_id,cls_name) VALUES ('cls001','a');
INSERT INTO class (cls_id,cls_name) VALUES ('cls002','b');
INSERT INTO class (cls_id,cls_name) VALUES ('cls003','c');
INSERT INTO class (cls_id,cls_name) VALUES ('cls004','d');




INSERT INTO timetable (t_id,t_name) VALUES ('t001','语文');
INSERT INTO timetable (t_id,t_name) VALUES ('t002','数学');
INSERT INTO timetable (t_id,t_name) VALUES ('t003','英语');
INSERT INTO timetable (t_id,t_name) VALUES ('t004','化学');


INSERT INTO student (std_id,cls_id,std_name) VALUES ('std001','cls001','张三');
INSERT INTO student (std_id,cls_id,std_name) VALUES ('std002','cls001','李四');
INSERT INTO student (std_id,cls_id,std_name) VALUES ('std003','cls002','王五');
INSERT INTO student (std_id,cls_id,std_name) VALUES ('std004','cls003','小刘');
INSERT INTO student (std_id,cls_id,std_name) VALUES ('std005','cls004','大气');




INSERT INTO score (s_id,std_id,t_id,s_value) VALUES ('s001','std001','t001',23);
INSERT INTO score (s_id,std_id,t_id,s_value) VALUES ('s002','std001','t001',33);
INSERT INTO score (s_id,std_id,t_id,s_value) VALUES ('s003','std001','t002',59);
INSERT INTO score (s_id,std_id,t_id,s_value) VALUES ('s004','std002','t003',66);
INSERT INTO score (s_id,std_id,t_id,s_value) VALUES ('s005','std002','t004',72);
INSERT INTO score (s_id,std_id,t_id,s_value) VALUES ('s006','std003','t001',87);
INSERT INTO score (s_id,std_id,t_id,s_value) VALUES ('s007','std003','t002',99);
INSERT INTO score (s_id,std_id,t_id,s_value) VALUES ('s008','std004','t003',61);
INSERT INTO score (s_id,std_id,t_id,s_value) VALUES ('s009','std005','t004',94);
INSERT INTO score (s_id,std_id,t_id) VALUES ('s010','std005','t004');
INSERT INTO score (s_id,std_id,t_id,s_value) VALUES ('s011','std001','t003',86);
INSERT INTO score (s_id,std_id,t_id,s_value) VALUES ('s012','std002','t003',88);




SELECT * FROM class;
SELECT * FROM timetable;
SELECT * FROM student;
SELECT * FROM score;


----请查询某一科目分数大于85分的所有学生的名单，
----查询列包括 学生姓名，班级名称，大于等于85分的科目数，小于85（包含无成绩的）科目数；


SELECT student.std_name,class.cls_name,
SUM(CASE WHEN score.s_value>=85 THEN 1 ELSE 0 END) AS '大于等于85分的科目数',
SUM(CASE WHEN score.s_value>=85 THEN 0 ELSE 1 END) AS '小于85（包含无成绩的）科目数',timetable.t_name,COUNT(*)
FROM student,class,score,timetable
WHERE score.t_id=timetable.t_id
AND score.std_id=student.std_id
AND student.cls_id=class.cls_id
OR score.s_value=NULL
GROUP BY score.std_id