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

网络投稿 02-07 4967

文章目录 0. 后续（2）-（3）1. 表格2. 题目3. 题目 + 解答1、查询课程编号为“01”的课程比“02”的课程成绩高的所有学生的学号和成绩2、查询平均成绩大于60分的学生的学号和平均成绩2.1、所有成绩小于60分的学生信息2.2、查询平均成绩小于60分的学生的学号和平均成绩，考虑没参加考试的情况3、查询所有学生的学号、姓名、选课数、总成绩4、查询姓“猴”的老师的个数5、查询没学过“张三”老师课的学生的学号、姓名6、查询学过“张三”老师所教的所有课的同学的学号、姓名7、查询学过编号为“01”的课程并且也学过编号为“02”的课程的学生的学号、姓名（！）7.1、查询学过编号为“01”的课程但没有学过编号为“02”的课程的学生的学号、姓名（！）8、查询课程编号为“02”的总成绩9、查询所有课程成绩小于60分的学生的学号、姓名10、查询没有学全所有课的学生的学号、姓名（！）11、查询至少有一门课与学号为“01”的学生所学课程相同的学生的学号和姓名

题目来源：超经典SQL练习题，做完这些你的ＳＱＬ就过关了（时间比较久，可能是原出处）

工具：Navicat For MySQL

0. 后续（2）-（3） SQL 经典50题（题目+解答）（2）SQL 经典50题（题目+解答）（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; 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; 2、查询平均成绩大于60分的学生的学号和平均成绩 SELECT s_id, AVG(s_score) avg_score FROM Score GROUP BY s_id HAVING avg_score > 60; 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 的思路 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;

另解：

# 使用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 字段的别名（突破执行顺序的羁绊！）） 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 4、查询姓“猴”的老师的个数 SELECT COUNT(t_name) FROM Teacher WHERE t_name LIKE "猴%" 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 = "张三" ) 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 ); 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; 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 8、查询课程编号为“02”的总成绩 SELECT SUM(s_score) FROM Score GROUP BY c_id -- WHERE c_id = '02' # 考察 HAVING，聚合条件限制不能使用WHERE HAVING c_id = '02' 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 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 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' # 将自己排除