19、 查询选修“3-105”课程的成绩高于“109”号同学成绩的所有同学的记录。
select * from score s where s.cno='3-105' and s.degree>(select degree from score e where e.sno='109' and e.cno='3-105');
20、查询score中选学多门课程的同学中分数为非最高分成绩的记录。
select * from score s where degree not in (select max(degree) from score e having count(e.sno)>1 group by sno );
21、查询成绩高于学号为“109”、课程号为“3-105”的成绩的所有记录。
select * from score s where s.degree>(select degree from score e where e.sno='109'and e.cno='3-105');
22、查询和学号为108的同学同年出生的所有学生的Sno、Sname和Sbirthday列。
select sname,sno,sbirthday from student where to_char(sbirthday,'yyyy')= (select to_char(s.sbirthday,'yyyy') from student s where s.sno='108');
23、查询“张旭“教师任课的学生成绩。
select degree from course s join teacher t on t.tno=s.tno join score e on s.cno=e.cno where t.tname='张旭';
24、查询选修某课程的同学人数多于5人的教师姓名。
select Tname from Teacher where Tno=(select Tno from Course where Cno=(select Cno from Score group by Cno having COUNT(Cno)>=5)) ;
25、查询95033班和95031班全体学生的记录。
select * from student s where s.sclass='95033'or s.sclass='95031' order by s.sclass;
26、 查询存在有85分以上成绩的课程Cno.
select distinct cno from score s where s.degree>85;
27、查询出“计算机系“教师所教课程的成绩表。
select degree from score s join course c on c.cno=s.cno join teacher t on t.tno=c.tno where t.depart='计算机系';
28、查询“计算机系”与“电子工程系“不同职称的教师的Tname和Prof。
select tname,prof from teacher where prof not in (select prof from teacher where depart='电子工程系' and prof in (select prof from teacher where depart='计算机系')) and depart in ('计算机系','电子工程系') ;
29、查询选修编号为“3-105“课程且成绩至少高于选修编号为“3-245”的同学的Cno、Sno和Degree,并按Degree从高到低次序排序。
select * from score where cno='3-105'and degree>(select max(degree) from score where cno='3-245') order by degree desc;
30、查询选修编号为“3-105”且成绩高于选修编号为“3-245”课程的同学的Cno、Sno和Degree.
select * from score where cno='3-105'and degree>(select max(degree) from score where cno='3-245') ;
31、 查询所有教师和同学的name、sex和birthday.
select sname 教师和学生姓名,ssex,sbirthday from student union select tname,tsex,tbirthday from teacher ;
32、查询所有“女”教师和“女”同学的name、sex和birthday.
select sname 女教师，女学生姓名,ssex,sbirthday from student where ssex='女' union select tname,tsex,tbirthday from teacher where tsex='女' ;
33、 查询成绩比该课程平均成绩低的同学的成绩表。
select * from score s where s.degree<(select avg(degree) from score e where e.cno=s.cno group by cno);
34、 查询所有任课教师的Tname和Depart.
select t.depart,t.tname from teacher t where t.tno in(select tno from course c where c.cno in ( select cno from score group by cno));
35 、 查询所有未讲课的教师的Tname和Depart.
select tname,depart from teacher where tno not in (select distinct tno from course where cno in (select distinct cno from score)) ;
36、查询至少有2名男生的班号。
select sclass from student where ssex='男' group by sclass having count(*)>=2 ;
37、查询Student表中不姓“王”的同学记录。
select * from student where sname not like '王%' ;
38、查询Student表中每个学生的姓名和年龄。
select sname,(to_char(sysdate,'yyyy')-to_char(sbirthday,'yyyy') )年龄 from student ;
39、查询Student表中最大和最小的Sbirthday日期值。
select max(sbirthday),min(sbirthday) from student ;
40、以班号和年龄从大到小的顺序查询Student表中的全部记录。
select * from student order by sclass desc,(to_char(sysdate,'yyyy')-to_char(sbirthday,'yyyy') ) desc ;
41、查询“男”教师及其所上的课程。
select cname,tsex from course c join teacher t on t.tno=c.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 in(select ssex from student where sname='李军') ;
44、查询和“李军”同性别并同班的同学Sname.
select sname from student where ssex in(select ssex from student where sname='李军') and sclass in(select sclass from student where sname='李军');
45、查询所有选修“计算机导论”课程的“男”同学的成绩表。
select * from score where sno in(select sno from student where ssex='男') and cno=(select cno from course where cname='计算机导论');
