SQL常见面试题集(一)

1、SQL试题集(一)Student(S#,Sname,Sage,Ssex)学生表Course(C#,Cname,T#)课程表SC(S#,C#,score)成绩表Teacher(T#,Tname)教师表问题：1、查询“001课程比“002课程成绩高的所有学生的学号；selecta.S#from(selects#,scorefromSCwhereC#='001')a,(selects#,scorefromSCwhereC#='002')bwherea.score>b.scoreanda.s#=b.s#;2、查询平均成绩大于60分的同学的学号和平均成绩；selec

2、tS#,avg(score)fromscgroupbyS#havingavg(score)>60;3、查询所有同学的学号、姓名、选课数、总成绩；selectStudent.S#,Student.Sname,count(SC.C#),sum(score)fromStudentleftOuterjoinSConStudent.S#=SC.S#groupbyStudent.S#,Sname4、查询姓“李的老师的个数；selectcount(distinct(Tname)fromTeacherwhereTnamelike'李'5、查询没学过“叶平老师课的同学的学号、姓名；sele

3、ctStudent.S#,Student.SnamefromStudentwhereS#notin(selectdistinct(SC.S#)fromSC,Course,TeacherwhereSC.C#=Course.C#andTeacher.T#=Course.T#andTeacher.Tname='叶平');6、查询学过“001并且也学过编号“002课程的同学的学号、姓名；selectStudent.S#,Student.SnamefromStudent,SCwhereStudent.S#=SC.S#andSC.C#='001'andexists(Sele

4、ct*fromSCasSC_2whereSC_2.S#=SC.S#andSC_2.C#='002');7、查询学过“叶平老师所教的所有课的同学的学号、姓名；selectS#,SnamefromStudentwhereS#in(selectS#fromSC,Course,TeacherwhereSC.C#=Course.C#andTeacher.T#=Course.T#andTeacher.Tname='叶平'groupbyS#havingcount(SC.C#)=(selectcount(C#)fromCourse,TeacherwhereTeacher.T#=

5、Course.T#andTname='叶平');8、查询课程编号“002的成绩比课程编号“001课程低的所有同学的学号、姓名；SelectS#,Snamefrom(selectStudent.S#,Student.Sname,score,(selectscorefromSCSC_2whereSC_2.S#=Student.S#andSC_2.C#='002')score2fromStudent,SCwhereStudent.S#=SC.S#andC#='001')S_2wherescore2<score;9、查询所有课程成绩小于60分的同学

6、的学号、姓名；selectS#,SnamefromStudentwhereS#notin(selectStudent.S#fromStudent,SCwhereS.S#=SC.S#andscore>60);10、查询没有学全所有课的同学的学号、姓名；selectStudent.S#,Student.SnamefromStudent,SCwhereStudent.S#=SC.S#groupbyStudent.S#,Student.Snamehavingcount(C#)<(selectcount(C#)fromCourse);11、查询至少有一门课与学号为“1001的同学所学相同的同

7、学的学号和姓名；selectS#,SnamefromStudent,SCwhereStudent.S#=SC.S#andC#inselectC#fromSCwhereS#='1001'12、查询至少学过学号为“001同学所有一门课的其他同学学号和姓名；selectdistinctSC.S#,SnamefromStudent,SCwhereStudent.S#=SC.S#andC#in(selectC#fromSCwhereS#='001');13、把“SC表中“叶平老师教的课的成绩都更改为此课程的平均成绩；updateSCsetscore=(selectavg(

8、SC_2.score)fromSCSC_2whereSC_2.C#=SC.C#)fromCourse,TeacherwhereCourse.C#=SC.C#andCourse.T#=Teacher.T#andTeacher.Tname='叶平');14、查询和“1002号的同学学习的课程完全相同的其他同学学号和姓名；selectS#fromSCwhereC#in(selectC#fromSCwhereS#='1002')groupbyS#havingcount(*)=(selectcount(*)fromSCwhereS#='1002');15、

9、删除学习“叶平老师课的SC表记录；DelectSCfromcourse,TeacherwhereCourse.C#=SC.C#andCourse.T#=Teacher.T#andTname='叶平'16、向SC表中插入一些记录,这些记录要求符合以下条件：没有上过编号“003课程的同学学号、2、号课的平均成绩；InsertSCselectS#,'002',(Selectavg(score)fromSCwhereC#='002')fromStudentwhereS#notin(SelectS#fromSCwhereC#='002');

10、17、按平均成绩从高到低显示所有学生的“数据库、“企业治理、“英语三门的课程成绩,按如下形式显示：学生ID,数据库,企业治理,英语,有效课程数,有效平均分SELECTS#as学生ID,(SELECTscoreFROMSCWHERESC.S#=t.S#ANDC#='004')AS数据库,(SELECTscoreFROMSCWHERESC.S#=t.S#ANDC#='001')AS企业治理,(SELECTscoreFROMSCWHERESC.S#=t.S#ANDC#='006')AS英语,COUNT(*)AS有效课程数,AVG(t.score)AS平

