十几道sql语句面试题.docxVIP

十几道sql语句面试题第1部分：题目：Student(S#,Sname,Sage,Ssex) 学生表 Course(C#,Cname,T#) 课程表 SC(S#,C#,score) 成绩表 Teacher(T#,Tname) 教师表问题： 1、查询“001”课程比“002”课程成绩高的所有学生的学号；selecta.S# from (select s#,score from SC where C#=’001′) a, (select s#,score from SC where C#=’002′) bwherea.scoreb.score and a.s#=b.s#; 2、查询平均成绩大于60分的同学的学号和平均成绩；select S#,avg(score)fromscgroup by S# having avg(score) 60; 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、查询姓“李”的老师的个数；select count(distinct(Tname))from Teacher where Tname like ‘李%’; 5、查询没学过“叶平”老师课的同学的学号、姓名；select Student.S#,Student.Snamefrom 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”课程的同学的学号、姓名；select Student.S#,Student.SnamefromStudent,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、查询学过“叶平”老师所教的所有课的同学的学号、姓名；select S#,Snamefrom Studentwhere 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分的同学的学号、姓名；select S#,Snamefrom Studentwhere S# not in (select Student.S# from Student,SC where S.S#=SC.S# and score60); 9、查询没有学全所有课的同学的学号、姓名；select Student.S#,Student.SnamefromStudent,SCwhereStudent.S#=SC.S#group by Student.S#,Student.Sname having count(C#) (select count(C#) from Course); 10、查询至少有一门课与学号为“1001”的同学所学相同的同学的学号和姓名；select S#,SnamefromStudent,SC where Student.S#=SC.S# and C# in （select C# from SC where S#=1001）;11、删除学习“叶平”老师课的SC表记录；Delect SCfrom course ,Teacher where Course.C#=SC.C# and Course.T#= Teacher.T# and Tname=叶平; 12、查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分 SELECT L.C# 课程ID,L.score最高分,R.score最低分 FROM SC L ,SC R WHERE L.C# = R.C#andL.score = (SELECT MAX(IL.score) FROM SC I