50道SQL练习题(持续更新中)

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

SELECT
	a.s_id,
	a.s_score 
FROM
	score a
	JOIN score b ON a.s_id = b.s_id 
	AND a.c_id = '01' 
	AND b.c_id = '02' 
WHERE
	a.s_score > b.s_score

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

select s.s_id, avg(s.s_score) avg_score from score s GROUP BY s_id HAVING avg_score > 60

02-2、所有成绩小于60分的学生信息

select * from student where s_id in(select s.c_id from score s GROUP BY s.s_id HAVING MAX(s_score) < 60)

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

SELECT
	s.s_id '学号',
	s.s_name '姓名',
	COUNT( sc.c_id ) '课程数量',
	SUM(
	IFNULL( sc.s_score, 0 )) '总成绩' 
FROM
	student s
	LEFT JOIN score sc ON s.s_id = sc.s_id 
GROUP BY
	sc.s_id

04、查询姓“李”的老师的个数

select COUNT(*) from teacher where t_name LIKE '李%'

05、查询学过"张三"老师授课的同学的信息

SELECT
	s.* 
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 s ON sc.s_id = s.s_id 
WHERE
	t.t_name = '张三'

06、查询没有学过"张三"老师授课的同学的信息

SELECT
	* 
FROM
	student 
WHERE
	s_id NOT IN (
	SELECT
		s.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 s ON sc.s_id = s.s_id 
	WHERE
	t.t_name = '张三')

07、查询没有学过"张三"老师授课的同学的信息

SELECT
	* 
FROM
	student 
WHERE
	s_id NOT IN (
	SELECT
		s.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 s ON sc.s_id = s.s_id 
	WHERE
	t.t_name = '张三')

08、查询学过编号为“01”的课程且学过编号为“02”的课程的学生的学号、姓名

SELECT
	stu.* 
FROM
	student stu
	INNER JOIN score s1 ON stu.s_id = s1.s_id
	INNER JOIN score s2 ON s1.s_id = s2.s_id 
WHERE
	s1.c_id = '01' 
	AND s2.c_id = '02'

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

SELECT
	* 
FROM
	student stu 
WHERE
	EXISTS ( SELECT 1 FROM score s1 WHERE stu.s_id = s1.s_id AND s1.c_id = '01' ) 
	AND NOT EXISTS ( SELECT 1 FROM score s1 WHERE stu.s_id = s1.s_id AND s1.c_id = '02' )

10、查询没有学全所有课程的同学的信息

SELECT * FROM student WHERE
	s_id IN (
	SELECT s_id FROM score GROUP BY s_id HAVING COUNT( c_id ) < 
		( SELECT COUNT( DISTINCT ( c_id )) FROM course ) 
	)

11、查询至少有一门课与学号为"01"的同学所学相同的同学的信息

SELECT DISTINCT( s.s_id ), s.* 
FROM
	student s
	INNER JOIN score sc ON s.s_id = sc.s_id 
WHERE
	s.s_id NOT IN ( '01' ) 
	AND c_id IN ( SELECT c_id FROM score WHERE s_id = '01' )

12、查询和"01"号的同学学习的课程完全相同的其他同学的信息

SELECT * FROM student WHERE s_id IN (
	SELECT st.s_id FROM
		(SELECT s1.s_id FROM score s1 left JOIN score s2 ON s1.c_id = s2.c_id AND s2.s_id = '01' ) st 
	GROUP BY s_id HAVING
	  COUNT(*) = ( SELECT COUNT(*) FROM score WHERE s_id = '01' ) and s_id != '01'
	)

13、查询没学过"张三"老师讲授的任一门课程的学生姓名

select * from student stu where
not EXISTS
(
	select 1 from score s where s.s_id = stu.s_id and s.c_id in
	(
		select c.c_id from course c INNER JOIN teacher t on c.t_id = t.t_id where t.t_name = '张三'
	)
)

14、查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩

SELECT
	stu.s_id,
	stu.s_name,
	AVG( s.s_score ) 
