SQL SERVER专题实验4 复杂查询

第1关：基本知识

任务描述

本关任务：根据复杂查询相关知识介绍，完成相关选择题。

知识要求

为了完成本关任务，你需要掌握：

1. 基于多个表或视图的SELECT语法结构；
2. 包括自然连接、普通连接、自身连接在内的各种连接查询使用场景及语法要点；
3. 包括左外连接、右外连接和全外连接的外连接查询使用场景及语法要点；
4. 包括相关子查询和不相关子查询的嵌套查询使用场景及语法要点；
5. 包括并集、交集和差集的集合查询使用场景及语法要点。
6. 基于派生表的查询。

知识链接

关于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分以上的学生信息*****
select  Student.*
from  Student,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关：嵌套查询（含相关子查询和不相关子查询）

任务描述

本关任务：

1. 用 SELECT 嵌套查询获取满足指定条件的结果；
2. 按要求返回查询表的属性列。

相关知识

为了完成本关任务，你需要掌握：

1. 不相关子查询的执行过程及使用方法；
2. 相关子查询的执行过程及使用方法；

具体使用示例请参考：4-3 嵌套查询示例

编程要求

本次编程任务是：

1. 根据4-1SELECT语法规则，参照4-2关于嵌套查询的使用方法示例，尽量先在本地或平台上完成示例内容练习；
2. “评测”通关。

平台命令行练习方法

1. 复制和自行录入如下内容，在命令窗口使用sqlcmd命令行工具连接SQL SERVER服务器，登录后检查数据库JX是否创建成功，数据表及数据是否正常：
   sqlcmd -S localhost -U sa -P '<123123Aa!@>
2. 若不正常，则导入本专题实验用的数据库脚本文件ex3.sql后再练习，命令如下:
   sqlcmd -S localhost -U sa -P '<123123Aa!@>' -i '/data/workspace/myshixun/ex4.sql'

评测通关方法

1. 将命令行中运行正确的语句复制到step4.sql中“-----------BEGIN-------”和“--------END------”区域，按序完成后进行“评测”。
2. 也可直接使用T-SQL命令完成如下要求并补全到step4.sql中对应区域，确认后进行“评测”。
3. 若“评测”不能通关，请检查补全内容的正确性。

具体任务

注意：请参考后面的正确运行结果，使用嵌套查询的方案写T-SQL语句

1. 查询没有选修课程的同学的基本信息
2. 查询选修课程数低于3门的学生信息（需含未选课的学生信息）
3. 查询所有不比“电子工程”系所有学生年龄大的其他系的学生信息
4. 查询选修‘3’号或‘4’号课程的同学的学号和姓名
5. 查询选修‘3’号和‘4’号课程的同学的学号和姓名

测试说明

测试过程：

• 本关将通过查询命令将执行结果与预期结果进行比较来判断命令是否正确
• 请确保所有需补全部分内容的正确性

如果操作正确，你将得到如下的结果：

---

