SQL 题
已知关系模式:
S (SNO,SNAME) 学生关系。SNO 为学号，SNAME 为姓名
C (CNO,CNAME,CTEACHER) 课程关系。CNO 为课程号， CNAME 为课程名，CTEACHER 为任课教师
SC(SNO,CNO,SCGRADE) 选课关系，SCGRADE 为成绩。
1>. 列出有二门以上(含两门)不及格课程的学生姓名及其平均成绩
select a.sname ,avg(SCGRADE)
from S a ,SC b
where a.sno =b.sno and SCGRADE < 60
group by a.sname
having count(b.sno) >=2
2>. 列出既学过“1”号课程，又学过“2”号课程的所有学生姓名
select a.sname from
( select a.sname ,a.sno
from S a , SC b
where a.sno =b.sno and b.cno='1') a,
( select a.sname ,a.sno
from S a , SC b
where a.sno =b.sno and b.cno='2' )b
where a.sname=b.sname
3>. 列出“1”号课成绩比“2”号同学该门课成绩高的所有学生的学号
select a.cno ,a.scgrade ,a.sno from
(select scgrade ,cno ,sno from sc where cno ='1') a ,
(select scgrade ,cno ,sno from sc where cno ='2') b
where a.scgrade >b.scgrade
4>. 列出“1”号课成绩比“2”号课成绩高的所有学生的学号及其“1”号课和“2”号课的成绩
select a.scgrade, a.cno
from SC a
where a.sno in (
(select a.sno from
(select scgrade ,cno,sno from sc where cno ='1') a ,
(select scgrade ,cno ,sno from sc where cno ='2') b
where a.sno =b.sno
and a.scgrade >b.scgrade ) )