SQL經典練習題48道之六(36-40)

接上篇 SQL經典練習題48道之五(31-35)
36、查詢所有任課教師的Tname和Depart.
答：
select tname,depart from teacher where tno in (select distinct tno from course where cno in (select cno from score group by cno) );
37、查詢所有未講課的教師的Tname和Depart.
答：
select tname,depart from teacher where tno not in (select distinct tno from course where cno in (select cno from score group by cno) );
38、查詢至少有2名男生的班號。
答：
select classnum from student where ssex=’男’ group by classnum having count(*)>1;
39、查詢Student表中不姓“王”的同學記錄。
答：
select * from student where sname not like ‘王%’;
40、查詢Student表中每個學生的姓名和年齡。
答：
select sname,(year(now())-year(sbirthday))age from student;