常见的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;

在这里插入图片描述

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

©️2020 CSDN 皮肤主题: 书香水墨 设计师:CSDN官方博客 返回首页