面试时经常会遇到的SQL语句

本文参考原博《走向面试之数据库基础：一、你必知必会的SQL语句练习-Part 1》

1. 建表写入数据

。。。已经打包好了看这里：等更新

2. 开始练习

• 查询“001”课程比“002”课程成绩高的所有学生的学号；

SELECT
	a.Sno 
FROM
	( SELECT Sno, score FROM score WHERE Cno = '001' ) AS a,
	( SELECT Sno, score FROM score WHERE Cno = '002' ) AS b 
WHERE
	a.Sno = b.Sno 
	AND a.score > b.score

结果：

• 查询平均成绩大于60分的同学的学号和平均成绩

SELECT
	Sno,
	avg( score ) 
FROM
	score 
GROUP BY
	Sno 
HAVING
	avg( score ) > 60

结果：

• 查询所有同学的学号、姓名、选课数、总成绩；
  （这道题有点难…）

SELECT
	s.Sno,
	s.Sname,
	count( sc.Cno ) AS coursenum,
	sum( sc.score ) 
FROM
	student AS s
	LEFT OUTER JOIN score AS sc ON s.Sno = sc.Sno 
GROUP BY
	s.Sno,
	s.Sname 
ORDER BY
	s.Sno

结果：

• 查询姓“叶”的老师的个数

SELECT
	count( DISTINCT Tname ) 
FROM
	teacher 
WHERE
	Tname LIKE '叶%'

结果：

• 查询没学过“叶平”老师课的同学的学号、姓名
  这题也有点难

SELECT
	s.Sno,
	s.Sname 
FROM
	student AS s 
WHERE
	s.Sno NOT IN (
	SELECT DISTINCT
		( sc.Sno ) 
	FROM
		score AS sc,
		course AS c,
		teacher AS t 
	WHERE
		sc.Cno = c.Cno 
		AND c.Tno = t.Tno 
	AND t.Tname = '叶平' 
	)

结果：

• 查询学过“001”并且也学过编号“002”课程的同学的学号、姓名；
  mysql目前不支持intersect,取交集可以用inner join... on

SELECT
	u1.* 
FROM
	(
	SELECT
		s.Sno,
		s.Sname 
	FROM
		student AS s,
		score AS sc 
	WHERE
		( s.Sno = sc.Sno AND sc.Cno = '001' ) 
	) AS u1
	INNER JOIN (
	SELECT
		s.Sno,
		s.Sname 
	FROM
		student AS s,
		score AS sc 
	WHERE
		( s.Sno = sc.Sno AND sc.Cno = '002' ) 
	) AS u2 ON u1.Sno = u2.Sno

结果：

或者：

SELECT
	sc.Sno,
	s.Sname 
FROM
	score sc,
	student s 
WHERE
	sc.Sno = s.Sno 
	AND sc.Cno IN ( 1, 2 ) 
GROUP BY
	sc.Sno 
HAVING
	COUNT( * ) = 2

结果一样

但不能这样（这样的结果是错的）

SELECT
	s.Sno,
	s.Sname 
FROM
	student AS s,
	score AS sc 
WHERE
	s.Sno = sc.Sno 
	AND sc.Cno =1 
	AND EXISTS ( SELECT * FROM student AS s, score AS sc WHERE s.Sno = sc.Sno AND sc.Cno = 2 )

• 查询学过“叶平”老师所教的所有课的同学的学号、姓名

SELECT
	s.Sno,
	s.Sname 
FROM
	student s,
	score sc,
	teacher t 
WHERE
	s.Sno = sc.Sno 
	AND sc.Cno = t.Tno 
	AND t.Tname = '叶平' 
GROUP BY
	s.Sno 
HAVING
	count( * ) = ( SELECT count( * ) FROM teacher t WHERE t.Tname = '叶平' )

结果：

有这么多人都修完了

• 查询课程编号“002”的成绩比课程编号“001”课程低的所有同学的学号、姓名

SELECT
	s.Sno,
	s.Sname 
FROM
	student s,
	( SELECT sc1.Sno, sc1.score FROM score sc1 WHERE sc1.Cno = 1 ) a,
	( SELECT sc2.Sno, sc2.score FROM score sc2 WHERE sc2.Cno = 2 ) b 
WHERE
	s.Sno = a.Sno 
	AND s.Sno = b.Sno 
	AND a.score > b.score

结果：

只有张三…

• 查询有课程成绩小于60分的同学的学号、姓名

SELECT
	s.Sno,
	s.Sname 
FROM
	student s,
	score sc 
WHERE
	s.Sno = sc.Sno 
	AND sc.score < 60 
GROUP BY
	s.Sno

结果：

• 查询至少有一门课与学号为“001”的同学所学相同的同学的学号和姓名

SELECT DISTINCT
	( s.Sno ),
	s.Sname 
FROM
	student s,
	score sc,
	( SELECT Cno FROM score sc WHERE sc.Sno = 1 ) a 
WHERE
	sc.Cno = a.Cno 
	AND sc.Sno = s.Sno
 /* GROUP BY s.Sno */
--用 DISTINCT 就不用 GROUP BY 了

或者

SELECT DISTINCT
	( s.Sno ),
	s.Sname 
FROM
	Student s,
	score sc 
WHERE
	s.Sno = sc.Sno 
	AND sc.Cno IN ( SELECT DISTINCT ( sc2.Cno ) FROM score sc2 WHERE sc2.Sno = 1 ) 
ORDER BY
	s.Sno ASC

结果：

• 查询和“002”号的同学学习的课程完全相同的其他同学学号和姓名
  这题难啊

SELECT
	s.Sno,
	s.Sname 
FROM
	Student s 
WHERE
	s.Sno != 2 
	AND s.Sno IN (
	SELECT DISTINCT
		( Sno ) 
	FROM
		score 
	WHERE
		Cno IN ( SELECT Cno FROM score WHERE Sno = 2 ) 
	GROUP BY
		Sno 
	HAVING
	COUNT( DISTINCT Cno ) = ( SELECT COUNT( DISTINCT Cno ) FROM score WHERE Sno = 2 ) 
	)

结果：

• 查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分

SELECT
	sc.Cno AS '课程号',
	max( sc.score ) AS '最高分',
	min( sc.score ) AS '最低分' 
FROM
	score sc 
GROUP BY
	sc.Cno

结果：

• 查询各科成绩前三名的记录:(不考虑成绩并列情况)
  这题不会…使用原帖的方法时报错了…
  百度了一下，找到了思路：

-- 如果比这个大的个数小于三个，说明这个数肯定排前三，同时拿出数值。
SELECT
	sc.Cno,
	c.Cname,
	sc.Sno,
	s.Sname,
	sc.score 
FROM
	Student s,
	score sc,
	Course c 
WHERE
	s.Sno = sc.Sno 
	AND sc.Cno = c.Cno 
	AND ( SELECT count( * ) FROM score sc2 WHERE sc2.Cno = sc.Cno AND sc2.score >= sc.score ) <= 3 
ORDER BY
	sc.Cno,
	sc.score DESC

注：这是不考虑成绩并列的情况，假如有成绩并列的…百度吧
结果:

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

SELECT
	s.Sno,
	s.Sname,
	s1.stuavg 
FROM
	student s,
	( SELECT Sno, avg( score ) AS stuavg FROM score GROUP BY score.Sno ) AS s1 
WHERE
	s.Sno = s1.Sno 
	AND s1.stuavg > 70

结果：

结束

sql语句就练到这里了，其实还有许多我都没有练，大家可以去看原贴…