SQL SERVER专题实行4 复杂查询
第1关：根本知识任务描述
本关任务：根据复杂查询相干知识介绍，完成相干选择题。
知识要求
为了完本钱关任务，你须要掌握：
[*]基于多个表或视图的SELECT语法结构；
[*]包括天然毗连、平凡毗连、自身毗连在内的各种毗连查询使用场景及语法要点；
[*]包括左外毗连、右外毗连和全外毗连的外毗连查询使用场景及语法要点；
[*]包括相干子查询和不相干子查询的嵌套查询使用场景及语法要点；
[*]包括并集、交集和差集的集合查询使用场景及语法要点。
[*]基于派生表的查询。
知识链接
关于SELECT语句在复杂查询中的使用场景、语法要点等相干内容和分类示例请参看：4-1 实行导引
测试阐明
本关重要介绍的是概念性知识，因此测试以选择题的情势对本关介绍的知识进行测试。
开始你的任务吧，祝你乐成！
1、关于自身毗连，下面说法精确的有：(ABC)
A、自身毗连的表必须取别名
B、自身毗连的目标列表达式必须带上表的别名
C、自身毗连通常用于查询同一个表中不同属性之间满足某种比较条件的效果
D、自身毗连也可以使用相同属性值的比较条件的作为毗连条件
2、关于天然毗连，下面说法精确的有：(ABC)
A、天然毗连可以理解为是主外码值相等且目标列表达式去掉外码生存主码的毗连
B、两个表即便没有参照完整性也可以有天然毗连
C、天然毗连的两个表中一定要有两组相同的属性名及其数据范例和宽度
D、天然毗连就是自身毗连
3、关于嵌套查询，下面说法精确的有：(ABCD)
A嵌套查询就是查询的条件表达式中含有查询块的查询
B、不相干子查询的执行顺序从层查询开始逐级向上一层查询推进，查询的效果放在上一层查询的条件表达式的常量部分
C、相干子查询一定有WHERE子句，而且都会带有父查询的某个属性作为条件表达式中常量部分的内容
D、某些嵌套子查询也可以用毗连查询来实现
4、关于集合查询，下面说法精确的有：(ABCD)
A、集合查询就是把表的查询操纵当集合操纵理解的查询
B、集合查询要求参与运算的子查询的目标列表达式必须个数相同且能按对应顺序进行比较
C、有些集合查询也可以用嵌套查询或毗连查询更换
D、集合查询的查询表目标列表达式为第一个子查询的目标列表达式
5、关于基于派生表的查询，下面说法精确的有：(ABD)
A、派生表就是子查询放到WHERE语句中作为查询对象的查询表
B、派生表在SELECT语句中必须使用别名
C、派生表的目标列表达式必须也使用别名
D、派生表的目标列表达式只有在含有聚集函数、计算表达式等环境下才必须使用别名
第2关：毗连查询（含平凡毗连、天然毗连和外毗连）
SET NOCOUNT ON
GO
USE JX
GO
----------------------BEGIN-------------------
--*****1、查询选修了‘3’号课程且成绩在70分以上的学生信息*****
selectStudent.*
fromStudent,SC
where Student.Sno=SC.Sno and Cno='3' and Grade>70
--*****2、查询选修了‘3’课程的学生姓名，成绩及任课教师姓名*****
select Sname,Grade,Tname
from Student,SC,Teacher
where Student.Sno=SC.Sno and Teacher.Tno=SC.Tno and Cno= '3'
--*****3、列出所有参加了"操作系统"课程考试的学生姓名和成绩*****
select Sname,Grade
from Student,SC,Course
where Student.Sno= SC.Sno AND SC.Cno=Course.Cno and Cname='操作系统'
--*****4、查询已开出课程中选课人数不足4人的课程号及人数信息*****
select Cno 课程号,count(*) 选课人数
from Student,SC
where Student.Sno=SC.Sno
GROUP BY SC.Cno HAVING count(*)<4
--*****5、查询有间接先修课的课程信息，以‘课程名’、‘间接先修课名’作列名*****
select a.Cname 课程名,c.Cname 间接先修课名
from Course a,Course b,Course c
where a.Cpno = b.Cno and b.Cpno= c.Cno
--*****6、查询所有同学的基本信息及选课情况（包含未选课同学的信息）*****
select Student.*,Cno,Tno,Grade
FROM Student LEFT JOIN SC ON Student.Sno=SC.Sno
----------------------END-----------------------
GO 第3关：嵌套查询（含相干子查询和不相干子查询）
任务描述
本关任务：
[*]用 SELECT 嵌套查询获取满足指定条件的效果；
[*]按要求返回查询表的属性列。
相干知识
为了完本钱关任务，你须要掌握：
[*]不相干子查询的执行过程及使用方法；
[*]相干子查询的执行过程及使用方法；
具体使用示例请参考：4-3 嵌套查询示例
编程要求
本次编程任务是：
[*]根据4-1SELECT语法规则，参照4-2关于嵌套查询的使用方法示例，尽量先在本地或平台上完成示例内容练习；
[*]“评测”通关。
平台下令行练习方法
[*]复制和自行录入如下内容，在下令窗口使用sqlcmd下令行工具毗连SQL SERVER服务器，登录后检查数据库JX是否创建乐成，数据表及数据是否正常：
sqlcmd -S localhost -U sa -P '<123123Aa!@>
[*]若不正常，则导入本专题实行用的数据库脚本文件ex3.sql后再练习，下令如下:
sqlcmd -S localhost -U sa -P '<123123Aa!@>' -i '/data/workspace/myshixun/ex4.sql'
评测通关方法
[*]将下令行中运行精确的语句复制到step4.sql中“-----------BEGIN-------”和“--------END------”地区，按序完成后进行“评测”。
[*]也可直接使用T-SQL下令完成如下要求并补全到step4.sql中对应地区，确认后进行“评测”。
[*]若“评测”不能通关，请检查补全内容的精确性。
具体任务
留意：请参考背面的精确运行效果，使用嵌套查询的方案写T-SQL语句
[*]查询没有选修课程的同学的根本信息
[*]查询选修课程数低于3门的学生信息（需含未选课的学生信息）
[*]查询所有不比“电子工程”系所有学生年龄大的其他系的学生信息
[*]查询选修‘3’号或‘4’号课程的同学的学号和姓名
[*]查询选修‘3’号和‘4’号课程的同学的学号和姓名
测试阐明
测试过程：
[*]本关将通过查询下令将执行效果与预期效果进行比较来判断下令是否精确
[*]请确保所有需补全部分内容的精确性
如果操纵精确，你将得到如下的效果：
https://img-blog.csdnimg.cn/img_convert/5d5a5ef6d22f5264458547fe898a992b.png
开始你的任务吧，祝你乐成！
SET NOCOUNT ON
GO
USE JX
GO
----------------------BEGIN-------------------
--*****1、查询没有选修课程的同学的基本信息*****
select Student.*
from Student left join SC on Student.Sno = SC.Sno
where SC.Sno is null
--*****2、查询选修课程数低于3门的学生信息（需含未选课的学生信息）*****
select *
from Student
where Sno NOTIN (select Sno from SC
group by Sno
HAVING COUNT(*)>=3
)
--*****3、查询所有不比“电子工程”系所有学生年龄大的其他系的学生信息*****
select *
from Student
where Sbirth>all(select Sbirth from Student where Sdept='电子工程') and Sdept<>'电子工程'
--*****4、查询选修‘3’号或‘4’号课程的同学的学号和姓名*****
select distinct Sno,Sname from Student where Sno in
(select Sno from SC where Cno in('3','4')
);
--*****5、查询选修‘3’号和‘4’号课程的同学的学号和姓名*****
selectStudent.Sno,Student.Sname
from SC,Student
WHERE SC.Sno=Student.Sno andCno='3' and SC.Sno IN (
select Sno from SC where SC.Cno='4'
)
----------------------END----------------------- 第4关：集合查询（并、交、差）
任务描述
本关任务：
[*]使用并、交、差的查询方式获取满足条件的查询效果；
[*]按要求返回查询表的属性列。
相干知识
为了完本钱关任务，你须要掌握：
[*]并集、差集、差集的集合查询语句对查询目标列表达式的要求；
[*]三种集合查询的SELECT语句使用方法。
具体使用示例请参考：4-4 集合查询示例
编程要求
本次编程任务是：
[*]根据4-1SELECT语句关于集合查询的语法规则，参照4-2关于并集、交集和差集的查询使用方法示例，尽量先在本地或平台上完成示例内容练习；
[*]“评测”通关。
平台下令行练习方法
[*]复制和自行录入如下内容，在下令窗口使用sqlcmd下令行工具毗连SQL SERVER服务器，登录后检查数据库JX是否创建乐成，数据表及数据是否正常：
sqlcmd -S localhost -U sa -P '<123123Aa!@>
[*]若不正常，则导入本专题实行用的数据库脚本文件ex3.sql后再练习，下令如下:
sqlcmd -S localhost -U sa -P '<123123Aa!@>' -i '/data/workspace/myshixun/ex4.sql'
评测通关方法
[*]将下令行中运行精确的语句复制到step4.sql中“-----------BEGIN-------”和“--------END------”地区，按序完成后进行“评测”。
[*]也可直接使用T-SQL下令完成如下要求并补全到step4.sql中对应地区，确认后进行“评测”。
[*]若“评测”不能通关，请检查补全内容的精确性。
具体任务
留意：请参考背面的精确运行效果，使用集合查询方案写T-SQL语句
[*]查询选课人数不足4人的课程号及人数信息（含无学生选修的课程）
[*]查询讲授‘3’号或 ‘4’号课程的老师姓名和课程名
[*]查询同时选修了‘信息体系’和‘数据结构’课程的学生名单
测试阐明
测试过程：
[*]本关将通过查询下令将执行效果与预期效果进行比较来判断下令是否精确
[*]请确保所有需补全部分内容的精确性
如果操纵精确，你将得到如下的效果：
https://img-blog.csdnimg.cn/img_convert/3051b91fa479da2b0982b347d4e17089.png
开始你的任务吧，祝你乐成！
SET NOCOUNT ON
GO
USE JX
GO
----------------------BEGIN-------------------
--*****1、查询选课人数不足4人的课程号及人数信息（含无学生选修的课程）****
select Cno '课程号',count(*) '选课人数'
from SC
group by Cno having count(*) < 4 union
select Cno,0
from Course where Cno not in(select Cno from SC);
--*****2、查询讲授‘3’号或 ‘4’号课程的老师姓名和课程名*****
select distinct Tname,Cname
from Teacher,SC,Course
where Teacher.Tno = SC.Tno and SC.Cno = Course.Cno and Sc.Tno in(select Tno from SC where Cno in('3','4'));
--*****3、查询同时选修了‘信息系统’和‘数据结构’课程的学生名单*****
select Sname
from student
where Sno in (select Sno from SC where Cno in (select Cno from Course where Cname = '信息系统'))
intersect select Sname
from Student
where Sno in (select Sno from SC where Cno in (select Cno from Course where Cname ='数据结构'))
----------------------END-----------------------
GO 第5关：基于派生表的查询
任务描述
本关任务：
[*]使用基于派生表的查询语句完成指定的数据查询；
[*]按要求返回效果。
相干知识
为了完本钱关任务，你须要掌握：
[*]基于派生表查询的使用场景；
[*]SQL Server中基于派生表查询语法并机动使用；
[*]派生表查询中，子查询天生派生表别名的使用以及目标列表达式是否须要使用别名的判断。
**具体使用示例请参考：**(https://data.educoder.net/api/attachments/2472063?disposition=inline)
编程要求
本次编程任务是：
[*]根据3-1SELECT语句关于ORDER BY子句的语法规则，参照3-2关于单表的排序使用方法示例，尽量先在本地或平台上完成示例内容练习；
[*]“评测”通关。
平台下令行练习方法
[*]复制和自行录入如下内容，在下令窗口使用sqlcmd下令行工具毗连SQL SERVER服务器，登录后检查数据库JX是否创建乐成，数据表及数据是否正常：
sqlcmd -S localhost -U sa -P '<123123Aa!@>
[*]若不正常，则导入本专题实行用的数据库脚本文件ex3.sql后再练习，下令如下:
sqlcmd -S localhost -U sa -P '<123123Aa!@>' -i '/data/workspace/myshixun/ex4.sql'
评测通关方法
[*]将下令行中运行精确的语句复制到step4.sql中“-----------BEGIN-------”和“--------END------”地区，按序完成后进行“评测”。
[*]也可直接使用T-SQL下令完成如下要求并补全到step4.sql中对应地区，确认后进行“评测”。
[*]若“评测”不能通关，请检查补全内容的精确性。
具体任务
留意：请参考背面的精确运行效果写T-SQL语句
[*]查询年龄小于所在系均匀年龄的学生信息及其所在系的均匀年龄
[*]查询所有讲授‘2’号课程的西席姓名
测试阐明
测试过程：
[*]本关将通过查询下令将执行效果与预期效果进行比较来判断下令是否精确
[*]请确保所有需补全部分内容的精确性
如果操纵精确，你将得到如下的效果：
https://img-blog.csdnimg.cn/img_convert/82613c41bb5a8d877b7afc62ffa8de36.png
开始你的任务吧，祝你乐成！
SET NOCOUNT ON
GO
USE JX
GO
----------------------BEGIN-------------------
--*****1、查询年龄小于所在系平均年龄的学生信息及所在系学生的平均年龄*****
select *,(
select avg(2022-year(sbirth)) from student a where a.sdept=b.sdept group by sdept)
'avg_year' from student b where 2022-year(sbirth) < (select avg(2022-year(sbirth))
from student c
where b.sdept = c.sdept
group by sdept
)
--*****2、查询所有讲授‘2’号课程的教师姓名*****
select *
from teacher ,Course
where Cno='2'
----------------------END-----------------------
GO select *
from teacher where Tno = '4' 第6关：复杂查询综合应用
任务描述
本关任务：
[*]在前几个实训的基础上，完成基于复杂查询的SELECT语句综合练习任务。
相干知识
为了完本钱关任务，你须要掌握：
[*]EXISTS的用法
[*]综合运用分析使用查询语句的能力。
编程要求
本次编程任务是：
[*]联合前面的内容，综合运用复杂查询语句进行查询；
[*]“评测”通关。
平台下令行练习方法
[*]复制和自行录入如下内容，在下令窗口使用sqlcmd下令行工具毗连SQL SERVER服务器，登录后检查数据库JX是否创建乐成，数据表及数据是否正常：
sqlcmd -S localhost -U sa -P '<123123Aa!@>
[*]若不正常，则导入本实行用的数据库脚本文件ex4.sql后再练习，下令如下:
sqlcmd -S localhost -U sa -P '<123123Aa!@>' -i '/data/workspace/myshixun/ex4.sql'
评测通关方法
[*]将下令行中运行精确的语句复制到step4.sql中“-----------BEGIN-------”和“--------END------”地区，按序完成后进行“评测”。
[*]也可直接使用T-SQL下令完成如下要求并补全到step6.sql中对应地区，确认后进行“评测”。
[*]若“评测”不能通关，请检查补全内容的精确性。
具体任务
留意：请参考背面的精确运行效果写T-SQL语句
[*]查询所有学生和西席的姓名（允许重名）并标识人员范例（学生/老师）。
[*]查询没有选修2号课程的同学的学号、姓名和系别
测试阐明
测试过程：
[*]本关将通过查询下令将执行效果与预期效果进行比较来判断下令是否精确
[*]请确保所有需补全部分内容的精确性
如果操纵精确，你将得到如下的效果：
https://img-blog.csdnimg.cn/img_convert/3ee40ac303429ac663976ae11c638ccd.png
开始你的任务吧，祝你乐成！
SET NOCOUNT ON
GO
USE JX
GO
----------------------BEGIN-------------------
--*****1、查询所有学生和教师的姓名（允许重名）并标识人员类型（学生/老师）*****
select sname '姓名','学生' '人员类型'
from Student union
select tname '姓名','教师' '人员类型'
from teacher;
--*****2、查询没有选修2号课程的同学的学号、姓名和系别*****
select Sno,Sname,Sdept
from Student
where not exists(select * from SC where SC.Sno=Student.Sno and SC.Cno='2')
----------------------END-----------------------
GO
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。
页:
[1]