SQL面试五十题（二）

– Student sid sname sage ssex
– Teacher tid tname
– Course cid cname tid
– Score sid cid score

– 20、查询学生的总成绩并进行排名（非重点）
select sid,sum(score),row_number() over (order by sum(score) desc) “排名”
from score
group by sid

– 19、按各科成绩进行排序，并显示排名（重点row_number 排名不会重复）
– 窗口函数（不会改变集合数。然而max等聚集函数改变了集合数）
– 第二个和第三个分数一样：rank 跳跃排序 1224; dense_rank 1223;row_number 1234
select sid,cid,score,row_number() over (partition by cid order by score desc) as “排名”
from Score

– 18、查询各科成绩最高分、最低分和平均分：以如下形式显示：课程ID，课程name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率
– 及格>=60,中等70-80,优良80-90，优秀>=90
– case when
select a.cid “课程ID”,b.cname “课程name”,max(a.score) “最高分”,min(a.score) “最低分”,avg(a.score) “平均分”,
sum(case when a.score>=60 then 1 else 0 end)/count(score) “及格率”,
sum(case when a.score>=70 and a.score<80 then 1 else 0 end)/count(score) “中等率”,
sum(case when a.score>=80 and a.score<90 then 1 else 0 end)/count(score) “优良率”,
sum(case when a.score>=90 then 1 else 0 end)/count(score) “优秀率”
from Score a
inner join Course b on a.cid=b.cid
group by a.cid,b.cname – group by 放除了聚集函数以外所有的搜索内容

– 难点是及格率的算法

– 17、按平均成绩从高到低显示学生所有课程的成绩以及每个学生的平均成绩（重点）
– 期望格式： 学号 课程一成绩 课程二成绩 课程三成绩 平均成绩
– case when和max配合使用
select
sid “学号”,
max(case when cid=‘01’ then score else null end) “语文”,
max(case when cid=‘02’ then score else null end) “数学”,
max(case when cid=‘03’ then score else null end) “英语”,
avg(score) “平均成绩”
from Score group by sid
order by avg(score) desc
– 难点：每个学生只有一行平均成绩但有多行课程成绩
– select sid,score,avgScore
– from Score a
– inner join
– (select sid,avg(score) avgScore from score group by sid) b on a.sid=b.sid
– order by avg(score) desc

– 16、检索"01"课程分数小于60，按分数降序排列的学生信息
select a.* from Student a
inner join (
select sid,score from Score where score<60 and cid=‘01’ ) b
on a.sid=b.sid
order by b.score desc
– 一定要查sid 要不然连接条件查不到

– 15、查询有两门及其以上不及格课程的同学的学号，姓名及其平均成绩
– count和distinct好兄弟
select a.sname,a.sid,b.avgsc from Student a
inner join (select sid,count(distinct score),avg(score) avgsc from Score where score<60
group by sid
having count(distinct score)>=2) b
on a.sid=b.sid

– 12、查询和01号同学所学课完全相同的其他同学的学号（重点）
– 思路：选课数等于01的选课数，并且选课不包含01没有选过的课
select sid from Score
group by sid
having count(distinct cid)=(select count(distinct cid) from Score where sid=‘01’) and sid!=‘01’
and sid not in (
select distinct sid from Score
where cid not in (select cid from Score where sid=‘01’))

– 11、查询至少有一门课与学号为01的学生所学课程相同的学生的学号和姓名（重点）
select a.sid,a.sname
from student a
inner join (
select distinct sid from Score
where cid in
(select cid from Score where sid=“01”))b
on a.sid=b.sid and a.sid!=“01”