学生表 S（SN0，SNAME） 其中SNO：学号 SNAME：学生姓名
教师表 T（TNO，TNAME，SCNO） 其中TNO：工号 TNAME：教师姓名 SCNO：教授课程
成绩表 SC（SCNO，SCNAME，SNO，SCGRADE） 其中SCNO：课程代号 SCNAME：课程名 SCGRADE：课程成绩

1：找出没有选修李伟老师课程的所有同学的姓名；

2：找出平均成绩最高的同学的姓名及每门功课的成绩；

3：找出至少有两门（含两门）功课不及格的同学的姓名及不及格课程的成绩；

请用SQL语句实现上面的查询！

1: select sname from s where sno in (select sno from sc where scno not in(select scno from t where tname='李伟'))

2: select sname,scname,scgrade from s,sc where s.sno=sc.sno and sc.sno=
(select top 1 sno from sc group by sno order by avg(scgrade) desc)

3: select sname,scname,scgrade from s,sc where s.sno=sc.sno and sc.sno in
(select sno from sc group by sno having sum(case when scgrade<60 then 1 else 0 end)>1)


麻烦各位看看这样写可以吗？如果不妥请写出你的见解！谢谢！

1
select sname from s
where not exists (
select 1 from SC
where SNO=s.SNO
and exists (
select 1 from T
where Tname='李伟'
and scno=sc.scno
)
)

select aname,sn.*
from s,sc
where s.sno=sc.sno
and sno=(
select top 1 SNO
from sc
group by sno
order by avg(SCGRADE) desc
)

我今天测试了一下不可以啊，只能选出一个符合条件的语句！

select sname,scname,scgrade from s,sc where s.sno=sc.sno and sc.sno in (select sno from sc group by sno having avg(scgrade) >=all(select avg(scgrade) from sc group by sno))

上面这个语句可以满足，但我觉得这样太复杂了，执行起来会满，不知道有没有对
select sname,scname,scgrade from s,sc where s.sno=sc.sno and sc.sno=(select top 1 sno from sc group by sno order by avg(scgrade) desc)

进行修改的语句，再次麻烦各位了！
最后谢谢给予回帖的热心朋友 林升 和 LouisXIV！

这样可以吗？
select sname,scname,scgrade from s,sc where s.sno=sc.sno and sc.sno in (select sno from sc group by sno having avg(scgrade)=(select top 1 avg(scgrade) from sc group by sno order by avg(scgrade) desc)

测试是可以的，就是不知道这样的复杂度和
select sname,scname,scgrade from s,sc where s.sno=sc.sno and sc.sno in (select sno from sc group by sno having avg(scgrade) >=all(select avg(scgrade) from sc group by sno))
比那个低点！