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;