SQL必会的50道面试题

常见的SQL面试题：经典50题 - 知乎
https://zhuanlan.zhihu.com/p/38354000
SQL面试必会50题 - 知乎
https://zhuanlan.zhihu.com/p/43289968

这个不错
https://blog.csdn.net/Berzingou/article/details/81323290

案例数据建立参考如下
表名和字段
–1.学生表

Student(s_id,s_name,s_birth,s_sex) –学生编号,学生姓名, 出生年月,学生性别

–2.课程表

Course(c_id,c_name,t_id) – –课程编号, 课程名称, 教师编号

–3.教师表

Teacher(t_id,t_name) –教师编号,教师姓名

–4.成绩表

Score(s_id,c_id,s_score) –学生编号,课程编号,分数
测试数据

–- 建表
–学生表
CREATE TABLE Student(
s_id VARCHAR(20),
s_name VARCHAR(20) NOT NULL DEFAULT ‘’,
s_birth VARCHAR(20) NOT NULL DEFAULT ‘’,
s_sex VARCHAR(10) NOT NULL DEFAULT ‘’,
PRIMARY KEY(s_id)
);
–课程表
CREATE TABLE Course(
c_id VARCHAR(20),
c_name VARCHAR(20) NOT NULL DEFAULT ‘’,
t_id VARCHAR(20) NOT NULL,
PRIMARY KEY(c_id)
);
–教师表
CREATE TABLE Teacher(
t_id VARCHAR(20),
t_name VARCHAR(20) NOT NULL DEFAULT ‘’,
PRIMARY KEY(t_id)
);
–成绩表
CREATE TABLE Score(
s_id VARCHAR(20),
c_id VARCHAR(20),
s_score INT(3),
PRIMARY KEY(s_id,c_id)
);
– 插入学生表测试数据– 插入学生表测试数据
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’ , ‘女’);
–课程表测试数据
insert into Course values(‘01’ , ‘语文’ , ‘02’);
insert into Course values(‘02’ , ‘数学’ , ‘01’);
insert into Course values(‘03’ , ‘英语’ , ‘03’);

–教师表测试数据
insert into Teacher values(‘01’ , ‘张三’);
insert into Teacher values(‘02’ , ‘李四’);
insert into Teacher values(‘03’ , ‘王五’);

–成绩表测试数据
insert into Score values(‘01’ , ‘01’ , 80);
insert into Score values(‘01’ , ‘02’ , 90);
insert into Score values(‘01’ , ‘03’ , 99);
insert into Score values(‘02’ , ‘01’ , 70);
insert into Score values(‘02’ , ‘02’ , 60);
insert into Score values(‘02’ , ‘03’ , 80);
insert into Score values(‘03’ , ‘01’ , 80);
insert into Score values(‘03’ , ‘02’ , 80);
insert into Score values(‘03’ , ‘03’ , 80);
insert into Score values(‘04’ , ‘01’ , 50);
insert into Score values(‘04’ , ‘02’ , 30);
insert into Score values(‘04’ , ‘03’ , 20);
insert into Score values(‘05’ , ‘01’ , 76);
insert into Score values(‘05’ , ‘02’ , 87);
insert into Score values(‘06’ , ‘01’ , 31);
insert into Score values(‘06’ , ‘03’ , 34);
insert into Score values(‘07’ , ‘02’ , 89);
insert into Score values(‘07’ , ‘03’ , 98);

1.查询课程编号为“01”的课程比“02”的课程成绩高的所有学生的学号（重点）
select a.s_id 学号 from
(select * from Score where c_id=‘01’) as a
join
(select * from Score where c_id=‘02’) as b
on a.s_id = b.s_id
where a.s_score>b.s_score;

2、查询平均成绩大于60分的学生的学号和平均成绩（简单，第二道重点）

select s_id,avg(s_score) avg_score from score
group by s_id
having avg_score>60

3、查询所有学生的学号、姓名、选课数、总成绩（不重要）
select s.s_id, s_name,b.cnt, b.sum_score from student s
left join
(
select s_id,
count(distinct c_id) cnt,
sum(s_score) sum_score
from score
group by s_id
) b
on s.s_id = b.s_id

4、查询姓“猴”的老师的个数（不重要）
select count(t_id) from teacher where t_name like ‘猴%’

5、查询没学过“张三”老师课的学生的学号、姓名（重点）
select s.s_id, s.s_name from student s
where s.s_id not in
(select sc.s_id from score sc
join course c on sc.c_id = c.c_id
join teacher t on t.t_id = c.t_id
where t_name = ‘张三’)

6、查询学过“张三”老师所教的所有课的同学的学号、姓名（重点）
select s.s_id, s.s_name from student s
join
(
select sc.s_id, count(sc.c_id) cnt from score sc
where sc.c_id in
(select c.c_id from course c
join teacher t on t.t_id = c.t_id
where t_name = ‘张三’)
group by sc.s_id
having cnt =
(select count(c.c_id) from course c
join teacher t on t.t_id = c.t_id
where t_name = ‘张三’)
) b
on s.s_id = b.s_id

7、查询学过编号为“01”的课程并且也学过编号为“02”的课程的学生的学号、姓名（重点）
select s.s_id, s.s_name from student s
join (select s_id from score sc where sc.c_id=‘01’) a on s.s_id=a.s_id
join (select s_id from score sc where sc.c_id = ‘02’) b on s.s_id=b.s_id

8、查询课程编号为“02”的总成绩（不重点）
select sum(s_score) from score where c_id=‘02’

