笔试题一
1:已知关系模式:
S(SNO, SNAME)学生关系,SNO为学号,SNAME为姓名;
C(CNO, CNAME, CTEACHER)课程关系,CNO为课程号,CNAME为课程名,CTEACHER为任课教师SC(SNO, CNO, SCGRADE)选课关系,SCGRADE为成绩
a:书写SQL,找出没有选修过‘李明’老师讲授课程的所有学生姓名:
方法一:
select sname from s where sno in (
select distinct sno from c, sc where c.cno = sc.cno and cteacher != '李明' and sc.sno = s.sno
);
方法二:
select sname from s where not exists(
select * from sc, c where sc.cno = c.cno and c.cteacher = '李明' and sc.sno = s.sno
)
b:书写SQL,列出有两门以上(含两门)不及格课程的学生姓名及其平均成绩:
select s.sname, avg_scgrade(sc.scgrade) from s, sc (
select sno from sc w