Question

高分求解一道SQL题目 高手赐教 急啊 急啊

已知关系模式：
S (SNO,SNAME） 学生关系。SNO 为学号，SNAME 为姓名
C (CNO,CNAME,CTEACHER) 课程关系。CNO 为课程号，CNAME 为课程名，CTEACHER 为任课教师
SC(SNO,CNO,SCGRADE) 选课关系。SCGRADE 为成绩
要求实现如下5个处理：
1． 找出没有选修过“李明”老师讲授课程的所有学生姓名
2． 列出有二门以上（含两门）不及格课程的学生姓名及其平均成绩
3． 列出既学过“1”号课程，又学过“2”号课程的所有学生姓名
4． 列出“1”号课成绩比“2”号同学该门课成绩高的所有学生的学号
5． 列出“1”号课成绩比“2”号课成绩高的所有学生的学号及其“1”号课和“2”号课的成绩

Answer 1

(1):
select sname
from S
where sno not in(select SNO
from C inner join SC
on C.CNO =SC.CNO
where C.cteacher='李明')

(2):
select sname = (select sname from S where S.SNO = SC.sno), 平均成绩 = avg(SCGRADE)
from SC
where SCGRADE < 60
group by SNO,CNO
having count(*) > 1

(3):
select SNAME
from SC left join S
on SC.SNO = S.SNO
where CNO = '1' or CNO = '2'
group by SNO
having count(*) = 2

(4):
select SNO
from SC
where CNO = '1' and SCGRADE > (select SCGRADE from SC where CNO = '1' and CNO = '2')

(5):
select a.SNO,a.SCGRADE as [1号成绩],b.SCGRADE as [2号成绩]
from (select *
from SC
where CNO = '1'
) a full join
(select *
from SC
where CNO = '2'
) b on a.SNO = b.SNO and a.CNO = b.CNO
where a.SNO is not null and b.SNO is not null and a.SCGRADE > b.SCGRADE

--问题5没有说明包不包含1,2两课只修1课的情况,上面的语句假设不包含这种情况

Answer 2

1. select sname from s where sno = (select sc.sno from sc where sc.cno=(select c.cno from c where cteacher='李明'))
2. select sname,sc.average(scgrade) from s,sc where sno in (select sno from sc ) and 2<=(select count(*) from sc where scgrad<60 group by sno)
3. select sno from s where sno in (select sc.sno from sc where cno='1' or cno='2')

……

Answer 3

1 select sno
from s,c,sc
where not exits
(select sno from s,c,sc
where s.sno=sc.sno and c.cno=sc.cnoand cteacher='李明')

我郁闷啦~