经典的SQL查询面试题

最近准备换工作，复习了一下数据库方面的知识，下面来讲讲这些经典的SQL查询：

• 学生表 student (sId,sName,sAge,sSex)
• 教师表 teacher (tId,tName)
• 课程表 course (cId,cName,tId)
• 成绩表 sc (sId,cId,score)

---

首先创建上面四个表，并插入一些数据，脚本下载地址http://download.csdn.net/detail/alanxien/8536323

问题：

１、查询“1”课程比“2”课程成绩高的所有学生的学号；

SELECT a.sId FROM (SELECT sId,score FROM sc WHERE cId=1) a,
(SELECT sId,score FROM sc where cId=2) b WHERE a.score>b.score AND 
a.sId=b.sId

分析：

1、根据题目，目标是要查找学号，所以首先 SELECT a.sId
2、查找哪些学生的学号？1课程比2课程成绩高的学生学号。问题分解：
（1）查找语文课程成绩单 SELECT sId,score FROM sc WHERE cId=1
（2）查找数学课程成绩单 SELECT sId,score FROM sc WHERE cId=2
（3）语文成绩大于数学成绩 a.score>b.score，当然是同一个同学的1课程和2课程比较，所以还得加上a.sId=b.sId

2、查询平均成绩大于60分的同学的学号和平均成绩；

SELECT sId,AVG(score) from sc GROUP BY sId HAVING AVG(score)>60

分析：

1、目标查找学号，平均成绩。SELECT sId,AVG(score)
2、按学号分组GROUP BY，查询平均成绩大于60分，需用到AVG(score)函数，由于where关键字不能和合计函数一起使用，所以要用HAVING关键字。

3、查询所有同学的学号、姓名、选课数、总成绩；

SELECT t1.sId,t2.sName,COUNT(t1.sId),SUM(score) FROM sc t1 
LEFT JOIN student t2 ON t1.sId=t2.sId GROUP BY t1.sId 

分析：

1、目标查找，学号，姓名，选课数，总成绩，SELECT t1.sId,t2.sName,COUNT(t1.sId),SUM(score)，由于从sc（成绩表）表中中无法查找出学生姓名，所以需要用到连接查询，按学号分组进行统计。

4、查询没学过“XXX”老师课的同学的学号、姓名；

select sId,sName from student WHERE sId NOT IN 
(SELECT sc.sId FROM sc,course,teacher WHERE sc.cId=course.cId 
and course.tId=teacher.tId AND teacher.tName='XXX') 

分析：

1、凡是类似这种查找，我们都可以先将已经学过XX老师课程的学号查找出来，在通过NOT IN来过滤数据。

5、查询学过“1”并且也学过编号“2”课程的同学的学号、姓名；

SELECT student.sId,student.sName FROM student,sc 
WHERE student.sId=sc.sId AND sc.cId=1 AND 
EXISTS(SELECT * FROM sc as t WHERE t.sId=sc.sId AND t.cId=2)  

分析：

1、EXISTS()返回就true和false两个值，当返回为true时证明存在学过2号课程，AND sc.cId=1即学过两门课程的同学。

6、查询学过“XXX”老师所教的所有课的同学的学号、姓名

SELECT sId,sName FROM student WHERE sId IN 
(SELECT sId FROM sc,course,teacher WHERE sc.cId=course.cId AND teacher.tId=course.tId AND teacher.tName='XXX' 
GROUP BY sId HAVING COUNT(sc.cId) = 
(SELECT COUNT(cId) FROM course,teacher WHERE course.tId=teacher.tId and tName='XXX')) 

分析：

1、首先统计XXX老师教了几门课 SELECT COUNT(cId) FROM course,teacher WHERE course.tId=teacher.tId and tName=’XXX’。
2、统计学过XXX老师课程学生，且课程总数等于XXX教课总数。
3、再连接学生表查找出学生姓名。

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

SELECT sId,sName from student WHERE sId NOT IN
(SELECT sId FROM sc WHERE score>60)

分析：

1、首先把课程成绩大于60的学生找出来，再通过NOT IN即得出没有一门成绩 超过60分的同学。

8、查询没有学全所有课的同学的学号、姓名；

SELECT student.sId,student.sName FROM student,sc
WHERE student.sId=sc.sId GROUP BY student.sId HAVING 
COUNT(cId) < (SELECT COUNT(cId) FROM course)

分析：

1、查找所有课程总数 SELECT COUNT(cId) FROM course，查找学生学过的课程总数 COUNT(cId)，当学过课程总数小于课程总数，即可得所有记录。

9、查询各科成绩最高分，最低的分和平均成绩；

SELECT MAX(sc.score),MIN(score),AVG(score) FROM sc GROUP BY sc.cId

10、查询学生平均成绩及其名次；

SET @index=0;
SELECT @index:=(@index+1) as '排名', t.a 
from ( SELECT AVG(score) a FROM sc GROUP BY sId ORDER BY AVG(score) DESC) t

分析：

1、查找平均成绩降序排列。
2、给记录添加index，自增长，即排名。

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

select * from sc a
where (select count(*) from sc where cId=a.cId and score>a.score)<3
order by a.cId,a.score desc

分析：

1、题目可以理解为，我们要查询的这个人，在这一门中分数比他高的少于3个人。
2、按课程号和成绩降序排列，括号里面SQL意思是，只有比他高的小于3人即满足条件。
3、这是一个典型的分组取前N条记录的例子，不过这里没考虑并列的情况。