《数据结构域算法设计-SQL语句多表查询教案》由会员分享，可在线阅读，更多相关《数据结构域算法设计-SQL语句多表查询教案（15页珍藏版）》请在金锄头文库上搜索。
1、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 分数*/-创建测试数据create table Student(S# varchar(10),Sname nvarc
2、har(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李云 , 1990-08-06 , N男)insert into Student values(05 , N周梅 , 1991-12
3、-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)insert into Course values(01 , N语文 , 02)insert into Course va
4、lues(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# varchar(10),C# varchar(10),score decimal(18,1)insert into SC
5、 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 into SC values(03 , 02 , 80)insert into SC values(03 , 03 , 80
6、)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 , 03 , 34)insert into SC values(07 , 02 , 89)insert into SC valu
7、es(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 cwhere 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“课程和“02“课程的情况和存在“01“课程但可能不存在“02“课程的情况(不存在时显示为 null)(以下存
8、在相同内容时不再解释)select a.* , b.score 课程“01“的分数,c.score 课程“02“的分数 from Student aleft 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.score 课程01的分数
9、,c.score 课程02的分数 from Student a , SC b , SC cwhere a.S# = b.S# and a.S# = c.S# and b.C# = 01 and c.C# = 02 and b.score = 60order 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# =
10、 b.S#group by a.S# , a.Snamehaving cast(avg(b.score) as decimal(18,2) 01-13、查询和“01“号的同学学习的课程完全相同的其他同学的信息 select Student.* from Student where S# in(select distinct SC.S# from SC where S# = t1.平均成绩) from (select m.S# 学生编号 , m.Sname 学生姓名 ,isnull(cast(avg(score) as decimal(18,2),0) 平均成绩from Student m le
11、ft join SC n on m.S# = n.S# group by m.S# , m.Sname) t1order by px-24.2 查询学生的平均成绩并进行排名，sql 2005用 rank,DENSE_RANK 完成，分平均成绩重复时保留名次空缺和不保留名次空缺两种。select t.* , px = rank() over(order by 平均成绩 desc) from(select m.S# 学生编号 , m.Sname 学生姓名 ,isnull(cast(avg(score) as decimal(18,2),0) 平均成绩from Student m left join
12、 SC n on m.S# = n.S# group by m.S# , m.Sname) torder by pxselect t.* , px = DENSE_RANK() over(order by 平均成绩 desc) from(select m.S# 学生编号 , m.Sname 学生姓名 ,isnull(cast(avg(score) as decimal(18,2),0) 平均成绩from Student m left join SC n on m.S# = n.S# group by m.S# , m.Sname) torder by px-25、查询各科成绩前三名的记录-25
13、.1 分数重复时保留名次空缺select m.* , n.C# , n.score from Student m, SC n where m.S# = n.S# and n.score in (select top 3 score from sc where C# = n.C# order by score desc) order by n.C# , n.score desc-25.2 分数重复时不保留名次空缺，合并名次-sql 2000用子查询实现select * from (select t.* , px = (select count(distinct score) from SC wh
14、ere C# = t.C# and score = t.score) fromsc t) m where px between 1 and 3 order by m.c# , m.px -sql 2005用 DENSE_RANK 实现select * from (select t.* , px = DENSE_RANK() over(partition by c# order by score desc) from sc t) m where pxbetween 1 and 3 order by m.C# , m.px -26、查询每门课程被选修的学生数 select c# , count(S
15、#)学生数 from sc group by C#-27、查询出只有两门课程的全部学生的学号和姓名 select Student.S# , Student.Snamefrom Student , SC where Student.S# = SC.S# group by Student.S# , Student.Snamehaving count(SC.C#) = 2order by Student.S#-28、查询男生、女生人数 select count(Ssex) as 男生人数 from Student where Ssex = N男select count(Ssex) as 女生人数 from Student where Ssex = N女select sum(case when Ssex = N男 then 1 else 0 end) 男生人数,sum(case when Ssex = N女 then 1 else 0end) 女生人数 from studentselect case when Ssex = N男 then N男生人数 else N女生人数 end 男女情况 , count(1) 人数
