最叼sql查询语句练习-有答案

最叼的SQL查询语句练习，学习后不必再学其它。我用的 ame,T#)课程表SC(S#,C#,score)成绩表Teacher(T#,Tname)教师表

selecta.S#from(selects#,scorefromSCwhereC#='001')a,(selects#,scorefromSCwhereC#='002')b 60分的同学的学号和平均成绩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、查询学过1并且也学过编 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表中插入一些记录，这些记录要求符合以下条件：没有上过编号 3学学号、2、InsertSCselectS#,'002',(SelectfromSCwhereC#='002')fromStudentwhereS#notin(SelectS#fromSCwhere 数据库、企业管理、英语三门的课程成绩， 学生ID,,数据库,企业管理,英语,有效课程数,有效平均分SELECTSas学生,(SELECTscoreFROMSCWHERESC.S#=t.S#ANDC#='004AS,(SELECTscoreFROMSCWHERESC.S#=t.SANDC#='001') ,(SELECTscoreFROMSCWHERESC.S#=t.S#ANDC#='006AS,COUNT*) 有效课程数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# 课程号 课程名,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行"显示):企业管理（001）（002OO& 003SELECTSUMCASEWHENC#='001'THENscoreELSE0ENDSUMCASEC#WHEN'001'THEN1ELSE0ENDAS企业管理平均分 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数据库平均分*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名的学生成绩单：企业管理 001，002， 003[学生ID],[学生],企业管理,,UML,数据库,平均成绩 top3SC.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,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学生学号,平均成绩FROMSELECTS#,AVGscore)平均成绩FROMSCGROUPBY)ASORDER :(不考虑成绩并列情况 SELECTt1.S#as学生ID,t1.C#as课程ID,Scoreas分数FROMSCt1WHEREscoreIN(SELECTTOP3scoreFROMSCWHEREt1.C#= ORDERBYscore)ORDERBYselectc#,count(S#)fromscgroupbyselectSC.S#,Student.Sname,count(C#)AS选课数fromSC,StudentwhereSC.S#=Student.S#groupbySC.S#,Student.SnamehavingcountSelectcount(Ssex)as男生人数 fromStudentgroupbySsexhavingSsex='男';Selectcount(Ssex)as人数 fromStudentgroupbySsexhavingSsex='女'；SELECTSnameFROMStudentWHERESnamelike张selectSname,count(*)fromStudentgroupbySname 31、1981年出生的学生(注：Student表中Sage列的类型是 selectSname, CONVERT(char(11),DATEPART(year,Sage))asagefrom SelectC#,Avg(score)fromSCgroupbyC#orderbyAvg(score),C#DESC 85的所有学生的学号、和平均成fromStudent,SCwhereStudent.S#=SC.S#groupbySC.S#,Sname 数据库，且分数低 60的学生和分fromStudent,SC,CoursewhereSC.S#=Student.S#and ame='数据库'andscoreSELECT FROMwhereSC.S#=Student.S#andSC.C#=Course.C# distinctstudent.S#,student.Sname,SC.C#,SC.scoreFROMstudent,ScWHERESC.score>=70ANDselectc#fromscwherescore<60orderbyC# 003且课程成绩 80分以上的学生的学号和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