sql面试题及答案.doc

文档介绍：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 # 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 #=# 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( #) from
SC,Course,Teacher where SC. C#=Course. C# and
Teacher. T#=Course. T# and Teacher. Tname='叶平');
6、 查询学过“001”并且也学过编号“002”课程的同学的学号、姓 名；
select #, from Student,SC where Student. S#=# and SC. C#=，00T 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
#=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 <score:
9、 查询所有课程成绩小于60分的同学的学号、姓名；
select S#,Sname
from Student
where S# not in (select Student. S# from Student, SC where #=SC. S# and score>60);
10、 查询没有学全所有课的同学的学号、姓名；
select Student. S#, Student. Sname