经典的SQL语句面试题.doc
如有侵权，请联系网站删除，仅供学习与交流经典的SQL语句面试题【精品文档】第 7 页Student(S#,Sname,Sage,Ssex) 学生表S#：学号；Sname：学生姓名；Sage：学生年龄；Ssex：学生性别Course(C#,Cname,T#) 课程表C#,课程编号；Cname：课程名字；T#：教师编号SC(S#,C#,score) 成绩表S#：学号；C#,课程编号；score：成绩Teacher(T#,Tname) 教师表 T#：教师编号； Tname：教师名字13、查询学生平均成绩及其名次(不用Row_num()1、查询“001”课程比“002”课程成绩高的所有学生的学号；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分的同学的学号和平均成绩；2、查询平均成绩大于60分的同学的学号和平均成绩；SELECT s# ,avg ( score ) FROM sc GROUP BY s# having avg ( score ) >60;3、查询所有同学的学号、姓名、选课数、总成绩；3、查询所有同学的学号、姓名、选课数、总成绩；SELECT student.s# ,student.sname ,COUNT ( sc.c# ) ,SUM ( score ) FROM student LEFT OUTER JOIN sc ON student.s#=sc.s# GROUP BY student.s# ,sname4、查询姓“李”的老师的个数；4、查询姓“李”的老师的个数；SELECT COUNT ( DISTINCT ( tname ) ) ，COUNT ( tname ) FROM teacher WHERE tname LIKE '李%'SELECT COUNT ( tname ) FROM teacher WHERE tname LIKE '李%'5、查询没学过“叶平”老师课的同学的学号、姓名；5、查询没学过“叶平”老师课的同学的学号、姓名；SELECT student.s# ,student.sname FROM student WHERE s# NOT IN ( SELECT DISTINCT ( sc.s# ) FROM sc ,course ,teacher WHERE sc.c#=course.c# AND teacher.t#=course.t# AND teacher.tname='叶平' ) ;6、查询学过“001”并且也学过编号“002”课程的同学的学号、姓名；6、查询学过“001”并且也学过编号“002”课程的同学的学号、姓名；SELECT student.s# ,student.sname FROM student ,sc WHERE student.s#=sc.s# AND sc.c#='001' and EXISTS ( SELECT * FROM sc AS sc_2 WHERE sc_2.s#=sc.s# AND sc_2.c#='002' 7、查询学过“叶平”老师所教的所有课的同学的学号、姓名；7、查询学过“叶平”老师所教的所有课的同学的学号、姓名；SELECT s# ,sname FROM student WHERE s# IN ( SELECT s# FROM sc ,course ,teacher WHERE sc.c#=course.c# AND teacher.t#=course.t# AND teacher.tname='叶平' GROUP BY s# having COUNT ( sc.c# ) = ( SELECT COUNT ( c# ) FROM course ,teacher WHERE teacher.t#=course.t# AND tname='叶平' 8、查询所有课程成绩小于60分的同学的学号、姓名；8、查询所有课程成绩小于60分的同学的学号、姓名；SELECT s# ,sname FROM student WHERE s# NOT IN ( SELECT student.s# FROM student ,sc WHERE s.s#=sc.s# AND score>60 9、查询没有学全所有课的同学的学号、姓名；9、查询没有学全所有课的同学的学号、姓名；SELECT student.s# ,student.sname FROM student ,sc WHERE student.s#=sc.s# GROUP BY student.s# ,student.sname having COUNT ( c# ) < ( SELECT COUNT ( c# ) FROM course 10、查询至少有一门课与学号为“1001”的同学所学相同的同学的学号和姓名；10、查询至少有一门课与学号为“1001”的同学所学相同的同学的学号和姓名；SELECT s# ,sname FROM student ,sc WHERE student.s#=sc.s# AND c# IN (select c# FROM sc WHERE s#='1001');11、删除学习“叶平”老师课的SC表记录；11、删除学习“叶平”老师课的SC表记录；delete sc FROM course ,teacher WHERE course.c#=sc.c# AND course.t#= teacher.t# AND tname='叶平'12、查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分12、查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分SELECT l.c# 课程id ,l.score 最高分 ,r.score 最低分 FROM sc l ,sc r WHERE l.c# = r.c# AND l.score = ( SELECT MAX ( il.score ) FROM sc il ,student im WHERE il.c# = l.c# AND im.s#=il.s# GROUP BY il.c# AND r.score = ( SELECT MIN ( ir.score ) FROM sc ir WHERE ir.c# = r.c# GROUP BY ir.c# (这里很奇怪，写成这样不可以吗？SELECT c# 课程id ,max(score) 最高分 ,min(score) 最低分 FROM scGROUP BY c#13、查询学生平均成绩及其名次13、查询学生平均成绩及其名次SELECT 1+ ( SELECT COUNT ( DISTINCT 平均成绩 ) FROM ( SELECT s# ,avg ( score ) 平均成绩 FROM sc GROUP BY s# ) t1 WHERE 平均成绩 > t2.平均成绩 ) 名次 ,s# 学生学号 ,平均成绩 FROM ( SELECT s# ,avg ( score ) 平均成绩 FROM sc GROUP BY s# ) t2 ORDER BY 平均成绩 desc;（这里用了 COUNT ( DISTINCT 平均成绩 ) ，考虑了并列相同名次的情况）14、查询各科成绩前三名的xuehao记录:(不考虑成绩并列情况)14、查询各科成绩前三名的记录:(不考虑成绩并列情况)SELECT t1.s# AS 学生id ,t1.c# AS 课程id ,score AS 分数 FROM sc t1 WHERE score IN ( SELECT top 3 score FROM sc WHERE t1.c#= c# ORDER BY score DESC ORDER BY t1.c#;15、查询每门功成绩最好的前两名15、查询每门功成绩最好的前两名SELECT t1.s# AS 学生id ,t1.c# AS 课程id ,score AS 分数 FROM sc t1 WHERE score IN ( SELECT top 2 score FROM sc WHERE t1.c#= c# ORDER BY score DESC ORDER BY t1.c#;(如果成绩有相同的情况，就可能出现多余两名的情况)
