SQL面试五十题（三）

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

– 没有第30题

– 29、查询名字中含有"风"字的学生信息（非重点）
select * from student
where sname like ‘%风%’

– 28、查询男生、女生人数(非重点)
select
count(case when ssex=“男” then 1 else 0 end) “男生人数”,
count(case when ssex=“女” then 1 else 0 end) “女生人数”
from student
– 或者
select ssex,count(ssex)
from student group by ssex

– 27、 查询出只选两门课程的全部学生的学号和姓名(非重点)
select * from student
where sid in (select sid from score group by sid having count(score)=2)
– 尽量少用子查询
select a.sid,a.sname
from student a
inner join score b
on a.sid=b.sid
group by a.sid,a.sname
having count(b.cid)=2

– 26、查询每门课程被选修的学生数(非重点)
select cid “课程”,count(score) “选课人数” from score group by cid

– 25、查询各科成绩前三名的记录（不考虑成绩并列情况）（同22题）
select a.*
from (select sid,cid,row_number() over (partition by cid order by score desc) pm from score) a
where pm<4
– 或者pm in (1,2,3)

– select a.*
– from (select sid,cid,row_number() over (partition by cid order by score desc) “排名” from score) a
– where “排名”<4
– 或者 “排名” in (1,2,3)不对？？？
其别名起英文的吧，不会有莫名其妙的问题

– 24、查询学生平均成绩及其名次（同19题，重点）
select sid,avg(score),row_number() over (order by avg(score) desc) “排名”
from score
group by sid

– 23、统计各科成绩各分数段人数：课程编号,课程名称,[100-85],[85-70],[70-60],[0-60]及所占百分比(同18题 重点)
select a.cid,b.cname,
sum(case when score>=85 and score<100 then 1 else 0 end)/count(score) “优秀率”,
sum(case when score>=70 and score<85 then 1 else 0 end)/count(score) “优良率”,
sum(case when score>=60 and score<70 then 1 else 0 end)/count(score) “中等率”,
sum(case when score>=0 and score<60 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

– 22、查询所有课程的成绩第2名到第3名的学生信息及该课程成绩(重点 类似25,子查询就是19题的内容)
– partiton by 按。。。分类
select *
from (select st.sid,st.sname,st.sage,st.ssex,cid,score,row_number() over(partition by cid order by score desc) m
from score sc inner join student st on sc.sid=st.sid
) a
where m in (2,3)

– 21、查询不同老师所教不同课程平均分从高到低显示（不是重点）
select te.tid,te.tname,avg(score)
from score sc
inner join course co on co.cid=sc.cid
inner join Teacher te on co.tid=te.tid
group by te.tid,te.tname order by avg(score) desc