10-18 Oracle 基础练习

--33.查询成绩比该课程平均成绩低的同学的成绩表。
select degree from score s join (select avg(degree) as d from score a group by cno)a on s.cno=a.cno and s.degree<a.d;

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

--34、查询所有任课教师的Tname和Depart.
select tname,depart from teacher where tno 
in(select tno from course where cno in (select cno from score ));

--35、查询所有未讲课的教师的Tname和Depart. 
select tname,depart from teacher where tno not
in(select tno from course where cno in (select cno from score ));

--36、查询至少有2名男生的班号。
select sclass from student where ssex='男' group by sclass having count(1)>1 ;

--37、查询Student表中不姓“王”的同学记
select * from student where sname not like'王%';

--38、查询Student表中每个学生的姓名和年龄。
select t.sname,t.sbirthday from student t;

--39、查询Student表中最大和最小的Sbirthday日期值。
select min(sbirthday), max(sbirthday) from student t;

--40、以班号和年龄从大到小的顺序查询Student表中的全部记录。
select * from student s order by s.sbirthday,s.sclass desc;

--41、查询“男”教师及其所上的课程。
select c.cname,t.tname from course c join teacher t on c.tno=t.tno where t.tsex='男';

--42、查询最高分同学的Sno、Cno和Degree列。
select * from score where degree=(select max(degree) from score);

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

--44、查询和“李军”同性别并同班的同学Sname.
select sname from student where ssex=(select ssex from student where sname='李军') 
and sclass=(select sclass from student where sname='李军');

--45、查询所有选修“计算机导论”课程的“男”同学的成绩表。
select degree from score s  join course c on c.cno=s.cno
join student t on t.sno=s.sno where c.cname='计算机导论'and t.ssex='男';