SQL 45道经典练习题

于 2020-09-07 20:43:57 发布
阅读量4.2k 收藏 54
点赞数 5
分类专栏： SQL 文章标签： mysql sql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/sosososoon/article/details/108455727
版权

数据表

  • 学生表 Student (SId,Sname,Sage,Ssex)

    • SId 学生编号
    • Sname 学生姓名
    • Sage 出生年月
    • Ssex 学生性别

  • 课程表 Course (CId,Cname,TId)

    • CId 课程编号
    • Cname 课程名称
    • TId 教师编号

  • 教师表 Teacher (TId,Tname)

    • TId 教师编号
    • Tname 教师姓名

  • 成绩表 SC (SId,CId,score)

    • SId 学生编号
    • CId 课程编号
    • score 分数

查询" 01 “课程比” 02 "课程成绩高的学生的信息及课程分数

USE test1;
SELECT * FROM
sc sc1 
JOIN sc sc2 USING (SId)
WHERE sc1.CId = '01' 
AND sc2.CId = '02'
AND sc1.score > sc2.score

查询同时存在" 01 “课程和” 02 "课程的情况

USE test1;
SELECT * FROM
sc sc1 
JOIN sc sc2 USING (SId)
WHERE sc1.CId = '01' 
AND sc2.CId = '02'

查询存在" 01 “课程但可能不存在” 02 "课程的情况 (不存在时显示为 null)

USE test1;
SELECT * FROM 
(
	SELECT * FROM sc sc1
	WHERE CId = '01'
) AS t1
LEFT JOIN
(
	SELECT * FROM sc sc2
	WHERE CId = '02') AS t2
USING (SId)

查询不存在" 01 “课程但存在” 02 "课程的情况

USE test1;
SELECT * FROM sc
WHERE SId NOT IN (
	SELECT SId FROM sc 
	WHERE CId = '01'
)
AND sc.CId = '02'

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

USE test1;
SELECT t1.SId, t1.Sname, t2.average
FROM student t1
JOIN (
	SELECT SId, AVG(score) average FROM sc
	GROUP BY SId
	HAVING average >= 60
) t2
USING (SId)

查询在 SC 表存在成绩的学生信息

USE test1;
SELECT * FROM student
WHERE SId IN (
	SELECT SId FROM sc
)

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

USE test1;
SELECT t1.SId, t1.Sname, t2.count_course, t2.sum_score
FROM student t1
LEFT JOIN (
	SELECT SId, COUNT(CId) as count_course, SUM(score) as sum_score
	FROM sc
	GROUP BY SId) t2
USING (SId)

查有成绩的学生信息

USE test1;
SELECT *
FROM student
WHERE EXISTS (
	SELECT * FROM sc 
	WHERE student.SId=sc.SId
)

查询「李」姓老师的数量

USE test1;
SELECT count(*) num FROM teacher
WHERE Tname REGEXP '^李'

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

方法1：使用嵌套查询

USE test1;
SELECT * FROM student
WHERE SId IN (
	SELECT SId FROM sc 
	WHERE CId IN (
		SELECT CId FROM course
		JOIN teacher USING (TId)
		WHERE Tname = '张三'
	)
)

方法2：使用自然连接

USE test1;
SELECT s.* FROM 
student s, teacher t, course c, sc
WHERE s.SId = sc.SId
AND t.TId = c.TId
AND c.CId = sc.CId
AND t.Tname = '张三'

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

方法1：

USE test1;
SELECT SId,COUNT(CId) course_num FROM sc
GROUP BY SId 
HAVING course_num < (
	SELECT COUNT(CId) FROM course
)

方法2：

USE test1;
SELECT t1.SId, t1.CId, COUNT(sc.CId) as course_num 
FROM (
	SELECT student.SId, course.CId
	FROM student,course 
) AS t1 
LEFT JOIN sc
ON t1.SId = sc.SId 
AND t1.CId = sc.CId
GROUP BY SId
HAVING course_num < (
		SELECT COUNT(CId) FROM course
)

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

USE test1;
SELECT DISTINCT student.* FROM sc
JOIN student 
USING (SId)
WHERE sc.CId IN (
	SELECT CId FROM sc WHERE SId = '01'
)

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

