一个面试题 oracle:学生表s（sno、sname)，课程表c（cno、cname,cteacher）选课表sc（sno、cno、score）

Question

找出没有选修“张三”老师课程的所有学生姓名；
列出有两门以上（含两门）不及格课程的学生姓名及其平均成绩。

Answer 1

--1
select * from s where s.sno not in(select sno from c,sc where c.cno=sc.cno and c.cteacher='张三')

--2
select (select sname from s where s.sno=main.sno),avg(sc.score)
from s main,sc
where main.sno=sc.sno
and main.sno in(select sno from s s1,sc sc1 where s1.sno=sc1.sno and sc1.score<60 group by s1.sno having count(sno)>=2)追问

第二个语句出错了 ，没得到

追答

select (select sname from s where s.sno=main.sno),avg(sc.score)
from s main,sc
where main.sno=sc.sno
and main.sno in(select s1.sno from s s1,sc sc1 where s1.sno=sc1.sno and sc1.score=2)
group by main.sno

少复制了最后一行 不好意思

本回答被提问者采纳