面试题: 经典50题"/>
常见的SQL面试题: 经典50题
题目参考:
.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 achievementgroup by student_numberhaving count(course_number) < (select count(course_number) from course));
第二十题:查询出只选修了两门课程的全部学生的学号和姓名
select student_number,student_name from student
where student_number in (select student_number from achievementgroup by student_numberhaving 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 Sleft join (select student_number,count(course_number) as totalCourse, sum(score) as totalScorefrom achievement group by student_number) Ton 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 aon 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 Sright join (select student_number,avg(score) as avgScorefrom achievementgroup by student_numberhaving avg(score) > 85) TON 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 sjoin achievement a on s.student_number = a.student_numberjoin 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 ajoin 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 sjoin achievement a on s.student_number = a.student_number
where a.course_number = 'C0003'and a.score > 80;
第二十九题:下面是学生的成绩表,achievement
编写SQL,使之一下面这种方式显示出来
解:
此题解法参考:
第一步:
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;
第三十题:未完待续。。。。
更多推荐
常见的SQL面试题: 经典50题
发布评论