SQL数据库面试题以及答案

　　数据库面试题：

　　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：教师名字

　　问题：

　　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 Outer join SC on Student.S#=SC.S#

　　group by Student.S#,Sname

　　4、查询姓“李”的老师的个数;

　　select count(distinct(Tname))

　　from Teacher

　　where Tname like '李%';

　　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”课程的同学的学号、姓名;

　　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、查询学过“叶平”老师所教的所有课的同学的学号、姓名;

　　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、查询课程编号“002”的成绩比课程编号“001”课程低的所有同学的学号、姓名;

　　Select S#,Sname from (select Student.S#,Student.Sname,score ,(select score from SC SC_2 where SC_2.S#=Student.S# and SC_2.C#='002') score2

　　from Student,SC where Student.S#=SC.S# and C#='001') S_2 where score2

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

　　select S#,Sname

　　from Student

　　where S# not in (select Student.S# from Student,SC where S.S#=SC.S# and score>60);

　　10、查询没有学全所有课的同学的学号、姓名;

　　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);

　　11、查询至少有一门课与学号为“1001”的同学所学相同的同学的学号和姓名;

　　select S#,Sname from Student,SC where Student.S#=SC.S# and C# in select C# from SC where S#='1001';