常见的sql面试题

《常见的sql面试题》由会员上传分享，免费在线阅读，更多相关内容在教育资源-天天文库。

1、学生表:Student(S1,Sname,Sage,Ssex)S1：学号；Sname：学生姓名；Sage：学生年龄；Ssex：学生性别课程表:Course(C1,Cname,T1)C1,课程编号；Cname：课程名字；T1：教师编号成绩表:SC(S1,C1,score)S1：学号；C1,课程编号；score：成绩教师表:Teacher(T,#Tname)T1：教师编号；Tname：教师名字问题：1、查询“001”课程比“002”课程成绩高的所有学生的学号；selecta.S1from(selectS1,scoref

2、romSCwhereC1='001')a,(selectS1,scorefromSCwhereC1='002')bwherea.score>b.scoreanda.S1=b.S1;2、查询平均成绩大于60分的同学的学号和平均成绩；selectS1,avg(score)fromscgroupbyS1havingavg(score)>60;3、查询所有同学的学号、姓名、选课数、总成绩；selectStudent.S1,Student.Sname,count(SC.C1),sum(score)fromStudentlef

3、tOuterjoinSConStudent.S1=SC.S1groupbyStudent.S1,Sname4、查询姓“李”的老师的个数；selectcount(distinct(Tname))fromTeacherwhereTnamelike'李%';5、查询没学过“叶平”老师课的同学的学号、姓名；selectStudent.S1,Student.SnamefromStudentwhereS1notin(selectdistinct(SC.S1)fromSC,Course,TeacherwhereSC.C1=Cou

4、rse.C1andTeacher.T1=Course.T1andTeacher.Tname='叶平');6、查询学过“001”并且也学过编号“002”课程的同学的学号、姓名；selectStudent.S1,Student.SnamefromStudent,SCwhereStudent.S1=SC.S1andSC.C1='001'andexists(Select*fromSCasSC_2whereSC_2.S1=SC.S1andSC_2.C1='002');7、查询学过“叶平”老师所教的所有课的同学的学号、姓名；s

5、electS1,SnamefromStudentwhereS1in(selectS1fromSC,Course,TeacherwhereSC.C1=Course.C1andTeacher.T1=Course.T1andTeacher.Tname='叶平'groupbyS1havingcount(SC.C1)=(selectcount(C1)fromCourse,TeacherwhereTeacher.T1=Course.T1andTname='叶平'));8、查询课程编号“002”的成绩比课程编号“001”课程低的

6、所有同学的学号、姓名；SelectS1,Snamefrom(selectStudent.S1,Student.Sname,score,(selectscorefromSCSC_2whereSC_2.S1=Student.S1andSC_2.C1='002')score2fromStudent,SCwhereStudent.S1=SC.S1andC1='001')S_2wherescore2

7、notin(selectStudent.S1fromStudent,SCwhereS.S1=SC.S1andscore>60);10、查询没有学全所有课的同学的学号、姓名；selectStudent.S1,Student.SnamefromStudent,SCwhereStudent.S1=SC.S1groupbyStudent.S1,Student.Snamehavingcount(C1)<(selectcount(C1)fromCourse);11、查询至少有一门课与学号为“1001”的同学所学相同的同学的学号

8、和姓名；selectS1,SnamefromStudent,SCwhereStudent.S1=SC.S1andC1inselectC1fromSCwhereS1='1001';12、查询至少学过学号为“001”同学所有一门课的其他同学学号和姓名；selectdistinctSC.S1,SnamefromStudent,SCwhereStudent.S1=S