USE test1;
SELECT sc2.SId, student.Sname FROM sc sc1 
JOIN sc sc2 
	ON sc1.CId = sc2.CId 
	AND sc1.SId != sc2.SId
	AND sc1.SID = '01'
JOIN student
	ON student.SId = sc2.SId
GROUP BY sc2.SId
HAVING COUNT(*) = (SELECT COUNT(*) FROM sc WHERE SId = '01')

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

USE test1;
SELECT s.Sname, COUNT(t1.CId) num
FROM student s
LEFT JOIN sc USING (SId)
LEFT JOIN (
	SELECT CId FROM course 
	JOIN teacher USING (TId)
	WHERE Tname = '张三'
) t1 
ON t1.CId = sc.CId
GROUP BY SId
HAVING num = 0

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

USE test1;
SELECT SId, Sname, AVG(score) average, COUNT(IF(score<60,1,NULL)) num
FROM sc
JOIN student
USING (SId)
GROUP BY SId
HAVING num>=2

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

USE test1;
SELECT student.*, score
FROM sc
JOIN student
USING (SId)
WHERE CId = '01'
AND score < 60
ORDER BY score DESC

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

USE test1;
SELECT SId, Sname, CId, score, (
	SELECT AVG(score) FROM sc
	WHERE SId = t1.SId
) AS average
FROM sc t1
JOIN student
USING (SId)
ORDER BY average DESC

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

以如下形式显示：课程 ID，课程 name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率

及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90

要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列

USE test1;
SELECT 
	CId, 
	Cname, 
	MAX(score) as max, 
	MIN(score) as min,
	AVG(score) as average,
	COUNT(SId) as num,
	COUNT(IF(score BETWEEN 60 AND 69,1,NULL)) / COUNT(SId)  as 及格,
	COUNT(IF(score BETWEEN 70 AND 79,1,NULL)) / COUNT(SId)  as 中等,
	COUNT(IF(score BETWEEN 80 AND 89,1,NULL)) / COUNT(SId)  as 优良,
	COUNT(IF(score >= 90,1,NULL)) / COUNT(SId)  as 优秀
FROM sc 
JOIN course
USING (CId)
GROUP BY CId
ORDER BY num,CId

按各科成绩进行排序，并显示排名， Score 重复时保留名次空缺

USE test1;
SET @curRank := 0;
SET @prevRank := NULL; 
SET @incRANK := 1;
SELECT CId, score, myrank
FROM (
	SELECT CId, score, @curRank := IF(@prevRank = score, @curRank, @incRank) AS myrank,
	@incRANK := @incRANK + 1,
	@prevRank := score
	FROM sc
	ORDER BY score DESC 
) t1

按各科成绩进行排序，并显示排名， Score 重复时合并名次

相同分数名次不同

USE test1;
SET @curRank := 0;
SELECT CId, score, @curRank:= @curRank+1 AS myrank
FROM sc
ORDER BY score DESC

相同分数名词相同

USE test1;
SET @curRank := 0;
SET @prevRank := NULL; 
SELECT CId, score,
CASE 
WHEN @prevRank = score THEN @curRank
WHEN @prevRank := score THEN @curRank:= @curRank + 1
END AS myrank
FROM sc
ORDER BY score DESC

查询学生的总成绩，并进行排名，总分重复时保留名次空缺

USE test1;
SET @curRank := 0;
SET @prevScore := NULL;
SET @incRank := 1;
SELECT SId, sumScore, myrank
FROM (
	SELECT SId, sumScore, 
	@curRank := IF(@prevScore = sumScore, @curRank, @incRank) AS myrank,
	@incRank := @incRank +1,
	@prevScore := sumScore
	FROM (
		SELECT SId, SUM(score) sumScore FROM sc
		GROUP BY SId
		ORDER BY sumScore DESC
	) t1
) t2

查询学生的总成绩，并进行排名，总分重复时合并名次

USE test1;
SET @curRank := 0;
SET @prevScore := NULL;
SELECT SId, sumScore,
CASE 
WHEN @prevScore = sumScore THEN @curRank
WHEN @prevScore := sumScore THEN @curRank := @curRank + 1
END AS myrank
FROM (
	SELECT SId, SUM(score) sumScore FROM sc
	GROUP BY SId
	ORDER BY sumScore DESC
) t1

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

