常见的SQL面试题: 经典50题

题目参考：
https://zhuanlan.zhihu.com/p/38354000
https://www.cnblogs.com/lhx0827/p/9567199.html

一、建表

学生表-student

-- 创建学生表
create table student(
    `student_number` varchar(255) not null comment '学号',
    `student_name` varchar(255) not null comment '学生姓名',
    `student_birth` date not null comment '出生日期',
    `student_sex` varchar(255) not null comment '性别',
    PRIMARY KEY (student_number)
) engine=InnoDB default charset = utf8 comment '学生表';

-- 初始化学生表数据
insert into student(student_number, student_name, student_birth, student_sex)
values ('S0001','猴子','1989-01-01','男'),
       ('S0002','猴子','1990-12-21','女'),
       ('S0003','马云','1991-12-21','男'),
       ('S0004','王思聪','1990-05-20','男');

成绩表-achievement

-- 创建成绩表
create table achievement(
    `student_number` varchar(255) not null comment '学号',
    `course_number` varchar(255) not null comment '课程号',
    `score` float(3) not null comment '成绩',
    primary key(student_number,course_number)
) engine = InnoDB default charset = utf8 comment '成绩表';

-- 初始化成绩表数据
insert into achievement(student_number, course_number, score)
values ('S0001' , 'C0001' , 80),
       ('S0001' , 'C0002' , 90),
       ('S0001' , 'C0003' , 99),
       ('S0002' , 'C0002' , 60),
       ('S0002' , 'C0003' , 80),
       ('S0003' , 'C0001' , 80),
       ('S0003' , 'C0002' , 80),
       ('S0003' , 'C0003' , 80);

课程表-course

create table course(
    `course_number` varchar(255) not null comment '课程号',
    `course_name` varchar(255) not null comment '课程名称',
    `teacher_number` varchar(255) not null comment '教师号',
    primary key (course_number)
) engine = InnoDB default charset = utf8 comment '课程表';

-- 初始化课程表数据
insert into course(course_number, course_name, teacher_number)
values ('C0001' , '语文' , 'T0002'),
       ('C0002' , '数学' , 'T0001'),
       ('C0003' , '英语' , 'T0003');

教师表-teacher

create table teacher(
    `teacher_number` varchar(20) not null comment '教师号',
    `teacher_name` varchar(20)  comment '教师姓名',
    primary key (teacher_number)
) engine = InnoDB charset = utf8 comment '教师表';

-- 初始化教师表数据
insert into teacher(teacher_number, teacher_name)
values ('T0001','孟扎扎'),
       ('T0002','马化腾'),
       ('T0003',null),
       ('T0004','');

二、练习

第一题：查询姓"猴"的学生名单

select * from student where student_name like '猴%';

第二题：查询姓名中最后一个字是"子"的学生名单

select * from student where student_name like '%子';

第三题：查询姓名中带"猴"的学生名单

select * from student where student_name like '%猴%';

第四题：查询姓“孟”老师的个数

select count(teacher_number) from teacher where teacher_name like '孟%';

第五题：查询课程编号为"C0002"的总成绩

select sum(score) from achievement where course_number = 'C0002';

第六题：查询选了课程的学生人数，注意：count(*)统计时，注意要去重

select count(distinct student_number) from achievement;

第七题：查询各科成绩最高分和最低分

select course_number,max(score),min(score) from achievement 
group by course_number;

第八题：查询每门课程被选修的学生数
注意：为什么不用count(distinct student_number) ？
因为：course_number + student_number 是唯一主键，不存在同一个学生，一门课，两个成绩；

select course_number, count(student_number) from achievement 
group by course_number;

第九题：查询男生，女生人数

select student_sex,count(student_number) from student 
group by student_sex;

第十题：查询平均成绩大于80分学生的学号和平均成绩
注意：对分组结果再指定条件用 having

select student_number,avg(score) from achievement 
group by student_number 
having avg(score) > 80;

第十一题：查询至少选修了两门课程的学生学号

select student_number,count(course_number) from achievement 
group by student_number 
having count(course_number) >= 2;

第十二题：查询同名同姓学生名单并统计同名人数

select student_name,count(student_number) from student 
group by student_name 
having count(student_number) >= 2;

第十三题：查询分数大于60分的课程并按课程号从大到小排列

select course_number,score from achievement 
where score > 60 
group by course_number, score 
order by course_number desc;

第十四题：查询每门课程的平均成绩，结果按平均成绩升序排序，平均成绩相同时，按课程号降序排列

select course_number, avg(score) from achievement 
group by course_number 
order by avg(score) asc, course_number desc;

第十五题：检索课程编号为“C0002”且分数大于等于80的学生学号，结果按分数降序排列

select course_number, student_number, score from achievement 
where course_number = 'C0002' and score >= 80 
order by score desc;

第十六题：统计每门课程的学生选修人数(超过1人的课程才统计)，
要求输出课程号和选修人数，查询结果按人数降序排序，若人数相同，按课程号升序排序

select course_number,count(student_number) from achievement
group by course_number
having count(student_number) > 1
order by count(student_number) desc, course_number asc;

第十七题：查询两门以上及格课程的同学的学号及其平均成绩

