SQL面试题总结、解答

重新整理练习一下常见的SQL面试题，按照经常出现的大题来分

我一直认为SQL不知道怎么写是因为：一些需求语句无法直译为SQL，必须转换方向翻译

这和中英互译是差不多的，如果按照字面意思，大部分语句都会翻译错误

1. 学生 - 课程数据库

有3张表，分别为

• 学生表：student(s_no, s_name, s_sex, s_age, s_dept)
• 课程表：course(c_no, c_name, c_pno, c_teacher, c_credit)
• 学生选课表：sc(s_no, c_no, grade)

具体SQL

SET FOREIGN_KEY_CHECKS=0;DROP TABLE IF EXISTS `student`;CREATE TABLE `student` (    `s_no` VARCHAR(9) PRIMARY KEY COMMENT '学号',    `s_name` VARCHAR (20) COMMENT '姓名',    `s_sex` CHAR(1) COMMENT '性别',    `s_age` TINYINT COMMENT '年龄',    `s_dept` VARCHAR (10) COMMENT '所在系');DROP TABLE IF EXISTS `course`;CREATE TABLE `course` (    `c_no` INT PRIMARY KEY auto_increment COMMENT '课程号',    `c_name` VARCHAR (20) COMMENT '课程名',    `c_pno` INT DEFAULT NULL COMMENT '先行课',    `c_teacher` VARCHAR(20) NOT NULL COMMENT '任课老师',    `c_credit` TINYINT COMMENT '学分',    CONSTRAINT `fk_course_cpno_course_cno` FOREIGN KEY (`c_pno`) REFERENCES `course` (`c_no`) ON DELETE CASCADE);DROP TABLE IF EXISTS `sc`;CREATE TABLE `sc` (    `s_no` VARCHAR(9) COMMENT '学号',    `c_no` INT COMMENT '课程号',    `grade` TINYINT COMMENT '成绩',    PRIMARY KEY (`s_no`, `c_no`),    CONSTRAINT `fk_sc_sno_student_sno` FOREIGN KEY (`s_no`) REFERENCES `student` (`s_no`) ON DELETE CASCADE,    CONSTRAINT `fk_sc_cno_course_cno` FOREIGN KEY (`c_no`) REFERENCES `course` (`c_no`) ON DELETE CASCADE);-- 插入数据INSERT INTO `student` VALUES ('1', '李勇', '男', 20, 'CS'),('2', '刘晨', '女', 19, 'CS'),('3', '王敏', '女', 18, 'MA'),('4', '张国立', '男', 19, 'IS');INSERT INTO `course` (`c_name`, `c_pno`, `c_teacher`, `c_credit`) VALUES ('数据库', 5, '李明', 4),('数学', NULL, '司马', 2),('信息系统', 1, '冯', 4),('操作系统', 6, '冯', 3),('数据结构', 7, '张', 4),('数据处理', NULL, '李明', 2),('C语言', 6, '周', 4);INSERT INTO `sc` VALUES ('1', 1, 92),('1', 2, 85),('1', 3, 88),('2', 1, 90),('2', 2, 90),('2', 3, 80);SET FOREIGN_KEY_CHECKS=1;

1. 找出没有选修过“李明”老师讲授课程的所有学生姓名

1. 转义：找选修过“李明”老师的学生，继而查询不在(not in)其中的学生

   SELECT T1.s_nameFROM student T1WHERE  T1.s_no NOT IN (      SELECT DISTINCT          T2.s_no      FROM          sc T2      LEFT JOIN course T3 ON T2.c_no = T3.c_no      WHERE          T3.c_teacher = '李明' );

2. 列出有二门以上(含两门)不及格课程的学生姓名及其平均成绩

1. 直译即可

   SELECT T2.s_name, AVG(T1.grade)FROM sc T1INNER JOIN student T2 ON T1.s_no = T2.s_noWHERE T1.s_no IN (     SELECT         s_no     FROM         student     WHERE         T1.grade < 60     GROUP BY         T1.s_no     HAVING         COUNT(`s_no`) > 1 );

3. 列出既学过“1”号课程，又学过“2”号课程的所有学生姓名

1. 在where里筛选1或者2的所有学生，按照学号分组，聚合统计个数如果是2那么就是所需

   SELECT T2.s_nameFROM sc T1INNER JOIN student T2 ON T1.s_no = T2.s_noWHERE T1.c_no IN (1, 2)GROUP BY T2.s_noHAVING COUNT(T1.c_no) = 2;

4. 列出“1”号课成绩比“2”号同学该门课成绩高的所有学生的学号

1. 直译即可

   SELECT s_noFROM sc T1WHERE T1.c_no = 1AND T1.grade > ( SELECT     grade FROM     sc WHERE     s_no = 2 AND c_no = 1);

5. 列出“1”号课成绩比“2”号课成绩高的所有学生的学号及其“1”号课和“2”号课的成绩

1. 转义：首先，分别查出1号课和2号课的两个关系；接着，连接两个关系，where条件第1个关系中grade大于等2个关系的grade

   SELECT T1.s_no, T1.grade '1号课成绩', T2.grade '2号课成绩'FROM (SELECT * FROM sc WHERE c_no = 1) T1 INNER JOIN  (SELECT * FROM sc WHERE c_no = 2) T2 ON T1.s_no = T2.s_noWHERE T1.grade > T2.grade;

6. 查询每门课程成绩都大于80分学生的姓名

1. 利用group by 和 min() SQL Aggregate 函数
   （转义：最小的课程分数大于80）

   SELECT  T1.s_nameFROM   student T1RIGHT JOIN sc T2 ON T1.s_no = T2.s_noGROUP BY T1.s_no HAVING MIN(T2.grade) > 80;

2. 利用嵌套查询
   （转义：找出有分数小于等于80的学生，继而查询不在(not in)其中的学生）

   SELECT DISTINCT T1.s_nameFROM student T1RIGHT JOIN sc T2 ON T1.s_no = T2.s_noWHERE T1.s_no NOT IN (    SELECT DISTINCT        s_no    FROM        sc     WHERE        grade <= 80);