《SQL查询练习及答案.docx》由会员分享，可在线阅读，更多相关《SQL查询练习及答案.docx（30页珍藏版）》请在三一文库上搜索。
1、问题及描述：- -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 nvarcha
2、r(10),Sage datetime,Ssexnvarchar(10)insert into Student values(01 , Ninsert into Student values(02 , Ninsert into Student values(03 , Ninsert into Student values(04 , Ninsert into Student values(05 , Ninsert into Student values(06 , Ninsert into Student values(07 , Ninsert into Student values(08 , N
3、赵雷 ,1990-01-01,N男 )钱电 ,1990-12-21,N男 )孙风 ,1990-05-20,N男 )李云 ,1990-08-06,N男 )周梅 ,1991-12-01,N女 )吴兰 ,1992-03-01,N女 )郑竹 ,1989-07-01,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 values(02, N数学,01)insert
4、 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 values(01 , 01 , 80)ins
5、ert 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)insert into SC values(0
6、4 , 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 values(07 , 03 , 98) go- -1
7、、查询01 课程比 02 课程成绩高的学生的信息及课程分数- -1.1 、查询同时存在01课程和 02课程的情况select a.* , b.score 课程 01 的分数 ,c.score 课程 02 的分数 from Student a , SC b , SCc 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课程和 02课程的情况和存在01课程但可能不存在02课程的情况(不存在时显示为null)( 以下存在相同内容时不再解释)select a.
8、* , b.score 课程 01 的分数 ,c.score 课程 02 的分数 from Student a left joinSC b on a.S# = b.S# and b.C# = 01 left join SC c on a.S# = c.S# and c.C# = 02 where b.score isnull(c.score,0)- -2 、查询01 课程比 02 课程成绩低的学生的信息及课程分数- -2.1 、查询同时存在01课程和 02课程的情况select a.* , b.score 课程 01的分数 ,c.score 课程 02 的分数 from Student a ,
9、 SC b , SC cwhere a.S# = b.S# and a.S# = c.S# and b.C# = 01 and c.C# = 02 and b.scorec.score- -2.2 、查询同时存在01课程和 02课程的情况和不存在01课程但存在 02 课程的情况select a.* , b.score 课程 01 的分数 ,c.score 课程 02 的分数 from Student a left joinSC b on a.S# = b.S# and b.C# = 01 left join SC c on a.S# = c.S# and c.C# = 02 where isn
10、ull(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 b where a.S# = b.S# group by a.S# , a.Sname having cast(avg(b.score) as decimal(18,2) 60 order by a.S#- -4.2 、
11、查询在 sc 表中不存在成绩的学生信息的 SQL 语句。select a.S# , a.Sname , isnull(cast(avg(b.score)as decimal(18,2),0) avg_score fromStudent a left join sc b on a.S# = b.S#group by a.S# , a.Snamehavingisnull(cast(avg(b.score) as decimal(18,2),0) 60 order by a.S#- -5 、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩- -5.1 、查询所有有成绩的 SQL 。sel
12、ect 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 b
13、y a.S#,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 Studen
14、t.S# = SC.S# and SC.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张三)orde
15、r by m.S#- -9 、查询学过编号为01并且也学过编号为 02 的课程的同学的信息- -方法 1select Student.* from Student , SC where Student.S# = SC.S# and SC.C# = 01 andexists (Select 1 from SC SC_2 where SC_2.S# = SC.S# and SC_2.C# = 02) order byStudent.S#-方法2 select Student.* from Student , SC where Student.S# = SC.S# and SC.C# = 02 an
16、dexists (Select 1 from SC SC_2 where SC_2.S# = SC.S# and SC_2.C# = 01) order byStudent.S#-方法 3select distinct S#select m.* from Student m where S# in ( select S# from (from SC where C# = 01union all select distinct S# from SC where C# =) order by m.S#02 的课程的同学的信息02) t group by S# having count(1) = 2
17、-10 、查询学过编号为01 但是没有学过编号为-方法1 select Student.* from Student , SC where Student.S# = SC.S# and SC.C# = 01 and notexists (Select 1 from SC SC_2 where SC_2.S# = SC.S# and SC_2.C# = 02) order byStudent.S#- -方法 2 select Student.* from Student , SC where Student.S# = SC.S# and SC.C# = 01and Student.S# not
18、in (Select SC_2.S# from SC SC_2 where SC_2.S# = SC.S# and SC_2.C# = 02) order by Student.S#- -11 、查询没有学全所有课程的同学的信息- -11.1 、select Student.* from Student , SC where Student.S# = SC.S# group by Student.S# , Student.Sname , Student.Sage , Student.Ssex having count(C#) (selectcount(C#) from Course)- -11
19、.2select Student.*from Student left join SCon Student.S#SC.S#group byStudent.S# , Student.Sname , Student.Sage , Student.Ssex having count(C#) (selectcount(C#) from Course)- -12 、查询至少有一门课与学号为 01 的同学所学相同的同学的信息select distinct Student.* from Student , SC where Student.S# = SC.S# and SC.C# in (select C#
20、 from SC where S# = 01) and Student.S# 01- -13 、查询和 01号的同学学习的课程完全相同的其他同学的信息select Student.* from Student where S# in (select distinct SC.S# from SC where S# 01 and SC.C# in (select distinct C# from SC where S# = 01)group by SC.S# havingcount(1) = (select count(1) from SC where S#=01)- -14 、查询没学过 张三
21、老师讲授的任一门课程的学生姓名select student.* from student where student.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 student.S#- -15 、查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩select student.S# , student.sname , cast(avg(score)
22、 as decimal(18,2) avg_score from student , sc where student.S# = SC.S# and student.S# in (select S# from SC wherescore = 2) group by student.S# , student.sname- -16 、检索 01 课程分数小于60 ，按分数降序排列的学生信息select student.* , sc.C# , sc.score from student , sc where student.S# = SC.S# and sc.score =60 ， 中等为： 70-
23、80 ， 优良为： 80-90 ，优秀为： =90- -方法1select m.C# 课 程 编 号 , m.Cname 课 程 名 称 ,max(n.score) 最 高 分 ,min(n.score) 最低分 , cast(avg(n.score) as decimal(18,2) 平均分, cast(selectcount(1) from SC where C# = m.C# and score = 60)*100.0 / (select count(1) from SCwhere C# = m.C#) as decimal(18,2) 及格率 (%), cast(select coun
24、t(1) from SC whereC# = m.C# and score = 70 and score= 80 and score = 90)*100.0 / (select count(1) from SC where C# = m.C#) as decimal(18,2)优秀率(%) from Course m , SC n where m.C# = n.C# group by m.C# , m.Cnameorder by m.C#- -方法2select m.C# 课程编号 , m.Cname 课程名称 , (select max(score) from SC where C#= m.
25、C#) 最高分 , (select min(score) from SC where C# = m.C#) 最低分 , (select cast(avg(score) as decimal(18,2) from SC where C# = m.C#) 平均分 , cast(selectcount(1) from SC where C# = m.C# and score = 60)*100.0 / (select count(1) from SC where C# = m.C#) as decimal(18,2) 及格率 (%), cast(select count(1) from SC whe
26、re C# = m.C# and score = 70 and score = 80 and score = 90)*100.0 / (select count(1) from SC where C# = m.C#) as decimal(18,2)优秀率(%) from Course m order by m.C#- -19 、按各科成绩进行排序，并显示排名- -19.1 sql 2000 用子查询完成- -Score 重复时保留名次空缺select t.* , px = (select count(1) from SC where C# = t.C# and score t.score)
27、+ 1 from sct order by t.c# , px -Score 重复时合并名次select t.* , px = (select count(distinct score)from SC where C# = t.C# and score = t.score) from sc t order by t.c# , px- -19.2 sql 2005 用 rank,DENSE_RANK 完成- -Score 重复时保留名次空缺(rank 完成 ) select t.* , px = rank() over(partition by c# order by score desc) f
28、rom sc t order by t.C# , px -Score 重 复 时 合 并 名 次 (DENSE_RANK 完 成 ) select t.* , px = DENSE_RANK()over(partition by c# order by score desc) from sc t order by t.C# , px-20 、查询学生的总成绩并进行排名-20.1 查询学生的总成绩select m.S# 学生编号 ,总成绩 from Student m left joinorder by 总成绩 desc-20.2 查询学生的总成绩并进行排名，缺和不保留名次空缺两种。select
29、 t1.* , px = (selectm.Sname 学生姓名 ,join SC n on m.S# = n.S#+ 1 from ( select m.S# 学生编号isnull(sum(score),0) 总成绩 m.Sname 学生姓名 ,isnull(sum(score),0)SC n on m.S# = n.S# group by m.S# , m.Snamesql 2000 用子查询完成，分总分重复时保留名次空count(1) from (isnull(sum(score),0)group by m.S# , m.Snamefrom Student m left join SC
30、n on m.S# = n.S#select m.S# 学 生 编 号 ,总成绩 from Student m left) t2 where 总成绩 t1. 总成绩 )m.Sname 学生姓名 ,group-可编辑修改-by m.S# , m.Sname ) t1 order by px select t1.* , px = (select count(distinct 总成绩 )from (select m.S# 学 生编 号 ,m.Sname 学 生 姓 名 ,groupisnull(sum(score),0) 总成绩 from Student m left join SC n on m.
31、S# = n.S#by m.S# , m.Sname ) t2 where 总成绩 = t1. 总成绩 ) from ( select m.S# 学生编 号 ,m.Sname 学生姓名 ,isnull(sum(score),0) 总成绩 fromStudent m left join SC n on m.S# = n.S#group by m.S# , m.Sname ) t1 order bypx- -20.3 查询学生的总成绩并进行排名， sql 2005 用 rank,DENSE_RANK 完成，分总分重复 时保留名次空缺和不保留名次空缺两种。select t.* , px = rank
32、() over(order by 总成绩 desc) from ( select m.S# 学生编号 , m.Sname 学生姓名 ,isnull(sum(score),0) 总成绩 from Student m leftjoin SC n on m.S# = n.S# group by m.S# , m.Sname ) t order by px select t.* , px = DENSE_RANK() over(order by 总成绩 desc) from ( select m.S# 学生编号 , m.Sname 学生姓名 ,isnull(sum(score),0) 总成绩 from
33、 Student m leftjoin SC n on m.S# = n.S# group by m.S# , m.Sname ) t order by px- -21 、查询不同老师所教不同课程平均分从高到低显示select m.T# , m.Tname , cast(avg(o.score) as decimal(18,2) avg_scorefrom Teacherm , Course n , SC o where m.T# = n.T# and n.C# = o.C# group by m.T# , m.Tname order by avg_score desc- -22 、查询所有课
34、程的成绩第2 名到第 3 名的学生信息及该课程成绩- -22.1 sql 2000 用子查询完成- -Score 重复时保留名次空缺select * from (select t.* , px = (select count(1) from SC where C# = t.C# and score t.score) + 1 from sc t) m where px between 2 and 3 order by m.c# , m.px-Score 重复时合并名次select * from (select t.* , px = (select count(distinct score) fr
35、om SC where C# =t.C# and score = t.score) from sc t) m where px between 2 and 3 order by m.c# , m.px- -22.2 sql 2005 用 rank,DENSE_RANK 完成- -Score 重复时保留名次空缺(rank 完成 )select * from (select t.* , px = rank() over(partition by c# order by score desc) from sc t) m where px between 2 and 3 order by m.C# ,
36、 m.px- -Score 重复时合并名次(DENSE_RANK 完成 )select * from (select t.* , px = DENSE_RANK() over(partition by c# order by score desc)from sc t) m where px between 2 and 3 order by m.C# , m.px- -23 、统计各科成绩各分数段人数：课程编号,课程名称,100-85,85-70,70-60,0-60 及所占百分比- -23.1 统计各科成绩各分数段人数：课程编号 ,课程名称 ,100-85,85-70,70-60,0-60-横
37、向显示select Course.C# 课程编号 , Cname as 课程名称 , sum(case when score = 85then 1 else 0 end) 85-100, sum(case when score = 70 and score = 60 and score 70 then 1 else 0 end) 60-70, sum(case when score = 85 then 85-100whenn.score = 70 and n.score = 60 and n.score = 85 then 85-100when n.score = 70and n.score= 60and n.score = 85then 85-100when n.score = 70 and n.score = 60 and n.score 70 then 60-70else 0-60 end)count(1) 数