select student_number,count(course_number),avg(score) from achievement
where score >= 60
group by student_number having count(course_number) >= 2;

第十八题：查询所有课程成绩大于等于60分学生的学号、姓名
方法一：为了查询姓名，所以连接学生表，做关联查询

select distinct achievement.student_number, student.student_name from achievement
join student on student.student_number = achievement.student_number
where achievement.score >= 60;

方法二：子查询

select student_number,student_name from student
where student_number in (
	select student_number from achievement 
	where score >= 60
);

第十九题：查询没有学全所有课的学生的学号、姓名

select student_number,student_name from student
where student_number in (select student_number from achievement
                         group by student_number
                         having count(course_number) < 
                         (select count(course_number) from course));

第二十题：查询出只选修了两门课程的全部学生的学号和姓名

select student_number,student_name from student
where student_number in (select student_number from achievement
                         group by student_number
                         having count(course_number) = 2);

第二十一题：查找1990年出生的学生名单

select * from student where year(student_birth) = '1990';

第二十二题：查询各科成绩前两名的记录
limit 2 弊端：成绩相同时，前两名实际人数会 > 2;

待测试

第二十三题：查询所有学生的学号、姓名、选课数、总成绩
注意：查询所有学生，所以采用外连接的时候要注意student要放置在左边，否则没有选课的学生信息就无法显示出来了。

-- 解法一：先查出成绩表，并分组，再与学生表进行关联查询
select S.student_number,S.student_name, T.totalCourse,T.totalScore from student S
    left join (select student_number,count(course_number) as totalCourse, sum(score) as totalScore
                    from achievement group by student_number) T
        on T.student_number = S.student_number;

-- 解法二：由于学号是主键，并且按照学号分组，可以直接关联学生表
select s.student_number, s.student_name, count(a.course_number),sum(a.score)
from student s left join achievement a
    on s.student_number = a.student_number
group by s.student_number;

第二十四题：查询平均成绩大于85的所有学生的学号、姓名和平均成绩
此题与上一题解法类似，添加了分组后再过滤（关键字 having）

-- 解法一：先分组查询出结果集，再连接student表，查询出学生名
-- 注意：右联是以右表为主要表，右表存在的字段，都会在结果中显示出来。
select S.student_number,S.student_name,T.avgScore from student S
    right join (select student_number,avg(score) as avgScore
                from achievement
                group by student_number
                having avg(score) > 85) T
    ON S.student_number = T.student_number;

-- 解法二：直接左联，再用having过滤小于85和NULL的成绩
select s.student_number,s.student_name,avg(a.score) from student s 
	left join achievement a on s.student_number = a.student_number 
	group by s.student_number having avg(score) > 85;

第二十五题：查询学生的选课情况：学号，姓名，课程号，课程名称

select s.student_number, s.student_name, c.course_number,c.course_name from student s
    join achievement a on s.student_number = a.student_number
    join course c on a.course_number = c.course_number;

第二十六题：查询出每门课程的及格人数和不及格人数

select course_number,sum(case when score >=60 then 1 else 0 end) as 及格人数,
       sum(case when score < 60 then 1 else 0 end) as 不及格人数
from achievement group by course_number;

第二十七：使用分段[100-90],[89-70],[69-60],[<60]来统计各科成绩，分别统计：各分数段人数，课程号和课程名称

select a.course_number,c.course_name,
       sum(case when a.score <= 100 and a.score >= 90 then 1 else 0 end) as `100-90`,
       sum(case when a.score <= 89 and a.score >= 80 then 1 else 0 end) as `89-80`,
       sum(case when a.score <= 79 and a.score >= 70 then 1 else 0 end) as `79-70`,
       sum(case when a.score <= 69 and a.score >= 60 then 1 else 0 end) as `69-60`,
       sum(case when a.score < 60 then 1 else 0 end) as `<60`
from achievement a
    join course c on a.course_number = c.course_number
group by a.course_number;

第二十八题：查询课程编号为0003且课程成绩在80分以上的学生的学号和姓名

select s.student_number,s.student_name,a.score from student s
    join achievement a on s.student_number = a.student_number
where a.course_number = 'C0003'
   and a.score > 80;

第二十九题：下面是学生的成绩表，achievement

编写SQL，使之一下面这种方式显示出来

解：
此题解法参考：https://mp.weixin.qq.com/s/6Kll4Q6Xp37i2PiLUh4cMA
第一步：

select student_number '学号','课程号C0001','课程号C0002','课程号C0003' from achievement;

第二步：

select student_number '学号',
        (case when course_number = 'C0001' then score else 0 end ) as '课程号C0001',
        (case when course_number = 'C0002' then score else 0 end ) as '课程号C0002',
        (case when course_number = 'C0003' then score else 0 end ) as '课程号C0003'
from achievement;

第三步：

select student_number '学号',
       max(case when course_number = 'C0001' then score else 0 end ) as '课程号C0001',
       max(case when course_number = 'C0002' then score else 0 end ) as '课程号C0002',
       max(case when course_number = 'C0003' then score else 0 end ) as '课程号C0003'
from achievement group by student_number;

第三十题：未完待续。。。。