sql常见面试题(共3篇).doc

sql常见面试题(共3篇) 回目录 1. Student(S#,Sname,Sage,Ssex) 学生表 2. Course(C#,Cname,T#) 课程表 3. SC(S#,C#,score) 成绩表 4. Teacher(T#,Tname) 教师表 5.6. 问题： 7. 1、查询“001”课程比“002”课程成绩高的所有学生的学号； 8. select a.S# from (select s#,score from SC where C#=#39;001#39;) a,( select s#,score 9. from SC where C#=#39;002#39;) b 10.where a.scoregt;b.score and a.s#=b.s#; 11.2、查询平均成绩大于60分的同学的学号和平均成绩； 12.select S#,avg(score) 13.from sc 14.group by S# having avg(score) gt;60; 15.3、查询所有同学的学号、姓名、选课数、总成绩； 16.select Student.S#,Student.Sname,count(SC.C#),sum(score) 17.from Student left Outer join SC on Student.S#=SC.S# 18.group by Student.S#,Sname 19.4、查询姓“李”的老师的个数； 20.select count(distinct(Tname)) 21.from Teacher 22.where Tname like #39;李%#39;; 5、查询没学过“叶平”老师课的同学的学号、姓名； 23.select Student.S#,Student.Sname 24.from Student 25.where S# not in (select distinct( SC.S#) from SC,Course,Tea cher whereSC.C#=Course.C# and Teacher.T#=Course.T# and Teacher.Tname=#39;叶平#39;); 26.6、查询学过“001”并且也学过编号“002”课程的同学的学号、姓 名； 27.select Student.S#,Student.Sname from Student,SC where Student .S#=SC.S# and SC.C#=#39;001#39;and exists( Select * from SC as SC_2 where SC_2.S#=SC.S# and SC_2.C#=#39;002#39;); 28.7、查询学过“叶平”老师所教的所有课的同学的学号、姓名； 29.select S#,Sname 30.from Student 31.where S# in (select S# from SC ,Course ,Teacher where SC.C#=C ourse.C# and Teacher.T#=Course.T# and Teacher.Tname=#39;叶平 #39; group by S# having count(SC.C#)=(select count(C#) from Course,Teacherwhere Teacher.T#=Course.T# and Tname=#39;叶平#39;)); 32.8、查询课程编号“002”的成绩比课程编号“001”课程低的所有同学的 学号、姓名； 33.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#=#39;002#39;) score2 34.from Student,SC where Student.S#=SC.S# and C#=#39;001#39;) S_2 wher e score2 lt;score; 35.9、查询所有课程成绩小于60分的同学的学号、姓名； 36.select S#,Sname 37.from Student 38.where S# not in (select Student.S# from Student,SC where S.S# =SC.S# and scoregt;60); 39.10、查询没有学全所有课的同学的学号、姓名； 40.select Student.S#,Student.Sname 41.from Student,SC 42.where Stud