USE test1;
SELECT 
	CId,
	Cname,
	COUNT(IF(score BETWEEN 85 AND 100,1,NULL)) AS '[85-100]',
	COUNT(IF(score BETWEEN 70 AND 84,1,NULL)) AS '[70-85)',
	COUNT(IF(score BETWEEN 60 AND 69,1,NULL)) AS '[60-70)',
	COUNT(IF(score BETWEEN 0 AND 59,1,NULL)) AS '[0,60)',
	COUNT(IF(score BETWEEN 85 AND 100,1,NULL))/COUNT(*) AS '[85-100]',
	COUNT(IF(score BETWEEN 70 AND 84,1,NULL))/COUNT(*) AS '[70-85)',
	COUNT(IF(score BETWEEN 60 AND 69,1,NULL))/COUNT(*) AS '[60-70)',
	COUNT(IF(score BETWEEN 0 AND 59,1,NULL))/COUNT(*) AS '[0,60)'
FROM sc 
JOIN course USING(CId)
GROUP BY CId

查询各科成绩前三名的记录

方法1：通过组内排名来实现

USE test1;
SET @curRank := 1;
SET @preCId := NULL;
SELECT * FROM (
	SELECT *,
	CASE 
	WHEN @preCId = CId THEN @curRank := @curRank + 1
	WHEN @preCId := CId THEN @curRank := 1
	END AS myrank
	FROM (
		SELECT * FROM sc
		ORDER BY CId,score DESC
	) t1
) t2
WHERE myrank <= 3

方法2：前三名转化为若大于此成绩的人数少于三

USE test1;
SELECT * FROM sc
WHERE (
	SELECT count(*) FROM sc t1 
	WHERE sc.CId = t1.CId 
	AND sc.score < t1.score
) < 3
ORDER BY CId ,sc.score DESC

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

USE test1;
SELECT CId, Cname, COUNT(SId) num 
FROM sc JOIN course USING (CId)
GROUP BY CId

查询出只选修两门课程的学生学号和姓名

方法1：

USE test1;
SELECT DISTINCT SId, Sname
FROM sc JOIN student USING (SId)
WHERE (
SELECT COUNT(*) FROM sc t1
	WHERE t1.SId = sc.SId
	GROUP BY SId
) = 2

方法2：

USE test1;
SELECT SId, Sname
FROM sc JOIN student USING (SId)
GROUP BY SId
HAVING COUNT(*) = 2

查询男生、女生人数

USE test1;
SELECT Ssex, COUNT(*) num
FROM student
GROUP BY Ssex

查询名字中含有「风」字的学生信息

USE test1;
SELECT * FROM student
WHERE Sname REGEXP '风'

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

USE test1;
SELECT * FROM student
WHERE Sname IN (
	SELECT Sname FROM student
	GROUP BY Sname, SSex
	HAVING COUNT(*) >= 2
)

查询 1990 年出生的学生名单

USE test1;
SELECT * FROM student 
WHERE YEAR(Sage) = 1990

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

USE test1;
SELECT CId, AVG(score) average
FROM sc
GROUP BY CId
ORDER BY average DESC, CId

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

USE test1;
SELECT SId, Sname, average
FROM student JOIN (
	SELECT SId, AVG(score) average FROM sc
	GROUP BY SId
	HAVING average >= 85
) t1 USING (SId)

查询课程名称为「数学」，且分数低于 60 的学生姓名和分数

USE test1;
SELECT  Sname, score
FROM sc 
JOIN course USING (CId)
JOIN student USING (SId)
WHERE Cname = '数学'
AND score < 60

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

USE test1;
SELECT SId, Sname, Cname, score
FROM student 
LEFT JOIN sc USING (SId)
LEFT JOIN course USING (CId)
ORDER BY SId

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

USE test1;
SELECT Sname, score, Cname
FROM sc
JOIN student USING (SId)
JOIN course USING (CId)
WHERE score > 70

查询存在不及格的课程

USE test1;
SELECT DISTINCT CId, Cname
FROM sc
JOIN course USING (CId)
WHERE score < 60

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

USE test1;
SELECT SId, Sname 
FROM sc
JOIN student USING (SId)
WHERE CId = 01
AND score > 80

求每门课程的学生人数

