《2022年数据库sql练习题收集 .pdf》由会员分享，可在线阅读，更多相关《2022年数据库sql练习题收集 .pdf（17页珍藏版）》请在taowenge.com淘文阁网|工程机械CAD图纸|机械工程制图|CAD装配图下载|SolidWorks_CaTia_CAD_UG_PROE_设计图分享下载上搜索。
1、use test /* 问题及描述：-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 分数select * from Student select * from Course select * from
2、 Teacher select * from SC */ -创建测试数据createtable 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 int
3、o Student values(04 , N 李云 , 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女) createtable Course(C# varchar(
4、10),Cname nvarchar(10),T# varchar(10) insert into Course values(01 , N语文 , 02) insert into Course values(02 , N数学 , 01) insert into Course values(03 , N英语 , 03) createtable Teacher (T# varchar(10),Tname nvarchar(10) insert into Teacher values(01 , N张三 ) insert into Teacher values(02 , N李四 ) insert i
5、nto Teacher values(03 , N王五 ) createtable SC(S# 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
6、, 03 , 80) 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 1 页，共 17 页 - - - - - - - - - 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(04 , 01 , 50) insert into SC values(04 , 02 , 30)
7、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、查询 01 课程比 02 课程成绩高的学生的信息及课程分数-1.1、查询同时存在01
8、 课程和 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课程和 02 课程的情况和存在01 课程但可能不存在02 课程的情况(不存在时显示为null)( 以下存在相同内容时不再解释) select a.* , b.score 课程 01的分数 ,c.score 课程 02的分数 fro
9、m Student a left join SC 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 , SC b , SC c where a.S# = b.S# and a.S# = c.S#
10、 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# = 01 left join SC c on a.S# = c.S# and c.C# = 02 where isnull (b.score,0) = 60 order by a.S# -4、查询平均成绩小于
11、分的同学的学生编号和学生姓名和平均成绩名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 2 页，共 17 页 - - - - - - - - - -4.1、查询在 sc表存在成绩的学生信息的SQL语句。select a.S# , a.Sname , cast(avg(b.score) asdecimal(18,2) avg_score from Student a , sc b where a.S# = b.S# group by a.S# , a.Sname having cast(
12、avg(b.score) asdecimal(18,2) 60 order by a.S# -4.2、查询在 sc表中不存在成绩的学生信息的SQL语句。select a.S# , a.Sname , isnull(cast(avg(b.score) asdecimal(18,2),0) avg_score from Student a left join sc b on a.S# = b.S# group by a.S# , a.Sname having isnull(cast(avg(b.score) asdecimal(18,2),0) 60 order by a.S# -5、查询所有同学
13、的学生编号、学生姓名、选课总数、所有课程的总成绩-5.1、查询所有有成绩的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 Studen
14、t a left join SC b on a.S# = b.S# group by a.S#,a.Sname order by a.S# -6、查询 李姓老师的数量-方法selectcount(Tname) 李姓老师的数量 from Teacher where Tname like N李% -方法selectcount(Tname) 李姓老师的数量 from Teacher where left(Tname,1) = N李 /* 李姓老师的数量- 1 */ -7、查询学过 张三 老师授课的同学的信息selectdistinct Student.* from Student , SC , Co
15、urse , Teacher 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 3 页，共 17 页 - - - - - - - - - whereStudent.S# =SC.S# and SC.C# =Course.C# and Course.T# =Teacher.T# andTeacher .Tname = N张三 order by Student.S# -8、查询没学过 张三 老师授课的同学的信息select m.* from Student m where S# not i
16、n (select distinct SC.S# from SC , Course , Teacher where SC.C# = Course.C# and Course.T# = Teacher.T# and Teacher.Tname = N张三 ) order bym.S# -9、查询学过编号为01 并且也学过编号为02的课程的同学的信息-方法select Student.* from Student , SC where Student.S# = SC.S# and SC.C# = 01 and exists (Select1 from SC SC_2 where SC_2.S# =
17、 SC.S# and SC_2.C# = 02) order by Student.S# -方法select Student.* from Student , SC where Student.S# = SC.S# and SC.C# = 02 and exists (Select1 from SC SC_2 where SC_2.S# = SC.S# and SC_2.C# = 01) order by Student.S# -方法select m.* from Student m where S# in ( select S# from ( selectdistinct S# from S
18、C where C# = 01 union all selectdistinct S# from SC where C# = 02 ) t group by S# having count(1) = 2 ) order by m.S# -10、查询学过编号为01但是没有学过编号为02 的课程的同学的信息-方法select Student.* from Student , SC where Student.S# = SC.S# and SC.C# = 01 and not exists(Select 1 from SC SC_2 where SC_2.S# = SC.S# and SC_2.C#
19、 = 02) order by Student.S# -方法select Student.* from Student , SC where Student.S# = SC.S# and SC.C# = 01 and Student.S# notin (Select SC_2.S# from SC SC_2 where SC_2.S# = SC.S# and SC_2.C# = 02) order byStudent.S# -11、查询没有学全所有课程的同学的信息-11.1、select Student.* from Student , SC where Student.S# = SC.S#
20、group by Student.S# , Student.Sname , Student.Sage , Student.Ssex having count(C#) (selectcount(C#) from Course)名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 4 页，共 17 页 - - - - - - - - - -11.2 select Student.* from Student left join SC on Student.S# = SC.S# group by
21、 Student.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# from SC where S# = 01) and Student.S# 01 -13、查询和 01号的同学学习的课程完全相同的其他同学的信息
22、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 whereS# = 01)group by SC.S# having count(1) = (select count(1) from SC where S#=01)-14、查询没学过 张三 老师讲授的任一门课程的学生姓名select student.* from student where student.S# not in(select di
23、stinct 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) asdecimal(18,2) avg_score from student , sc where student.S# = SC.S# and student
24、.S# in (select S# from SC where score = 2) group by student.S# , student.sname -16、检索 01 课程分数小于，按分数降序排列的学生信息select student.* , sc.C# , sc.score from student , sc where student.S# = SC.S# and sc.score =60，中等为： -80，优良为： -90，优秀为： =90 -方法select m.C# 课程编号 , m.Cname 课程名称 ,max(n.score) 最高分 , min(n.score) 最
25、低分 , cast(avg(n.score) asdecimal(18,2) 平均分 , cast(select count(1) from SC where C# = m.C# and score = 60)* 100.0 / (select count(1) fromSC where C# = m.C#) asdecimal(18,2) 及格率 (%) , cast(select count(1) from SC where C# = m.C# and score = 70 and score = 80 and score = 90)* 100.0 / (select count(1) f
26、romSC where C# = m.C#) asdecimal(18,2) 优秀率 (%) from Course m , SC n where m.C# = n.C# group by m.C# , m.Cname order by m.C# -方法select m.C# 课程编号 , m.Cname 课程名称 ,(selectmax(score) from SC where C# = m.C#) 最高分 , (selectmin(score) from SC where C# = m.C#) 最低分 , (selectcast(avg(score) as decimal(18,2) fr
27、om SC where C# = m.C#) 平均分 , cast(select count(1) from SC where C# = m.C# and score = 60)* 100.0 / (select count(1) fromSC where C# = m.C#) asdecimal(18,2) 及格率 (%) , cast(select count(1) from SC where C# = m.C# and score = 70 and score = 80 and score = 90)* 100.0 / (select count(1) fromSC where C# =
28、 m.C#) asdecimal(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) + 1 from sc t orderby t.c# , px -Score重复时合并名次select t.* , px = (select count(distinct score) from SC where C#
29、 = t.C# and score = t.score) fromsc 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) from sc t order by t.C# , px -Score重复时合并名次(DENSE_RANK 完成 ) select t.* , px = DENSE_RANK () over(partition by c# order
30、by score desc) from sc t order byt.C# , px -20、查询学生的总成绩并进行排名-20.1 查询学生的总成绩select m.S# 学生编号 ,m.Sname 学生姓名 , isnull (sum(score),0) 总成绩 from Student m left join SC n on m.S# = n.S# group by m.S# , m.Sname order by 总成绩 desc -20.2 查询学生的总成绩并进行排名，sql 2000用子查询完成，分总分重复时保留名次空缺和不保留名次空缺两种。select t1.* , px = (se
31、lectcount(1) from( select m.S# 学生编号 ,m.Sname 学生姓名 , isnull (sum(score),0) 总成绩 from Student m left join SC n on m.S# = n.S# group by m.S# , m.Sname ) t2 where 总成绩 t1.总成绩 ) + 1 from( select m.S# 学生编号 ,m.Sname 学生姓名 , 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 7 页，共
32、17 页 - - - - - - - - - isnull (sum(score),0) 总成绩 from Student m left join SC n on m.S# = n.S# group by m.S# , m.Sname ) t1 order by px select t1.* , px = (selectcount(distinct 总成绩 ) from( select m.S# 学生编号 ,m.Sname 学生姓名 , isnull (sum(score),0) 总成绩 from Student m left join SC n on m.S# = n.S# group by
33、 m.S# , m.Sname ) t2 where 总成绩 = t1.总成绩 ) from( select m.S# 学生编号 ,m.Sname 学生姓名 , isnull (sum(score),0) 总成绩 from Student m left join SC n on m.S# = n.S# group by m.S# , m.Sname ) t1 order by px -20.3 查询学生的总成绩并进行排名，sql 2005用 rank,DENSE_RANK 完成，分总分重复时保留名次空缺和不保留名次空缺两种。select t.* , px = rank() over(order
34、 by 总成绩 desc) from ( select m.S# 学生编号 ,m.Sname 学生姓名 , isnull (sum(score),0) 总成绩 from Student m left join 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 Student
35、m left join SC n on m.S# = n.S# group by m.S# , m.Sname ) t order by px 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 8 页，共 17 页 - - - - - - - - - -21、查询不同老师所教不同课程平均分从高到低显示select m.T# , m.Tname , cast(avg(o.score) as decimal(18,2) avg_score from Teacher m , Course n
36、 , SC o where m.T# = n.T# and n.C# = o.C# group by m.T# , m.Tname order by avg_score desc -22、查询所有课程的成绩第名到第名的学生信息及该课程成绩-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.
37、c# , m.px -Score重复时合并名次select * from (select t.* , px = (select count(distinct score) from 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(partitio
38、n by c# order by score desc) from sc t) m wherepx between 2 and 3 order by m.C# , 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
39、,85-70,70-60,0-60及所占百分比-23.1 统计各科成绩各分数段人数：课程编号,课程名称 ,100-85,85-70,70-60,0-60 -横向显示select Course.C# 课程编号 , Cname as 课程名称 , sum(casewhen score = 85 then 1 else 0 end) 85-100 , sum(casewhen score = 70 and score = 60 and score 70 then 1 else 0 end) 60-70 , sum(casewhen score = 85 then 85-100 when n.scor
40、e = 70 and n.score = 60 and n.score = 85 then 85-100 when n.score = 70 and n.score = 60 and n.score = 85 then 85-100 when n.score = 70 and n.score = 60 and n.score = 85 then 85-100 when n.score = 70 and n.score = 60 and n.score 70 then 60-70 else0-60 end) order by m.C# , m.Cname , 分数段-23.2 统计各科成绩各分数
41、段人数：课程编号 ,课程名称 ,100-85,85-70,70-60,60及所占百分比-横向显示select m.C# 课程编号 , m.Cname 课程名称 , (selectcount(1) from SC where C# = m.C# and score 60) 0-60 , cast(select count(1) from SC where C# = m.C# and score = 60 and score = 60 and score = 70 and score = 70 and score = 85) 85-100 , cast(select count(1) from S
42、C where C# = m.C# and score = 85)* 100.0 / (select count(1) fromSC where C# = m.C#) asdecimal(18,2) 百分比 (%) from Course m 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 10 页，共 17 页 - - - - - - - - - order by m.C# -纵向显示 (显示存在的分数段) select m.C# 课程编号 , m.Cname 课程名称 , 分数段
43、 = ( case when n.score = 85 then 85-100 when n.score = 70 and n.score = 60 and n.score = 85 then 85-100 when n.score = 70 and n.score = 60 and n.score = 85 then 85-100 when n.score = 70 and n.score = 60 and n.score = 85 then 85-100 when n.score = 70 and n.score = 60 and n.score t1.平均成绩 ) + 1 from( s
44、elect m.S# 学生编号 ,m.Sname 学生姓名 , isnull (cast(avg(score) asdecimal(18,2),0) 平均成绩 from Student m left join SC n on m.S# = n.S# group by m.S# , m.Sname ) t1 order by px select t1.* , px = (selectcount(distinct 平均成绩 ) from( select m.S# 学生编号 ,m.Sname 学生姓名 , isnull (cast(avg(score) asdecimal(18,2),0) 平均成绩
45、 from Student m left join SC n on m.S# = n.S# group by m.S# , m.Sname ) t2 where 平均成绩 = t1.平均成绩 ) from( select m.S# 学生编号 ,m.Sname 学生姓名 , isnull (cast(avg(score) asdecimal(18,2),0) 平均成绩 from Student m left join SC n on m.S# = n.S# group by m.S# , m.Sname ) t1 order by px -24.2 查询学生的平均成绩并进行排名，sql 2005
46、用rank,DENSE_RANK 完成，分平均成绩重复时保留名次空缺和不保留名次空缺两种。select t.* , px = rank() over(order by 平均成绩 desc) from ( select m.S# 学生编号 ,m.Sname 学生姓名 , isnull (cast(avg(score) asdecimal(18,2),0) 平均成绩 from Student m left join SC n on m.S# = n.S# group by m.S# , m.Sname ) t order by px 名师资料总结 - - -精品资料欢迎下载 - - - - - -
47、 - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 12 页，共 17 页 - - - - - - - - - select t.* , px = DENSE_RANK () over(order by 平均成绩 desc) from ( select m.S# 学生编号 ,m.Sname 学生姓名 , isnull (cast(avg(score) asdecimal(18,2),0) 平均成绩 from Student m left join SC n on m.S# = n.S# group by m.S# , m.Sname ) t orde
48、r by px -25、查询各科成绩前三名的记录-25.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
49、(distinct score) from SC where C# = t.C# andscore = t.score) from sc 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 px between 1 and 3 order by m.C# , m.px -26、查询每门课
50、程被选修的学生数select c# , count(S#)学生数 from sc group by C# -27、查询出只有两门课程的全部学生的学号和姓名select Student.S# , Student.Sname from Student , SC where Student.S# = SC.S# group by Student.S# , Student.Sname having count(SC.C#) = 2 order by Student.S# -28、查询男生、女生人数selectcount(Ssex) as 男生人数 from Student where Ssex = N