SQL数据库面试题以及答案精编

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