SQL常見面試題集(一)

1、SQL試題集(一)Student(S#,Sname,Sage,Ssex)學(xué)生表Course(C#,Cname,T#)課程表SC(S#,C#,score)成績(jī)表Teacher(T#,Tname)教師表問題：1、查詢“001課程比“002課程成績(jī)高的所有學(xué)生的學(xué)號(hào)；selecta.S#from(selects#,scorefromSCwhereC#='001')a,(selects#,scorefromSCwhereC#='002')bwherea.score>b.scoreanda.s#=b.s#;2、查詢平均成績(jī)大于60分的同學(xué)的學(xué)號(hào)和平均成績(jī)；selec

2、tS#,avg(score)fromscgroupbyS#havingavg(score)>60;3、查詢所有同學(xué)的學(xué)號(hào)、姓名、選課數(shù)、總成績(jī)；selectStudent.S#,Student.Sname,count(SC.C#),sum(score)fromStudentleftOuterjoinSConStudent.S#=SC.S#groupbyStudent.S#,Sname4、查詢姓“李的老師的個(gè)數(shù)；selectcount(distinct(Tname)fromTeacherwhereTnamelike'李'5、查詢沒學(xué)過(guò)“葉平老師課的同學(xué)的學(xué)號(hào)、姓名；sele

3、ctStudent.S#,Student.SnamefromStudentwhereS#notin(selectdistinct(SC.S#)fromSC,Course,TeacherwhereSC.C#=Course.C#andTeacher.T#=Course.T#andTeacher.Tname='葉平');6、查詢學(xué)過(guò)“001并且也學(xué)過(guò)編號(hào)“002課程的同學(xué)的學(xué)號(hào)、姓名；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、查詢學(xué)過(guò)“葉平老師所教的所有課的同學(xué)的學(xué)號(hào)、姓名；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、查詢課程編號(hào)“002的成績(jī)比課程編號(hào)“001課程低的所有同學(xué)的學(xué)號(hào)、姓名；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、查詢所有課程成績(jī)小于60分的同學(xué)

6、的學(xué)號(hào)、姓名；selectS#,SnamefromStudentwhereS#notin(selectStudent.S#fromStudent,SCwhereS.S#=SC.S#andscore>60);10、查詢沒有學(xué)全所有課的同學(xué)的學(xué)號(hào)、姓名；selectStudent.S#,Student.SnamefromStudent,SCwhereStudent.S#=SC.S#groupbyStudent.S#,Student.Snamehavingcount(C#)<(selectcount(C#)fromCourse);11、查詢至少有一門課與學(xué)號(hào)為“1001的同學(xué)所學(xué)相同的同

7、學(xué)的學(xué)號(hào)和姓名；selectS#,SnamefromStudent,SCwhereStudent.S#=SC.S#andC#inselectC#fromSCwhereS#='1001'12、查詢至少學(xué)過(guò)學(xué)號(hào)為“001同學(xué)所有一門課的其他同學(xué)學(xué)號(hào)和姓名；selectdistinctSC.S#,SnamefromStudent,SCwhereStudent.S#=SC.S#andC#in(selectC#fromSCwhereS#='001');13、把“SC表中“葉平老師教的課的成績(jī)都更改為此課程的平均成績(jī)；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號(hào)的同學(xué)學(xué)習(xí)的課程完全相同的其他同學(xué)學(xué)號(hào)和姓名；selectS#fromSCwhereC#in(selectC#fromSCwhereS#='1002')groupbyS#havingcount(*)=(selectcount(*)fromSCwhereS#='1002');15、

9、刪除學(xué)習(xí)“葉平老師課的SC表記錄；DelectSCfromcourse,TeacherwhereCourse.C#=SC.C#andCourse.T#=Teacher.T#andTname='葉平'16、向SC表中插入一些記錄,這些記錄要求符合以下條件：沒有上過(guò)編號(hào)“003課程的同學(xué)學(xué)號(hào)、2、號(hào)課的平均成績(jī)；InsertSCselectS#,'002',(Selectavg(score)fromSCwhereC#='002')fromStudentwhereS#notin(SelectS#fromSCwhereC#='002');

10、17、按平均成績(jī)從高到低顯示所有學(xué)生的“數(shù)據(jù)庫(kù)、“企業(yè)治理、“英語(yǔ)三門的課程成績(jī),按如下形式顯示：學(xué)生ID,數(shù)據(jù)庫(kù),企業(yè)治理,英語(yǔ),有效課程數(shù),有效平均分SELECTS#as學(xué)生ID,(SELECTscoreFROMSCWHERESC.S#=t.S#ANDC#='004')AS數(shù)據(jù)庫(kù),(SELECTscoreFROMSCWHERESC.S#=t.S#ANDC#='001')AS企業(yè)治理,(SELECTscoreFROMSCWHERESC.S#=t.S#ANDC#='006')AS英語(yǔ),COUNT(*)AS有效課程數(shù),AVG(t.score)AS平

11、均成績(jī)FROMSCAStGROUPBYS#ORDERBYavg(t.score)18、查詢各科成績(jī)最高和最低的分：以如下形式顯示：課程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、按各科平均成績(jī)從低到高和及格率的百分?jǐn)?shù)從高到低順序SELECTt.C#AS課程號(hào),max(course.Cname)AS課程名,isnull(AVG(score),0)AS平均成績(jī),100*SUM(CASEWHENisnull(score,0)>=60THEN1ELSE0END)/COUNT(*)AS及格百分?jǐn)?shù)FROMSCT,Coursewheret.C#=course.C#GROUPBYt.C#ORDERBY100*SUM(CASEWHENsnull(score,0)>=60THEN1ELSE0END)/COUNT(*)DESC20、查詢?nèi)缦抡n程平均成

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

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

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

16、D)AS數(shù)據(jù)庫(kù)及格百分?jǐn)?shù)FROMSC21、查詢不同老師所教不同課程平均分從高到低顯示SELECTmax(Z.T#)AS教師ID,MAX(Z.Tname)AS教師姓名,C.C#AS課程ID,MAX(C.Cname)AS課程名稱,AVG(Score)AS平均成績(jī)FROMSCAST,CourseASC,TeacherASZwhereT.C#=C.C#andC.T#=Z.T#GROUPBYC.C#ORDERBYAVG(Score)DESC22、查詢?nèi)缦抡n程成績(jī)第3名到第6名的學(xué)生成績(jī)單：企業(yè)治理(001),馬克思(002),UML(003),數(shù)據(jù)庫(kù)(004)學(xué)生ID,學(xué)生女4名,企業(yè)治理,馬克思,UM

17、L,數(shù)據(jù)庫(kù),平均成績(jī)SELECTDISTINCTtop3SC.S#As學(xué)生學(xué)號(hào),Student.SnameAS學(xué)生姓名,T1.scoreAS企業(yè)治理,T2.scoreAS馬克思,T3.scoreASUML,T4.scoreAS數(shù)據(jù)庫(kù),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)計(jì)列印各科成績(jī),各分?jǐn)?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、查詢學(xué)生平均成績(jī)及其名次SELECT1+(SELECTCOUNT(distinct平均成績(jī))FROM(SELECTS#,AVG(score)AS平均成績(jī)FROMSCGROUPBYS#)AST1WHERE?F均成績(jī)>T2.平均成績(jī))as名次,S#as學(xué)生學(xué)號(hào),平均成績(jī)FROM(SELECTS#,AVG(score)平均成績(jī)FROMSCGROUPBYS#)AST2ORDERB什均成績(jī)desc;25、查詢各科成績(jī)前三名的記錄:(不考慮成績(jī)并列情況)SELECTtl.S#as學(xué)生ID,t1.C#as課程ID,Sco

22、reas分?jǐn)?shù)FROMSCt1WHEREscoreIN(SELECTTOP3scoreFROMSCWHEREt1.C#=C#ORDERBYscoreDESC)ORDERBYtl.C#;26、查詢每門課程被選修的學(xué)生數(shù)selectc#,count(S#)fromscgroupbyC#;27、查詢出只選修了一門課程的全部學(xué)生的學(xué)號(hào)和姓名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、查詢姓“張的學(xué)生名單SELECTSnameFROMStudentWHERESnamelike'張'30、查詢同名同性學(xué)生名單,并統(tǒng)計(jì)同名人數(shù)selectSname,count(*)fromStudentgroupbySnamehavingcount(*)>1;31、1981年出生的學(xué)生名單(注：Student

24、表中Sage列的類型是datetime)selectSname,CONVERT(char(11),DATEPART(year,Sage)asagefromstudentwhereCONVERT(char(11),DATEPART(year,Sage)尸1981'32、查詢每門課程的平均成績(jī),結(jié)果按平均成績(jī)升序排列,平均成績(jī)相同時(shí),按課程號(hào)降序排列SelectC#,Avg(score)fromSCgroupbyC#orderbyAvg(score),C#DESC;33、查詢平均成績(jī)大于85的所有學(xué)生的學(xué)號(hào)、姓名和平均成績(jī)selectSname,SC.S#,avg(score)fromSt

25、udent,SCwhereStudent.S#=SC.S#groupbySC.S#,Snamehavingavg(score)>85;34、查詢課程名稱為“數(shù)據(jù)庫(kù),且分?jǐn)?shù)低于60的學(xué)生姓名和分?jǐn)?shù)SelectSname,isnull(score,0)fromStudent,SC,CoursewhereSC.S#=Student.S#andSC.C#=Course.C#andCourse.Cname='數(shù)據(jù)庫(kù)'andscore<60;35、查詢所有學(xué)生的選課情況；SELECTSC.S#,SC.C#,Sname,CnameFROMSC,Student,Coursewher

26、eSC.S#=Student.S#andSC.C#=Course.C#;36、查詢?nèi)魏我婚T課程成績(jī)?cè)?0分以上的姓名、課程名稱和分?jǐn)?shù)；SELECTdistinctstudent.S#,student.Sname,SC.C#,SC.scoreFROMstudent,ScWHERESC.score>=70ANDSC.S#=student.S#;37、查詢不及格的課程,并按課程號(hào)從大到小排列selectc#fromscwherescore<60orderbyC#;38、查詢課程編號(hào)為003且課程成績(jī)?cè)?0分以上的學(xué)生的學(xué)號(hào)和姓名；selectSC.S#,Student.Snamefrom

27、SC,StudentwhereSC.S#=Student.S#andScore>80andC#='003'39、求選了課程的學(xué)生人數(shù)selectcount(*)fromsc;40、查詢選修“葉平老師所授課程的學(xué)生中,成績(jī)最高的學(xué)生姓名及其成績(jī)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、查詢各個(gè)課程及相應(yīng)的選修人數(shù)selectcount(*)fromscgroupbyC#;42、查詢不同課程成績(jī)相同的學(xué)生的學(xué)號(hào)、課程號(hào)、學(xué)生成績(jī)selectdistinctA.S#,B.scorefromSCA,SCBwhereA.Score=B.