SQL语句面试题经典50题（二）：复杂查询【图文并茂，附答案】

￡神魔★判官ぃ 2024-02-05 21:00 29阅读 0赞

## 表分析 ##

已知有如下4张表：

学生表：student(学号,学生姓名,出生年月,性别)

成绩表：score(学号,课程号,成绩)

课程表：course(课程号,课程名称,教师号)

教师表：teacher(教师号,教师姓名)

*  1.学生表  
    Student(s\_id,s\_name,s\_birth,s\_sex) —学生编号,学生姓名, 出生年月,学生性别
 *  2.课程表  
    Course(c\_id,c\_name,t\_id) – —课程编号, 课程名称, 教师编号
 *  3.教师表  
    Teacher(t\_id,t\_name) —教师编号,教师姓名
 *  4.成绩表  
    Score(s\_id,c\_id,s\_score) —学生编号,课程编号,分数

根据以上信息按照下面要求写出对应的SQL语句。

ps：这些题考察SQL的编写能力，对于这类型的题目，需要你先把4张表之间的关联关系搞清楚了，最好的办法是自己在草稿纸上画出关联图，然后再编写对应的SQL语句就比较容易了。下图是我画的这4张表的关系图，可以看出它们之间是通过哪些外键关联起来的：

[![img][]][img 1]

## 创建数据库和表 ##

为了演示题目的运行过程，我们先按下面语句在客户端navicat中创建数据库和表。

![img][img 2]

![img][img 3]

-- 建表
    -- 学生表
    CREATE TABLE `Student`(
    	`s_id` VARCHAR(20),
    	`s_name` VARCHAR(20) NOT NULL DEFAULT '',
    	`s_birth` VARCHAR(20) NOT NULL DEFAULT '',
    	`s_sex` VARCHAR(10) NOT NULL DEFAULT '',
    	PRIMARY KEY(`s_id`)
    );
    -- 课程表
    CREATE TABLE `Course`(
    	`c_id`  VARCHAR(20),
    	`c_name` VARCHAR(20) NOT NULL DEFAULT '',
    	`t_id` VARCHAR(20) NOT NULL,
    	PRIMARY KEY(`c_id`)
    );
    -- 教师表
    CREATE TABLE `Teacher`(
    	`t_id` VARCHAR(20),
    	`t_name` VARCHAR(20) NOT NULL DEFAULT '',
    	PRIMARY KEY(`t_id`)
    );
    -- 成绩表
    CREATE TABLE `Score`(
    	`s_id` VARCHAR(20),
    	`c_id`  VARCHAR(20),
    	`s_score` INT(3),
    	PRIMARY KEY(`s_id`,`c_id`)
    );
    -- 插入学生表测试数据
    insert into Student values('01' , '赵雷' , '1990-01-01' , '男');
    insert into Student values('02' , '钱电' , '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' , '女');
    -- 课程表测试数据
    insert into Course values('01' , '语文' , '02');
    insert into Course values('02' , '数学' , '01');
    insert into Course values('03' , '英语' , '03');
    
    -- 教师表测试数据
    insert into Teacher values('01' , '张三');
    insert into Teacher values('02' , '李四');
    insert into Teacher values('03' , '王五');
    
    -- 成绩表测试数据
    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);

### Student表： ###

[![image-20191212102315915][]][image-20191212102315915 1]

### Course表： ###

[![image-20191212102510760][]][image-20191212102510760 1]

### Teacher表： ###

[![Teacher][]][Teacher 1]

### Score表： ###

[![Score][]][Score 1]

## 复杂查询 ##

### 查询所有课程成绩小于60分学生的学号、姓名 ###

第一种做法：将学号分组 在每个学号组 找成绩最高值<60的学号

SELECT score.s_id,student.s_name
    FROM score
    INNER JOIN student
    ON score.s_id = student.s_id
    GROUP BY s_id
    HAVING MAX(s_score) <60

![image-20191215011833878][]

第二种解法：

step1: 查询出学生课程数的统计量

step2: 查询出学生课程成绩<60分对应课程数的统计量

