资源描述:
《sqlserver经典查询练习!!!!!.doc》由会员上传分享,免费在线阅读,更多相关内容在行业资料-天天文库。
1、经典练习--创建测试数据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'男
2、')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'王菊','
3、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))i
4、nsertintoTeachervalues('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('0
5、1','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)insertintoSCval
6、ues('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、查询
7、"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.score>c.score--1.2、查询同时存在"01"课程和"02"课程的情况和存在"01"课程但可能不存在"02"课程的情况(不存在时显示为
8、null)(以下存在相同内容时不再解释)selecta.*,b.score[课程"01"的分数],c.score[课程"02"的分数]fromStudentaleftjoinSCbona.S#=b.S#andb.C#='01'leftjoinSCcona.S#=c.S#andc.C#='02'whereb.score>isnull(c.score,0) --2、查询"01"课程比"02"课程成绩低的学生的信息及课程分数--2.1、查询同时存在"01"课程和"02"课程的情况selecta.*,b