数据库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;