SELECT A.s_id,S.s_name
    FROM
    
    (SELECT s_id,COUNT(c_id) AS cnt
    FROM score
    GROUP BY score.s_id) AS A
    
    INNER JOIN
    
    (SELECT s_id,COUNT(c_id) AS cnt
    FROM score
    WHERE s_score<60
    GROUP BY s_id) AS B
    
    ON A.s_id = B.s_id
    
    INNER JOIN student AS S
    ON A.s_id = S.s_id
    
    WHERE A.cnt = B.cnt

![image-20191216100937996][]

### 查询没有学全所有课的学生的学号、姓名 ###

-- 第一种解法：
    -- 首先学号分组  做having条件：每个学号统计出不同课程数量  小于 课程表中不同课程数量
    -- 上面结果为没有学全所有课程的学号
    -- 然后在学生表中  WHERE s_id IN ()条件  查找出学号、姓名。（这一步也可以用 INNER JOIN ON。）
    
    SELECT s_id,s_name FROM student
    WHERE s_id IN
    (
    SELECT s_id
    FROM score
    GROUP BY s_id
    HAVING COUNT(DISTINCT c_id) < (SELECT COUNT(DISTINCT c_id) FROM course)
    )
    
    
    -- 这种解法后来发现score表会存在一些学生没有成绩。比如说王菊同学一门课程成绩都不存在。
    -- 但是上述做法没有把王菊同学查找出来。

--------------------

links：[https://enfangzhong.github.io/2019/12/03/SQL%E8%AF%AD%E5%8F%A5%E9%9D%A2%E8%AF%95%E7%BB%8F%E5%85%B850%E9%A2%98][https_enfangzhong.github.io_2019_12_03_SQL_E8_AF_AD_E5_8F_A5_E9_9D_A2_E8_AF_95_E7_BB_8F_E5_85_B850_E9_A2_98]

[img]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/30/d332399fc5e841f88fcd651b7ad0faa5.png
[img 1]: https://enfangzhong.github.io/2019/12/03/SQL%E8%AF%AD%E5%8F%A5%E9%9D%A2%E8%AF%95%E7%BB%8F%E5%85%B850%E9%A2%98/v2-86fd263583a6cead51675982c1735e68_hd.jpg
[img 2]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/30/cbf672b24df0429bb1beb81258590744.png
[img 3]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/30/bac983dd41e64fea9e3808e03082909a.png
[image-20191212102315915]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/30/7659d08f038a4c279087f5aee206287c.png
[image-20191212102315915 1]: https://enfangzhong.github.io/2019/12/03/SQL%E8%AF%AD%E5%8F%A5%E9%9D%A2%E8%AF%95%E7%BB%8F%E5%85%B850%E9%A2%98/image-20191212102315915.png
[image-20191212102510760]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/30/3fe9ca1ce413468a98d0e3de01f863d6.png
[image-20191212102510760 1]: https://enfangzhong.github.io/2019/12/03/SQL%E8%AF%AD%E5%8F%A5%E9%9D%A2%E8%AF%95%E7%BB%8F%E5%85%B850%E9%A2%98/image-20191212102510760.png
[Teacher]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/30/bd53b904636a459d88c984154779686c.png
[Teacher 1]: https://enfangzhong.github.io/2019/12/03/SQL%E8%AF%AD%E5%8F%A5%E9%9D%A2%E8%AF%95%E7%BB%8F%E5%85%B850%E9%A2%98/image-20191212102618628.png
[Score]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/30/3c1d4ac05db543aa88065ddff6a55c03.png
[Score 1]: https://enfangzhong.github.io/2019/12/03/SQL%E8%AF%AD%E5%8F%A5%E9%9D%A2%E8%AF%95%E7%BB%8F%E5%85%B850%E9%A2%98/image-20191212102654572.png
[image-20191215011833878]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/30/d0b79b136cb94b4f9c8d590e02c34b2c.png
[image-20191216100937996]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/30/10f07d09fa5e4d57909f04b4c0a6d48c.png
[https_enfangzhong.github.io_2019_12_03_SQL_E8_AF_AD_E5_8F_A5_E9_9D_A2_E8_AF_95_E7_BB_8F_E5_85_B850_E9_A2_98]: https://enfangzhong.github.io/2019/12/03/SQL%E8%AF%AD%E5%8F%A5%E9%9D%A2%E8%AF%95%E7%BB%8F%E5%85%B850%E9%A2%98