资源描述:
《常见的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_2wherescore27、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