已知关系模式:
S (SNO,SNAME) 学生关系。SNO 为学号,SNAME 为姓名
C (CNO,CNAME,CTEACHER) 课程关系。CNO 为课程号,CNAME 为课程名,CTEACHER 为任课教师
SC(SNO,CNO,SCGRADE) 选课关系。SCGRADE 为成绩
列出有二门以上(含两门)不及格课程的学生姓名及其平均成绩应该怎么写这个sql语句?
S (SNO,SNAME) 学生关系。SNO 为学号,SNAME 为姓名
C (CNO,CNAME,CTEACHER) 课程关系。CNO 为课程号,CNAME 为课程名,CTEACHER 为任课教师
SC(SNO,CNO,SCGRADE) 选课关系。SCGRADE 为成绩
列出有二门以上(含两门)不及格课程的学生姓名及其平均成绩应该怎么写这个sql语句?
from s
where sno in (select sno from sc where scgrade<60 group by sno having count(*)>1)
from s S
where sno in (select sno from sc where scgrade<60 group by sno having count(*)>1)
From (Select SNO,Avg(SCGRADE) As SCGRADE_AVG
From sc
Group By SNO
) As a
Inner Join S As b On b.SNO=a.SNO
Where Exists(Select 1
From SC
Where SNO=b.SNO
And SCGRADE<60
Having Count(*)>=2
)
s , sc , (select sno from sc where scgrade < 60 group by sno having count(*) > 1) t
where s.sno = sc.sno and s.sno = t.sno
group by s.sname