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_name
   FROM
    student T1
   WHERE
     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 T1
   INNER JOIN student T2 ON T1.s_no = T2.s_no
   WHERE
    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_name
   FROM
    sc T1
   INNER JOIN student T2 ON T1.s_no = T2.s_no
   WHERE
    T1.c_no IN (1, 2)
   GROUP BY
    T2.s_no
   HAVING
    COUNT(T1.c_no) = 2;

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

1. 直译即可

   SELECT
    s_no
   FROM
    sc T1
   WHERE
    T1.c_no = 1
   AND 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_no
   WHERE T1.grade > T2.grade;

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

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

   SELECT
     T1.s_name
   FROM
      student T1
   RIGHT JOIN sc T2 ON T1.s_no = T2.s_no
   GROUP BY T1.s_no HAVING MIN(T2.grade) > 80;

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

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