数据库SQL实战-考试分数(mysql)
1. 考试分数(一)
1.1 题目描述
牛客每次考试完,都会有一个成绩表(grade),如下:
请你写一个sql语句查询各个岗位分数的平均数,并且按照分数降序排序,结果保留小数点后面3位(3位之后四舍五入):
1.2 语句实现
-- select job, round(avg(score),3) avg
select job, round(sum(score)/count(id),3) avg
from grade
group by job
order by avg desc;
2. 考试分数(二)
2.1 题目描述
牛客每次考试完,都会有一个成绩表(grade),如下:
请你写一个sql语句查询用户分数大于其所在工作(job)分数的平均分的所有grade的属性,并且以id的升序排序,如下:
2.2 语句实现
select g.*
from grade g
join (select job, round(avg(score),3) as avg
from grade
group by job) as a
on g.job = a.job
where g.score > a.avg
order by g.id;
3. 考试分数(三)
3.1 题目描述
牛客每次举办企业笔试的时候,企业一般都会有不同的语言岗位,比如C++工程师,JAVA工程师,Python工程师,每个用户笔试完有不同的分数,现在有一个分数(grade)表简化如下:
不同的语言岗位(language)表简化如下:
请你找出每个岗位分数排名前2名的用户,得到的结果先按照language的name升序排序,再按照积分降序排序,最后按照grade的id升序排序,得到结果如下:
3.2 语句实现
select id, name, score
from (select g.id, l.name, g.score,
dense_rank() over (partition by g.language_id order by g.score desc) as num
from grade g
inner join language l
on g.language_id = l.id) as ng
where num <= 2
order by name, score desc;
4. 考试分数(四)
4.1 题目描述
牛客每次考试完,都会有一个成绩表(grade),如下:
请你写一个sql语句查询各个岗位分数升序排列之后的中位数位置的范围,并且按job升序排序,结果如下:
4.2 语句实现
-- floor()向下取整,ceil()向上取整
select job, floor((count(job)+1)/2) start, ceil((count(job)+1)/2) end
from grade
group by job
order by job;
5. 考试分数(五)
5.1 题目描述
grade表同题4,请你写一个sql语句查询各个岗位分数的中位数位置上的所有grade信息,并且按id升序排序,结果如下:
5.2 语句实现
-- 题3与题4的结合
select b.*
from
(select job, floor((count(job)+1)/2) as start, ceil((count(job)+1)/2) as end
from grade
group by job) as a
join
(select *, dense_rank() over (partition by job order by score desc) as t_rank
from grade) as b
on (a.job = b.job and b.t_rank between a.start and a.end)
order by b.id;