--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 = '计算机导论';
