数据分析sql面试必会6题经典_数据分析系列（7）：经典SQL题

做完了这些SQL题，你的SQL水平一定可以上一个台阶。小白，你好。
请知悉：我是用HQL的语法风格写的标准SQL代码，所以有些语句性能不是最优，但并不影响你做题！

表结构如下：

建表语句如下：

create table Student(sid varchar(10),sname varchar(10),sage datetime,ssex nvarchar(10));
insert into Student values('01' , '赵雷' , '1990-01-01' , '男');
insert into Student values('02' , '钱电' , '1990-12-21' , '男');
insert into Student values('03' , '孙风' , '1990-05-20' , '男');
insert into Student values('04' , '李云' , '1990-08-06' , '男');
insert into Student values('05' , '周梅' , '1991-12-01' , '女');
insert into Student values('06' , '吴兰' , '1992-03-01' , '女');
insert into Student values('07' , '郑竹' , '1989-07-01' , '女');
insert into Student values('08' , '王菊' , '1990-01-20' , '女');
create table Course(cid varchar(10),cname varchar(10),tid varchar(10));
insert into Course values('01' , '语文' , '02');
insert into Course values('02' , '数学' , '01');
insert into Course values('03' , '英语' , '03');
create table Teacher(tid varchar(10),tname varchar(10));
insert into Teacher values('01' , '张三');
insert into Teacher values('02' , '李四');
insert into Teacher values('03' , '王五');
create table SC(sid varchar(10),cid varchar(10),score decimal(18,1));
insert into SC values('01' , '01' , 80);
insert into SC values('01' , '02' , 90);
insert into SC values('01' , '03' , 99);
insert into SC values('02' , '01' , 70);
insert into SC values('02' , '02' , 60);
insert into SC values('02' , '03' , 80);
insert into SC values('03' , '01' , 80);
insert into SC values('03' , '02' , 80);
insert into SC values('03' , '03' , 80);
insert into SC values('04' , '01' , 50);
insert into SC values('04' , '02' , 30);
insert into SC values('04' , '03' , 20);
insert into SC values('05' , '01' , 76);
insert into SC values('05' , '02' , 87);
insert into SC values('06' , '01' , 31);
insert into SC values('06' , '03' , 34);
insert into SC values('07' , '02' , 89);
insert into SC values('07' , '03' , 98);

1.查询"01"课程比"02"课程成绩高的学生的信息及课程分数

select t2.sid,t2.sname,t2.sage,t2.ssex,t1.score,t1.score1
from (select ti1.*,ti2.score as score1 from --查询"01"课程比"02"课程成绩高的信息；注意加上ti2成绩（有where）
(
select * from SC
where cid='01')ti1
inner join 
(select * from SC where cid='02')ti2
on ti1.sid=ti2.sid
where ti1.score>ti2.score )t1
inner join  (
select * from Student)t2
on t1.sid=t2.sid

2、查询同时存在01和02课程的情况

select t2.sid,t2.sname,t2.sage,t2.ssex,t1.score,t1.score1
from (select ti1.*,ti2.score as score1 from 
(
select * from SC
where cid='01')ti1
inner join 
(select * from SC where cid='02')ti2
on ti1.sid=ti2.sid
)t1
inner join  (
select * from Student)t2
on t1.sid=t2.sid

3、查询选择了02课程但没有01课程的情况

 SELECT *
 FROM SC
 WHERE SC.sid NOT IN (SELECT sid FROM SC WHERE SC.cid = '01')
 AND SC.cid = '02';

比较与下文代码的区别：

select ti1.*,ti2.score as score1 from 
(
select * from SC
where cid <>'01')ti1
inner join 
(select * from SC where cid='02')ti2
on ti1.sid=ti2.sid

前者是从选择了02课程中刨除了选择01课程的所有学生id；

后者是取“没有选择01课程”与“选择02课程”的交集；

具体哪种理解对，各位看官自辩，我只提供一种参考；

4、查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩

