32、查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列select c.cno ,c.cname,a.avg_score
from course c,(select cno,round(avg(score,2 avg_score from sc group by cno awhere c.cno = a.cnoorder by a.avg_score desc ,c.cno;
33、查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩select st.sno, st.sname, a.avg_score from student st, (select sno, round(avg(score, 2 avg_score from sc group by sno having round(avg(score, 2 >= 85 a where st.sno = a.sno;
34、查询课程名称为\数学\，且分数低于60的学生姓名和分数select st.sname,a.scorefrom student st,(select sno, score from sc where cno = (select cno from course where cname = '数学' and score < 60 awhere st.sno = a.sno
35、查询所有学生的课程及分数情况select st.*, c.cname, sc.cno, sc.score from student st, course c, sc where st.sno = sc.sno and sc.cno = c.cno
36、查询任何一门课程成绩在70分以上的姓名、课程名称和分数；select st.*, c.cname, sc.cno, sc.score from student st, course c, sc, (select sno, min(score from sc group by sno having min(score >= 70 a where st.sno = a.sno and a.sno = sc.sno and sc.cno = c.cno
37、查询不及格的课程 select st.*,c.cname,a.cno,a.scorefrom student st,course c,(select * from sc where score < 60 awhere st.sno = a.snoand c.cno = a.cno
38、查询课程编号为01且课程成绩在80分以上的学生的学号和姓名select st.*,c.cname,a.cno,a.scorefrom student st,course c,(select sno,cno,scorefrom scwhere cno = '01' and score >= 80 awhere st.sno = a.snoand c.cno = a.cno
39、求每门课程的学生人数select a.cno,c.cname,a.cnfrom course c,(select cno,count(sno cn from sc group by cno awhere c.cno = a.cno
40、查询选修\张三\老师所授课程的学生中，成绩最高的学生信息及其成绩select st.*, c.cname, b.cno, b.score
from student st, course c, (select * from sc where score = (select max(score from (select * from sc where cno = (select cno from course where tno = (select tno from teacher where tname = '张三' a and cno = (select cno from course where tno = (select tno from teacher where tname = '张三' b where st.sno = b.sno and c.cno = b.cno
41、查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩select distinct sc1.sno, sc1.cno, sc1.scorefrom sc sc1,sc sc2where sc1.score = sc2.scoreand sc1.cno != sc2.cno
42、查询每门功成绩最好的前两名
select st.sno, st.sname, st.sage, st.ssex, a.cno, a.paim from student st, (select sno, cno, score, dense_rank( over(partition by cno order by score desc paim from sc a where st.sno = a.sno and a.paim <= 2
select st.sno, st.sname, st.sage, st.ssex, a.cno, a.paim from student st, (select sno, cno, score, rank( over(partition by cno order by score desc paim from sc a where st.sno = a.sno and a.paim <= 2
44、检索至少选修两门课程的学生学号select st.sno,st.sname,aa.cnfrom student st,(select sno,count(cno cn from sc group by sno having count(cno >= 2 aawhere st.sno = aa.sno
46、查询各学生的年龄select st.*, (to_char(sysdate, 'yyyy' - to_char(st.sage, 'yyyy' \年龄\
47、查询本周过生日的学生select st.* from student st where to_char(st.sage, 'mmdd' between to_char(trunc(sysdate, 'iw', 'mmdd' and to_char(trunc(sysdate, 'iw' + 6, 'mmdd'
48、查询下周过生日的学生select st.* from student st where to_char(st.sage, 'mmdd' between to_char(trunc(sysdate, 'iw'+7, 'mmdd' and to_char(trunc(sysdate, 'iw' + 13, 'mmdd'
select to_char(trunc(sysdate, 'iw'+7, 'mmdd' from dual; select to_char(trunc(sysdate, 'iw'+ 13, 'mmdd' from dual;
49、查询本月过生日的学生select st.* from student st where to_char(st.sage, 'mm' = to_char(sysdate,'mm'
50、查询下月过生日的学生select st.*from student st where to_char(st.sage, 'mm' = to_char(add_months(trunc(sysdate,1,'mm'