50道SQL面试题

50道SQL面试题

���需要互关的小伙伴,关注一下,有关必回关,争取今年认证早日拿到博客专家

环境

-- ------------------------------ Table structure for teacher-- ----------------------------DROP TABLE IF EXISTS `teacher`;CREATE TABLE `teacher`(    `t_id`   varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,    `t_name` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '',    PRIMARY KEY (`t_id`) USING BTREE) ENGINE = InnoDB  CHARACTER SET = utf8mb4  COLLATE = utf8mb4_general_ci  ROW_FORMAT = Dynamic;-- ------------------------------ Records of teacher-- ----------------------------INSERT INTO `teacher` VALUES ('01', '数学老师-杰斯');INSERT INTO `teacher` VALUES ('02', '语文老师-盲僧');INSERT INTO `teacher` VALUES ('03', '英语老师-菲欧娜');-- ------------------------------ Table structure for student-- ----------------------------DROP TABLE IF EXISTS `student`;CREATE TABLE `student`(    `s_id`    varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,    `s_name`  varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '',    `s_birth` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '',    `s_sex`   varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '',    PRIMARY KEY (`s_id`) USING BTREE) ENGINE = InnoDB  CHARACTER SET = utf8mb4  COLLATE = utf8mb4_general_ci  ROW_FORMAT = Dynamic;-- ------------------------------ Records of student-- ----------------------------INSERT INTO `student` VALUES ('01', '流浪法师-瑞兹', '1990-01-01', '男');INSERT INTO `student` VALUES ('02', '探险家-EZ', '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', '女');-- ------------------------------ Table structure for score-- ----------------------------DROP TABLE IF EXISTS `score`;CREATE TABLE `score`(    `s_id`    varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,    `c_id`    varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,    `s_score` int(0)        NULL DEFAULT NULL,    PRIMARY KEY (`s_id`, `c_id`) USING BTREE) ENGINE = InnoDB  CHARACTER SET = utf8mb4  COLLATE = utf8mb4_general_ci  ROW_FORMAT = Dynamic;-- ------------------------------ Records of score-- ----------------------------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);-- ------------------------------ Table structure for course-- ----------------------------DROP TABLE IF EXISTS `course`;CREATE TABLE `course`(    `c_id`   varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,    `c_name` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '',    `t_id`   varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,    PRIMARY KEY (`c_id`) USING BTREE) ENGINE = InnoDB  CHARACTER SET = utf8mb4  COLLATE = utf8mb4_general_ci  ROW_FORMAT = Dynamic;-- ------------------------------ Records of course-- ----------------------------INSERT INTO `course` VALUES ('01', '语文', '01');INSERT INTO `course` VALUES ('02', '数学', '01');INSERT INTO `course` VALUES ('03', '英语', '03');

50道SQL面试题