开始你的任务吧，祝你成功！

 

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 NOT  IN (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’号课程的同学的学号和姓名*****
select  Student.Sno,Student.Sname
from SC,Student
WHERE SC.Sno=Student.Sno and  Cno='3' and SC.Sno IN (
    select Sno from SC where SC.Cno='4' 
)
 
 
 
----------------------END-----------------------

第4关：集合查询（并、交、差）

任务描述

本关任务：

1. 使用并、交、差的查询方式获取满足条件的查询结果；
2. 按要求返回查询表的属性列。

相关知识

为了完成本关任务，你需要掌握：

1. 并集、差集、差集的集合查询语句对查询目标列表达式的要求；
2. 三种集合查询的SELECT语句使用方法。

具体使用示例请参考：4-4 集合查询示例

编程要求

本次编程任务是：

1. 根据4-1SELECT语句关于集合查询的语法规则，参照4-2关于并集、交集和差集的查询使用方法示例，尽量先在本地或平台上完成示例内容练习；
2. “评测”通关。

平台命令行练习方法

1. 复制和自行录入如下内容，在命令窗口使用sqlcmd命令行工具连接SQL SERVER服务器，登录后检查数据库JX是否创建成功，数据表及数据是否正常：
   sqlcmd -S localhost -U sa -P '<123123Aa!@>
2. 若不正常，则导入本专题实验用的数据库脚本文件ex3.sql后再练习，命令如下:
   sqlcmd -S localhost -U sa -P '<123123Aa!@>' -i '/data/workspace/myshixun/ex4.sql'

评测通关方法

1. 将命令行中运行正确的语句复制到step4.sql中“-----------BEGIN-------”和“--------END------”区域，按序完成后进行“评测”。
2. 也可直接使用T-SQL命令完成如下要求并补全到step4.sql中对应区域，确认后进行“评测”。
3. 若“评测”不能通关，请检查补全内容的正确性。

具体任务

注意：请参考后面的正确运行结果，使用集合查询方案写T-SQL语句

1. 查询选课人数不足4人的课程号及人数信息（含无学生选修的课程）
2. 查询讲授‘3’号或 ‘4’号课程的老师姓名和课程名
3. 查询同时选修了‘信息系统’和‘数据结构’课程的学生名单

测试说明

测试过程：

• 本关将通过查询命令将执行结果与预期结果进行比较来判断命令是否正确
• 请确保所有需补全部分内容的正确性

如果操作正确，你将得到如下的结果：

---

开始你的任务吧，祝你成功！

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关：基于派生表的查询

任务描述

本关任务：

1. 使用基于派生表的查询语句完成指定的数据查询；
2. 按要求返回结果。

相关知识

为了完成本关任务，你需要掌握：

1. 基于派生表查询的使用场景；
2. SQL Server中基于派生表查询语法并灵活使用；
3. 派生表查询中，子查询生成派生表别名的使用以及目标列表达式是否需要使用别名的判断。

**具体使用示例请参考：**[4-5 基于派生表的查询示例](https://data.educoder.net/api/attachments/2472063?disposition=inline)

编程要求

本次编程任务是：

1. 根据3-1SELECT语句关于ORDER BY子句的语法规则，参照3-2关于单表的排序使用方法示例，尽量先在本地或平台上完成示例内容练习；
2. “评测”通关。

平台命令行练习方法

1. 复制和自行录入如下内容，在命令窗口使用sqlcmd命令行工具连接SQL SERVER服务器，登录后检查数据库JX是否创建成功，数据表及数据是否正常：
   sqlcmd -S localhost -U sa -P '<123123Aa!@>
2. 若不正常，则导入本专题实验用的数据库脚本文件ex3.sql后再练习，命令如下:
   sqlcmd -S localhost -U sa -P '<123123Aa!@>' -i '/data/workspace/myshixun/ex4.sql'

评测通关方法

1. 将命令行中运行正确的语句复制到step4.sql中“-----------BEGIN-------”和“--------END------”区域，按序完成后进行“评测”。
2. 也可直接使用T-SQL命令完成如下要求并补全到step4.sql中对应区域，确认后进行“评测”。
3. 若“评测”不能通关，请检查补全内容的正确性。

具体任务

注意：请参考后面的正确运行结果写T-SQL语句

1. 查询年龄小于所在系平均年龄的学生信息及其所在系的平均年龄
2. 查询所有讲授‘2’号课程的教师姓名

测试说明

测试过程：

• 本关将通过查询命令将执行结果与预期结果进行比较来判断命令是否正确
• 请确保所有需补全部分内容的正确性

如果操作正确，你将得到如下的结果：

---

开始你的任务吧，祝你成功！

 

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关：复杂查询综合应用

任务描述

本关任务：

1. 在前几个实训的基础上，完成基于复杂查询的SELECT语句综合练习任务。

相关知识

为了完成本关任务，你需要掌握：

1. EXISTS的用法
2. 综合运用分析使用查询语句的能力。

编程要求

本次编程任务是：

1. 结合前面的内容，综合运用复杂查询语句进行查询；
2. “评测”通关。

平台命令行练习方法

1. 复制和自行录入如下内容，在命令窗口使用sqlcmd命令行工具连接SQL SERVER服务器，登录后检查数据库JX是否创建成功，数据表及数据是否正常：
   sqlcmd -S localhost -U sa -P '<123123Aa!@>
2. 若不正常，则导入本实验用的数据库脚本文件ex4.sql后再练习，命令如下:
   sqlcmd -S localhost -U sa -P '<123123Aa!@>' -i '/data/workspace/myshixun/ex4.sql'

评测通关方法

1. 将命令行中运行正确的语句复制到step4.sql中“-----------BEGIN-------”和“--------END------”区域，按序完成后进行“评测”。
2. 也可直接使用T-SQL命令完成如下要求并补全到step6.sql中对应区域，确认后进行“评测”。
3. 若“评测”不能通关，请检查补全内容的正确性。

具体任务

注意：请参考后面的正确运行结果写T-SQL语句

1. 查询所有学生和教师的姓名（允许重名）并标识人员类型（学生/老师）。
2. 查询没有选修2号课程的同学的学号、姓名和系别

测试说明

测试过程：

• 本关将通过查询命令将执行结果与预期结果进行比较来判断命令是否正确
• 请确保所有需补全部分内容的正确性

如果操作正确，你将得到如下的结果：

---

开始你的任务吧，祝你成功！

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