《sqlserver经典查询练习!!!!!》由会员分享，可在线阅读，更多相关《sqlserver经典查询练习!!!!!（21页珍藏版）》请在金锄头文库上搜索。
1、细心整理经典练习-创立测试数据create table Student(S# varchar(10),Sname nvarchar(10),Sage datetime,Ssex nvarchar(10)insert into Student values(01 , N赵雷 , 1990-01-01 , N男)insert into Student values(02 , N钱电 , 1990-12-21 , N男)insert into Student values(03 , N孙风 , 1990-05-20 , N男)insert into Student values(04 , N李云 ,
2、 1990-08-06 , N男)insert into Student values(05 , N周梅 , 1991-12-01 , N女)insert into Student values(06 , N吴兰 , 1992-03-01 , N女)insert into Student values(07 , N郑竹 , 1989-07-01 , N女)insert into Student values(08 , N王菊 , 1990-01-20 , N女)create table Course(C# varchar(10),Cname nvarchar(10),T# varchar(10
3、)insert into Course values(01 , N语文 , 02)insert into Course values(02 , N数学 , 01)insert into Course values(03 , N英语 , 03)create table Teacher(T# varchar(10),Tname nvarchar(10)insert into Teacher values(01 , N张三)insert into Teacher values(02 , N李四)insert into Teacher values(03 , N王五)create table SC(S
4、# varchar(10),C# varchar(10),score decimal(18,1)insert into SC values(01 , 01 , 80)insert into SC values(01 , 02 , 90)insert into SC values(01 , 03 , 99)insert into SC values(02 , 01 , 70)insert into SC values(02 , 02 , 60)insert into SC values(02 , 03 , 80)insert into SC values(03 , 01 , 80)insert
5、into SC values(03 , 02 , 80)insert into SC values(03 , 03 , 80)insert into SC values(04 , 01 , 50)insert into SC values(04 , 02 , 30)insert into SC values(04 , 03 , 20)insert into SC values(05 , 01 , 76)insert into SC values(05 , 02 , 87)insert into SC values(06 , 01 , 31)insert into SC values(06 ,
6、03 , 34)insert into SC values(07 , 02 , 89)insert into SC values(07 , 03 , 98)go-1、查询01课程比02课程成果高的学生的信息及课程分数-1.1、查询同时存在01课程和02课程的状况select a.* , b.score 课程01的分数,c.score 课程02的分数 from Student a , SC b , SC c where a.S# = b.S# and a.S# = c.S# and b.C# = 01 and c.C# = 02 and b.score c.score-1.2、查询同时存在01课
7、程和02课程的状况和存在01课程但可能不存在02课程的状况(不存在时显示为null)(以下存在一样内容时不再说明)select a.* , b.score 课程01的分数,c.score 课程02的分数 from Student a left join SC b on a.S# = b.S# and b.C# = 01left join SC c on a.S# = c.S# and c.C# = 02where b.score isnull(c.score,0)-2、查询01课程比02课程成果低的学生的信息及课程分数-2.1、查询同时存在01课程和02课程的状况select a.* , b.
8、score 课程01的分数,c.score 课程02的分数 from Student a , SC b , SC c where a.S# = b.S# and a.S# = c.S# and b.C# = 01 and c.C# = 02 and b.score c.score-2.2、查询同时存在01课程和02课程的状况和不存在01课程但存在02课程的状况select a.* , b.score 课程01的分数,c.score 课程02的分数 from Student a left join SC b on a.S# = b.S# and b.C# = 01left join SC c o
9、n a.S# = c.S# and c.C# = 02where isnull(b.score,0) = 60 order by a.S#-4、查询平均成果小于60分的同学的学生编号和学生姓名和平均成果-4.1、查询在sc表存在成果的学生信息的SQL语句。select a.S# , a.Sname , cast(avg(b.score) as decimal(18,2) avg_scorefrom Student a , sc bwhere a.S# = b.S#group by a.S# , a.Snamehaving cast(avg(b.score) as decimal(18,2) 6
10、0order by a.S#-4.2、查询在sc表中不存在成果的学生信息的SQL语句。select a.S# , a.Sname , isnull(cast(avg(b.score) as decimal(18,2),0) avg_scorefrom Student a left join sc bon a.S# = b.S#group by a.S# , a.Snamehaving isnull(cast(avg(b.score) as decimal(18,2),0) 60 order by a.S#-5、查询全部同学的学生编号、学生姓名、选课总数、全部课程的总成果-5.1、查询全部有成果
11、的SQL。select a.S# 学生编号, a.Sname 学生姓名, count(b.C#) 选课总数, sum(score) 全部课程的总成果from Student a , SC b where a.S# = b.S# group by a.S#,a.Sname order by a.S#-5.2、查询全部(包括有成果和无成果)的SQL。select a.S# 学生编号, a.Sname 学生姓名, count(b.C#) 选课总数, sum(score) 全部课程的总成果from Student a left join SC b on a.S# = b.S# group by a.S
12、#,a.Sname order by a.S#-6、查询李姓老师的数量 -方法1select count(Tname) 李姓老师的数量 from Teacher where Tname like N李%-方法2select count(Tname) 李姓老师的数量 from Teacher where left(Tname,1) = N李/*李姓老师的数量 - 1*/-7、查询学过张三老师授课的同学的信息 select distinct Student.* from Student , SC , Course , Teacher where Student.S# = SC.S# and SC.
13、C# = Course.C# and Course.T# = Teacher.T# and Teacher.Tname = N张三order by Student.S#-8、查询没学过张三老师授课的同学的信息 select m.* from Student m where S# not in (select distinct SC.S# from SC , Course , Teacher where SC.C# = Course.C# and Course.T# = Teacher.T# and Teacher.Tname = N张三) order by m.S#-9、查询学过编号为01并且也学过编号为02的课程的同学的信息-方法1select Student.* from Student , SC where Student.S# = SC.S# and SC.C# = 01 and exists (Select 1 from SC SC_2 where SC_2.S# = SC.S# and SC_2.C# = 02) order by Student.S#-方法2select Student.* from Student , SC where Student.S# = SC.S# and SC.C# = 02 and exists (Sele