FROM
	student stu
	INNER JOIN score s ON stu.s_id = s.s_id 
WHERE
	stu.s_id IN ( SELECT s_id FROM score WHERE s_score < 60 GROUP BY s_id HAVING COUNT( s_id ) >= 2 ) 
GROUP BY
	stu.s_id

15、检索"01"课程分数小于60，按分数降序排列的学生信息

select stu.* from student stu INNER JOIN score sc on stu.s_id = sc.s_id where sc.c_id = '01' and sc.s_score < 60 order by sc.s_score desc

16、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩

select s2.*, s1.avgScore from (select s_id, avg(s_score) avgScore from score GROUP BY s_id order by avgScore desc) s1 LEFT JOIN score s2 on s1.s_id = s2.s_id

17、查询各科成绩最高分、最低分和平均分：

以如下形式显示：课程ID，课程name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率
（及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90）

select s.c_id '课程ID', c.c_name '课程名', min(s.s_score) '最低分', avg(s.s_score) '平均分',
CONCAT(ROUND(100 * sum(case when s.s_score >= 60 then 1 else 0 end) / count(s.s_score),2), '%') '及格率',
CONCAT(ROUND(100 * sum(case when s.s_score > 60 and s.s_score < 80 then 1 else 0 end) / count(s.s_score),2), '%') '中等率',
CONCAT(ROUND(100 * sum(case when s.s_score >= 90 then 1 else 0 end) / count(s.s_score),2), '%') '优良率'
from score s LEFT JOIN course c on s.c_id = c.c_id GROUP BY s.c_id

18、按各科成绩进行排序，并显示排名

select ROW_NUMBER() OVER(PARTITION BY s.c_id order by s.s_score desc), s.* from score s

19、按各科成绩进行排序，并显示排名

SELECT
	ROW_NUMBER() over ( ORDER BY s.t_score DESC ),
	s.* 
FROM
	( SELECT s_id, sum( s_score ) 't_score' FROM score GROUP BY s_id ) s

20、查询不同老师所教不同课程平均分从高到低显示

SELECT
	t.t_name,
	c.c_name,
	savg.avgScore 
FROM
	teacher t
	LEFT JOIN course c ON t.t_id = c.t_id
	LEFT JOIN ( SELECT AVG( s.s_score ) 'avgScore', s.* FROM score s GROUP BY s.c_id ) savg ON c.c_id = savg.c_id 
ORDER BY
	savg.avgScore DESC

21、查询所有课程的成绩第2名到第3名的学生信息及该课程成绩

SELECT sr.* FROM
	(
	SELECT
		DENSE_RANK() over ( PARTITION BY s.c_id ORDER BY s.s_score desc) 'rank',
		s.c_id,
		c.c_name,
		st.*
	FROM
		score s
	INNER JOIN student st ON s.s_id = st.s_id INNER JOIN course c on s.c_id = c.c_id
	) sr where sr.rank >= 2 and sr.rank <=3;

22、统计各科成绩各分数段人数：课程编号,课程名称,[100-85],[85-70],[70-60],[0-60]及所占百分比

SELECT c.c_id,c.c_name, 
SUM(CASE WHEN sc.s_score < 100 and sc.s_score >= 85 THEN 1 ELSE 0 END) '100-85',
CONCAT(ROUND(SUM(CASE WHEN sc.s_score < 100 and sc.s_score >= 85 THEN 1 ELSE 0 END) / COUNT(*) * 100, 2), '%') '100-85占比',
SUM(CASE WHEN sc.s_score < 85 and sc.s_score >= 70 THEN 1 ELSE 0 END) '85-70',
SUM(CASE WHEN sc.s_score < 70 and sc.s_score >= 60 THEN 1 ELSE 0 END) '70-60',
SUM(CASE WHEN sc.s_score < 60 and sc.s_score >= 0 THEN 1 ELSE 0 END) '0-60'
from course c INNER JOIN score sc on c.c_id = sc.c_id GROUP BY c_id

