SQL数据库面试题以及
答案精编
Document number：WTT-LKK-GBB-08921-EIGG-22986
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 > and # = #;
2、查询平均成绩大于60分的同学的学号和平均成绩 select S#, avg(score) from sc group by S# having avg(score)>60
3、查询所有同学的学号、姓名、选课数、总成绩 select #, , count#), sum(score) from student left outer join SC on # = # group by #, Sname 4、查询姓‘李’的老师的个数： select count(distinct(Tname)) from teacher
where tname like '李%';
5、查询没有学过“叶平”老师可的同学的学号、姓名： select #, from Student
where S# not in (select distinct#) from SC,Course,Teacher
where #=# AND #=# AND ='叶平');
6、查询学过“叶平”老师所教的所有课的同学的学号、姓名：
select S#,Sname from Student
where S# in (select S# from SC ,Course ,Teacher where #=# and #=#
and ='叶平' group by S#
having count#)=(select count(C#) from Course,Teacher
where #=# and Tname='叶平'));
7、查询学过“011”并且也学过编号“002”课程的同学的学号、姓名： select #,
from Student,SC where #=# and #='001'and
exists( Select * from SC as SC_2 where #=# and #='002');
8、查询课程编号“002”的成绩比课程编号“001”课程低的所有同学的学号、姓名： Select S#,Sname
from (select #,,score ,
(select score from SC SC_2 where #=# and #='002') score2
from Student,SC
where #=# and C#='001') S_2 where score2 < score;
9、查询所有课程成绩小于60的同学的学号、姓名： select S#, sname from student where s# not in
(select # from student, sc where # = # and score>60);
10、查询没有学全所有课的同学的学号、姓名： select #,
from student, sc
SQL数据库面试题以及答案精编
