sql面试题（学生表_课程表_成绩表）

sql面试题（学生表课程表成绩表）

转自： http://www.cnblogs.com/qixuejia/p/3637735.html

（只转载部分题目）

表架构

Student(S#,Sname,Sage,Ssex) 学生表 
Course(C#,Cname,T#) 课程表 
SC(S#,C#,score) 成绩表 
Teacher(T#,Tname) 教师表

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

SELECT a.S# FROM 
(SELECT S#, score FROM SC WHERE C# = '001') a,
(SELECT S#, score FROM SC WHERE C# = '002') b
WHERE a.score > b.score AND a.S# = b.S#;                 

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

SELECT S#, AVG(score)
FROM SC
GROUP BY S#
HAVING AVG(score) > 60;

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

SELECT Student.S#, Student.Sname, COUNT(SC.C#), SUM(score)
FROM Student LEFT JOIN SC ON Student.S# = SC.S#
GROUP BY Student.S#

4、查询学过“001”并且也学过编号“002”课程的同学的学号、姓名；

SELECT Student.S#, Student.Sname, FROM Student, SC 
WHERE Student.S# = SC.S# AND 
      SC.C# = '001' AND 
      EXISTS (SELECT C# FROM SC AS SC_2 WHERE SC_2.S# = SC.S# AND SC_2.C# = '002')

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

SELECT S#, Sname FROM Student
WHERE S# NOT IN (SELECT　S.S# FROM Student AS S, SC 
                 WHERE S.S# = SC.S# AND score > 60);

6、查询成绩排名的前三名；

以下引用自：http://www.cnblogs.com/freefenglei/p/5757874.html

SELECT* FROM Student a 
WHERE a.id IN (SELECT b.id FROM Student b 
               WHERE b.classId = a.classId 
               ORDER BY grade DESC 
               LIMIT 0,3) ;
或者

SELECT * FROM Student b 
WHERE
NOT EXISTS(SELECT * FROM Student c 
           WHERE c.classId = b.classId AND b.grade < c.grade 
           GROUP BY c.classId 
           HAVING COUNT(*) > 3 )