23、查询学生平均成绩及其名次

SELECT DENSE_RANK() over (order by ss.avgScore desc) 'rank', ss.*
FROM
	(
	SELECT st.s_id, st.s_name, AVG( s_score ) avgScore FROM student st
	LEFT JOIN score s ON st.s_id = s.s_id GROUP BYs.s_id 
	) ss

24、查询各科成绩前三名的记录（不考虑成绩并列情况）

SELECT * FROM
	( SELECT ROW_NUMBER() over ( PARTITION BY s.c_id ORDER BY s.s_score DESC ) 'rank', s.* FROM score s ) srank 
WHERE srank.rank IN (1,2,3)

25、查询每门课程被选修的学生数

select c.c_name '课程', COUNT(sc.s_id) from score sc INNER JOIN course c on sc.c_id = c.c_id GROUP BY sc.c_id

26、查询出只有两门课程的全部学生的学号和姓名

select s.s_id,s.s_name from student s where s.s_id in
(
	select s_id from score GROUP BY s_id HAVING COUNT(c_id) = 2
);

27、查询男生、女生人数

select s_sex '性别', COUNT(*) '人数' from student GROUP BY s_sex

28、查询名字中含有"风"字的学生信息

select * from student where s_name LIKE '%风%'

29、查询同名同性别学生名单，并统计同名人数

select s_name,COUNT(*) from student GROUP BY s_name,s_sex HAVING COUNT(*) > 1

30、查询1990年出生的学生名单

select * from student where YEAR(s_birth) = '1990'

31、查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩

select s.s_id,s.s_name,avg(sc.s_score) avgScore from student s JOIN score sc on s.s_id = sc.s_id GROUP BY sc.s_id HAVING avgScore >= 85

32、查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列

select c_id,AVG(s_score) avgScore from score GROUP BY c_id order by avgScore desc,c_id asc;

33、查询课程名称为"数学"，且分数低于60的学生姓名和分数

select s.s_name,sc.s_score from student s INNER JOIN score sc on s.s_id = sc.s_id INNER JOIN course c on sc.c_id = c.c_id where sc.s_score < 60 and c.c_name = '数学'

34、查询所有学生的课程及分数情况（存在学生没成绩，没选课的情况）

SELECT
	s.s_name,
	c.c_name,
	sc.s_score 
FROM
	student s
	LEFT JOIN score sc ON s.s_id = sc.s_id
	INNER JOIN course c ON sc.c_id = c.c_id

35、查询任何一门课程成绩在70分以上的姓名、课程名称和分数

-- 方式1
select s.s_name,c.c_name,sc.s_score from student s INNER JOIN score sc on s.s_id = sc.s_id INNER JOIN course c on sc.c_id = c.c_id
where s.s_id in(
	select s1.s_id from 
		(select s_id, COUNT(c_id) cNum from score GROUP BY s_id) s1 
		INNER JOIN
		(select s_id, COUNT(c_id) c70Num from score where s_score > 70 GROUP BY s_id) s2 
		on s1.s_id = s2.s_id and s1.cNum = s2.c70Num
)
-- 方式2
select s.s_name,c.c_name,sc.s_score from student s INNER JOIN score sc on s.s_id = sc.s_id INNER JOIN course c on sc.c_id = c.c_id
where s.s_id in(
		select s_id FROM score GROUP BY s_id HAVING MIN(s_score) > 70
)

36、查询学生不及格的课程并按课程号从大到小排列

select * from score where s_score < 60 order by c_id desc

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

select stu.s_id,stu.s_name,sc.* from score sc INNER JOIN student stu on sc.s_id = stu.s_id where sc.c_id = '03' and sc.s_score > 80

38、求每门课程的学生人数

select c_id,COUNT(s_id) from score GROUP BY c_id

39、查询选修“张三”老师所授课程的学生中成绩最高的学生姓名及其成绩

