SQL经典练习题48道之四(26-30)

接上篇SQL经典练习题48道之三(20-25)
26、查询选修某课程的同学人数多于5人的教师姓名。
答：
select tname from teacher where tno in(select tno from course where cno in(select cno from score group by cno having count(*)>5));
27、查询95033班和95031班全体学生的记录。
答：
select * from student where classnum=’95033’ or classnum=’95031’;
28、查询存在有85分以上成绩的课程Cno.
答：
select distinct cno from score where degree>85;
select distinct cno from score group by cno,degree having degree>85;
29、查询出”计算机系”教师所教课程的成绩表。
答：
select * from score where cno in (select cno from course where tno in (select tno from teacher where depart=’计算机系’));
30、查询“计算机系”与“电子工程系“不同职称的教师的Tname和Prof。
答：
select distinct tname,prof from teacher where depart in (‘计算机系’,’电子工程系’) group by prof having count(*)=1;