数据库建标练习完结篇（32~45）

--32、查询所有“女”教师和“女”同学的name、sex和birthday.

select sname, ssex, sbirthday from student where ssex = '女' 
union select tname, tsex,tbirthday from teacher where tsex = '女';

--33、查询成绩比该课程平均成绩低的同学的成绩表。

select c.cno,c.degree from score c 

where c.degree  < (select avg(s.degree) from score s  where s.cno = c.cno  group by s.cno);


--34、 查询所有任课教师的Tname和Depart.

select  t.tname, t.depart from teacher t, course c where t.tno = c.tno;

--35，查询所有未讲课的教师的Tname和Depart.

select distinct t.tname, t.depart from teacher t, course c where t.tno not in (select tno from course);


--36、查询至少有2名男生的班号。

select * from (select s.sclass, count(s.sclass) c from student s where s.ssex = '男' group by s.sclass) where  c>=2 ;
  
--37、查询Student表中不姓“王”的同学记录。

select * from student s where s.sname not like '王%'; 


--38、查询Student表中每个学生的姓名和年龄。

select s.sname,to_char(sysdate,'yyyy')-to_char(s.sbirthday,'yyyy') from student s;

--39、查询Student表中最大和最小的Sbirthday日期值。

select min(s.sbirthday) 最大生日,max(s.sbirthday) 最小生日 from student s;

--40、以班号和年龄从大到小的顺序查询Student表中的全部记录。

select * from student s order by s.sclass desc ,s.sbirthday;

--41、查询“男”教师及其所上的课程。

select t.tname,c.cname from teacher t,course c where t.tno = c.tno and t.tsex = '男';

--42、查询最高分同学的Sno、Cno和Degree列。

select s.sname,c.cname,r.degree from student s ,course c,score r where r.degree = (select max(degree) from score)
and s.sno = r.sno and c.cno = r.cno;

--43、查询和“李军”同性别的所有同学的Sname.
select t.sname from student t where t.ssex = (select s.ssex from student s where s.sname = '李军');


--44、查询和“李军”同性别并同班的同学Sname.

select t.sname from student t where t.ssex = (select s.ssex from student s where s.sname = '李军') and 
t.sclass = (select s.sclass from student s where s.sname = '李军');

--45、查询所有选修“计算机导论”课程的“男”同学的成绩表。


select t.sname,t.ssex,s.degree from score s,course c,student t where s.cno = c.cno 
and s.sno = t.sno and t.ssex = '男' and c.cname = '计算机导论';