Mysql必练50题(第四天)

第十六题
检索"01"课程分数小于60,按分数降序排列的学生信息

select * from student where s_id in 
(select s_id from score where c_id = 1 and s_score < 60);

Mysql必练50题(第四天)
第十七题

select s.s_id,s.s_name,
sum(case c_id when 1 then s_score else 0 end) as "语文",
sum(case c_id when 2 then s_score else 0 end) as "数学",
sum(case c_id when 3 then s_score else 0 end) as "英语",
round(avg(sc.s_score),2) as "平均成绩" from student s inner join score sc on s.s_id = sc.s_id 
group by sc.s_id order by avg(sc.s_score) desc;

Mysql必练50题(第四天)
第十八题
查询各科成绩最高分、最低分和平均分,以如下形式显示:课程ID,课程name,最高分,最低分,平均分,及格率,中等率,优良率,优秀率,及格为>=60,中等为:70-80,优良为:80-90,优秀为:>=90

select sc.c_id as "课程ID",c.c_name as "课程name",max(sc.s_score) as "最高分",
min(sc.s_score) as "最低分",avg(sc.s_score) as "平均分",
concat(round(sum(case when s_score>=60 then 1 else 0 end)/count(*)*100,2),"%") as "及格率",
concat(round(sum(case when s_score between 70 and 80 then 1 else 0 end)/count(*)*100,2),"%") as "中等率",
concat(round(sum(case when s_score between 80 and 90 then 1 else 0 end)/count(*)*100,2),"%") as "优良率",
concat(round(sum(case when s_score>=90 then 1 else 0 end)/count(*)*100,2),"%") as "优秀率"
from score sc left join course c on sc.c_id = c.c_id group by sc.c_id;

Mysql必练50题(第四天)
第十九题
按各科成绩进行排序,并显示排名

select sc.c_id , c.c_name,s.s_id,s.s_name,sc.s_score,
(rank() over (partition by c_id order by s_score desc)) as "rank"
from student s inner join score sc on s.s_id = sc.s_id 
inner join course c on sc.c_id = c.c_id;

Mysql必练50题(第四天)
第二十题
查询学生的总成绩并进行排名

select s.s_id,s.s_name,sum(sc.s_score) as total,
(rank() over(order by sum(sc.s_score) desc)) as r1
from student s inner join score sc on s.s_id = sc.s_id group by sc.s_id

Mysql必练50题(第四天)

上一篇:简单存储过程


下一篇:Be careful when emitting branches in C1 LIR