查询所授每门课程的平均成绩均在70分以上( 含70分)的教师。
提示:MSSQLServer 评测SQL语句。
表结构:
请在这里写定义表结构的SQL语句。例如:
-- 课程表
CREATE TABLE cou (
cno char(4) NOT NULL,
cname nvarchar(30) NOT NULL,
credit smallint DEFAULT NULL,
ptime char(5) DEFAULT NULL,
teacher nvarchar(10) DEFAULT NULL,
PRIMARY KEY ( cno )
);
-- 学生选课成绩表
CREATE TABLE sc (
sno char(4) NOT NULL, -- 学生学号
cno char(4) NOT NULL, -- 课程号
grade decimal(4,1) DEFAULT NULL,
PRIMARY KEY ( sno , cno ),
CONSTRAINT fk_sc_cno FOREIGN KEY ( cno ) REFERENCES cou ( cno )
)
表样例
请在这里给出上述表结构对应的表样例。例如
cou
表:
sc
表:
输出样例:
请在这里给出输出样例。例如:
这个是sc表的内容
INSERT INTO `sc`(`sno`, `cno`, `grade`) VALUES ('S001', 'C001', 68.6);
INSERT INTO `sc`(`sno`, `cno`, `grade`) VALUES ('S001', 'C002', 95.5);
INSERT INTO `sc`(`sno`, `cno`, `grade`) VALUES ('S001', 'C003', 74.5);
INSERT INTO `sc`(`sno`, `cno`, `grade`) VALUES ('S002', 'C001', 70.0);
INSERT INTO `sc`(`sno`, `cno`, `grade`) VALUES ('S002', 'C002', 86.1);
INSERT INTO `sc`(`sno`, `cno`, `grade`) VALUES ('S003', 'C001', 89.3);
INSERT INTO `sc`(`sno`, `cno`, `grade`) VALUES ('S003', 'C005', 78.8);
INSERT INTO `sc`(`sno`, `cno`, `grade`) VALUES ('S003', 'C006', 67.6);
INSERT INTO `sc`(`sno`, `cno`, `grade`) VALUES ('S004', 'C002', 67.6);
INSERT INTO `sc`(`sno`, `cno`, `grade`) VALUES ('S004', 'C004', 50.2);
INSERT INTO `sc`(`sno`, `cno`, `grade`) VALUES ('S005', 'C002', 80.9);
INSERT INTO `sc`(`sno`, `cno`, `grade`) VALUES ('S010', 'C001', 82.0);
这个是cou表的内容
INSERT INTO `cou`(`cno`, `cname`, `credit`, `ptime`, `teacher`) VALUES ('C001', '高等数学', 8, '1', '孙老师');
INSERT INTO `cou`(`cno`, `cname`, `credit`, `ptime`, `teacher`) VALUES ('C002', 'C语言', 5, '2', '王老师');
INSERT INTO `cou`(`cno`, `cname`, `credit`, `ptime`, `teacher`) VALUES ('C003', '数据结构', 4, '3', '王老师');
INSERT INTO `cou`(`cno`, `cname`, `credit`, `ptime`, `teacher`) VALUES ('C004', '操作系统', 3, '4', '罗老师');
INSERT INTO `cou`(`cno`, `cname`, `credit`, `ptime`, `teacher`) VALUES ('C005', '组成原理', 4, '3', '陈老师');
INSERT INTO `cou`(`cno`, `cname`, `credit`, `ptime`, `teacher`) VALUES ('C006', '数据库原理', 3, '5', '陈老师');
INSERT INTO `cou`(`cno`, `cname`, `credit`, `ptime`, `teacher`) VALUES ('C007', 'JAVA程序设计', 3, '3', '李老师');
select teacher
from cou
left join sc on cou.cno=sc.cno
where teacher not in
(select a.teacher
from
(SELECT cou.cno,sno,cname,credit,grade,teacher
FROM cou
left join sc on cou.cno=sc.cno
GROUP BY cou.cno,sno,cname,credit,grade,teacher) a
GROUP BY cno,teacher
having avg(grade)<70)
and grade is not null
GROUP BY teacher
做法就是先筛选出所有的老师所授课程的平均分,求出所授课程平均分小于70分的老师,然后利用not in 将其排除,最后再用where排除李老师即可(因为李老师成绩为null,所有第一次选取不到李老师,需要后续取排除)
版权声明:本文为wzy992981933原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接和本声明。