9、查询所有课程成绩小于60分的学生的学号、姓名
select s.s_id, s.s_name from student s join
(
select sc.s_id, max(sc.s_score) max_score from score sc
group by sc.s_id
having max_score<60
) b on s.s_id = b.s_id
union
select s.s_id, s.s_name from student s where s.s_id not in (select s_id from score);

10.查询没有学全所有课的学生的学号、姓名(重点)
select s.s_id, s.s_name from student s join
(
select s_id, count(distinct c_id) cnt from score
group by s_id
having cnt < (select count(c_id) from course)
) b on s.s_id = b.s_id;

11、查询至少有一门课与学号为“01”的学生所学课程相同的学生的学号和姓名（重点）
select distinct s.s_id, s.s_name from student s left join score sc
on s.s_id = sc.s_id
where sc.c_id in
(select c_id from score where s_id=‘01’) and s.s_id !=‘01’;

12.查询和“01”号同学所学课程完全相同的其他同学的学号(重点)
select s.s_id from score s left join
(
select s_id,count(distinct c_id) cnt from score where c_id in
(select c_id from score where s_id=‘01’)
group by s_id
having cnt = (select count(distinct c_id) from score where s_id=‘01’)
) b on s.s_id = b.s_id
where s.s_id != ‘01’
group by s.s_id
having count(distinct s.c_id)=(select count(distinct c_id) from score where s_id=‘01’);

– 13、查询没学过"张三"老师讲授的任一门课程的学生姓名 和47题一样（重点，能做出来）
select s.s_id from student s where s.s_id not in
(
select s_id from score
where c_id in
(select c.c_id from course c join teacher t
on c.t_id=t.t_id
where t.t_name=‘张三’)
);

15、查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩（重点）
select s.s_id, s.s_name,avg(sc.s_score) from student s
join score sc on s.s_id = sc.s_id
where s.s_id in
(select s_id from score
where s_score<60
group by s_id
having count(c_id) >1)
group by s.s_id, s.s_name

16、检索"01"课程分数小于60，按分数降序排列的学生信息（和34题重复，不重点）

17、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩(重重点与35一样)

select s.s_id,
s.c_id,
s.s_score,
b.avg_score from score s join
(select s_id, avg(s_score) avg_score from score
group by s_id) b
on s.s_id=b.s_id
order by b.avg_score desc;

优化
select s.s_id,
max(case when s.c_id=‘01’ then s.s_score else null end) ‘语文’,
max(case when s.c_id=‘02’ then s.s_score else null end) ‘数学’,
max(case when s.c_id=‘03’ then s.s_score else null end) ‘英语’,
avg(s.s_score) avg_score from score s
group by s.s_id
order by avg_score desc;

18.查询各科成绩最高分、最低分和平均分：以如下形式显示：课程ID，课程name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率

–及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90 (超级重点)

select c_id, max(s_score),min(s_score),avg(s_score),
sum(case when s_score>=60 then 1 else 0 end)/count(s_score) ‘及格率’,
sum(case when s_score>=70 and s_score<80 then 1 else 0 end)/count(s_score) ‘中等率’,
sum(case when s_score>=80 and s_score<90 then 1 else 0 end)/count(s_score) ‘优良率’,
sum(case when s_score>=90 then 1 else 0 end)/count(s_score) ‘优秀率’
from score
group by c_id;

优化
select c_id, max(s_score),min(s_score),avg(s_score),
avg(case when s_score>=60 then 1 else 0 end) ‘及格率’,
avg(case when s_score>=70 and s_score<80 then 1 else 0 end) ‘中等率’,
avg(case when s_score>=80 and s_score<90 then 1 else 0 end) ‘优良率’,
avg(case when s_score>=90 then 1 else 0 end)/count(s_score) ‘优秀率’
from score
group by c_id

– 19、按各科成绩进行排序，并显示排名(重点row_number)
select s_id,c_id,s_score, row_number() over(partition by c_id order by s_score)
from score

三种排名方式
row_number: 1,2,3,4
rank:1,2,2,4
dense_rank:1,2,2,3

23、使用分段[100-85],[85-70],[70-60],[<60]来统计各科成绩，分别统计各分数段人数：课程ID和课程名称(重点和18题类似)
select c_id,
sum(case when s_score>=85 then 1 else 0 end) ‘85-100’,
sum(case when s_score>=70 and s_score<85 then 1 else 0 end) ‘85-70’,
sum(case when s_score>=60 and s_score<70 then 1 else 0 end) ‘70-60’,
sum(case when s_score<60 then 1 else 0 end) ‘<60’
from score
group by c_id;

25、查询各科成绩前三名的记录（不考虑成绩并列情况）（重点 与22题类似）
select s1.s_score,s1.c_id from score s1 join score s2
on s1.c_id = s2.c_id
where s1.s_score <= s2.s_score
group by s1.s_score,s1.c_id
having count(s1.s_score)<=3

35、查询所有学生的课程及分数情况（重点）

select s_id,
max(case when s.c_id=‘01’ then s_score end) ‘01’,
max(case when s.c_id=‘02’ then s_score end) ‘02’,
max(case when s.c_id=‘03’ then s_score end) ‘03’
from score s
group by s_id;

41.查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩 （重点）
select * from
(select s_id,
max(case when s.c_id=‘01’ then s_score end) ‘01’,
max(case when s.c_id=‘02’ then s_score end) ‘02’,
max(case when s.c_id=‘03’ then s_score end) ‘03’
from score s
group by s_id) b
where b.01=b.02 or b.01=b.03 or b.02=b.03;

46、查询各学生的年龄（精确到月份）
select s_id, s_birth,
timestampdiff(year,s_birth,now()) age,
timestampdiff(day,s_birth,curdate()) months
from student;