MySQL经典题目

一、常见的SQL面试题：经典50题。

来自于知乎分享https://zhuanlan.zhihu.com/p/38354000（部分题目）

表结构：

学生表：student(学号,学生姓名,出生年月,性别)

 

 

成绩表：score(学号,课程号,成绩)

 

 

 

课程表：course(课程号,课程名称,教师号)

教师表：teacher(教师号,教师姓名)

 

 

1. 查询各科成绩最高和最低的分， 以如下的形式显示：课程号，最高分，最低分（groupby分组聚合）

select courseid,max(score),min(score) from score group by courseid;

 2. 查询平均成绩大于60分学生的学号和平均成绩（having）

select stuid,avg(score)
from score
group by stuid
having avg(score) > 60;

3. 查询同名同性学生名单并统计同名人数

select stuname,count(stuid)
from student
group by stuname
having count(stuid) > 1;

 4. 查询每门课程的平均成绩，结果按平均成绩升序排序，平均成绩相同时，按课程号降序排列

select courseid,avg(score)
from score
group by courseid 
order by avg(score),courseid desc;

 6. 查询两门以上不及格课程的同学的学号及其平均成绩

select stuid,avg(score) as avg_score
from score
where score < 60
group by stuid
having count(courseid) >=2;

 7. 查询所有课程成绩小于80分学生的学号、姓名

select student.stuid,student.stuname
from student 
where stuid not in(
select distinct stuid 
from score
where score >80
);

 8. 1990年出生的学生名单（stubirtthdate为date类型）

select stuid,stuname
from student 
where year(stubirthdate) = 1990;

 9.

TOPN问题 查询各科成绩最高的记录（关联子查询）

select *
from score as a
where score = (select max(score) from score as b where a.courseid = b.courseid);

上述关联子查询顺序

1. select * from score

2. select max(score) from score where courseid = 0001

3. select * from score where score =81 and courseid = 0001 

 TOPN问题 查询各科成绩前两名的记录（union）

(select * from score where courseid = 0001 order by score desc limit 2)
union
(select * from score where courseid = 0002 order by score desc limit 2)
union
(select * from score where courseid = 0003 order by score desc limit 2);

 10. 查询出每门课程的及格人数和不及格人数（case表达式）

select courseid,
sum(case when score >= 60 then 1 else 0 end) as jigerenshu,
sum(case when score < 60 then 1 else 0 end) as bujigerenshu 
from score
group by courseid;

11. 使用分段[100-85],[85-70],[70-60],[<60]来统计各科成绩，分别统计：各分数段人数，课程号和课程名称（case表达式）

select score.courseid,course.coursename,
sum(case when score between 85 and 100 then 1 else 0 end) as '[85-100]',
sum(case when score between 70 and 85 then 1 else 0 end) as '[70-85]',
sum(case when score between 60 and 70 then 1 else 0 end) as '[60-70]'
from score right join course
on score.courseid = course.courseid
group by courseid;

 12. 行列互换

参考https://mp.weixin.qq.com/s/6Kll4Q6Xp37i2PiLUh4cMA

下面是学生的成绩表（表名score，列名：学号、课程号、成绩）

 

使用sql实现将该表行转列为下面的表结构

 

select stuid,
max(case courseid when '0001' then score else 0 end) as '课程号0001',
max(case courseid when '0002' then score else 0 end) as '课程号0002',
max(case courseid when '0003' then score else 0 end) as '课程号0003'
from score
group by stuid;

 

二、互联网校招SQL笔试经典50题及答案解析

来自知乎分享https://zhuanlan.zhihu.com/p/53302593（部分题目）

表结构

学生表Student

 

 

 

教师表Teacher

 

 

 

课程表Course

 

成绩表SC

 

 

 

1. 查询“01”课程比“02”课程成绩高的所有学生的学号（生成新表 表连接）

select distinct t1.sid as sid
from 
(select * from sc where cid='01') as t1
left join 
(select * from sc where cid='02') as t2
on t1.sid=t2.sid
where t1.score>t2.score;

 2. 查询学过编号“01”并且也学过编号“02”课程的同学的学号、姓名

select sid,sname
from student
where sid IN
(select `sid`
from SC
group by `sid`
#分组保证 学生 01 和 02课程都学过
having count(if(cid='01',score,null))>0 and count(if(cid='02',score,null))>0);

 3. 查询所有课程成绩小于60分的同学的学号、姓名；

select s_sid.`sid`,student.sname
from (select distinct `sid`
from sc
group by `sid`
having max(score) < 60)s_sid
left join student on s_sid.`sid` = student.`sid`;

 4. 查询没有学全所有课的同学的学号、姓名；

select ssid.`sid`,student.sname
from (select `sid`
from sc
group by `sid` having count(`score`) < (select count(*) from course))ssid
left join student on ssid.`sid` = student.`sid`	;

 5. 查询至少有一门课与学号为“01”的同学所学相同的同学的学号和姓名

select
distinct sc.sid
from 
(
select
cid
from sc
where sid='01'
)t1
left join sc
on t1.cid=sc.cid;

 6. 查询和"01"号的同学学习的课程完全相同的其他同学的学号和姓名

select t1.`sid` ,student.sname
from
(select sc.`sid`
from 
(
select cid
from sc
where `sid` = 01
)t1
left join sc on t1.`cid` = sc.`cid`
group by `sid`
having count(distinct sc.cid)= (select count(distinct cid) from sc where sid = '01'))t1
left join student on t1.`sid` = student.`sid`;

 7. 查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩

select ssid.`sid`,student.sname,ssid.`avg(score)`
from 
(
select sid,avg(score)
from sc
group by sid
having count(if(score<60,score,NULL)) >= 2
)ssid left join student on ssid.`sid` = student.`sid`;

 8. 查询所有课程的成绩第2名到第3名的学生信息及该课程成绩（窗口函数）

select `sid`,rank_num,score,`cid`
from 
(
select rank() over(PARTITION by `cid` order by score desc) as rank_num,
`sid`,`score`,`cid`
from sc
)t
where rank_num in (2,3);

 9. 统计各科成绩各分数段人数：课程编号,课程名称,[100-85],[85-70],[70-60],[0-60]及所占百分比

select sc.`cid`,cname,
count(if(score between 85 and 100,sid,NULL))/count(sid)
,count(if(score between 70 and 85,sid,null))/count(sid)
,count(if(score between 60 and 70,sid,null))/count(sid)
,count(if(score between 0 and 60,sid,null))/count(sid)
from sc
left join course on sc.`cid` = course.`cid`
group by sc.`cid`;

 10. 查询学生平均成绩及其名次（窗口函数）

select sid,avg_score,rank() over(order by avg_score desc)
from 
(select sid,avg(score) as avg_score
from sc
group by `sid`)t;

 11. 查询各科成绩前三名的记录（窗口函数）

select * from 
(select `cid`,`sid`,rank() over (PARTITION by `cid` order by score desc) as rank_num
from sc)t
where rank_num <=3;

12. 查询各学生的年龄 （curdate()）

select sid,sname,year(curdate())-year(sage) as age
from student;

 

2020.4.19 15:00