• SQL 经典50题（题目+解答）（1）

    文章目录

    0. 后续（2）-（3）

    题虽是老题，但表格和题目都是自己最近实践过的，同时使用Navicat For MySQL也实现了线下刷题，能更加直观地观察数据。题不多，但刷完对理解SQL的基础概念的理解和常用技巧的练习都有不少帮助。

    若有帮助欢迎点赞、收藏、评论~

    1. 表格

    题目涉及到四张表格：
    （后面有实现代码）

    • 学生表（Student）
      在这里插入图片描述

    • 课程表（Course）

      在这里插入图片描述

    • 教师表（Teacher）
      在这里插入图片描述

    • 成绩表（Score）
      在这里插入图片描述

    附表格创建代码：

    # Student 学生表
CREATE TABLE Student
(
s_id VARCHAR(20),
s_name VARCHAR(20) NOT NULL,
s_birth VARCHAR(20) NOT NULL, 
s_sex VARCHAR(10) NOT NULL,
PRIMARY KEY(s_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', '女');


# Course 课程表
CREATE TABLE Course
(
c_id VARCHAR(20),
c_name VARCHAR(20) NOT NULL,
t_id VARCHAR(20) NOT NULL,
PRIMARY KEY(c_id)
);

INSERT INTO Course VALUES('01', '语文', '02');
INSERT INTO Course VALUES('02', '数学', '01');
INSERT INTO Course VALUES('03', '英语', '03');


# Teacher 教师表
CREATE TABLE Teacher
(
t_id VARCHAR(20),
t_name VARCHAR(20) NOT NULL DEFAULT '',
PRIMARY KEY(t_id)
);

INSERT INTO Teacher VALUES('01', '张三');
INSERT INTO Teacher VALUES('02', '李四');
INSERT INTO Teacher VALUES('03', '王五');


# Score 分数表
CREATE TABLE Score
(
s_id VARCHAR(20),
c_id VARCHAR(20),
s_score INT(3),
PRIMARY KEY(s_id, c_id)  # 注意这里是联合主键
);

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);

# 四张表
SELECT * FROM Student;
SELECT * FROM Course;
SELECT * FROM Teacher;
SELECT * FROM Score;

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39
    • 40
    • 41
    • 42
    • 43
    • 44
    • 45
    • 46
    • 47
    • 48
    • 49
    • 50
    • 51
    • 52
    • 53
    • 54
    • 55
    • 56
    • 57
    • 58
    • 59
    • 60
    • 61
    • 62
    • 63
    • 64
    • 65
    • 66
    • 67
    • 68
    • 69
    • 70
    • 71
    • 72
    • 73
    • 74
    • 75
    • 76
    • 77
    • 78
    • 79
    • 80

    2. 题目

    （看下面）

    3. 题目 + 解答

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

    多次将Score表自连接实现将同一个字段两次使用：

    ## 自连接
SELECT st.*, m.s_score1, m.s_score2
FROM 
		(
		SELECT sc1.s_id, sc1.s_score s_score1, sc2.s_score s_score2  # 注意有两个成绩
		FROM Score sc1
		JOIN Score sc2 
		ON sc1.s_id = sc2.s_id 
			 AND sc1.c_id = '01'  #  因为是INNER JOIN 下面的条件可以不写在WHERE中
			 AND sc2.c_id = '02'
			 AND sc1.s_score > sc2.s_score
		) m
JOIN Student st 
ON m.s_id = st.s_id;

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14

    2、查询平均成绩大于60分的学生的学号和平均成绩

    SELECT s_id, AVG(s_score) avg_score 
FROM Score
GROUP BY s_id
HAVING avg_score > 60;

    • 1
    • 2
    • 3
    • 4

    2.1、所有成绩小于60分的学生信息

    SELECT st.s_id, st.s_name, st.s_birth, st.s_sex
FROM Student st
JOIN (
		SELECT s_id, MAX(s_score) min_score  # 可以对上边界来进行限制，来满足【所有】这个条件
		FROM Score
		GROUP BY s_id
		HAVING min_score < 60) t 
ON st.s_id = t.s_id
## 也可以使用 WHERE > 60 + NOT IN 的思路

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9

    2.2、查询平均成绩小于60分的学生的学号和平均成绩，考虑没参加考试的情况

    -- ## 错误：当前使用计数的方式来设置条件，无法匹配到没参加考试的情况 （考点其实应该是 LEFT JOIN）
-- SELECT  s_id,
-- 				CASE
-- 			      WHEN COUNT(s_id) = 1 THEN SUM(s_score) / 3  #注意s_score必须在聚合函数内
-- 						WHEN COUNT(s_id) = 2 THEN SUM(s_score) / 3
-- 						WHEN COUNT(s_id) = 3 THEN AVG(s_score)
-- 						ELSE 0
-- 			  END avg_score
-- FROM Score
-- GROUP BY s_id
-- HAVING avg_score < 60


# 正解 （还有更简单的方法：IFNULL(col, value)）
SELECT m.s_id, 
       AVG(m.score) avg_score
FROM 
		(
		SELECT st.s_id, 
					 IF(sc.s_score IS NULL, 0, sc.s_score) score
		FROM Student st 
		LEFT JOIN Score sc 
		ON st.s_id = sc.s_id 
		) m  # 将未参加考试的部分记零分
GROUP BY m.s_id
HAVING avg_score < 60;

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26

    另解：

    # 使用IFNULL() 一步到位
SELECT m.s_id, AVG(IFNULL(m.s_score, 0)) avg_score
FROM 
		(
		SELECT st.s_id, sc.s_score
		FROM Student st 
		LEFT JOIN Score sc 
		ON st.s_id = sc.s_id
		) m 
GROUP BY m.s_id
HAVING AVG(IFNULL(m.s_score, 0)) < 60  
-- HAVING avg_score < 60  # why this also OK ！！（记住HAVING 可以使用SELECT 字段的别名（突破执行顺序的羁绊！））

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12

    3、查询所有学生的学号、姓名、选课数、总成绩

    -- ## 错误：没有考虑到可能有学生完全没有选课，应该使用LEFT JOIN
-- SELECT st.s_id, st.s_name, COUNT(sc.c_id), SUM(sc.s_score)
-- FROM Student st 
-- JOIN Score sc 
-- ON st.s_id = sc.s_id
-- GROUP BY st.s_id, st.s_name


## 正解
SELECT st.s_id, st.s_name, COUNT(sc.c_id), SUM(sc.s_score)
FROM Student st 
LEFT JOIN Score sc 
ON st.s_id = sc.s_id
GROUP BY st.s_id, st.s_name

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14

    4、查询姓“猴”的老师的个数

    SELECT COUNT(t_name)
FROM Teacher
WHERE t_name LIKE "猴%"

    • 1
    • 2
    • 3

    5、查询没学过“张三”老师课的学生的学号、姓名

    -- ## 错误（没选课程和选了课程的同学都没找出来）
-- SELECT DISTINCT st.s_id, st.s_name   # 注意 DISTINCT 去重
-- FROM Student st
-- JOIN Score sc 
-- ON st.s_id = sc.s_id 
-- JOIN Course c
-- ON sc.c_id = c.c_id
-- JOIN Teacher t 
-- ON c.t_id = t.t_id
-- WHERE t.t_name != "张三"


## 正解：【没有】这个条件可以使用 NOT IN
SELECT st.s_id, st.s_name
FROM  Student st 
WHERE s_id NOT IN 
			(
			SELECT sc.s_id 
			FROM Score sc
			JOIN Course c 
			ON sc.c_id = c.c_id 
			JOIN Teacher t
			ON c.t_id = t.t_id
			WHERE t.t_name = "张三"
			)

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25

    6、查询学过“张三”老师所教的所有课的同学的学号、姓名

    （后来觉得这里用count的方法会更好）

    ## 有点难度，想不过来就很难【自连接的情况】
SELECT st.s_id, st.s_name
FROM Student st 
WHERE st.s_id IN 
			(
			SELECT DISTINCT sc.s_id
			FROM
					(SELECT c.c_id
					FROM Course c
					JOIN Teacher t 
					ON c.t_id = t.t_id
					WHERE t.t_name = "张三") s  # “张三”老师所教的所有课
			LEFT JOIN Score sc
			ON s.c_id = sc.c_id
			WHERE sc.s_id IS NOT NULL    
			);

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16

    7、查询学过编号为“01”的课程并且也学过编号为“02”的课程的学生的学号、姓名（！）

    -- ## 不严谨的奇葩解法
-- SELECT st.s_id, st.s_name
-- FROM (
-- 			SELECT s_id, Group_CONCAT(c_id) c_str
-- 			FROM Score 
-- 			GROUP BY s_id) t  # 分组进行进行行合并
-- JOIN Student st 
-- ON t.s_id = st.s_id
-- WHERE c_str LIKE '%01%' AND c_str LIKE '%02%'
-- 


# 正解：自连接
SELECT st.s_id, st.s_name
FROM Student st 
JOIN 
		(
		SELECT sc1.* 
		FROM Score sc1 
		JOIN Score sc2 
		ON sc1.s_id = sc2.s_id
		WHERE sc1.c_id = '01'     # 这里不需要使用IN，也不需要纠结顺序问题，因为两张表都是Score
					AND sc2.c_id = '02'
		) m 
ON st.s_id = m.s_id;

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25

    7.1、查询学过编号为“01”的课程但没有学过编号为“02”的课程的学生的学号、姓名（！）

    ## 同样的奇葩解法
-- SELECT DISTINCT st.s_id, st.s_name  ## 注意使用DISTINCT
-- FROM (
-- 			SELECT s_id, Group_CONCAT(c_id) c_str
-- 			FROM Score 
-- 			GROUP BY s_id) t  # 分组进行进行行合并
-- JOIN Student st 
-- ON t.s_id = st.s_id
-- WHERE c_str LIKE '%01%' AND c_str NOT LIKE '%02%'


## 正解：自连接
SELECT DISTINCT st.s_id, st.s_name  ## 注意使用DISTINCT
FROM Student st 
JOIN 
		(
		SELECT sc1.* 
		FROM Score sc1
		JOIN Score sc2 
		ON sc1.c_id = '01'   ## 无关次序
			 AND sc2.c_id != '02'
		) m
ON st.s_id = m.s_id

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23

    8、查询课程编号为“02”的总成绩

    SELECT SUM(s_score)
FROM Score
GROUP BY c_id
-- WHERE c_id = '02'  # 考察 HAVING，聚合条件限制不能使用WHERE
HAVING c_id = '02'

    • 1
    • 2
    • 3
    • 4
    • 5

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

    ## 【所有】这个条件使用边界值进行限定
SELECT DISTINCT st.s_id, st.s_name
FROM Student st 
JOIN 
		(
		SELECT s_id, 
					 MIN(s_score) min_score
		FROM Score s
		GROUP BY s.s_id
		HAVING min_score < 60
		) s # 满足条件的学生
ON st.s_id = s.s_id

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12

    10、查询没有学全所有课的学生的学号、姓名 （！）

    ## 这题用LEFT JOIN也不好使
SELECT DISTINCT st.s_id, st.s_name
FROM Student st 
JOIN 
		(
		SELECT m.s_id
		FROM (
					SELECT s_id, COUNT(c_id) cnt 
					FROM Score
					GROUP BY s_id
				 ) m
		WHERE m.cnt != (SELECT COUNT(c_id) FROM Course)
		) n  # 子查询注意都要使用别名
ON st.s_id = n.s_id

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14

    11、查询至少有一门课与学号为“01”的学生所学课程相同的学生的学号和姓名

    -- ## 错误：误解题意+没有排除自己
-- SELECT st.s_id, st.s_name
-- FROM 
-- 		(
-- 		SELECT DISTINCT s_id
-- 		FROM  Score
-- 		WHERE c_id = '01'
-- 		) m  # 至少有一门课与学号为“01”的学生id
-- JOIN Student st 
-- WHERE m.s_id = st.s_id



SELECT DISTINCT st.s_id, st.s_name 
FROM Student st
JOIN Score sc
ON st.s_id = sc.s_id 
WHERE sc.c_id IN
			(SELECT c_id FROM Score WHERE s_id = '01') 
			AND sc.s_id != '01'  # 将自己排除

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20

    先自我介绍一下，小编13年上师交大毕业，曾经在小公司待过，去过华为OPPO等大厂，18年进入阿里，直到现在。深知大多数初中级java工程师，想要升技能，往往是需要自己摸索成长或是报班学习，但对于培训机构动则近万元的学费，着实压力不小。自己不成体系的自学效率很低又漫长，而且容易碰到天花板技术停止不前。因此我收集了一份《java开发全套学习资料》送给大家，初衷也很简单，就是希望帮助到想自学又不知道该从何学起的朋友，同时减轻大家的负担。添加下方名片，即可获取全套学习资料哦

  • 相关阅读:
    jdk+tomcat+mysql+war打包整合成exe文件，Windows下一键安装
    图片懒加载
    2024年FPGA可以进吗
    Go invalid memory address or nil pointer dereference错误 空指针问题
    如何借助边缘智能网关打造智慧城市便民驿站
    数据库系统概论第五版（笔记+习题答案）（全）
    Keycloak服务开发-认证服务SPI
    微信小程序基于java实现v2支付，提现，退款
    Linux操作系统从BIOS到bootloader是如何运行的
    Mac 上 VMvare 虚拟机 Centos 上的 Docker 容器中的文件夹共享到 Mac 实体机
  • 原文地址：https://blog.csdn.net/m0_67401499/article/details/126117671