-- 50道SQL面试题-- 1、查询课程编号为“01”的课程比“02”的课程成绩高的所有学生的学号（难）select c1.s_id from (select * from score where c_id=01) c1 JOIN (select * from score where c_id=02) c2 on c1.s_id = c2.S_id where c1.s_score>c2.s_score;-- 2、查询平均成绩大于60分的学生的学号和平均成绩(凑数的,分组统计having过滤)select s_id,AVG(s_score) avg_score from score GROUP BY s_id HAVING AVG(s_score)>60 ORDER BY avg_score desc;-- 3、查询所有学生的学号、姓名、选课数、总成绩(凑数的,分组统计)select s.s_id,s_name,count(sc.c_id),sum(sc.s_score) from  student s left join score sc on s.s_id=sc.s_id GROUP BY s.s_id,s_name;-- 4、查询姓“猴”的老师的个数(纯凑数的)select count(*) FROM teacher where t_name like '%杰%';-- 5、查询没学过“数学老师-杰斯”老师课的学生的学号、姓名(凑数的)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 not in (select co.c_id FROM teacher t left join course co on t.t_id=co.t_id where t_name = '数学老师-杰斯');-- 6、查询学过“数学老师-杰斯”老师所教的所有课的同学的学号、姓名(有意思,所有课程,一个`查询结果`和另一个`查询结果`有某种函数关系,注意不再是查询条件,是结果集之间的对比)select s.s_id,s.s_name from student s left join score sc on s.s_id=sc.s_id and sc.c_id in (select co.c_id from  teacher t join course co on t.t_id=co.t_id where t_name = '数学老师-杰斯')  group by s.s_id,s.s_name HAVING count(*) = (select count(*) from  teacher t join course co on t.t_id=co.t_id where t_name = '数学老师-杰斯');-- 7、查询学过编号为“01”的课程并且也学过编号为“02”的课程的学生的学号、姓名(和6是同意一类型的需要一丢丢思路)select s.s_id,s_name from student s left join score sc1 on s.s_id=sc1.s_id join score sc2 on sc1.s_id = sc2.s_id  where sc1.c_id = 01 and sc2.c_id = 02;select s_id,s_name from student where s_id in (select s1.s_id from score s1 join score s2 on s1.s_id=s2.s_id where s1.c_id = 01 and s2.c_id = 02);select s_id,s_name from student where s_id in (select s_id from score where c_id in(01,02) group by s_id having count(c_id) = 2);-- 8、查询课程编号为“02”的总成绩(简单)select sum(s_score) from score where c_id = 02;-- 9、查询所有课程成绩都小于60分的学生的学号、姓名(有意思,如果不用max可能难度不小)select s.s_id,s_name from student s left join score sc on s.s_id=sc.s_id group by s.s_id,s_name having max(sc.s_score)85;# 理想的select s.s_id,s.s_name,avg(s_score) from student s join score s2 on s.s_id = s2.s_id group by s.s_id,s.s_name having avg(s_score) > 85;-- 33、查询每门课程的平均成绩，结果按平均成绩升序排序，平均成绩相同时，按课程号降序排列(简单-凑数的吧)select c_id,avg(s_score) avg_score from score group by c_id order by avg_score desc,c_id;-- 34、查询课程名称为"数学"，且分数低于60的学生姓名和分数(纯凑数的吧)select student.s_name,s_score from student join score s on student.s_id = s.s_id join course c on s.c_id = c.c_id where c_name = '数学' and s_score 70;-- 37、查询不及格的课程并按课程号从大到小排列(简单的单表查询),题目的意思至少有一门不及格还是所有都不及格,或者学生与学生不及格的课程# 至少有一门不及格select c_id from score group by c_id having min(s_score) =2;-- 44、查询选修了全部课程的学生信息(不难)select * from student where s_id in (select s_id from score group by s_id having count(c_id)=(select count(*) from course));select s_id,(select s.s_name from student s where s.s_id=score.s_id) from score group by s_id having count(c_id)=(select count(*) from course);-- 45、查询各学生的年龄(函数)select *,(year(curdate())-year(s_birth)) from student;-- 46、查询两门以上不及格课程的同学的学号及其平均成绩(一般统计)select s_id,avg(s_score) from score where s_score =2;-- 47、查询本月过生日的学生(函数)select * from student where month(s_birth) = month(now());-- 48、查询下一个月过生日的学生(函数)select * from student where month(s_birth) = month(now())+2;

值得关注的

1、查询课程编号为“01”的课程比“02”的课程成绩高的所有学生的学号

理解:同一学生的01比02高,例如:张三的01比张三的02高

select s1.s_id,s1.c_id,s1.s_score,s2.c_id,s2.s_score from score s1 join score s2 on s1.s_id = s2.s_id where s1.c_id = 01 and s2.c_id = 02 and s1.s_score>s2.s_score;

6、查询学过“数学老师-杰斯”老师所教的所有课的同学的学号

select s.s_id,s.s_name from student s join score s2 on s.s_id = s2.s_id join course c on s2.c_id = c.c_id join teacher t on c.t_id = t.t_id where t.t_name='数学老师-杰斯' group by s.s_id,s.s_name having count(c.c_id) = (select count(*) from teacher t2 join course c2 on t2.t_id = c2.t_id where t2.t_name='数学老师-杰斯');

select s.s_id,s.s_name from student s join score sc on s.s_id=sc.s_id and sc.c_id in (select co.c_id from  teacher t join course co on t.t_id=co.t_id where t_name = '数学老师-杰斯')  group by s.s_id,s.s_name HAVING count(*) = (select count(*) from  teacher t join course co on t.t_id=co.t_id where t_name = '数学老师-杰斯');

10、查询没有学全所有课的学生的学号、姓名(和6差不多)

select s.s_id,s.s_name from student s join score s2 on s.s_id = s2.s_id group by s.s_id,s.s_name having count(s2.c_id)  
12、查询和“01”号同学所学课程完全相同的其他同学的学号（和6差不多）
 
select s_id from score where c_id in (select c_id from score where s_id = 01) and s_id != 01 group by s_id having count(c_id) = (select count(*) from score where s_id = 01);
 
select s.s_id from (select c_id from score where s_id = 01) t join score s on t.c_id=s.c_id where s.s_id!=01 group by s.s_id having count(*)= (select count(*)from score where s_id=01);
 
44、查询选修了全部课程的学生信息
 
select * from student where s_id in (select s_id from score group by s_id having count(c_id)=(select count(*) from course));
 
select s_id,(select s.s_name from student s where s.s_id=score.s_id) from score group by s_id having count(c_id)=(select count(*) from course);
 
9、查询所有课程成绩都小于60分的学生的学号、姓名(有意思,如果不用max可能难度不小)
 
select s.s_id,s_name from student s left join score sc on s.s_id=sc.s_id group by s.s_id,s_name having max(sc.s_score)