select t1.sid,t1.sname,t2.avg_score
from (
select sid,sname from Student)t1
inner join (
select sid,avg(score) as avg_score from SC
group by sid  having avg(score)>=60)t2
on t1.sid=t2.sid
group by t1.sid,t1.sname

5、查询在 SC 表存在成绩的学生信息

select distinct t2.*
from (
select * from SC)t1
inner join (
select * from Student)t2
on t1.sid=t2.sid;

6、查询所有同学的学生编号、学生姓名、选课总数、所有课程的成绩总和

select t1.sid,t1.sname,t2.num_cou,t2.sum_score
from (
select * from Student)t1
inner join (
select sid,count(distinct cid) as num_cou,sum(score) as sum_score
from SC group by sid
)t2
on t1.sid=t2.sid

7、查询没有学全所有课程的同学的信息

select t1.sid,t1.sname,count(DISTINCT t2.cid)
from (
select sid,sname from Student)t1
left join (
select sid,cid from SC)t2
on t1.sid=t2.sid
group by t1.sid
HAVING count( t2.cid)< (select count(DISTINCT cid) from Course);

在mysql中，where后可以跟子查询，却不能跟聚合函数！但having后可以跟聚合函数，同时可以加子查询！

提问1：倘若将上述语句由left join 改为inner join ，你觉得会有什么变化？这对你以后写SQL有什么启示？

提问2：join在mysql中是非常耗费计算资源的，能不能使用其它语句替换？使用not in

select sid,sname from Student
where sid not in (select sid from SC group by sid  having count(cid)=3)

8、查询至少有一门课与学号为" 01 "的同学所学相同的同学的信息

select distinct t1.* from (
select * from Student )t1
inner join (select * from SC )t2
on t1.sid=t2.sid 
where t2.cid in (
select cid from SC where sid='01')
and t1.sid!='01'

有时感觉in，子查询还真好用，但hive使用起来有局限啊！

9.查询和" 01 "号的同学学习的课程完全相同的其他同学的信息

select  t1.* from (
select * from Student )t1
inner join (select * from SC )t2
on t1.sid=t2.sid 
where t2.cid in (
select cid from SC where sid='01')
group by t1.sid
having count(distinct t2.cid)= (select count(distinct cid) from SC where sid='01')
and t1.sid!='01'

在第8题基础上，限制课程数为‘01’号同学选课数就好

10.查询没学过"张三"老师讲授的任一门课程的学生姓名

SELECT sname FROM student 
WHERE sid NOT IN (
SELECT sid FROM sc 
LEFT JOIN course ON sc.cid=course.cid
LEFT JOIN teacher ON course.tid=teacher.tid 
WHERE tname='张三' )

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

select t1.sid,t1.sname,t2.avg_score
from (
select sid,sname from Student)t1
inner join (
select sid,avg(score) avg_score from SC group by sid)t2
on t1.sid=t2.sid
inner join(
select sid from SC group by sid having count(case when score<60 then 1 end)>=2)t3
on t2.sid=t3.sid

12按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩

select t1.*,t2.score,t2.avg_score
from (
select sid,sname from Student )t1
left join (
select sid,score,avg(score) over(partition by sid ) as 'avg_score'
from SC)t2
on t1.sid=t2.sid
order by t2.avg_score desc

13、查询各科成绩最高分、最低分和平均分,以如下形式显示

课程 ID，课程 name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率

及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90

select t1.cname,t2.cid,t2.最高成绩,t2.最低成绩,t2.平均成绩,t2.及格率,t2.中等率,t2.优良率,t2.优秀率
from (select cid,cname from Course)t1
inner join (
select cid ,max(score) as '最高成绩',min(score) '最低成绩',avg(score) as '平均成绩',
count(distinct case when score>=60 then sid end)/count(distinct sid) as '及格率',
count(distinct case when score BETWEEN 60 and 70 then sid end)/count(distinct sid) as '中等率',
count(distinct case when score BETWEEN  80 and 90 then sid end )/count(distinct sid) as '优良率',
count(distinct case when score BETWEEN  90 and 100 then sid end )/count(distinct sid) as '优秀率'
from SC 
group by cid)t2
on t1.cid=t2.cid

