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;