SQL练习题

sql文件

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for course
-- ----------------------------
DROP TABLE IF EXISTS `course`;
CREATE TABLE `course`  (
  `c_id` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `c_name` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `t_id` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  PRIMARY KEY (`c_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of course
-- ----------------------------
INSERT INTO `course` VALUES ('01', '语文', '02');
INSERT INTO `course` VALUES ('02', '数学', '01');
INSERT INTO `course` VALUES ('03', '英语', '03');

-- ----------------------------
-- Table structure for score
-- ----------------------------
DROP TABLE IF EXISTS `score`;
CREATE TABLE `score`  (
  `s_id` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `c_id` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `s_score` int NULL DEFAULT NULL,
  PRIMARY KEY (`s_id`, `c_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of score
-- ----------------------------
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);

-- ----------------------------
-- Table structure for student
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student`  (
  `s_id` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `s_name` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `s_birth` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `s_sex` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  PRIMARY KEY (`s_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of student
-- ----------------------------
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', '女');

-- ----------------------------
-- Table structure for teacher
-- ----------------------------
DROP TABLE IF EXISTS `teacher`;
CREATE TABLE `teacher`  (
  `t_id` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `t_name` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  PRIMARY KEY (`t_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of teacher
-- ----------------------------
INSERT INTO `teacher` VALUES ('01', '张三');
INSERT INTO `teacher` VALUES ('02', '李四');
INSERT INTO `teacher` VALUES ('03', '王五');

SET FOREIGN_KEY_CHECKS = 1;

数据表关系结构

1.查询课程编号为"01"的课程比"02"的课程成绩高的学生学号、姓名和这两门课的成绩

难点在于在成绩表中将学号和两门成绩放在同一行，如果做到这一点就很容易解决问题了

先在score表分别找出课程01和02的信息，然后内连接使得01和02的信息凑成一行

然后再内连接学生表按要求查询

-- 查询课程编号为"01"的课程比"02"的课程成绩高的学生学号、姓名和这两门课的成绩
SELECT a.s_id '学号',s.s_name '姓名',a.s_score '01成绩',b.s_score '02成绩' from 
(SELECT * from score WHERE c_id = '01') a
INNER JOIN
(SELECT * from score WHERE c_id = '02') b
on a.s_id = b.s_id
INNER JOIN student s on a.s_id = s.s_id 
WHERE a.s_score > b.s_score

小bug：上述情况没考虑只修了一门课的学生如06、07号，就当语数英可选修，sql可改写成左连接，保证查到所有选修了课程01的学生

方法2：自连接

再和学生表进行多表查询

-- 查询课程编号为"01"的课程比"02"的课程成绩高的学生学号、姓名和这两门课的成绩
SELECT s.s_id '学号',s.s_name '姓名', a.s_score '01成绩',b.s_score '02成绩'FROM
score a,score b,student s
WHERE a.c_id = '01'
and b.c_id = '02'
and a.s_id = b.s_id
and s.s_id = a.s_id
and a.s_score > b.s_score

方法3：casewhen

casewhen能对每一条数据进行筛选，符合条件就返回then后的数据，否则返回null，截图太长就以三个学生的数据为例。

加上max...group by是为了提取有用的数据，按学号分组，学号01的学生01成绩的三条数据max就取80,02成绩同理取90，如此就能合成为1条数据了。

然后把整个结果当成一个表t，但是要把t内的别名都去掉，不然不好调用t的字段

-- 查询课程编号为"01"的课程比"02"的课程成绩高的学生学号、姓名和这两门课的成绩
SELECT
s.s_id '学号',
s.s_name '姓名',
t.s01 '01成绩',
t.s02 '02成绩'
FROM
(
SELECT a.s_id,
max(case when a.c_id='01' then a.s_score end) s01,
max(case when a.c_id='02' then a.s_score end) s02
from score a
GROUP BY a.s_id
HAVING s01 > s02 OR s02 is null
) t,student s
WHERE s.s_id = t.s_id

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

先以学号为分组查出score表的每个学生的平均分（round用于定义保留小数点后几位数），把结果当成a表和学生表联合查询

-- 查询平均成绩大于60分的学生学号、姓名和平均成绩
SELECT a.id '学号',b.s_name '姓名',a.score '平均成绩'
FROM
(
SELECT s_id id,ROUND(AVG(s_score),2) score
FROM score
GROUP BY s_id
) a,student b
WHERE a.id = b.s_id  AND a.score > 60

简化score表的查询结果

SELECT s_id id,ROUND(AVG(s_score),2) score
FROM score
GROUP BY s_id HAVING score > 60

用having直接对分组后的数据进行筛选，就不用嵌套查询了

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

单纯看成绩表是不能把没有选课的学生统计到的，如8号王菊

-- 查询所有学生的学号、姓名、选课数和总成绩
SELECT a.s_id '学号',a.s_name '姓名',COUNT(b.c_id) '选课数',IFNULL(SUM(b.s_score),0) '总成绩'
FROM student a
LEFT JOIN score b
ON a.s_id = b.s_id
GROUP BY b.s_id
-- ifnull处理sum结果为null时转为0

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

-- 查询姓“猴”的老师的个数
SELECT COUNT(t_id) '个数'
FROM teacher
WHERE t_name like '猴%'

5.查询没学过张三老师课的学生的学号和姓名

先在teacher表根据名字查询tid，tid能在course表中进一步查到cid，cid能在score表中查到sid，sid能在student表中查到sname。整个链条是步步关联的，所以可以联表查询。

-- 查询没学过张三老师课的学生的学号和姓名
SELECT s_id '学号',s_name '姓名'
FROM student
WHERE s_id NOT IN
(
SELECT sc.s_id FROM teacher t 
LEFT JOIN course c ON t.t_id = c.t_id
LEFT JOIN score sc ON c.c_id = sc.c_id
LEFT JOIN student st ON sc.s_id = st.s_id
WHERE t_name = '张三'
)

当然也可以一步步地嵌套查询，但太多了。

6.查询学过张三老师所有课的学生的学号和姓名

这里为了避免歧义，往course表新增张三的物理课，再在score表新增8号学生的数学物理成绩。这样就指明是查张三所有课的学生信息，而不是查学过张三课的所有学生信息，两者区别在于张三教一门课还是多门课。

如果沿用上一题的思路，查到的会是学过张三课的学生，而不是所有课的。

按理应该只有08号学生王菊符合要求,学号08的结果有两条，可通过以学号来分组，再having筛选出结果数与张三课程数相等的结果

-- 查询学过张三老师所有课的学生的学号和姓名
SELECT sc.s_id '学号',st.s_name '姓名' FROM teacher t 
LEFT JOIN course c ON t.t_id = c.t_id
LEFT JOIN score sc ON c.c_id = sc.c_id
LEFT JOIN student st ON sc.s_id = st.s_id
WHERE t_name = '张三'
GROUP BY sc.s_id
HAVING COUNT(sc.s_id) = (
	SELECT COUNT(a.c_id) 
	FROM course a 
	INNER JOIN teacher b ON a.t_id = b.t_id
	WHERE b.t_name = '张三'
)

7.查询学过01和02课程的学生学号与姓名

由于不能通过c_id = '01' and c_id = '02'查到
需要取这两个结果的交集

-- 查询学过01和02课程的学生学号与姓名
SELECT s_id '学号',s_name '姓名' 
FROM student
WHERE s_id IN
(
SELECT a.s_id
FROM
(
SELECT s_id FROM score
WHERE c_id = '01'
) a
INNER JOIN
(
SELECT s_id FROM score
WHERE c_id = '02'
) b
ON a.s_id = b.s_id
)

8.查询02号课程的总成绩

-- 查询02号课程的总成绩
SELECT *,SUM(s_score) FROM score WHERE c_id = '02'

方法2

分组，不过复杂了

-- 查询02号课程的总成绩
SELECT c_id,SUM(s_score) FROM score
GROUP BY c_id
HAVING c_id = '02'

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

为更好表现，把score表的5号学生的01课程分数从76改成56.符合要求的只有04和06

方法1

根据学号分组，再筛选出最大值小于60的学生

-- 查询所有课程成绩都小于60分的学生学号和姓名
SELECT sc.s_id '学号',s_name '姓名'
FROM score sc,student st
WHERE sc.s_id = st.s_id
GROUP BY sc.s_id
HAVING MAX(s_score) < 60

方法2

找到所学课程数与不合格课程数相等的学生

-- 查询所有课程成绩都小于60分的学生学号和姓名
SELECT a.s_id,cnt '所学课程数',cntm '不合格课程数' FROM
(
SELECT s_id,COUNT(c_id) cnt FROM score GROUP BY s_id -- 所学课程的数量
) a
INNER JOIN
(
SELECT s_id,COUNT(s_id) cntm FROM score WHERE s_score < 60 GROUP BY s_id -- 成绩小于60的课程数
) b
ON a.s_id =b.s_id
WHERE cnt = cntm

10.查询没有学全所有课的学生学号和姓名

新开个数据库，使用原sql文件建表
以学生为单位筛选a表选修课程数小于课程总数的学生

-- 查询没有学全所有课的学生学号和姓名
SELECT s_id '学号',s_name '姓名'
FROM (
SELECT st.s_id,s_name,c_id,s_score FROM student st
LEFT JOIN score sc ON st.s_id = sc.s_id
) a -- 学生表左连接成绩表，防止漏掉没选课的学生
GROUP BY s_id HAVING IFNULL(COUNT(c_id),0) < -- 以学生为单位筛选a表选修课程数小于课程总数的学生
(
SELECT COUNT(c_id) cnt FROM course -- 课程总数
)

另一种思路就是找出学过全部课程的学号然后反选

11.查询至少有一门课与学号01的学生所学课程相同的其他学生学号和姓名

-- 查询至少有一门课与学号01的学生所学课程相同的其他学生学号和姓名
SELECT DISTINCT sc.s_id '学号',s_name '姓名'
FROM score sc,student st
WHERE c_id
IN 
(
SELECT c_id FROM score WHERE s_id = '01'
) 
AND sc.s_id != '01' AND sc.s_id = st.s_id

12.查询与学号01的学生所学课程完全相同的其他学生学号和姓名

为突出题目效果，在score表中给7号学生新增4号课程的成绩74分

-- 查询与学号01的学生所学课程完全相同的其他学生学号和姓名
SELECT s_id,s_name FROM student WHERE s_id IN
(
SELECT s_id FROM score
WHERE s_id != '01'
GROUP BY s_id HAVING COUNT(c_id) = (
SELECT COUNT(c_id) FROM score WHERE s_id = '01' -- 01学生所学课程数
) -- 找出学了跟01学生学了同等数量课程的其他学生
)
AND s_id NOT IN -- 排除肯定不与01的学生所学课程相同的学生
( 
SELECT DISTINCT s_id FROM score WHERE c_id NOT IN -- 找出相比01所选课程选了其他课的学生
(
SELECT c_id FROM score WHERE s_id = '01' -- 学号01的学生所学课程
)
)

13.查询两门及其以上课程不及格的学生学号、姓名和平均成绩

-- 查询两门及其以上课程不及格的学生学号、姓名和平均成绩
SELECT a.s_id '学号',s_name '姓名',b.avg '平均分' FROM student a
INNER JOIN
(
SELECT s_id,ROUND(AVG(s_score),1) avg 
FROM score 
WHERE s_score < 60
GROUP BY s_id
HAVING COUNT(c_id) >= 2
) b
ON a.s_id = b.s_id

14.查询01课程小于60分的学生信息并按分数降序排列

-- 查询01课程小于60分的学生信息并按分数降序排列
SELECT b.*,a.s_score FROM
(
SELECT s_id,s_score FROM score WHERE c_id = '01' AND s_score < 60 -- 01课程小于60分的学生
) a
INNER JOIN student b
ON a.s_id = b.s_id
ORDER BY a.s_score DESC

15.查询所有学生的所有课程成绩以及平均分并按平均分从高到低排列

方法1

-- 查询所有学生的所有课程成绩以及平均分并按平均分从高到低排列
SELECT a.*,b.avg FROM
(
SELECT st.s_id,c_id,s_score FROM student st LEFT JOIN score sc ON st.s_id = sc.s_id
) a -- 避免没选课的学生被漏掉
INNER JOIN
(
SELECT s_id,ROUND(AVG(s_score),1) avg FROM score GROUP BY s_id
) b -- 各学生的平均分
ON a.s_id = b.s_id
ORDER BY avg DESC

方法2

仅适用于课程数较少的情况，因为这种方法需要手动行转列，有几门课就要写几次case when

-- 查询所有学生的所有课程成绩以及平均分并按平均分从高到低排列
SELECT s_id,
MAX(CASE WHEN c_id = '01' THEN s_score ELSE NULL END) '语文',
-- GROUP BY后利用case when行转列，但出于规范在GROUP BY中查询的应该是统计函数相关的
-- 所以用max或者min也行
MAX(CASE WHEN c_id = '02' THEN s_score ELSE NULL END) '数学',
MAX(CASE WHEN c_id = '03' THEN s_score ELSE NULL END) '英语',
MAX(CASE WHEN c_id = '04' THEN s_score ELSE NULL END) '物理',
ROUND(AVG(s_score),1) '平均分'
FROM
(SELECT st.s_id,c_id,s_score FROM student st LEFT JOIN score sc ON st.s_id = sc.s_id) a  -- 避免没选课的学生被漏掉
GROUP BY s_id

16.查询各科成绩最高分、最低分、平均分和及格率

关键在于巧用外连接把score缺少的各科目合格数pass连接上去

-- 查询各科成绩最高分、最低分、平均分和及格率
SELECT c_id '课程id',MAX(s_score) '最高分',
MIN(s_score) '最低分',
ROUND(AVG(s_score),1) '平均分',
(CASE WHEN pass/COUNT(c_id) THEN CONCAT(ROUND((pass/COUNT(c_id)*100),1),'%')ELSE CONCAT(0,'%') END) '合格率' -- concat将数值以%形式显示
-- 判断条件可以不写成IFNULL(pass,0)/COUNT(a.c_id)，因为当case when的条件是null时，会去到else的分支
FROM
(
SELECT a.*,b.pass FROM score a LEFT JOIN
(
SELECT c_id,COUNT(c_id) pass FROM score WHERE s_score >= 60 GROUP BY c_id -- 统计出每门课合格的数量
) b -- 不包含所有成绩都不合格的课程，所以要left join和select要用case when显示合格率结果
ON a.c_id = b.c_id
) c
GROUP BY c_id

17.按各科成绩排序，并显示排名

如图是三个常用的排序类窗口函数，row_number(),rank(),dense_rank()
row_number()是连续的，即使同分排名也会有先后。
dense_rank()在同分的情况下排名相同，下一个不同分的排名+1
rank()在同分的情况下排名相同，但下一个不同分的排名+同分的数量（是第几个人就排几个）

-- 按各科成绩排序，并显示排名
SELECT sc.s_id '学号',s_name '姓名',c_name '科目',s_score '成绩',
RANK() OVER(PARTITION BY c_name ORDER BY s_score DESC) '排名' FROM score sc -- 按rank()的方式以科目为分组，根据成绩降序排列
INNER JOIN course c ON sc.c_id = c.c_id
INNER JOIN student st ON sc.s_id = st.s_id

18.查询学生总成绩并降序排名

新增9号学生陈空，体现没有选课的情形

不需要partition by

-- 查询学生总成绩并降序排名
SELECT s_id,SUM(score) total,RANK() OVER(ORDER BY SUM(score) DESC) 'rank' FROM
(
SELECT st.s_id,c_id,IFNULL(s_score,0) score FROM student st LEFT JOIN score sc ON st.s_id = sc.s_id -- 避免漏掉没成绩的学生
) a
GROUP BY s_id

19.查询各老师所教课程的平均分，从高到低显示

数据表只涉及一个老师教多门课程的情况，如果后续有一门课程有多个老师的情况再补充sql

-- 查询各老师所教课程的平均分，从高到低显示
SELECT t_name '姓名',c_name '课程',ROUND(AVG(a.s_score),1) '平均分'FROM
(
SELECT t.t_id,t_name,c.c_id,c_name,s.s_id,s_score FROM teacher t
LEFT JOIN course c ON t.t_id = c.t_id
LEFT JOIN score s ON c.c_id = s.c_id
) a
GROUP BY t_id,c_id -- 以联合老师id和课程id为分组
ORDER BY AVG(a.s_score) DESC

20.查询每门课程的成绩在第二和第三名的学生信息及该课程成绩

先排名再筛选
用in筛选范围

-- 查询每门课程的成绩在第二和第三名的学生信息及该课程成绩
SELECT * FROM
(
SELECT st.*,sc.c_id,c_name,s_score,
DENSE_RANK() OVER(PARTITION BY sc.c_id ORDER BY s_score DESC) 'rank'
FROM score sc
INNER JOIN student st ON sc.s_id = st.s_id
INNER JOIN course c ON c.c_id = sc.c_id
) a
WHERE a.rank in (2,3)

21.使用分段[100-85),[85-70),[70-60),[<60]来统计各科成绩，分别统计各分数段人数

统计人数用sum或者count，而要显示多个分数段就需要多列，用casewhen更合适

-- 使用分段[100-85),[85-70),[70-60),[<60]来统计各科成绩，分别统计各分数段人数
SELECT c.c_id,c.c_name,
SUM(CASE WHEN s.s_score <= 100 AND s.s_score > 85 THEN 1 ELSE 0 END) '(85,100]',
-- 85-100分数段的人数，case when来判断，sum来统计
COUNT(CASE WHEN s.s_score <= 85 AND s.s_score > 70 THEN 1 ELSE NULL END) '(70,85]',
-- 第二种写法count只统计not null的，0也算，所以else要写null
SUM(CASE WHEN s.s_score <= 70 AND s.s_score > 60 THEN 1 ELSE 0 END) '(60,70]', 
SUM(CASE WHEN s.s_score <= 60 AND s.s_score > 0 THEN 1 ELSE 0 END) '(0,60]'
FROM score s
INNER JOIN course c ON s.c_id= c.c_id
GROUP BY c.c_id,c.c_name -- 为了显示课程名就联合分组了

22.查询学生平均成绩及其名次

-- 查询学生平均成绩及其名次
SELECT s_id,s_name,ROUND(AVG(score),1) avg,
RANK() OVER(ORDER BY AVG(score) DESC) 'rank'
FROM
(
SELECT st.s_id,s_name,c_id,IFNULL(s_score,0) score FROM student st LEFT JOIN score sc ON st.s_id = sc.s_id
) a
GROUP BY s_id

23.查询每门课被选修的学生数

新增5号生物课，模拟存在有的课没人选的情况

-- 查询每门课被选修的学生数
SELECT c.c_id,c_name,COUNT(s_id)
FROM score s
RIGHT JOIN course c ON s.c_id = c.c_id -- 避免漏掉没人选的课
GROUP BY c.c_id,c_name -- 如果只以c_id分组，mysql中没错，sql sever会报错

24.查询只有两门课的学生学号和姓名

-- 查询只有两门课的学生学号和姓名
SELECT sc.s_id,s_name FROM score sc
INNER JOIN student st ON sc.s_id = st.s_id
GROUP BY s_id HAVING COUNT(c_id) = 2

25.查询男女生人数

-- 查询男女生人数
SELECT s_sex,COUNT(s_id) FROM student GROUP BY s_sex
-- 不分组的话可以用sum或者count结合case when

26.查询名字含有“风”的学生信息

-- 查询名字含有“风”的学生信息
SELECT * FROM student WHERE s_name LIKE '%风%'

27.查询1990年出生的学生信息

两种方法，1是like，2是时间函数

-- 查询1990年出生的学生信息
SELECT * FROM student WHERE s_birth LIKE '1990%'

SELECT * FROM student WHERE YEAR(s_birth) = '1990' -- YEAR(date)函数能返回date格式的年份值
-- Date格式YYYY-MM-DD
-- YYYYMMDD
-- YYYY/MM/DD

SELECT MONTH('2022-05-23 15:30:00');  -- 结果为5，Month(date)函数能返回date格式的月份值

SELECT DAY('2022-05-23 15:30:00'); -- 结果为23，Day(date)函数能返回date格式的日份值

select sysdate(); -- 2023-07-23 18:29:03 获得当前日期+时间

select curdate(); -- 2023-07-23 获取当前日期

select curtime(); -- 18:30:58 获取当前时间

SELECT NOW() -- 2023-07-23 18:29:15 当前UTC日期时间函数

SELECT DATE(NOW()) -- 2023-07-28 年月日

28.查询平均成绩大于等于85的所有学生的信息

-- 查询平均成绩大于等于85的所有学生的信息
SELECT st.*,ROUND(AVG(s_score),1) avg FROM score sc
INNER JOIN student st ON st.s_id = sc.s_id
GROUP BY s_id HAVING AVG(s_score) >= 85

29.查询每门课的平均分，按平均分升序排序，同分则按课程号降序排列

-- 查询每门课的平均分，按平均分升序排序，同分则按课程号降序排列
SELECT s.c_id,c_name,ROUND(AVG(s_score),1) avg FROM score s
INNER JOIN course c ON c.c_id = s.c_id
GROUP BY c_id ORDER BY AVG(s_score),c_id DESC

30.查询数学成绩不及格（<60）的学生姓名和分数

-- 查询数学成绩不及格（<60）的学生姓名和分数
SELECT c_name,s_name,s_score FROM score sc
INNER JOIN course c ON sc.c_id = c.c_id
INNER JOIN student st ON st.s_id = sc.s_id
WHERE c_name = '数学' AND s_score < 60

31.查询所有学生的各课程成绩

暂时把course的04号生物课删掉

-- 查询所有学生的各课程成绩
SELECT st.s_id,st.s_name,
MAX(CASE WHEN c.c_name = '语文' THEN sc.s_score ELSE NULL END) '语文',
MAX(CASE WHEN c.c_name = '数学' THEN sc.s_score ELSE NULL END) '数学',
MAX(CASE WHEN c.c_name = '英语' THEN sc.s_score ELSE NULL END) '英语',
MAX(CASE WHEN c.c_name = '物理' THEN sc.s_score ELSE NULL END) '物理'
FROM student st
LEFT JOIN score sc ON st.s_id = sc.s_id
LEFT JOIN course c ON c.c_id = sc.c_id
GROUP BY st.s_id

32.查询成绩在70分以上的学生姓名、课程名称和分数

-- 查询成绩在70分以上的学生姓名、课程名称和分数
SELECT s_name,c_name,s_score FROM score sc
INNER JOIN course c ON sc.c_id = c.c_id
INNER JOIN student st ON st.s_id = sc.s_id
WHERE s_score > 70

33.查询不及格的学生及其课程，按课程号从大到小排列

-- 查询不及格的学生及其课程，按课程号从大到小排列
SELECT s_name,sc.c_id,c_name,s_score FROM score sc
INNER JOIN student st ON st.s_id = sc.s_id
INNER JOIN course c ON c.c_id = sc.c_id
WHERE s_score < 60
ORDER BY sc.c_id DESC

34.查询课程编号为03且成绩在80分以上的学生学号和姓名

-- 查询课程编号为03且成绩在80分以上的学生学号和姓名
SELECT sc.s_id,s_name,s_score FROM score sc
INNER JOIN student st ON sc.s_id = st.s_id
WHERE c_id = '03' AND s_score > 80

35.查询每门课程的学生人数

-- 查询每门课程的学生人数
SELECT c.c_id,c_name,COUNT(s_id) 'num'FROM course c
LEFT JOIN score s ON c.c_id = s.c_id
GROUP BY c.c_id

36.查询选修“张三”老师所授课程成绩最好的学生学号、姓名和课程成绩

方法1 子查询max&With

-- 查询选修“张三”老师所授课程成绩最好的学生学号、姓名和课程成绩
SELECT *
FROM
(
SELECT sc.s_id,s_name,c_name,s_score FROM course c
INNER JOIN teacher t ON c.t_id = t.t_id
INNER JOIN score sc ON sc.c_id = c.c_id
INNER JOIN student st ON st.s_id = sc.s_id
WHERE t_name = '张三'
) a
WHERE a.s_score = -- WHERE后面不能等于聚合函数如where a.s_score = MAX(a.s_score)，所以要子查询
(
SELECT MAX(s_score)
FROM a
)

可以使用with把联表查询得到的新表a设为临时内存表，这样系统就能直接select from了。
在 MySQL 中，WITH 子句通常被称为 "Common Table Expressions"（CTE），俗称内存临时表，并且是从 MySQL 8.0 版本开始引入的。

-- 查询选修“张三”老师所授课程成绩最好的学生学号、姓名和课程成绩
WITH target AS ( -- 创建一个临时内存表
SELECT sc.s_id,s_name,c_name,s_score FROM course c
INNER JOIN teacher t ON c.t_id = t.t_id
INNER JOIN score sc ON sc.c_id = c.c_id
INNER JOIN student st ON st.s_id = sc.s_id
WHERE t_name = '张三'
)
SELECT *
FROM
target
WHERE s_score = -- WHERE后面不能等于聚合函数如where a.s_score = MAX(a.s_score)，所以要子查询
(
SELECT MAX(s_score)
FROM target
)

方法2 limit

-- 查询选修“张三”老师所授课程成绩最好的学生学号、姓名和课程成绩
SELECT sc.s_id,s_name,c_name,s_score FROM course c
INNER JOIN teacher t ON c.t_id = t.t_id
INNER JOIN score sc ON sc.c_id = c.c_id
INNER JOIN student st ON st.s_id = sc.s_id
WHERE t_name = '张三'
ORDER BY s_score DESC
LIMIT 1 -- 等值于limit 0,1

37.统计每门课程的选修人数，按人数降序排列，若人数相同则按课程号升序排列

-- 统计每门课程的选修人数，按人数降序排列，若人数相同则按课程号升序排列
SELECT c.c_id,c_name,COUNT(s_id) FROM course c
LEFT JOIN score s ON c.c_id = s.c_id
GROUP BY c.c_id
ORDER BY COUNT(s_id) DESC,c.c_id

38.查询至少选修两门课的学生学号和姓名

-- 查询至少选修两门课的学生学号和姓名
SELECT sc.s_id,s_name,COUNT(c_id) FROM score sc
INNER JOIN student st ON sc.s_id = st.s_id
GROUP BY sc.s_id
HAVING COUNT(c_id) >= 2

39.查询选修全部课程的学生信息

新增9号学生，学过所有课程

UPDATE `exercises`.`student` SET `s_birth` = '1990-09-09', `s_sex` = '男' WHERE `s_id` = '09';
INSERT INTO `score`(`s_id`, `c_id`, `s_score`) VALUES ('09', '01', 91);
INSERT INTO `score`(`s_id`, `c_id`, `s_score`) VALUES ('09', '02', 92);
INSERT INTO `score`(`s_id`, `c_id`, `s_score`) VALUES ('09', '03', 93);
INSERT INTO `score`(`s_id`, `c_id`, `s_score`) VALUES ('09', '04', 94);
INSERT INTO `score`(`s_id`, `c_id`, `s_score`) VALUES ('09', '05', 95);

-- 查询选修全部课程的学生信息
SELECT st.* FROM score sc
INNER JOIN student st ON sc.s_id = st.s_id
GROUP BY st.s_id
HAVING COUNT(c_id) = (
SELECT COUNT(c_id) FROM course
)

40.查询各学生的年龄

-- 查询各学生的年龄
SELECT s_name,s_birth,YEAR(NOW())-YEAR(s_birth) age FROM student
-- 方式2 DATEDIFF(NOW(),s_birth)/365
SELECT s_name,s_birth,FLOOR(DATEDIFF(NOW(),s_birth)/365) age FROM student -- floor向下取整
-- DATEDIFF(a,b) a-b的天数差
-- SELECT DATEDIFF('2022-04-30','2022-04-29'); -- 1
-- SELECT DATEDIFF('2022-04-30','2022-04-30'); -- 0
-- SELECT DATEDIFF('2022-04-29','2022-04-30');-- -1

41.查询下周过生日的学生

题目的意思是下一周过生日，不是7天后过生日
考虑使用week函数通过对比周数来找到结果。

周数的范围从一年的总时间来划分的

-- SELECT WEEK(date[,mode])
SELECT WEEK(NOW(),1) -- 30

SELECT WEEK('1990-05-07',1) -- 19
SELECT WEEK('2000-05-07',1) -- 18
-- week(s_birth,1) = WEEK(date(NOW()),1) + 1行不通

但是这种方法对于不同年份的同一天week的结果是不同的，也对于生日在本月最后一周的情况不适用。
所以把前面生日的年份换成今年的年份，通过拼接和截取的方式换成今年的；再比较周数
不过当现在时间的周数是52的时候，无法确定下一周还是不是在今年。53的情况可以case when，当前周数53说明下一周必定在明年，下周的week值必是1

-- 查询下周过生日的学生
SELECT * FROM student WHERE WEEK(CONCAT(YEAR(NOW()),SUBSTRING(s_birth,5,6)),1) = WEEK(NOW(),1) + 1
/*
SELECT YEAR(NOW()) -- 2023
SELECT SUBSTRING(s_birth,5,6) FROM student -- 比如-01-01
SELECT CONCAT(YEAR(NOW()),SUBSTRING(s_birth,5,6)) FROM student --比如2023-01-01
SELECT WEEK(NOW(),1) -- 30
SELECT WEEK('2023-08-06',1) -- 31
*/

42.查询本月过生日的学生

-- 查询本月过生日的学生
SELECT * FROM student WHERE MONTH(s_birth) = MONTH(DATE(NOW()))
-- month返回月份值，DATE(NOW())可换成CURRENT_DATE

43.查询下个月过生日的学生

-- 查询下个月过生日的学生
SELECT * FROM student WHERE MONTH(s_birth) = MOD(MONTH(CURRENT_DATE)+1,12) -- 对12取余，解决1月生日的情况

44.冒险者和金币

假设有一家冒险者公会，他们有一张名为 rewards 的表格，用于记录每个冒险者在各个任务中获得的金币奖励情况。
表格字段如下：

• adventurer_id：冒险者ID，唯一标识每个冒险者。
• adventurer_name：冒险者姓名。
• task_id：任务ID，唯一标识每个任务。
• task_name：任务名称。
• reward_coins：冒险者在该任务中获得的金币奖励数量。
  

请你编写一条 SQL 查询语句，依次输出每个冒险者的 id（adventurer_id）、冒险者姓名（adventurer_name）、获得的总金币奖励（total_reward_coins），并按照总金币奖励从高到低排序，其中只列出总金币奖励排名前 3 的冒险者。

SELECT adventurer_id, adventurer_name,
SUM(reward_coins) AS total_reward_coins
FROM rewards
GROUP BY adventurer_id, adventurer_name
ORDER BY total_reward_coins DESC
LIMIT 3;

45.魔法学院

假设有一家魔法学院，里面有许多学员在不同科目上进行学习和考试。有一张名为magic_scores的表格，用于记录每位学员在不同科目中的考试成绩情况。表格字段如下：

• student_id：学员ID，唯一标识每位学员。
• student_name：学员姓名。
• subject_id：科目ID，唯一标识每个科目。
• subject_name：科目名称。
• score：学员在该科目的考试成绩。
  

请你编写一条 SQL 查询语句，依次输出每位学员的学院 ID（student_id）、学员姓名（student_name）、科目 ID（subject_id）、科目名称（subject_name）、学员在该科目的考试成绩（score）、该学员在每个科目中的成绩排名（score_rank），并将结果按照成绩从高到低进行排序。

select student_id,student_name,subject_id,subject_name,score,
rank() over(partition by subject_id order by score desc) as score_rank
from magic_scores

46.大浪淘鸡

在神秘的海岛上，有一只传说中的大浪淘鸡，它身躯高大威武，羽毛闪烁着神秘的光芒。岛上的居民都传说大浪淘鸡是海洋之神的化身，它能够操纵海浪，带来平静或狂暴的海洋。为了验证这个传说是否属实，岛上的居民决定对大浪淘鸡进行观测和记录。
有一张 chicken_observation 的表格，用于记录居民观测大浪淘鸡的信息。表格字段如下：

• observation_id：观测记录ID，唯一标识每条观测记录
• observer_name：观测者姓名
• observation_date：观测日期
• observation_location：观测地点
• wave_intensity：观测到的海浪强度，用整数表示，数值越大，海浪越狂暴
  

请你编写一条 SQL 查询语句，找出观测地点包含 "大浪淘鸡" 且海浪强度超过 5 的观测记录，并依次输出每位观测者的姓名（observer_name）、观测日期（observation_date）以及观测到的海浪强度（wave_intensity）。

select observer_name,observation_date,wave_intensity
from chicken_observation
where observation_location like '%大浪淘鸡%'
and wave_intensity > 5