SQL经典50题

参考内容

首先这篇记录是根据下面B站的视频进行的简单记录！
因为是自己个人写的，所以有些SQL是自己的想法，可能会与视频中有出入，但个人认为有些SQL有几种写法，自己喜欢哪种写哪种，而且视频中只是讲了这些题目的实现逻辑，实际当中肯定要更加规范一点。
以下内容中如有错误，请各位大佬及时指出，并请不吝赐教！

SQL面试50题

视频中参考文章：

图解SQL面试题：经典50题

【SQL】SQL经典50题&答案

数据库

下载这个SQL文件新建一个数据库直接导入即可，sql语句太长就不往这放了

sql50.sql

50题

有些例题比较简单可以略过！

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

SELECT
	Student.*,
	01_score.CId,
	1_score,
	02_score.CId,
	2_score 
FROM
	Student
	JOIN ( SELECT SId, CId, score AS 1_score FROM SC WHERE CId = '01' ) AS 01_score ON Student.SId = 01_score.SId
	JOIN ( SELECT SId, CId, score AS 2_score FROM SC WHERE CId = '02' ) AS 02_score ON 01_score.SId = 02_score.SId 
WHERE
	1_score > 2_score
1
2
3
4
5
6
7
8
9
10
11
12

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

SELECT
	sc.SId,
	avg( sc.score ) 
FROM
	sc 
GROUP BY
	sc.SId 
HAVING
	avg( sc.score )> 60
1
2
3
4
5
6
7
8
9

3.(略)查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩

SELECT
	a.Sid,
	count( sc.Cid ),
	IFNULL( sum( sc.score ), 0 ) 
  -- sum( CASE WHEN sc.score IS NULL THEN 0 ELSE sc.score END )
FROM
	student AS a
	LEFT JOIN sc ON sc.SId = a.Sid 
GROUP BY
	a.Sid
1
2
3
4
5
6
7
8
9
10

4.(略)查询姓“张”的老师个数

SELECT
	count( Tid ) 
FROM
	teacher 
WHERE
	Tname LIKE '张%'
1
2
3
4
5
6

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

SELECT
	student.SId,
	student.sname 
FROM
	student 
WHERE
	student.sid NOT IN (
	SELECT
		sc.SId 
	FROM
		sc
		INNER JOIN course ON sc.Cid = course.CId
		INNER JOIN teacher ON course.TId = teacher.TId 
	WHERE
	teacher.Tname = '张三' 
	)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

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

SELECT
	student.SId,
	student.sname,
	course.Cname,
	teacher.Tname 
FROM
	sc
	INNER JOIN course ON sc.Cid = course.CId
	INNER JOIN teacher ON course.TId = teacher.TId
	INNER JOIN student ON sc.SId = student.sid 
WHERE
	teacher.Tname = '张三' 
ORDER BY
	student.sid
1
2
3
4
5
6
7
8
9
10
11
12
13
14

7.查询学过01课程并且学过02课程的学生

SELECT
	* 
FROM
	student 
WHERE
	student.sid IN (
	SELECT
		a.sid 
	FROM
		( SELECT sid FROM sc WHERE sc.CId = '01' ) AS a
	INNER JOIN ( SELECT sid FROM sc WHERE sc.CId = '02' ) AS b ON a.sid = b.sid 
	)
1
2
3
4
5
6
7
8
9
10
11
12

8.(略)查询学过02课程的总成绩

SELECT
	sum( sc.score ) 
FROM
	sc 
WHERE
	sc.cid = '02'
1
2
3
4
5
6

9.查询所有课程成绩小于60的学生

SELECT
	a.sid,
	student.sname
FROM
	( SELECT sc.sid, count( sc.CId ) AS acount FROM sc WHERE sc.score < 60 GROUP BY sc.sid )
	AS a
	INNER JOIN 
	( SELECT sc.sid, count( sc.CId ) AS bcount FROM sc GROUP BY sc.sid ) 
	AS b ON a.sid = b.sid 
	inner join student on student.sid = a.sid
WHERE
	a.acount = b.bcount
1
2
3
4
5
6
7
8
9
10
11
12