实 验4
实验项目名称 连接、嵌套和集合查询(2课时，综合) 实验主要内容及方法 各种连接、嵌套和集合查询方法 实验要求：
（7） 掌握连接、嵌套和集合查询语句的一般格式。; （8） 掌握连接、嵌套和集合查询的各种使用方法。 实验目的：熟练掌握连接、嵌套和集合查询的使用。 实验设备：装有SQL SERVER 2005的电脑 实验步骤：
(1) 启动SSMS；
(2) 选择SQL SERVER后，按确认； (9) 选择数据库；
(10) 综合练习如下例题： 一、连接查询
1 不同表之间的连接查询
例3.37 查询每个学生及其选修课程的情况。
本查询实际上是涉及Students与Reports两个表的连接操作。这两个表之间的联系是通过公共属性Sno实现的，因此，
21
其操作命令为：
SELECT Students.*, Reports.* FROM Students, Reports
WHERE Students.Sno = Reports.Sno；
说明：若在以上等值连接中把目标列中重复的属性列去掉则为自然连接，其命令为
SELECT Students.Sno, Sname, Ssex, Sage, Sdept, Cno, Grade
FROM Students, Reports
WHERE Students.Sno= Reports.Sno;
例3.38 查询每个学生的学号(Sno)、姓名(Sname)、选修的课程名(Cname)及成绩(Grade)。
本查询涉及到三个表的连接操作，完成该查询的SQL语句如下：
SELECT Students.Sno, Sname, Cname, Grade FROM Students, Reports, Courses WHERE
Students.Sno=
Reports.Sno
AND
Reports.Cno=Courses.Cno; 2 自身连接
例3.39 查`询每一门课的间接先修课(即先修课的先修课)。
22
在Courses表关系中，只有每门课的直接先修课信息，而没有先修课的先修课。要得到这个信息，必须先对一门课找到其先修课，再按此先修课的课程号，查找它的先修课程。这就需要要将Courses表与其自身连接。为方便连接运算，这里为Courses表取两个别名分别为A，B。则完成该查询的SQL语句为：
SELECT A.Cno, A.Cname, B.Pre_Cno FROM Courses A, Courses B WHERE A.Pre_Cno =B.Cno; 3 外连接
例3.40把例3.37中的等值连接改为左连接。该左连接操作在SQL Server 2000中的命令格式为：
SELECT Students.Sno, Sname, Ssex, Sdept, Cno, Grade FROM Students LEFT JOIN Reports ON Students.Sno= Reports.Sno;
说明：以上左连接操作也可以用如下的右连接操作代替，其结果完全一样。
SELECT Students.Sno, Sname, Ssex, Sdept, Cno, Grade FROM Reports
RIGHT JOIN Students ON
23
Reports.Sno=Students.Sno;
二、嵌套查询
1 带谓词IN的嵌套查询
例3.41 查询选修了编号为“C02”的课程的学生姓名(Sname)和所在系(Sdept)。
SELECT Sname, Sdept FROM Students WHERE Sno IN (SELECT Sno FROM Reports WHERE Cno='C02');
例3.42 查询与“李伟”在同一个系学习的学生学号(Sno)、姓名(Sname)和系名(Sdept)。
该查询可构造嵌套查询实现，其SQL语句如下： SELECT Sno, Sname, Sdept FROM Students WHERE Sdept IN (SELECT Sdept FROM Students WHERE Sname=’李伟’);
24
说明：本例中的查询也可以用自身连接来完成，其SQL语句如下：
SELECT A.Sno , A.Sname , A.Sdept FROM Students A , Students B
WHERE A.Sdept=B.Sdept AND B.Sname=’李伟’; 例3.43 查询选修了课程名为“数据结构”的学生学号(Sno)和姓名(Sname)。
本查询涉及学号、姓名和课程名(Cname)三个属性。学号和姓名存放在Students表中，课程名的存放在Courses表中，但Students与Courses两个表之间没有公共属性，必须通过Reports表建立它们之间的联系。所以本查询实际上涉及三个关系的连接操作。
SELECT Sno, Sname /* ③最后在Studenst关系中 */
FROM Students /* 取出Sno和Sname */ WHERE Sno IN
(SELECT Sno /*② 然后在SC关系中找出 */
FROM Reports /*选修了3号课程的学生学号*/
WHERE Cno IN
25