SQL面试宝典：展示你的数据库能力的挑战 --内附.sql

表结构

---

 

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

分析

• 需要查询的表有两个 ，成绩表，学生表
• 成绩表分开查，分别查出01，02的课程成绩
• 将两个课程成绩进行对比
• 需要查询出来的字段：学生的所有信息，以及两科的课程分数

实现：

SELECT
	s.*,
	c1.score 01课程,
	c2.score 02课程 
FROM
	( SELECT * FROM t_mysql_score WHERE cid = '01' ) c1,
	( SELECT * FROM t_mysql_score WHERE cid = '02' ) c2,
	t_mysql_student s 
WHERE
	c1.score > c2.score  --  c1 表中的成绩（score）大于 c2 表中的成绩。
	AND c1.sid = c2.sid  --  c1 表中的学生ID（sid）等于 c2 表中的学生ID
 	AND c2.sid = s.sid   --  c2 表中的学生ID（sid）等于 s 表中的学生ID。

运行结果：

---

 题2：查询同时存在" 01 "课程和" 02 "课程的情况

分析:

• 同题1一样，但是少了一个条件，不需要将课程分数进行对比

实现：

SELECT
	s.*,
	c1.score 01课程,
	c2.score 02课程 
FROM
	( SELECT * FROM t_mysql_score WHERE cid = '01' ) c1,
	( SELECT * FROM t_mysql_score WHERE cid = '02' ) c2,
	t_mysql_student s 
WHERE
 c1.sid = c2.sid 
 	AND c1.sid = s.sid

运行结果

 

---

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

分析：

• 左连接返回左表中的所有行以及右表中与左表满足连接条件的匹配行。如果右表中没有匹配的行，将返回 NULL 值
• 根据左外链接的概念，我们将可能为null的02课程表放在右边，如果出现没有的情况下也会查出来，只不过为null值

实现：

select s1.*,s2.score from 
(select * from t_mysql_score where cid='01') s1
left join
(SELECT * from t_mysql_score where cid='02') s2 on s1.sid=s2.sid

运行结果：

---

 

题四： 查询不存在" 01 "课程但只存在" 02 "课程的情况

分析：

• 先查询所有的课程
• 查询出01的课程的学生
• 条件：拿到01课程的学生直接排除掉然后加条件剩下的学生是学了02课程的

实现：

SELECT
	* 
FROM
	t_mysql_score sc 
WHERE
	sc.sid NOT IN ( SELECT sc.sid FROM t_mysql_score sc WHERE sc.cid = '01' ) 
	AND sc.cid = '02'

运行结果:

---

 

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

分析：

• 需要查询出来的字段：学生编号和学生姓名和平均成绩
• 需要用到的表：学成表，成绩表
• 条件，平均成绩大于60

实现：

SELECT
 st.sid,st.sname,ROUND(AVG(sc.score),2) 
FROM
	t_mysql_score sc,
	t_mysql_student st
WHERE
sc.sid=st.sid
 GROUP BY
 st.sid,st.sname
 HAVING
 AVG(sc.score)>60

运行结果：

 

---

 

题6：查询在t_mysql_score表存在成绩的学生信息

分析：

• 查所有的学生的sid
• 只要sid在成绩表中，就是有成绩

实现：

SELECT * FROM 
t_mysql_student st
where st.sid
in
(SELECT sc.sid FROM t_mysql_score sc)

运行结果：

 

---

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

分析：

• 需要用到行转列的语法，需要查询出来的字段为学生编号，学生姓名、选课总数、所有课程的总成绩、每课的成绩
• 需要用到的表，成绩表，课程表，学生表

实现：

SELECT
	st.sid,
	st.sname,
	COUNT( sc.cid ) 选课数,
	SUM( sc.score ) 总成绩,
	sum( CASE WHEN sc.cid = '01' THEN sc.score END ) 数学,
	sum( CASE WHEN sc.cid = '02' THEN sc.score END ) 语文,
	sum( CASE WHEN sc.cid = '03' THEN sc.score END ) 物理 
FROM
	t_mysql_score sc,
	t_mysql_course c,
	t_mysql_student st 
WHERE
	sc.sid = st.sid 
	AND sc.cid = c.cid 
GROUP BY
	st.sid,
	st.sname

运行结果：