oracle查询练习24-28

24、查询选修某课程的同学人数多于5人的教师姓名

select t.tname from teacher t,score s,course c 
where  t.tno=c.tno and c.cno=s.cno and  s.cno =(select cno from score group by cno having count(cno)>5) 
group by t.tname

25、查询95033班和95031班全体学生的记录。

select st.sno,st.sname,st.ssex,st.sbirthday,st.class,s.cno,s.degree,c.cname,c.tno,t.tname
from student st,score s,course c,teacher t 
where st.sno=s.sno and c.cno=s.cno and c.tno=t.tno

26、  查询存在有85分以上成绩的课程Cno.

select distinct(cno) from SCORE t where degree >85

27、查询出“计算机系“教师所教课程的成绩表。

select s.cno,s.degree from score s,course c,teacher t where t.tno=c.tno and c.cno=s.cno
and  t.depart='计算机系'

28、查询“计算机系”与“电子工程系“不同职称的教师的Tname和Prof

select t.tname,t.prof from teacher t 
where tno not in(select a.tno from teacher a,teacher b where a.depart!=b.depart and a.prof=b.prof )
and t.depart in ('计算机系','电子工程系')