11、均成绩FROMSCAStGROUPBYS#ORDERBYavg(t.score)18、查询各科成绩最高和最低的分：以如下形式显示：课程ID,最高分,最低分SELECTL.C#As课程ID,L.scoreAS最高分,R.scoreAS最低分FROMSCL,SCASRWHEREL.C#=R.C#andL.score=(SELECTMAX(IL.score)FROMSCASIL,StudentASIMWHEREL.C#=IL.C#andIM.S#=IL.S#GROUPBYIL.C#)ANDR.Score=(SELECTMIN(IR.score)FROMSCASIRWHERER.C#=IR.C#GRO

12、UPBYIR.C#);19、按各科平均成绩从低到高和及格率的百分数从高到低顺序SELECTt.C#AS课程号,max(course.Cname)AS课程名,isnull(AVG(score),0)AS平均成绩,100*SUM(CASEWHENisnull(score,0)>=60THEN1ELSE0END)/COUNT(*)AS及格百分数FROMSCT,Coursewheret.C#=course.C#GROUPBYt.C#ORDERBY100*SUM(CASEWHENsnull(score,0)>=60THEN1ELSE0END)/COUNT(*)DESC20、查询如下课程平均成

13、绩和及格率的百分数(用"1行"显示)：企业治理(001),马克思(002),OO&UML(003),数据库(004)SELECTSUM(CASEWHENC#='001'THENscoreELSE0END)/SUM(CASEC#WHEN'001'THEN1ELSE0END)AS企业治理平均分,100*SUM(CASEWHENC#='001'ANDscore>=60THEN1ELSE0END)/SUM(CASEWHENC#='001'THEN1ELSE0END)AS企业治理及格百分数,SUM(CASE

14、WHENC#='002'THENscoreELSE0END)/SUM(CASED#WHEN002'THEN1ELSE0END)AS马克思平均分,100*SUM(CASEWHENC#='002'ANDscore>=60THEN1ELSE0END)/SUM(CASEWHENC#='002'THEN1ELSE0END)AS马克思及格百分数,SUM(CASEWHENC#='003'THENscoreELSE0END)/SUM(CASED#WHEN003'THEN1ELSE0END)ASUML平均分,100*SUM(C

15、ASEWHENC#='003'ANDscore>=60THEN1ELSE0END)/SUM(CASEWHENC#='003'THEN1ELSE0END)ASUML及格百分数,SUM(CASEWHENC#='004'THENscoreELSE0END)/SUM(CASED#WHEN004'THEN1ELSE0END)AS数据库平均分,100*SUM(CASEWHENC#='004'ANDscore>=60THEN1ELSE0END)/SUM(CASEWHENC#='004'THEN1ELSE0EN

16、D)AS数据库及格百分数FROMSC21、查询不同老师所教不同课程平均分从高到低显示SELECTmax(Z.T#)AS教师ID,MAX(Z.Tname)AS教师姓名,C.C#AS课程ID,MAX(C.Cname)AS课程名称,AVG(Score)AS平均成绩FROMSCAST,CourseASC,TeacherASZwhereT.C#=C.C#andC.T#=Z.T#GROUPBYC.C#ORDERBYAVG(Score)DESC22、查询如下课程成绩第3名到第6名的学生成绩单：企业治理(001),马克思(002),UML(003),数据库(004)学生ID,学生女4名,企业治理,马克思,UM

17、L,数据库,平均成绩SELECTDISTINCTtop3SC.S#As学生学号,Student.SnameAS学生姓名,T1.scoreAS企业治理,T2.scoreAS马克思,T3.scoreASUML,T4.scoreAS数据库,ISNULL(T1.score,0)+ISNULL(T2.score,0)+ISNULL(T3.score,0)+ISNULL(T4.score,0)as总分FROMStudent,SCLEFTJOINSCAST1ONSC.S#=T1.S#ANDT1.C#='001'LEFTJOINSCAST2ONSC.S#=T2.S#ANDT2.C#='

18、002'LEFTJOINSCAST3ONSC.S#=T3.S#ANDT3.C#='003'LEFTJOINSCAST4ONSC.S#=T4.S#ANDT4.C#='004'WHEREstudent.S#=SC.S#andISNULL(T1.score,0)+ISNULL(T2.score,0)+ISNULL(T3.score,0)+ISNULL(T4.score,0)NOTIN(SELECTDISTINCTTOP15WITHTIESISNULL(T1.score,0)+ISNULL(T2.score,0)+ISNULL(T3.score,0)+ISNUL

19、L(T4.score,0)FROMscLEFTJOINscAST1ONsc.S#=T1.S#ANDT1.C#='k1'LEFTJOINscAST2ONsc.S#=T2.S#ANDT2.C#='k2'LEFTJOINscAST3ONsc.S#=T3.S#ANDT3.C#='k3'LEFTJOINscAST4ONsc.S#=T4.S#ANDT4.C#='k4'ORDERBYISNULL(T1.score,0)+ISNULL(T2.score,0)+ISNULL(T3.score,0)+ISNULL(T4.score,0)DESC);2

20、3、统计列印各科成绩,各分数段人数：课程ID,课程名称,100-85,85-70,70-60,<60SELECTSC.C#as课程ID,Cnameas课程名称,SUM(CASEWHENscoreBETWEEN85AND100THEN1ELSE0END)AS100-85,SUM(CASEWHENscoreBETWEEN70AND85THEN1ELSE0END)AS85-70,SUM(CASEWHENscoreBETWEEN60AND70THEN1ELSE0END)AS70-60,SUM(CASEWHENscore<60THEN1ELSE0END)AS60-FROMSC,Coursew

21、hereSC.C#=Course.C#GROUPBYSC.C#,Cname;24、查询学生平均成绩及其名次SELECT1+(SELECTCOUNT(distinct平均成绩)FROM(SELECTS#,AVG(score)AS平均成绩FROMSCGROUPBYS#)AST1WHERE?F均成绩>T2.平均成绩)as名次,S#as学生学号,平均成绩FROM(SELECTS#,AVG(score)平均成绩FROMSCGROUPBYS#)AST2ORDERB什均成绩desc;25、查询各科成绩前三名的记录:(不考虑成绩并列情况)SELECTtl.S#as学生ID,t1.C#as课程ID,Sco

22、reas分数FROMSCt1WHEREscoreIN(SELECTTOP3scoreFROMSCWHEREt1.C#=C#ORDERBYscoreDESC)ORDERBYtl.C#;26、查询每门课程被选修的学生数selectc#,count(S#)fromscgroupbyC#;27、查询出只选修了一门课程的全部学生的学号和姓名selectSC.S#,Student.Sname,count(C#)AS选课数fromSC,StudentwhereSC.S#=Student.S#groupbySC.S#,Student.Snamehavingcount(C#)=1;28、查询男生、女生人数Sel

23、ectcount(Ssex)as男生人数fromStudentgroupbySsexhavingSsex='男'Selectcount(Ssex)as女生人数fromStudentgroupbySsexhavingSsex='女'29、查询姓“张的学生名单SELECTSnameFROMStudentWHERESnamelike'张'30、查询同名同性学生名单,并统计同名人数selectSname,count(*)fromStudentgroupbySnamehavingcount(*)>1;31、1981年出生的学生名单(注：Student

24、表中Sage列的类型是datetime)selectSname,CONVERT(char(11),DATEPART(year,Sage)asagefromstudentwhereCONVERT(char(11),DATEPART(year,Sage)尸1981'32、查询每门课程的平均成绩,结果按平均成绩升序排列,平均成绩相同时,按课程号降序排列SelectC#,Avg(score)fromSCgroupbyC#orderbyAvg(score),C#DESC;33、查询平均成绩大于85的所有学生的学号、姓名和平均成绩selectSname,SC.S#,avg(score)fromSt

25、udent,SCwhereStudent.S#=SC.S#groupbySC.S#,Snamehavingavg(score)>85;34、查询课程名称为“数据库,且分数低于60的学生姓名和分数SelectSname,isnull(score,0)fromStudent,SC,CoursewhereSC.S#=Student.S#andSC.C#=Course.C#andCourse.Cname='数据库'andscore<60;35、查询所有学生的选课情况；SELECTSC.S#,SC.C#,Sname,CnameFROMSC,Student,Coursewher

26、eSC.S#=Student.S#andSC.C#=Course.C#;36、查询任何一门课程成绩在70分以上的姓名、课程名称和分数；SELECTdistinctstudent.S#,student.Sname,SC.C#,SC.scoreFROMstudent,ScWHERESC.score>=70ANDSC.S#=student.S#;37、查询不及格的课程,并按课程号从大到小排列selectc#fromscwherescore<60orderbyC#;38、查询课程编号为003且课程成绩在80分以上的学生的学号和姓名；selectSC.S#,Student.Snamefrom

27、SC,StudentwhereSC.S#=Student.S#andScore>80andC#='003'39、求选了课程的学生人数selectcount(*)fromsc;40、查询选修“叶平老师所授课程的学生中,成绩最高的学生姓名及其成绩selectStudent.Sname,scorefromStudent,SC,CourseC,TeacherwhereStudent.S#=SC.S#andSC.C#=C.C#andC.T#=Teacher.T#andTeacher.Tname='叶平'andSC.score=(selectmax(score)fromSCwhereC#=C.C#);41、查询各个课程及相应的选修人数selectcount(*)fromscgroupbyC#;42、查询不同课程成绩相同的学生的学号、课程号、学生成绩selectdistinctA.S#,B.scorefromSCA,SCBwhereA.Score=B.