14、按各科成绩进行排序，并显示排名， Score 重复时保留名次空缺

select *,dense_rank()over(PARTITION by cid order by score desc) from SC

rank()over():1,1,3,4
dense_rank)()over():1,1,2,3
ronnum()over():1,2,3,4-----(mysql5.6不支持直接使用rownum，mysql8.0可以)

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

select t1.cid,t1.cname,t2.*
from (
select cid,cname from course)t1
inner join (
select cid,
count(distinct case when score BETWEEN 85 and 100 then sid end)/count(distinct sid) as '[80-100]百分比',
count(distinct case when score BETWEEN 70 and 85 then sid end)/count(distinct sid)  as '[70-85]百分比',
count(distinct case when score BETWEEN 60 and 70 then sid end )/count(distinct sid)  as  '[60-70]百分比',
count(distinct case when score BETWEEN 0 and 60 then sid end)/count(distinct sid)  as '[0-60]百分比'
from SC
group by cid )t2
on t1.cid=t2.cid

16、查询各科成绩前三名的记录

SELECT a.*,COUNT(b.score) +1 AS ranking
FROM SC AS a LEFT JOIN SC AS b 
ON a.cid = b.cid AND a.score<b.score
GROUP BY a.cid,a.sid
HAVING ranking <= 3
ORDER BY a.cid,ranking;

这是原生态的方法啊；值得推荐！！！

17、查询任何一门课程成绩在 70 分以上的姓名、课程名称和分数

select  distinct t1.*,t2.score,t3.cname
from (
select sid ,sname from Student)t1
inner join (
select sid,cid,score from SC
where score>70)t2
on t1.sid=t2.sid
inner join (
select cid,cname  from Course)t3
on t2.cid=t3.cid

18、成绩不重复，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩

select t4.*,t3.score
from (
select tid from Teacher where tname='张三')t1
inner join (
select cid,tid from Course)t2
on t1.tid=t2.tid
inner join (select sid,cid,score from SC  )t3
on t2.cid=t3.cid
inner join (select * from Student )t4
on t3.sid=t4.sid
order by t3.score desc
limit 1

19、查询每门成绩最好的前两名

select t2.cid,t1.sid,t1.sname
from (
select sid,sname from Student )t1
inner join (-通过自连接的方式找出每门成绩最好的前n名
SELECT a.*,COUNT(b.score)+1  AS ranking
FROM SC AS a LEFT JOIN SC AS b 
ON a.cid = b.cid AND a.score<b.score
GROUP BY a.cid,a.sid
HAVING ranking <= 2
ORDER BY a.cid,ranking )t2
on t1.sid=t2.sid
order by t2.cid

20、查询选修了全部课程的学生信息

select t1.*
from (
select * from Student)t1
inner join (
select sid from SC group by sid having count(cid)=(select count(distinct cid ) from Course))t2
on t1.sid=t2.sid

21、

1）查询各学生的年龄，只按年份来算

select sname,year(now())-year(sage) as '年龄' from Student

2）按照出生日期来算，当前月日 < 出生年月的月日则，年龄减一

select sname,case when DATE_FORMAT(NOW(), '%m-%d') - DATE_FORMAT(sage, '%m-%d')<0 then year(now())-year(sage)-1 else year(now())-year(sage) end as '年龄'
from Student;

ps：

本篇文章，非常适合新手小白做练习，我也给了建表语句，做题就对了！

看完，请点赞，我要赞，嘻嘻（来自最右小哥哥要赞视频）

如果想看解析过程，请参考：

Roar：sql 经典50题--可能是你见过的最全解析​zhuanlan.zhihu.com

想知道更多的数据分析笔试面试题，请关注我的【数据分析专栏】：

数据分析​zhuanlan.zhihu.com