测试数据SQL

--1.学生表Student(S#,Sname,Sage,Ssex)--S#学生编号,Sname学生姓名,Sage出生年月,Ssex学生性别--2.课程表Course(C#,Cname,T#)--C#--课程编号,Cname课程名称,T#教师编号--3.教师表Teacher(T#,Tname)--T#教师编号,Tname教师姓名--4.成绩表SC(S#,C#,score)--S#学生编号,C#课程编号,score分数--创建测试数据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)go--1、查询01课程比02课程成绩高的学生的信息及课程分数--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课程成绩低的学生的信息及课程分数--2.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--2.2、查询同时存在01课程和02课程的情况和不存在01课程但存在02课程的情况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(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分的同学的学生编号和学生姓名和平均成绩--4.1、查询在sc表存在成绩的学生信息的SQL语句。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.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、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩--5.1、查询所有有成绩的SQL。selecta.S#[学生编号],a.Sname[学生姓名],count(b.C#)选课总数,sum(score)[所有课程的总成绩]fromStudenta,SCbwherea.S#=b.S#groupbya.S#,a.Snameorderbya.S#--5.2、查询所有(包括有成绩和无成绩)的SQL。selecta.S#[学生编号],a.Sname[学生姓名],count(b.C#)选课总数,sum(score)[所有课程的总成绩]fromStudentaleftjoinSCbona.S#=b.S#groupbya.S#,a.Snameorderbya.S#--6、查询李姓老师的数量--方法1selectcount(Tname)[李姓老师的数量]fromTeacherwhereTnamelikeN'李%'--方法2selectcount(Tname)[李姓老师的数量]fromTeacherwhereleft(Tname,1)=N'李'/*李姓老师的数量-----------1*/--7、查询学过张三老师授课的同学的信息selectdistinctStudent.*fromStudent,SC,Course,TeacherwhereStudent.S#=SC.S#andSC.C#=Course.C#andCourse.T#=Teacher.T#andTeacher.Tname=N'张三'orderbyStudent.S#--8、查询没学过张三老师授课的同学的信息selectm.*fromStudentmwhereS#notin(selectdistinctSC.S#fromSC,Course,TeacherwhereSC.C#=Course.C#andCourse.T#=Teacher.T#andTeacher.Tname=N'张三')orderbym.S#--9、查询学过编号为01并且也学过编号为02的课程的同学的信息--方法1selectStudent.*fromStudent,SCwhereStudent.S#=SC.S#andSC.C#='01'andexists(Select1fromSCSC_2whereSC_2.S#=SC.S#andSC_2.C#='02')orderbyStudent.S#--方法2selectStudent.*fromStudent,SCwhereStudent.S#=SC.S#andSC.C#='02'andexists(Select1fromSCSC_2whereSC_2.S#=SC.S#andSC_2.C#='01')orderbyStudent.S#--方法3selectm.*fromStudentmwhereS#in(selectS#from(selectdistinctS#fromSCwhereC#='01'unionallselectdistinctS#fromSCwhereC#='02')tgroupbyS#havingcount(1)=2)orderbym.S#--10、查询学过编号为01但是没有学过编号为02的课程的同学的信息--方法1selectStudent.*fromStudent,SCwhereStudent.S#=SC.S#andSC.C#='01'andnotexists(Select1fromSCSC_2whereSC_2.S#=SC.S#andSC_2.C#='02')orderbyStudent.S#--方法2selectStudent.*fromStudent,SCwhereStudent.S#=SC.S#andSC.C#='01'andStudent.S#notin(SelectSC_2.S#fromSCSC_2whereSC_2.S#=SC.S#andSC_2.C#='02')orderbyStudent.S#--11、查询没有学全所有课程的同学的信息--11.1、selectStudent.*fromStudent,SCwhereStudent.S#=SC.S#groupbyStudent.S#,Student.Sname,Student.Sage,Student.Ssexhavingcount(C#)(selectcount(C#)fromCourse)--11.2sele