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、查詢所有同學的學號、姓名、選課數(shù)、總成績；selectStudent.S#,Student.Sname,count(SC.C#),sum(score)fromStudentleftOuterjoinSConStudent.S#=SC.S#groupbyStudent.S#,Sname4、查詢姓“李的老師的個數(shù)；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、按平均成績從高到低顯示所有學生的“數(shù)據(jù)庫、“企業(yè)治理、“英語三門的課程成績,按如下形式顯示：學生ID,數(shù)據(jù)庫,企業(yè)治理,英語,有效課程數(shù),有效平均分SELECTS#as學生ID,(SELECTscoreFROMSCWHERESC.S#=t.S#ANDC#='004')AS數(shù)據(jù)庫,(SELECTscoreFROMSCWHERESC.S#=t.S#ANDC#='001')AS企業(yè)治理,(SELECTscoreFROMSCWHERESC.S#=t.S#ANDC#='006')AS英語,COUNT(*)AS有效課程數(shù),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、按各科平均成績從低到高和及格率的百分數(shù)從高到低順序SELECTt.C#AS課程號,max(course.Cname)AS課程名,isnull(AVG(score),0)AS平均成績,100*SUM(CASEWHENisnull(score,0)>=60THEN1ELSE0END)/COUNT(*)AS及格百分數(shù)FROMSCT,Coursewheret.C#=course.C#GROUPBYt.C#ORDERBY100*SUM(CASEWHENsnull(score,0)>=60THEN1ELSE0END)/COUNT(*)DESC20、查詢?nèi)缦抡n程平均成

13、績和及格率的百分數(shù)(用"1行"顯示)：企業(yè)治理(001),馬克思(002),OO&UML(003),數(shù)據(jù)庫(004)SELECTSUM(CASEWHENC#='001'THENscoreELSE0END)/SUM(CASEC#WHEN'001'THEN1ELSE0END)AS企業(yè)治理平均分,100*SUM(CASEWHENC#='001'ANDscore>=60THEN1ELSE0END)/SUM(CASEWHENC#='001'THEN1ELSE0END)AS企業(yè)治理及格百分數(shù),SUM(CASE

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

15、ASEWHENC#='003'ANDscore>=60THEN1ELSE0END)/SUM(CASEWHENC#='003'THEN1ELSE0END)ASUML及格百分數(shù),SUM(CASEWHENC#='004'THENscoreELSE0END)/SUM(CASED#WHEN004'THEN1ELSE0END)AS數(shù)據(jù)庫平均分,100*SUM(CASEWHENC#='004'ANDscore>=60THEN1ELSE0END)/SUM(CASEWHENC#='004'THEN1ELSE0EN

16、D)AS數(shù)據(jù)庫及格百分數(shù)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、查詢?nèi)缦抡n程成績第3名到第6名的學生成績單：企業(yè)治理(001),馬克思(002),UML(003),數(shù)據(jù)庫(004)學生ID,學生女4名,企業(yè)治理,馬克思,UM

17、L,數(shù)據(jù)庫,平均成績SELECTDISTINCTtop3SC.S#As學生學號,Student.SnameAS學生姓名,T1.scoreAS企業(yè)治理,T2.scoreAS馬克思,T3.scoreASUML,T4.scoreAS數(shù)據(jù)庫,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、統(tǒng)計列印各科成績,各分數(shù)段人數(shù)：課程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分數(shù)FROMSCt1WHEREscoreIN(SELECTTOP3scoreFROMSCWHEREt1.C#=C#ORDERBYscoreDESC)ORDERBYtl.C#;26、查詢每門課程被選修的學生數(shù)selectc#,count(S#)fromscgroupbyC#;27、查詢出只選修了一門課程的全部學生的學號和姓名selectSC.S#,Student.Sname,count(C#)AS選課數(shù)fromSC,StudentwhereSC.S#=Student.S#groupbySC.S#,Student.Snamehavingcount(C#)=1;28、查詢男生、女生人數(shù)Sel

23、ectcount(Ssex)as男生人數(shù)fromStudentgroupbySsexhavingSsex='男'Selectcount(Ssex)as女生人數(shù)fromStudentgroupbySsexhavingSsex='女'29、查詢姓“張的學生名單SELECTSnameFROMStudentWHERESnamelike'張'30、查詢同名同性學生名單,并統(tǒng)計同名人數(shù)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、查詢每門課程的平均成績,結(jié)果按平均成績升序排列,平均成績相同時,按課程號降序排列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、查詢課程名稱為“數(shù)據(jù)庫,且分數(shù)低于60的學生姓名和分數(shù)SelectSname,isnull(score,0)fromStudent,SC,CoursewhereSC.S#=Student.S#andSC.C#=Course.C#andCourse.Cname='數(shù)據(jù)庫'andscore<60;35、查詢所有學生的選課情況；SELECTSC.S#,SC.C#,Sname,CnameFROMSC,Student,Coursewher

26、eSC.S#=Student.S#andSC.C#=Course.C#;36、查詢?nèi)魏我婚T課程成績在70分以上的姓名、課程名稱和分數(shù)；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、求選了課程的學生人數(shù)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、查詢各個課程及相應(yīng)的選修人數(shù)selectcount(*)fromscgroupbyC#;42、查詢不同課程成績相同的學生的學號、課程號、學生成績selectdistinctA.S#,B.scorefromSCA,SCBwhereA.Score=B.