Sql高级查询练习题(有答案!)

--createdatabasepractice--onprimary--(name='practice_data',--filename='E:\sqlspace\practice_data.mdf'--)--usepractice--createtableStudent(S#varchar(10),Snamenvarchar(10),Sagedatetime,Ssexnvarchar(10))--insertintoStudentvalues('01',N'赵雷','1990-01-01',N'男')--insertintoStudentvalues('02',N'钱电','1990-12-21',N'男')--insertintoStudentvalues('03',N'孙风','1990-05-20',N'男')--insertintoStudentvalues('04',N'李云','1990-08-06',N'男')--insertintoStudentvalues('05',N'周梅','1991-12-01',N'女')--insertintoStudentvalues('06',N'吴兰','1992-03-01',N'女')--insertintoStudentvalues('07',N'郑竹','1989-07-01',N'女')--insertintoStudentvalues('08',N'王菊','1990-01-20',N'女')--createtableCourse(C#varchar(10),Cnamenvarchar(10),T#varchar(10))--insertintoCoursevalues('01',N'语文','02')--insertintoCoursevalues('02',N'数学','01')--insertintoCoursevalues('03',N'英语','03')--createtableTeacher(T#varchar(10),Tnamenvarchar(10))--insertintoTeachervalues('01',N'张三')--insertintoTeachervalues('02',N'李四')--insertintoTeachervalues('03',N'王五')--createtableSC(S#varchar(10),C#varchar(10),scoredecimal(18,1))--insertintoSCvalues('01','01',80)--insertintoSCvalues('01','02',90)--insertintoSCvalues('01','03',99)--insertintoSCvalues('02','01',70)--insertintoSCvalues('02','02',60)--insertintoSCvalues('02','03',80)--insertintoSCvalues('03','01',80)--insertintoSCvalues('03','02',80)--insertintoSCvalues('03','03',80)--insertintoSCvalues('04','01',50)--insertintoSCvalues('04','02',30)--insertintoSCvalues('04','03',20)--insertintoSCvalues('05','01',76)--insertintoSCvalues('05','02',87)--insertintoSCvalues('06','01',31)--insertintoSCvalues('06','03',34)--insertintoSCvalues('07','02',89)--insertintoSCvalues('07','03',98)--gousepracticeselect*fromSCselect*fromTeacherselect*fromStudentselect*fromCourse--1、查询01课程比02课程成绩高的学生的信息及课程分数selecta.*,b.scoreas'01课程分数',c.scoreas'02课程分数'fromstudentasaleftjoinSCasbona.S#=b.S#andb.C#='01'leftjoinSCasconc.S#=a.S#andc.C#='02'whereb.scoreISNULL(c.score,0)--1.1、查询同时存在01课程和02课程的情况selecta.*,b.score[课程'01'的分数],c.score[课程'02'的分数]fromStudenta,SCb,SCcwherea.S#=b.S#anda.S#=c.S#andb.C#='01'andc.C#='02'andb.scorec.score--1.2、查询同时存在01课程和02课程的情况和存在01课程但可能不存在02课程的情况(不存在时显示为null)(以下存在相同内容时不再解释)selecta.*,b.score[课程01的分数],c.score[课程02的分数]fromStudentaleftjoinSCbona.S#=b.S#andb.C#='01'leftjoinSCcona.S#=c.S#andc.C#='02'whereb.scoreisnull(c.score,0)--2、查询01课程比02课程成绩低的学生的信息及课程分数selecta.*,c.scoreas'01课程分数',b.scoreas'02课程分数'fromStudentasaleftjoinSCasconc.S#=a.S#andc.C#='01'leftjoinSCasbonb.S#=a.S#andb.C#='02'wherec.scoreISNULL(b.score,0)--2.1、查询同时存在01课程和02课程的情况selecta.*,b.scoreas'01课程分数',c.scoreas'02课程分数'fromstudentasa,SCasb,SCascwhereb.C#='01'andc.C#='02'andb.S#=a.S#andc.S#=a.S#andb.scorec.score----------------------------------------------selecta.*,b.score[课程'01'的分数],c.score[课程'02'的分数]fromStudenta,SCb,SCcwherea.S#=b.S#anda.S#=c.S#andb.C#='01'andc.C#='02'andb.scorec.score--2.2、查询同时存在01课程和02课程的情况和不存在01课程但存在02课程的情况--分析：即如果没有01的分数，有02的分数就认为是01的分数比02的分数低，就要用isnull函数，判断是否为空，如果空则用0分替代--所以查询时要用left做外链接,把所有的人都查进来selecta.*,b.scoreas'01课程分数',c.scoreas'02课程分数'fromStudentasaleftjoinSCasbona.S#=b.S#andb.C#='01'leftjoinSCasconc.S#=a.S#andc.C#='02'whereISNULL(b.score,0)c.score-------------------------------------------------------------------selecta.*,b.score[课程01的分数],c.score[课程02的分数]fromStudentaleftjoinSCbona.S#=b.S#andb.C#='01'leftjoinSCcona.S#=c.S#andc.C#='02'whereisnull(b.score,0)c.score--3、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩selecta.S#,a.Sname,CAST(avg(c.score)asdecimal(5,2))fromStudentasajoinSCascona.S#=c.S#groupbya.S#,a.SnamehavingCAST(avg(c.score)asdecimal(5,2))=60orderbya.S#----------------------------------------------------------selecta.S#,a.Sname,cast(avg(b.score)asdecimal(18,2))avg_scorefromStudenta,scbwherea.S#=b.S#groupbya.S#,a.Snamehavingcast(avg(b.score)asdecimal(18,2))=60orderbya.S#--4、查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩selecta.S#学号,a.Sname姓名,CAST(AVG(b.score)asdecimal(5,2))平均分fromStudentasa,SCasbwherea.S#=b.S#groupbya.S#,a.SnamehavingCAST(AVG(b.score)asdecimal(5,2))60------------------------------------------------------4.1、查询在sc表存在成绩的学生信息的SQL语句。selecta.*fromstudentasawherea.S#in(selectdistinct(b.S#)fromSCasb)---------------------------------------------------selecta.S#,a.Sname,cast(avg(b.score)asdecimal(18,2))avg_scorefromStudenta,scbwherea.S#=b.S#groupbya.S#,a.Snamehavingcast(avg(b.score)asdecimal(18,2))60orderbya.S#--4.2、查询在sc表中不存在成绩的学生信息的SQL语句。selecta.*fromstudentasawherea.S#notin(selectb.S#fromSCasb)--------------------------------------------------selecta.S#,a.Sname,isnull(cast(avg(b.score)asdecimal(18,2)),0)avg_scorefromStudentaleftjoinscbona.S#=b.S#groupbya.S#,a.Snamehavingisnull(cast(avg(b.score)asdecimal(18,2)),0)60orderbya.S#--5、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩selecta.S#学生编号,a.Sname姓名,COUNT(b.C#)选课总数,总成绩=isnull(convert(varchar(20),sum(b.score)),'空')---聚合函数里面的内容可以不放分组里面fromStudentasajoinSCasbona.S#=b.S#groupbya.S#,b.S#,a.Sname----------------------------------------usepractice--5.1、查询所有有成绩的SQL。selecta.S#学号,a.Sname姓名,count(b.C#)选课总数,SUM(b.score)总成绩fromStudentasa,SCa