最叼sql查詢語句練習(xí)-有答案

最叼的SQL查詢語句練習(xí)，學(xué)習(xí)后不必再學(xué)其它。我用的 ame,T#)課程表SC(S#,C#,score)成績表Teacher(T#,Tname)教師表

selecta.S#from(selects#,scorefromSCwhereC#='001')a,(selects#,scorefromSCwhereC#='002')b 60分的同學(xué)的學(xué)號(hào)和平均成績fromscgroupbyS#havingavg(score)fromStudentleftOuterjoinSConStudent.S#=SC.S#groupbyStudent.S#,Snameselectcount(distinct(Tname))fromTeacherwhereTnamelike李 fromStudentwhereS#notin(selectdistinct(SC.S#)fromSC,Course,TeacherwhereSC.C#=Course.C#andTeacher.T#=Course.T#andTeacher.Tname='');6、查詢學(xué)過1并且也學(xué)過編 2selectStudent.S#,Student.SnamefromStudent,SCwhereStudent.S#=SC.S#andSC.C#='001'andexists(Select*fromSCasSC_2whereSC_2.S#=SC.S#andSC_2.C#='002');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#=Course.T#andTname=' 0 0SelectS#,Snamefrom(selectStudent.S#,Student.Sname,score,(selectscorefrom SCSC_2whereSC_2.S#=Student.S#andSC_2.C#='002')score2fromStudent,SCwhereStudent.S#=SC.S#andC#='001')S_2wherescore2 selectS#,SnamefromStudentwhereS#notin(selectStudent.S#fromStudent,SCwhereS.S#=SC.S#andfromStudent,SCwhereStudent.S#=SC.S# Student.S#,Student.Sname count(C#)from 10selectS#,SnamefromStudent,SCwhereStudent.S#=SC.S#andC#inselectC#fromSCwhere 1selectdistinctSC.S#,SnamefromStudent,SCwhereStudent.S#=SC.S#andC#in(selectC#fromSCwhereupdateSCsetscore=(selectavg(SC_2.score)fromSCSC_2 Course.T#=Teacher.T#andTeacher.Tname='');selectS#fromSCwhereC#in(selectC#fromSCwhereS#='1002')groupbyS#havingcount(*)=(selectcount(*)fromSCwhere SC表記錄DelectfromcoursewhereCourse.C#=SC.C#andCourse.T#=Teacher.T#andTname=' SC表中插入一些記錄，這些記錄要求符合以下條件：沒有上過編號(hào) 3學(xué)學(xué)號(hào)、2、InsertSCselectS#,'002',(SelectfromSCwhereC#='002')fromStudentwhereS#notin(SelectS#fromSCwhere 數(shù)據(jù)庫、企業(yè)管理、英語三門的課程成績， 學(xué)生ID,,數(shù)據(jù)庫,企業(yè)管理,英語,有效課程數(shù),有效平均分SELECTSas學(xué)生,(SELECTscoreFROMSCWHERESC.S#=t.S#ANDC#='004AS,(SELECTscoreFROMSCWHERESC.S#=t.SANDC#='001') ,(SELECTscoreFROMSCWHERESC.S#=t.S#ANDC#='006AS,COUNT*) 有效課程數(shù)AVGt.scoreASFROMSCAStGROUPBYORDERBY SELECTL.C#As課程ID,L.scoreAS最高分,R.scoreAS最低分FROMSCL,SCASRWHEREL.C#=R.C#L.score=(SELECTMAX(IL.score)FROMSCASIL,StudentASIMWHEREL.C#=IL.C#andIM.S#=IL.S#GROUPBYIL.C#)=(SELECTMINFROMSCASIRWHERER.C#=IR.C#GROUPBY t.C# 課程號(hào) 課程名,isnull(AVG(score),0) 績,100*SUM(CASE isnullscore,060THEN1ELSE0ENDCOUNT(* FROMSCT,Coursewheret.C#=course.C#GROUPBYt.C#ORDERBY100*SUM(CASE isnull(score,0)>=60THEN10END)/COUNT(*) (用"1行"顯示):企業(yè)管理（001）（002OO& 003SELECTSUMCASEWHENC#='001'THENscoreELSE0ENDSUMCASEC#WHEN'001'THEN1ELSE0ENDAS企業(yè)管理平均分 END)/SUM(CASEWHENC#='001'THEN1ELSE0AS,SUM(CASEWHENC#='002'THENscoreELSE0END)/SUM(CASEC# THEN1ELSE0END) END)/SUM(CASEWHENC#='002'THEN1ELSE0END) ,SUM(CASEWHENC#='003'THENscoreELSE0END)/SUMCASECWHEN'003THEN1ELSE0ENDASUML平均分 ENDSUMCASEWHENC#='003'THEN1ELSE0ENDASUML,SUM(CASEWHENC#='004'THENscoreELSE0ENDSUM(CASEC#WHEN THEN1ELSE0END)AS數(shù)據(jù)庫平均分*SUM(CASEWHENC#='004' score>=60THEN1ELSE0END)/SUM(CASEC#='004'THEN1ELSE0ENDASFROM 教師 ID,MAX(Z.Tname) 教師姓名,C.C# 課程ＩＤ,MAX( ame)AS課程名稱,AVG(Score)AS平均成績FROMSCAST,CourseASC,TeacherASZwhereT.C#=C.C#andC.T#=Z.T#GROUPBYORDERBYAVG(Score) 3名到 6名的學(xué)生成績單：企業(yè)管理 001，002， 003[學(xué)生ID],[學(xué)生],企業(yè)管理,,UML,數(shù)據(jù)庫,平均成績 top3SC.S#As學(xué)生學(xué)號(hào),Student.SnameAS學(xué)生,T1.scoreAS企業(yè)管理,T2.scoreAS ,T3.scoreASUML,T4.scoreAS,ISNULL(T1.score,0)+ISNULL(T2.score,0)+ISNULL(T3.score,0)+ISNULL(T4.score,asFROM LEFTJOINSCASONSC.S#=T1.S#ANDT1.C#='001'LEFTJOINSCAST2ONSC.S#=T2.S#ANDT2.C#='002'LEFTJOINSCAST3ONSC.S#=T3.S#ANDT3.C#='003'LEFTJOINSCAST4ONSC.S#=T4.S#ANDT4.C#=WHEREstudent.S#=SC.S#ISNULL(T1.score,0)+ISNULL(T2.score,0)+ISNULL(T3.score,0)+ISNULL(T4.score,0)NOTINTOP15WITHISNULL(T1.score,0) ISNULL(T2.score,0) ISNULL(T3.score,0) ISNULL(T4.score,0)FROMLEFTJOINscASONsc.S#=T1.S#ANDT1.C#='k1'LEFTJOINscAST2ONsc.S#=T2.S#ANDT2.C#='k2'LEFTJOINscAST3ONsc.S#=T3.S# T3.C#=LEFTJOINscASONsc.S#=T4.S# T4.C#= ISNULL(T1.score,0) ISNULL(T2.score,0) ISNULL(T3.score,0) ISNULL(T4.score,0)DESC); :課程ID,課程名稱,[100-85],[85-70],[70-60],[SELECTSC.Cas課程IDCnameas課程名稱,SUM(CASEWHENscoreBETWEEN85AND100THEN1ELSE0END)AS[100-,SUM(CASEWHENscoreBETWEEN70AND85THEN1ELSE0END)AS[85-,SUM(CASEWHENscoreBETWEEN60AND70THEN1ELSE0END)AS[70-,SUM(CASEWHENscore<60THEN1ELSE0END)AS[60-FROMSC,CoursewhereSC.C#=Course.C#GROUPBY SELECT1+(SELECTCOUNT(distinct平均成績)FROMSELECTS#,AVGscoreAS平均成績FROMSCGROUPBYS#)AS >T2.平均成績)as名次,S#as學(xué)生學(xué)號(hào),平均成績FROMSELECTS#,AVGscore)平均成績FROMSCGROUPBY)ASORDER :(不考慮成績并列情況 SELECTt1.S#as學(xué)生ID,t1.C#as課程ID,Scoreas分?jǐn)?shù)FROMSCt1WHEREscoreIN(SELECTTOP3scoreFROMSCWHEREt1.C#= ORDERBYscore)ORDERBYselectc#,count(S#)fromscgroupbyselectSC.S#,Student.Sname,count(C#)AS選課數(shù)fromSC,StudentwhereSC.S#=Student.S#groupbySC.S#,Student.SnamehavingcountSelectcount(Ssex)as男生人數(shù) fromStudentgroupbySsexhavingSsex='男';Selectcount(Ssex)as人數(shù) fromStudentgroupbySsexhavingSsex='女'；SELECTSnameFROMStudentWHERESnamelike張selectSname,count(*)fromStudentgroupbySname 31、1981年出生的學(xué)生(注：Student表中Sage列的類型是 selectSname, CONVERT(char(11),DATEPART(year,Sage))asagefrom SelectC#,Avg(score)fromSCgroupbyC#orderbyAvg(score),C#DESC 85的所有學(xué)生的學(xué)號(hào)、和平均成fromStudent,SCwhereStudent.S#=SC.S#groupbySC.S#,Sname 數(shù)據(jù)庫，且分?jǐn)?shù)低 60的學(xué)生和分fromStudent,SC,CoursewhereSC.S#=Student.S#and ame='數(shù)據(jù)庫'andscoreSELECT FROMwhereSC.S#=Student.S#andSC.C#=Course.C# distinctstudent.S#,student.Sname,SC.C#,SC.scoreFROMstudent,ScWHERESC.score>=70ANDselectc#fromscwherescore<60orderbyC# 003且課程成績 80分以上的學(xué)生的學(xué)號(hào)和selectSC.S#,Student.Snamefrom SC,StudentwhereSC.S#=Student.S#andScore>80andselectcount(*)fromfromStudent,SC,CoursewhereStudent.S#=SC.S#andSC.C#=C.C#C.T#=Teacher.T#andTeacher.Tname=''andSC.score=(selectmax(score)from SCwhereC#=C.C#);selectcount(*)fromscgro