round((select count(1 from sc where cno = '02' and score >= 80 and score < 90 / (select count(1 from sc where cno = '02' * 100, 2 \优良率\where cno = '02' and score >= 90 / (select count(1 from sc where cno = '02' * 100, 2 \优秀率\score >= 60 / (select count(1 from sc where cno = '03' * 100, 2 \及格率\count(1 from sc where cno = '03' and score >= 70 and score < 80 / (select count(1 from sc where cno = '03' * 100, 2 \中等率\score >= 80 and score < 90 / (select count(1 from sc where cno = '03' * 100, 2 \优良率\round((select count(1 from sc where cno = '03' and score > 90 / (select count(1 from sc where cno = '03' * 100, 2 \优秀率\and a.cno = d.cno and a.cno = e.cno;
19、按各科成绩进行排序，并显示排名select sno, cno, score, rank( over(partition by cno order by score desc \名次\
select sno, cno, score, dense_rank( over(partition by cno order by score desc \名次\from sc;
20、查询学生的总成绩并进行排名20.1 查询学生的总成绩select a.sno, a.sname, nvl(b.sum_score, 0 \总成绩\group by sno order by sno b where a.sno = b.sno(+;
20.2 查询学生的总成绩并进行排名。select c.\学生编号\学生姓名\总成绩\总成绩\排名\学生编号\\学生姓名\总成绩\sum_score from sc group by sno order by sno b where a.sno = b.sno(+ c
21、查询不同老师所教不同课程平均分从高到低显示select a.tno, a.tname, c.avg_score \平均分\from sc group by cno c where a.tno = b.tno and b.cno = c.cno order by \平均分\
22、查询所有课程的成绩第2名到第3名的学生信息及该课程成绩Score 重复时保留名次空缺select * from (select sno, cno, score, rank( over(partition by cno order by score desc order_sc from sc a where a.order_sc in (2, 3;
Score 重复时合并名次 select * from (select sno, cno, score,
dense_rank( over(partition by cno order by score desc order_sc from sc a where a.order_sc in (2, 3;
23 、 统 计 各 科 成 绩 各 分 数 段 人 数 ： 课 程 编 号 , 课 程 名称,\85\及所占百分比23.1 统 计 各 科 成 绩 各 分 数 段 人 数 ： 课 程 编 号 , 课 程 名称,\100\sc where cno = '01' and score >= 85 and score <= 100 \where cno = '01' and score >= 70 and score < 85 \cno = '01' and score >= 60 and score < 70 \'01' and score < 60 \
where cno = '02' and score >= 85 and score <= 100 \sc where cno = '02' and score >= 70 and score < 85 \where cno = '02' and score >= 60 and score < 70 \cno = '02' and score < 60 \where cno = '03' and score >= 85 and score <= 100 \where cno = '03' and score >= 70 and score < 85 \cno = '03' and score >= 60 and score < 70 \'03' and score < 60 \
纵向显示 1(显示存在的分数段;
select a.cno, a.cname, b.ff, b.c1 from course a, (select cno, c1, '0-60' ff from (select cno, count(1 c1 from sc where score < 60 group by cno union select cno, c1, '60-70' ff from (select cno, count(1 c1 from sc where score >= 60 and score < 70 group by cno
union select cno, c1, '70-85' ff from (select cno, count(1 c1 from sc where score >= 70 and score < 85 group by cno union select cno, c1, '85-100' ff from (select cno, count(1 c1 from sc where score >= 85 and score <= 100
group by cno b where a.cno = b.cno
23.2 统 计 各 科 成 绩 各 分 数 段 人 数 ： 课 程 编 号 , 课 程 名称,\85\及所占百分比横向显示select aa.cno, aa.cname, bb.\bb.\百分比(%\百分比(%\百分比(%\100\百分比(%\cno = '01' and score < 60 \score < 60 * 100 / (select count(1 from sc where cno = '01', 2 \百分比(%\count(1 from sc where cno = '01' and score >= 60 and score < 70 \count(1 from sc where cno = '01' and score >= 60 and score < 70 * 100 / (select count(1 from sc where cno = '01', 2 \百分比(%\score >= 70 and score < 85 \score >= 70 and score < 85 * 100 / (select count(1 from sc where cno = '01', 2 \百分比(%\round(((select count(1 from sc where cno = '01' and score >= 85 and score <= 100 * 100 / (select count(1 from sc where cno = '01', 2 \百分比(%\(select count(1 from sc
where cno = '02' and score < 60 \'02' and score < 60 * 100 / (select count(1 from sc where cno = '02', 2 \百分比(%\(select count(1 from sc where cno = '02' and score >= 60 and score < 70 \round(((select count(1 from sc where cno = '02' and score >= 60 and score < 70 * 100 / (select count(1 from sc where cno = '02', 2 \百分比(%\cno = '02' and score >= 70 and score < 85 \cno = '02' and score >= 70 and score < 85 * 100 / (select count(1 from sc where cno = '02', 2 \百分比(%\
100 \<= 100 * 100 / (select count(1 from sc where cno = '02', 2 \百分比(%\(select '03' cno, (select count(1 from sc where cno = '03' and score < 60 \round(((select count(1 from sc where cno = '03' and score < 60 * 100 / (select count(1 from sc where cno = '03', 2 \百分比(%\score >= 60 and score < 70 \score >= 60 and score < 70 * 100 / (select count(1 from sc where cno = '03', 2 \百分比(%\
(select count(1 from sc where cno = '03' and score >= 70 and score < 85 \round(((select count(1 from sc where cno = '03' and score >= 70 and score < 85 * 100 / (select count(1 from sc where cno = '03', 2 \百分比(%\cno = '03' and score >= 85 and score <= 100 \where cno = '03' and score >= 85 and score <= 100 * 100 / (select count(1 from sc where cno = '03', 2 \百分比(%\
纵向显示
select t1.*, round(t1.num / t2.all_num * 100, 2 || '%' 百分比 from (select m.cno, m.cname, (case when n.score >= 85 then '85-100' when n.score >= 70 and n.score < 85 then '70-85' when n.score >= 60 and n.score < 70 then '60-70' else '0-60' end as px, count(1 num from Course m, sc n where m.cno = n.cno group by m.cno, m.Cname, (case when n.score >= 85 then '85-100' when n.score >= 70 and n.score < 85 then '70-85' when n.score >= 60 and n.score < 70 then '60-70' else '0-60' end order by m.cno, m.cname, px t1, (select m.cno, m.cname, count(1 all_num from course m, sc n where m.cno = n.cno group by m.cno, m.cname order by m.cno, m.cname t2 where t1.cno = t2.cno
24、 查询学生的平均成绩并进行排名select c.sno, st.sname, c.avg_score, c.paim
from student st, (select b.*, rownum paim from (select a.sno, round(avg(score, 2 avg_score from (select s.sno sno, nvl(sc.score, 0 score from student s, sc where s.sno = sc.sno(+ a group by a.sno order by avg_score desc b c where st.sno = c.sno;
25、查询各科成绩前三名的记录25.1 分数重复时保留名次空缺select st.*, d.cno, d.score from student st, (select a.* from (select * from sc where cno = '01' order by score desc a where rownum <= 3 union all select b.* from (select * from sc where cno = '02' order by score desc b where rownum <= 3 union all select c.* from (select * from sc where cno = '03' order by score desc c where rownum <= 3 d where st.sno = d.sno
25.2 分数重复时不保留名次空缺，合并名次select
st.sno,st.sname,st.sage,st.ssex,b.cno,b.score,b.paimfrom student st,(select
sno,cno,score,paim from (select sno, cno, score, dense_rank( over(partition by cno order by score desc paim from sc awhere a.paim <= 3 bwhere st.sno = b.sno
26、查询每门课程被选修的学生数select cno,count(1 from sc group by cno; 27、查询出只有两门课程的全部学生的学号和姓名 select st.sno, st.sname from student st, (select a.sno from (select sno, count(cno cn from sc group by sno a where a.cn = 2 b where st.sno = b.sno;
28、查询男生、女生人数select '男生人数' \男女情况\count(ssex cn from student where ssex = '男'union allselect '女生人数' \男女情况\from (select count(ssex cn from student where ssex = '女'
29、查询名字中含有\风\字的学生信息select * from student where sname like '%风%'
31、查询1990年出生的学生名单(注：Student表中Sage列的类型是dateselect * from student where to_char(sage,'yyyy' = '1990'