第二十一题
查询不同老师所教不同课程平均分从高到低显示
select t_name,c_name,round(avg(sc.s_score),2) as avg_score from score sc left join
course c on sc.c_id = c.c_id left join teacher t
on c.t_id = t.t_id group by t.t_id order by avg_score desc;
第二十二题
查询所有课程的成绩第2名到第3名的学生信息及该课程成绩
select s_id,rank_num,s_score,c_id from
(select s_id,(row_number() over(partition by c_id order by s_score desc)) as rank_num,
s_score,c_id from score) as t where rank_num in (2,3);
第二十三题
统计各科成绩各分数段人数：课程编号,课程名称,[100-85],[85-70],[70-60],[0-60]及所占百分比
select sc.c_id,c.c_name,
sum(case when s_score between 85 and 100 then 1 else 0 end) as "[100-85]",
concat(round(sum(case when s_score between 85 and 100 then 1 else 0 end)/count(*)*100,2),"%") as "百分比",
sum(case when s_score between 70 and 85 then 1 else 0 end) as "[85-70]",
concat(round(sum(case when s_score between 70 and 85 then 1 else 0 end)/count(*)*100,2),"%") as "百分比",
sum(case when s_score between 60 and 70 then 1 else 0 end) as "[70-60]",
concat(round(sum(case when s_score between 60 and 70 then 1 else 0 end)/count(*)*100,2),"%") as "百分比",
sum(case when s_score between 0 and 60 then 1 else 0 end) as "[0-60]",
concat(round(sum(case when s_score between 0 and 60 then 1 else 0 end)/count(*),2),"%") as "百分比"
from score sc left join course c on sc.c_id = c.c_id group by c_id;
第二十四题
查询学生平均成绩及其名次
select s.s_name,s.s_id,round(avg(sc.s_score),2) as avg_score,
(rank() over(order by avg(sc.s_score) desc)) as score_order
from student s inner join score sc on s.s_id = sc.s_id group by sc.s_id;
第二十五题
查询各科成绩前三名的记录
select s.s_id,sc.c_id,s.s_name,c.c_name,sc.t1
from student s inner join
(select s_id,c_id,(row_number() over(partition by c_id order by s_score desc)) as t1 from score) sc
on s.s_id = sc.s_id
inner join course c on sc.c_id = c.c_id
where sc.t1 in (1,2,3) order by sc.c_id;
``
