SQL的45题练习

本人很久之前学习过SQL语句，后来忘差不多了，于是在网上找SQL练习题，然后看到SQL45题，原链接地址
->https://blog.csdn.net/flycat296/article/details/63681089

原作者使用SQLServer数据库，本copy后改成MySQL数据库，使用8.0.18，下面给出SQL脚本

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for course
-- ----------------------------
DROP TABLE IF EXISTS `course`;
CREATE TABLE `course` (
  `id` varchar(10) DEFAULT NULL,
  `Cname` varchar(10) DEFAULT NULL,
  `TId` varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of course
-- ----------------------------
INSERT INTO `course` VALUES ('1', '语文', '2');
INSERT INTO `course` VALUES ('2', '数学', '1');
INSERT INTO `course` VALUES ('3', '英语', '3');

-- ----------------------------
-- Table structure for sc
-- ----------------------------
DROP TABLE IF EXISTS `sc`;
CREATE TABLE `sc` (
  `SId` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  `CId` varchar(10) DEFAULT NULL,
  `score` int(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of sc
-- ----------------------------
INSERT INTO `sc` VALUES ('1', '1', '80');
INSERT INTO `sc` VALUES ('1', '2', '90');
INSERT INTO `sc` VALUES ('1', '3', '99');
INSERT INTO `sc` VALUES ('2', '1', '70');
INSERT INTO `sc` VALUES ('2', '2', '60');
INSERT INTO `sc` VALUES ('2', '3', '80');
INSERT INTO `sc` VALUES ('3', '1', '80');
INSERT INTO `sc` VALUES ('3', '2', '80');
INSERT INTO `sc` VALUES ('3', '3', '80');
INSERT INTO `sc` VALUES ('4', '1', '50');
INSERT INTO `sc` VALUES ('4', '2', '30');
INSERT INTO `sc` VALUES ('4', '3', '20');
INSERT INTO `sc` VALUES ('5', '1', '76');
INSERT INTO `sc` VALUES ('5', '2', '87');
INSERT INTO `sc` VALUES ('6', '1', '31');
INSERT INTO `sc` VALUES ('6', '3', '34');
INSERT INTO `sc` VALUES ('7', '2', '89');
INSERT INTO `sc` VALUES ('7', '3', '98');

-- ----------------------------
-- Table structure for student
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
  `id` varchar(10) DEFAULT NULL,
  `Sname` varchar(10) DEFAULT NULL,
  `Sage` date DEFAULT NULL,
  `Ssex` varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO `student` VALUES ('1', '赵雷', '1990-01-01', '男');
INSERT INTO `student` VALUES ('2', '钱电', '1990-12-21', '男');
INSERT INTO `student` VALUES ('3', '孙风', '1990-05-20', '男');
INSERT INTO `student` VALUES ('4', '李云', '1990-08-06', '男');
INSERT INTO `student` VALUES ('5', '周梅', '1991-12-01', '女');
INSERT INTO `student` VALUES ('6', '吴兰', '1992-03-01', '女');
INSERT INTO `student` VALUES ('7', '郑竹', '1989-07-01', '女');
INSERT INTO `student` VALUES ('8', '王菊', '1990-01-20', '女');

-- ----------------------------
-- Table structure for teacher
-- ----------------------------
DROP TABLE IF EXISTS `teacher`;
CREATE TABLE `teacher` (
  `id` varchar(10) DEFAULT NULL,
  `Tname` varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of teacher
-- ----------------------------
INSERT INTO `teacher` VALUES ('1', '张三');
INSERT INTO `teacher` VALUES ('2', '李四');
INSERT INTO `teacher` VALUES ('3', '王五');

然后是题目答案：由于原作者的题目和答案分开，不方便查看，所以本人将题目以及对应的SQL语句放在一块，并对一些题目删改（主要是偷懒）

第一题：查询" 1 “课程比” 2 "课程成绩高的学生的信息及课程分数

SELECT s.*, sc1.cid as '课程一', sc1.score as '课程一分数', sc2.cid '课程二', sc2.score as '课程二分数'
from(
	SELECT *
	from sc
	WHERE CId=1) sc1
join (
	SELECT *
	from sc
	WHERE CId=2) sc2
on sc1.score>sc2.score JOIN student s on sc1.sid=s.id

第二题：查询同时有课程" 1 “和课程” 2 "的同学

SELECT * from (SELECT * from sc WHERE CId=1) a
JOIN (SELECT * from  sc WHERE CId=2) b on a.sid=b.sid

第三题：查询存在" 1 “课程但可能不存在” 2 "课程的情况(不存在时显示为 null )

SELECT * from (SELECT * from sc WHERE CId=1) a
	LEFT JOIN (SELECT * from  sc WHERE CId=2) b on a.sid=b.sid

第三题：查询存在" 2 “课程但可能不存在” 1 "课程的情况(不存在时显示为 null )

SELECT * from (SELECT * from sc WHERE CId=1) a
RIGHT JOIN (SELECT * from  sc WHERE CId=2) b on a.sid=b.sid

第四题：查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩

SELECT s.id, s.Sname, s.Sage, s.Ssex,sc1.avgScore
FROM student s	LEFT JOIN (SELECT *,AVG(score)as 'avgScore'
	from sc
	GROUP BY SId) sc1 on sc1.SId=s.id
WHERE sc1.avgScore>=60

第五题：查询在 SC 表存在成绩的学生信息

SELECT DISTINCT(sid), s.Sname, s.Sage, s.Ssex
FROM sc sc1 JOIN student s on sc1.SId=s.id

第六题：查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩的显示为 null )

SELECT s.id, s.Sname, s.Sage, s.Ssex, a.courseNum as '选课数量', a.scores as '总分'
FROM student s LEFT JOIN
(SELECT sid, COUNT(cid) as 'courseNum', SUM(score) as 'scores'
from sc GROUP BY SId) a on a.sid=s.id

第七题：查询「李」姓老师的数量 (此题考察模糊查询)

SELECT COUNT(Tname) as 李姓老师数量
FROM teacher
WHERE Tname LIKE '李%'

第八题：查询学过「张三」老师授课的同学的信息

SELECT sc1.sid, s.Sname, s.Sage, s.Ssex FROM sc sc1 join 
	(SELECT id from teacher WHERE Tname='张三') t on sc1.CId=t.id 
	LEFT JOIN student s on sc1.SId=s.id

第九题：查询没有学全所有课程的同学的信息

SELECT *
from student
WHERE id NOT in (SELECT sid from sc
GROUP BY SId HAVING COUNT(CId) = (SELECT count(id) from course))

第十题：查询至少有一门课与学号为" 01 "的同学所学相同的同学的信息

SELECT sc1.SId, s.Sname, s.Sage, s.Ssex
from sc sc1  JOIN student s on sc1.SId=s.id
WHERE sc1.Sid!=1 AND sc1.Cid in (SELECT CId FROM sc WHERE SId = 1)
GROUP BY SId

第十一题：查询和" 01 "号的同学学习的课程完全相同的其他同学的信息 (跟上面一题类似，只不过加上了一个Having子句筛选)

SELECT sc1.SId, s.Sname, s.Sage, s.Ssex
from sc sc1  JOIN student s on sc1.SId=s.id
WHERE sc1.Sid!=1 AND sc1.Cid in (SELECT CId FROM sc WHERE SId = 1)
GROUP BY SId
HAVING count(CId)>=3

第十二题：查询没学过"张三"老师讲授的任一门课程的学生姓名

SELECT * from student 
WHERE id not in (SELECT Sid
	from sc
	WHERE CId in (SELECT id from course WHERE TId = (SELECT id FROM teacher WHERE Tname='张三'))
	GROUP BY SId)

第十三题：查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩

SELECT s.*,AVG(s1.score) '平均成绩' 
from sc s1 LEFT JOIN student s on s1.SId=s.id
WHERE score<60
GROUP BY SId
HAVING COUNT(CId)>1

第十四题：检索" 01 "课程分数小于 60，按分数降序排列的学生信息

SELECT s.*, a.score
FROM sc a LEFT JOIN student s on a.SId=s.id
WHERE a.CId=1 AND a.score<60
ORDER BY a.score DESC

第十五题：按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩**（可能本人版本不对，只出现一个sum函数可以正确查询，但3个sum函数一起出现，不知道为什么就不能用了）**

SELECT SId,
	SUM(CASE CId WHEN CId=1 THEN score ELSE NULL END) as '课程1',
	SUM(CASE CId WHEN CId=2 THEN score ELSE NULL END) as '课程2',
	SUM(CASE CId WHEN CId=3 THEN score ELSE NULL END) as '课程3',
	AVG(score) as '平均成绩'
FROM sc
GROUP BY SId
ORDER BY score DESC

第十六题：查询各科成绩最高分、最低分和平均分：以如下形式显示：课程 ID，课程 name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率
及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90
要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列

SELECT CId,c.Cname,MAX(score) '最高分',MIN(score) '最低分',AVG(score) '平均分',
	COUNT(IF(score>=60 ,TRUE,NULL))/COUNT(score) as '及格率',
	COUNT(IF(score>=70 AND score<80,TRUE,NULL))/COUNT(score) as '中等率',
	COUNT(IF(score>=80 AND score<90,TRUE,NULL))/COUNT(score) as '优良率',
	COUNT(IF(score>=90,TRUE,NULL))/COUNT(score) as '优秀率'
FROM sc s JOIN course c on s.CId=c.id
GROUP BY CId

第十七题：按各科成绩进行排序，并显示排名， Score 重复时保留名次空缺
（此句有问题，改成：列出每个有成绩的学生的总成绩和总成绩排名，学生姓名和各科成绩排名）

SELECT RANK()over(ORDER BY a.total DESC) total_rank , a.total, a.SId, s.Sname, b.rank1, c.rank2, d.rank3
	FROM (SELECT SId,sum(score) as total FROM sc GROUP BY SId) as a
	LEFT JOIN student s on a.SId=s.id
	LEFT JOIN( 
		select sid, rank() over(partition by cid order by score desc) as 'rank1' 
		from sc where cid=1) b on a.SId=b.sid
	LEFT JOIN( 
		select sid, rank() over(partition by cid order by score desc) as 'rank2' 
		from sc where cid=2) c on a.SId=c.sid
	LEFT JOIN( 
		select sid, rank() over(partition by cid order by score desc) as 'rank3' 
		from sc where cid=3) d on a.SId=d.sid

******注：rank()函数不去重，dense_rank()去重，此处使用的时候是让有相同的成绩的人有相同的排名

第十八题：列出每个有成绩的学生的总成绩和总成绩排名（是上面一题的简化版本）

SELECT RANK()over(ORDER BY a.total DESC) total_rank , a.total, a.SId, s.Sname
	FROM (SELECT SId,sum(score) as total FROM sc GROUP BY SId) as a
	LEFT JOIN student s on a.SId=s.id

第十九题：统计各科成绩各分数段人数：课程编号，课程名称，[100-85]，[85-70]，[70-60]，[60-0] 及所占百分比
解题，因为SQL语句过多，所以写前两个字段

SELECT s.CId, COUNT(s.score) '总人数', a.`[100-85]人数`, a.`百分比`, b.`[85-70]人数`,b.`百分比`
	FROM sc s
	LEFT JOIN (
		SELECT s.CId,COUNT(s.score) '[100-85]人数', COUNT(s.score)/s1.zong '百分比'
		FROM sc	s LEFT JOIN (SELECT Cid, COUNT(score) zong from sc GROUP BY CId) s1 on s.CId = s1.Cid
		WHERE s.score>85
		GROUP BY s.CId) as a on s.CId=a.CId
	LEFT JOIN(
		SELECT s.CId,COUNT(s.score) '[85-70]人数', COUNT(s.score)/s1.zong '百分比'
		FROM sc	s LEFT JOIN (SELECT Cid, COUNT(score) zong from sc GROUP BY CId) s1 on s.CId = s1.Cid
		WHERE s.score<=85 AND s.score>70
		GROUP BY s.CId) as b on s.CId=b.CId
	GROUP BY s.CId

第二十题：查询各科成绩前三名的记录

SELECT s.*, b.Cname, a.ranking, a.score
FROM (SELECT SId, CId, score , dense_rank() over(PARTITION BY CId ORDER BY score DESC) ranking
			from sc) a LEFT JOIN student s on a.SId=s.id LEFT JOIN course b on a.CId=b.id
WHERE ranking<4
ORDER BY ranking

第二十一题：查询每门课程被选修的学生数

SELECT CId, c.Cname, COUNT(SId) '选修人数'
FROM sc s LEFT JOIN course c on s.CId=c.id
GROUP BY CId

第二十二题：查询出只选修两门课程的学生学号和姓名

SELECT SId, s.Sname
FROM sc a LEFT JOIN student s on a.SId=s.id
GROUP BY SId
HAVING COUNT(CId)=2

第二十三题：查询男生、女生人数

SELECT b.male,COUNT(Ssex)-b.male as 'famale'
FROM student a LEFT JOIN (SELECT id,COUNT(Ssex) as 'male' FROM student WHERE Ssex='男') b
on a.id=b.id

第二十四题：查询名字中含有「风」字的学生信息

SELECT * from student where Sname LIKE '%风%'

第二十五题：查询同名同性别学生名单，并统计同名人数

SELECT a.*, b.`同名人`
FROM student a LEFT JOIN (SELECT Sname,Ssex,COUNT(*) as '同名人' FROM student 
GROUP BY Sname,Ssex) b ON a.Sname=b.Sname and a.Sname=b.Ssex
WHERE b.同名人>1

第二十六题：查询 1990 年出生的学生名单

SELECT *
FROM student
WHERE Sage LIKE '1990%'

第二十七题：查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列

SELECT *, AVG(score) as avgScore
FROM sc
GROUP BY CId
ORDER BY avgScore DESC, CID ASC

第二十八题：查询平均成绩大于等于 85 的所有学生的学号、姓名和平均成绩

SELECT b.SId, s.Sname, b.avgScore
FROM student s RIGHT JOIN (SELECT SId, AVG(score) as avgScore
	FROM sc 
	GROUP BY SId
	HAVING avgScore>=85) b on s.id=b.SId

第二十九题：查询课程名称为「数学」，且分数低于 60 的学生姓名和分数

SELECT s.Sname, a.score
FROM sc a LEFT JOIN student s on a.SId=s.id
WHERE a.CId=(SELECT id FROM course WHERE Cname LIKE '数学') AND a.score<60

第三十题：查询所有学生的课程及分数情况（存在学生没成绩，没选课的情况）

SELECT s.Sname, a.*
FROM student s LEFT JOIN sc a on s.id=a.SId

第三十一题：查询任何一门课程成绩在 70 分以上的姓名、课程名称和分数

SELECT s.Sname, c.Cname, a.score
FROM student s LEFT JOIN sc a on s.id=a.SId
	LEFT JOIN course c on a.CId=c.id
WHERE a.score>70

第三十二题：查询不及格的课程（这题有着严重的歧义，读不懂，没有做，直接粘贴的博主的代码

select * from SC where score<60

第三十三题：查询课程编号为 01 且课程成绩在 80 分以上的学生的学号和姓名

SELECT a.SId, s.Sname
FROM sc a LEFT JOIN student s on a.SId=s.id
WHERE a.CId=1 AND a.score>=80

第三十四题：求每门课程的学生人数（这一题也太简单了吧）

SELECT a.CId, b.Cname, COUNT(a.SId) as '总人数'
FROM sc a LEFT JOIN course b on a.CId=b.id
GROUP BY a.CId

第三十五题：成绩不重复，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩
（笔者刚开始没注意前面要求不重复，用了三十六题的解法，成功解错，正确解法是下面的先进行排序）

SELECT s.*, b.score
FROM student s LEFT JOIN (
	SELECT Sid, score, DENSE_RANK() over(PARTITION BY CId ORDER BY score DESC) as Ranking
	FROM sc
	WHERE CId=(SELECT b.id
		FROM teacher a LEFT JOIN course b on a.id=b.TId
		WHERE a.Tname='张三')) b on s.id=b.Sid
WHERE b.Ranking=1

第三十六题：成绩有重复的情况下，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩
（采用跟上一题完全不同的解法，除了要连接4张表以外，没什么难的）

SELECT b.TId,a.Tname,  b.Cname, d.*, MAX(c.score) as '所教学生最高成绩'
FROM teacher a LEFT JOIN course b on a.id=b.TId
	LEFT JOIN sc c on b.id=c.CId
	LEFT JOIN student d on c.SId=d.id
WHERE a.Tname='张三'

第三十七题：查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩 （此题太难，不会做，直接复制原作者的代码->下面这段，仍然没有得出正确的结果，期望有人给出正确答案）

select C.SId,max(C.CId) Cid,max(C.score) score from SC C 
left join(select SId,avg(score)A from SC group by SId)B 
on C.SId=B.SId
where C.score=B.A
group by C.SId
having COUNT(0)=(select COUNT(0)from SC where SId=C.SId)

第三十八题：查询每门功成绩最好的前两名（考察ROW_NUMBER()的使用）

SELECT a.*
FROM (SELECT SId, CId,score, ROW_NUMBER() over(PARTITION BY CId ORDER BY score DESC) as ranking
FROM sc) as a WHERE a.ranking<=2

第三十九题：统计每门课程的学生选修人数（超过 5 人的课程才统计）

SELECT CId, COUNT(SId) as '人数'
FROM sc
GROUP BY CId
HAVING COUNT(SId)>5

第四十题：检索至少选修两门课程的学生学号 （与二十二题重复，所以我直接上面的复制代码，稍微修改了一下）

SELECT SId, s.Sname
FROM sc a LEFT JOIN student s on a.SId=s.id
GROUP BY SId
HAVING COUNT(CId)>1

第四十一题：查询选修了全部课程的学生信息（又是重复上面考点的一题）

SELECT SId, s.Sname
FROM sc a LEFT JOIN student s on a.SId=s.id
GROUP BY SId
HAVING COUNT(CId)=(SELECT COUNT(id) FROM course)

第四十二题：查询各学生的年龄，只按年份来算 （一开始没明白什么意思，后来发现数据库Sage字段不是年龄，是出生日期…用的是year函数）

SELECT Sname, YEAR(NOW())-YEAR(Sage) as age FROM student

第四十三题：按照出生日期来算，当前月日 < 出生年月的月日则，年龄减一

SELECT Sname, TIMESTAMPDIFF(YEAR,Sage,NOW()) as age FROM student

第四十四题：查询本周过生日的学生

select * from student where week(now()) = week(sage)

第四十五题：查询本月过生日的学生

select * from student where MONTH(now()) = MONTH(sage)

总结：断断续续做了2天多，题目有难有易，做完确实感觉提高了不少，但是因为是偷懒用的函数，如：rank()排序什么的，但是本人真的太菜了，别人写好的排序语句放在眼前都看不懂，唉！