USE test1;
SELECT CId, COUNT(*) num
FROM sc
GROUP BY CId

成绩不重复，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩

USE test1;
SELECT SId, Sname, Sage, Ssex, score
FROM sc
JOIN student USING (SId)
WHERE CId = (
	SELECT CId 
	FROM teacher JOIN course USING (TId)
	WHERE Tname = '张三'
)
ORDER BY score DESC
LIMIT 1

成绩有重复的情况下，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩

方法1：

USE test1;
SELECT SId, Sname, Sage, Ssex, score
FROM sc
JOIN student USING (SId)
WHERE CId = (
	SELECT CId 
	FROM teacher JOIN course USING (TId)
	WHERE Tname = '张三'
)
AND score = (
	SELECT MAX(score) 
	FROM sc 
	JOIN course USING (CId)
	JOIN teacher USING (TId)
	WHERE Tname = '张三'
)

方法2：

USE test1;
SET @prevScore := NULL;
SET @myRank := 0;
SELECT student.*, t1.score
FROM student 
JOIN (
	SELECT sc.SId, sc.score, 
	CASE 
		WHEN @prevScore = sc.score THEN @myRank 
		WHEN @prevScore := sc.score THEN @myRank := @myRank+1 
	END AS myRank
	FROM course, teacher, sc 
	WHERE course.CId = sc.CId
	AND course.TId = teacher.TId
	AND teacher.Tname = '张三'
	ORDER BY sc.score DESC
) AS t1 
ON student.SId=t1.SId
WHERE t1.myRank = 1

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

USE test1;
SELECT * FROM sc t1
WHERE EXISTS (
	SELECT * FROM sc t2
	WHERE t1.score = t2.score
	AND t1.CId != t2.CId
	AND t1.SId = t2.SId
)

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

USE test1;
SELECT * 
FROM sc t1
WHERE (
	SELECT COUNT(*) FROM sc t2 
	WHERE t1.CId = t2.CId
	AND t1.score < t2.score
) < 2
ORDER BY CId

统计每门课程的学生选修人数（超过 5 人的课程才统计）

USE test1;
SELECT CId, COUNT(*) num
FROM sc
GROUP BY CId
HAVING num > 5

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

USE test1;
SELECT SId FROM sc
GROUP BY SId
HAVING COUNT(*) >= 2

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

USE test1;
SELECT SId, Sname
FROM sc
JOIN student USING (SId)
GROUP BY SId
HAVING COUNT(*) = (
	SELECT COUNT(*) FROM course
)

查询各学生的年龄，只按年份来算

方法1

USE test1;
SELECT SId, Sname, YEAR(Sage) birth_year
FROM student

方法2

USE test1;
SELECT SId, Sname, TIMESTAMPDIFF(YEAR,Sage,CURDATE()) age
FROM student

按照出生日期来算，当前月日 < 出生年月的月日则，年龄减一

USE test1;
SELECT *,
CASE 
	WHEN MONTH(Sage) > MONTH(NOW()) THEN YEAR(NOW()) - YEAR(Sage)
	WHEN MONTH(Sage) < MONTH(NOW()) THEN YEAR(NOW()) - YEAR(Sage) - 1
	WHEN DAY(Sage) >= DAY(NOW()) THEN YEAR(NOW()) - YEAR(Sage)
	ELSE YEAR(NOW()) - YEAR(Sage) - 1
END age
FROM student

查询本周过生日的学生

USE test1;
SELECT * FROM student
WHERE YEARWEEK(student.Sage) = YEARWEEK(CURDATE())

查询下周过生日的学生

方法1

	USE test1;
	SELECT * FROM student
	WHERE YEARWEEK(student.Sage) = YEARWEEK(CURDATE()) + 1

方法2

USE test1;
SELECT * FROM student
WHERE YEARWEEK(Sage) = CONCAT(YEAR(CURDATE()),WEEK(CURDATE())+1)

查询本月过生日的学生

USE test1;
SELECT * FROM student
WHERE MONTH(Sage) = MONTH(CURDATE())

查询下月过生日的学生

USE test1;
SELECT * FROM student
WHERE MONTH(Sage) = (MONTH(CURDATE())+1) MOD 12
sosososoon
关注
  • 5
    点赞
  • 踩
  • 54
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值