实验05 交互式SQL-连接查询、嵌套查询和集合查询

实验5 交互式SQL：连接查询、嵌套查询和集合查询
一、实验目的
1、掌握自然连接查询的方法。

2、掌握自身连接查询的方法。

3、掌握常用嵌套查询的方法。

4、了解集合查询的一般方法。

二、实验环境
1、硬件
（1）PC机。

2、软件
（1）操作系统：Windows XP或Windows 7。

（2）DBMS：Microsoft SQL Server 2005标准版或Microsoft SQL Server 2008企业版。

三、实验内容及步骤
1、预备内容
（1）修改SQL Server服务器身份验证模式
打开Microsoft SQL Server Management Studio，以Windows身份验证连接到SQL Server 服务器，在“对象资源管理器”中，用鼠标右键单击SQL Server服务器连接，在弹出式菜单中选择“属性(R)”选项，出现“服务器属性”窗口。

在“服务器属性”窗口中，选择“安全性”页，然后选择“服务器身份验证”为“SQL Server和Windows身份验证模式”，最后点击“确定”按钮，弹出消息对话框（直到重新启动SQL Server后，您所做的某些配置更改才会生效），点击此消息对话框中的“确定”按钮，返回Microsoft SQL Server Management Studio。

在“对象资源管理器”中，用鼠标右键单击SQL Server服务器连接，在弹出式菜单中选择“重新启动(A)”，弹出消息对话框（是否需要重新启动…上的MSSQLSERVER服务），点击此消息对话框中的“是”按钮，重启SQL Server服务。

（2）创建数据库、登录名和数据库用户
在当前SQL Server连接上新建查询，输入（可复制）并执行以下SQL脚本，创建学生选课数据库xsxk，定义SQL Server登录名tom（也可自定登录名），在数据库xsxk中为登录名
（3）重新连接SQL Server服务器
断开当前连接，以登录名tom通过SQL Server身份验证连接到SQL Server服务器。

（4）定义架构
新建查询，输入并执行以下SQL脚本，在数据库xsxk中定义架构study，修改数据库用户tom_user的默认架构为study。

（5）定义基本表
根据教材第82页的图3.2，新建查询，输入以下SQL脚本，在架构study中定义基本表
2、自然连接查询
（1）参照教材第101页[例34]，设计SQL脚本，查询所有选修了课程的学生的姓名和所在系，验证成功后将SQL脚本写入以下方框中。

（2）参照教材第101页[例34]，设计SQL脚本，查询所有被选修的课程的课程名及其学分，验证成功后将SQL脚本写入以下方框中。

（3）参照教材第101页[例34]，设计SQL脚本，查询所有选修了课程的男学生的姓名、所选课程名及其成绩，验证成功后将SQL脚本写入以下方框中。

3、自身连接查询
（1）练习教材第102页[例35]，体验并掌握自身连接查询的方法。

（2）参照教材第102页[例35]，设计SQL脚本，查询所有与刘晨同龄的学生的姓名、性别和年龄，验证成功后将SQL脚本写入以下方框中。

（3）参照教材第102页[例35]，设计SQL脚本，查询所有与课程“数据库”学分相同的课程的课程号、课程名和学分，验证成功后将SQL脚本写入以下方框中。

4、嵌套查询
（1）参照教材第104页[例39]，设计SQL脚本，在学生表student（第82页图3.2a）中，查询所有与学生“张立”同龄的学生的姓名、性别和年龄，验证成功后将SQL脚本写入以下方框中。

（2）参照教材第104页[例39]，设计SQL脚本，在课程表course（第82页图3.2b）中，查询所有与课程“数据结构”学分相同的课程的课程名和学分，验证成功后将SQL脚本写入以下方框中。

（3）参照教材第105页[例40]，设计SQL脚本，查询学生“李勇”选修课程的课程号、课程名及其学分，验证成功后将SQL脚本写入以下方框中。

（4）在课程表course（第82页图3.2b）中，每门课程都有学分，使用聚集函数avg（参考第98页）可以计算出全部课程的平均学分，例如课程表course中7门课程的平均学分约为3.3。

现在想从course中找出学分高于平均学分3.3的所有课程，要求输出课程号、课程名及学分。

试根据教材第106页关于“带有比较运算符的子查询”的内容，设计此SQL脚本，验证成功后将SQL脚本写入以下方框中。

（5）在学生选课表sc（第82页图3.2c）中，一般每门课程有多个学生选修，可以使用聚集函数avg（参考第98页）计算出每门课程的平均成绩。

现在想从sc中找出每门课程中成绩大于该课程平均成绩的所有学生，要求输出学号、课程号和成绩字段。

例如，如果5个学生选修了同一门课，经计算，该课程平均成绩为80分，则需从这5个学生中，找出成绩大于80分的学生。

请根据教材第107页[例41]和关于“相关子查询”的内容，设计此SQL 脚本，验证成功后将SQL脚本写入以下方框中。

（6）练习教材第108页[例42]和[例43]，体验并掌握带有any或all谓词的子查询的使用方法。

（7）参照教材第109页[例44]，设计SQL脚本，查询学号为200215121的学生所选课程的课程号和课程名，验证成功后将SQL脚本写入以下方框中。

5、集合查询
（1）练习教材第112页[例48]至[例52]，体验并掌握集合查询的一般使用方法。

四、实验报告要求
根据以上实验过程，写出实验报告，重点写出此次实验的收获和心得体会。