SELECT
	stu.s_name,
	MAX( sc.s_score ) 
FROM
	score sc
	INNER JOIN teacher t ON sc.c_id = t.t_id
	INNER JOIN student stu ON sc.s_id = stu.s_id 
WHERE
	t.t_name = '张三'

40、查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩

SELECT
	s1.s_id,
	s1.c_id,
	s1.s_score,
	s2.s_id,
	s2.c_id,
	s2.s_score 
FROM
	score s1
	INNER JOIN score s2 ON s1.s_score = s2.s_score 
WHERE
	s1.s_id != s2.s_id 
	AND s1.c_id != s2.c_id

41、查询存在不同课程但成绩相同的学生的学生编号、课程编号、学生成绩

SELECT
	s1.s_id,
	s1.c_id,
	s1.s_score,
	s2.c_id,
	s2.c_id 
FROM
	score s1
	INNER JOIN score s2 ON s1.s_id = s2.s_id 
WHERE
	s1.s_score = s2.s_score 
	AND s1.c_id != s2.c_id

42、查询每门课程成绩最好的前两名

select sc.*,stu.s_name from student stu INNER JOIN(
SELECT
	s.c_id,
	RANK() over ( PARTITION BY s.c_id ORDER BY s.s_score DESC ) 'rank',
	s.s_id,
	s.s_score 
FROM
	score s) sc on stu.s_id = sc.s_id where sc.rank <= 2

43、统计每门课程的学生选修人数（超过5人的课程才统计）。要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列

SELECT
	c_id,
	COUNT( s_id ) 'stuNumber' 
FROM
	score 
GROUP BY
	c_id 
HAVING
	stuNumber > 5 
ORDER BY
	stuNumber DESC,
	c_id

44、检索至少选修两门课程的学生学号

select s_id from score GROUP BY s_id HAVING COUNT(c_id) >= 2

45、查询选修了全部课程的学生信息

select s_id from score GROUP BY s_id HAVING COUNT(c_id) >= (select COUNT(c_id) from course)

46、查询各学生的年龄（精确到月份）

SELECT
	s_id,
	s_name,
	CONCAT( TIMESTAMPDIFF( YEAR, s_birth, CURDATE()), '岁',( TIMESTAMPDIFF( MONTH, s_birth, CURDATE()) % 12 ), '月' ) 
FROM
	student

47、查询没学过“张三”老师讲授的任意一门课程的学生姓名

SELECT
	stu.* 
FROM
	student stu 
WHERE
	NOT EXISTS (
	SELECT
		1 
	FROM
		score s
		INNER JOIN course c ON s.c_id = c.c_id
		INNER JOIN teacher t ON c.t_id = t.t_id 
	WHERE
		t.t_name = '张三' 
	AND s.s_id = stu.s_id 
	)

48、查询两门以上不及格课程的同学的学号及其平均成绩

select s_id,AVG(s_score) 'avgScore' from score where s_id
in(
select s_id from score where s_score < 60 GROUP BY s_id HAVING COUNT(c_id) > 2
)

49、查询下月过生日的学生

SELECT * FROM student 
WHERE
	MONTH ( s_birth ) = IF(MONTH (CURDATE()) = 12, 1,MONTH (CURDATE()) + 1)

50、查询下周过生日的学生

SELECT * 
FROM student 
WHERE WEEK(s_birth) = (SELECT WEEK(CURRENT_DATE + INTERVAL 1 WEEK));

SQL素材

CREATE TABLE `course`  (
  `c_id` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `c_name` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `t_id` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  PRIMARY KEY (`c_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_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');

CREATE TABLE `course`  (
  `c_id` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `c_name` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `t_id` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  PRIMARY KEY (`c_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_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');

CREATE TABLE `student`  (
  `s_id` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `s_name` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `s_birth` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `s_sex` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  PRIMARY KEY (`s_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_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', '女');


CREATE TABLE `teacher`  (
  `t_id` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `t_name` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '',
  PRIMARY KEY (`t_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

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