实验四 连接、嵌套和集合查询
一．实验目的及要求
1．熟练连接、嵌套和集合查询。

二．实验任务
1．掌握连接查询的方法。
3．掌握嵌套查询的方法。
4．掌握集合查询的方法。

三、操作要点
1．注意外连接的语法。

四、注意事项
1．在SQL SERVER环境下，集合查询的限制。

五、实验学时：6学时

六、实验重点及难点
1．外连接的概念和语法。
2． EXIST的用法。

七、实验步骤
(1)　选择SQL　SERVER后，按确认；
(2) 选择数据库Univisity；
(3) 进行以下工作：

1．查询每个学生及其选修课程的情况。
先创建每门课的先休课视图:
Create view SU_Cxianxiuke as
SELECT Cno,Cname,(select Cname from Courses as Courses1 where Courses1.Cno=Courses.Pre_Cno)as 先修课 from Courses 

再进行四个表的连接查询
select s.Sno AS 学号,s.Sname as 姓名,先修课
from Students as s,Courses as c,Reports as r,SU_Cxianxiuke as x
where c.Cno=r.Cno and s.Sno=r.Sno and x.Cno=c.Cno and x.先修课 is not null

2．查询每一门课的间接先修课(即先修课的先修课)。
自连接
select c1.Cname,c2.Cname from Courses as c1,Courses c2
WHERE c1.Pre_Cno=c2.Cno

3．查询与“李伟”在同一个系学习的学生学号(Sno)、姓名(Sname)和系名(Sdept)。
select Sno,Sname,Sdept   from Students
where Sdept=(select Sdept from Students where Sname='李伟')

4．查询选修了课程名为“数据结构”的学生学号(Sno)和姓名(Sname)。
select S.Sno,Sname from Students as S,Courses AS C, Reports AS R
where S.Sno=R.Sno and R.Cno=C.Cno AND C.Cname='数据结构'

5．查询与“S04”号学生在同一个系学习的学生学号(Sno)、姓名(Sname)和系名(Sdept)。
select Sno,Sname,Sdept   from Students
where Sdept=(select Sdept from Students where Sno='S04')

6．查询非自动化系的不超过自动化系所有学生的年龄的学生姓名(Sname)和年龄(Sage)。
也就是查询其他系学生中所有年龄比自动化系学生中年龄最大的学生年龄小的姓名和年龄
select Sname,Sage from Students
WHERE Sdept <> '自动化' AND Sage<=(select Max(Sage) from Students WHERE Sdept='自动化')

7．查询所有选修了编号为“C01”课程的学生姓名(Sname)和所在系(Sdept)。
select Sname,Sdept from Students,Reports,Courses
where Students.Sno=Reports.Sno AND Reports.Cno=Courses.Cno AND Courses.Cno='C01'

8．查询选修了所有课程的学生姓名(Sname)和所在系。
没有一个学生，选修了课程表中所有课程
    select Sname,Sdept from Students
where not exists(select *from Courses where not exists  (select *from Reports where Sno =Students.Sno and Cno=Courses.Cno))

9．查询计算机科学系的学生或年龄不大于20岁的学生信息。
   select * from Students
where Sdept='计算机' or Sage<=20

拓展题：
校学生报考四六级情况如下表格，请以此为基础建立考试报名数据库（关系模式）。
学号 姓名 班级代码 班级名称 考试代码 考试名称
201440710127 王大阳 201440710 环境艺术设计2014(1)班 02 英语四级
201340920142 李强国 201340920 药学2013(1)班 03 英语六级
201240540211 方一民 201240540 财务管理2012(2)班
201340420121 张平风 201340420 网络工程2013班 02 英语四级
201340110320 何亚雄 201340110 机械设计制造及其自动化2013(3)班 02 英语四级
201240110109 王庭瑞 201240110 机械设计制造及其自动化2012(1)班
201440210241 宋立涛 201440210 电子信息工程2014(2)班 02 英语四级

一、创建数据库:
CREATE DATABASE KaoShi
ON
(NAME=University_Data,
FILENAME='D:\SQLSHUJU\Kaoshi.mdf',     --数据文件
SIZE=100MB,
MAXSIZE=200,
FILEGROWTH=20
)
LOG ON
(NAME=University_Log,
FILENAME='D:\SQLSHUJU\kaoshi.ldf',        --日志文件
size=100mb,
maxsize=200,
filegrowTh=20)
go

创建表:根据表图关系:共创建4张表，分别是Students(学生基本信息表)、Class(班级信息表)、ApplyTable(报名科目信息表)、baoKao(报名表)

Create table Class
(Cno char(9),
Cname varchar(25) NOT NULL,
PRIMARY KEY(Cno)
)
Create table Students
(Sno char(12),
Sname Varchar(10),
Scno char(9),
PRIMARY KEY(Sno),
FOREIGN KEY(Scno)REFERENCES Class(Cno)
)

CREATE TABLE ApplyTable
(Tno char(2),
Tname char(10),
PRIMARY KEY(Tno)
)

CREATE TABLE baoKao
(Sno char(12),
Tno CHAR(2),
PRIMARY KEY(Sno,Tno),
FOREIGN KEY(Sno)REFERENCES Students(Sno),
FOREIGN KEY(Tno)REFERENCES ApplyTable(Tno)
)


alter table Class
alter column Cname varchar(30)




请查询：
1、查询报考了四六级学生的学号、姓名、班级、报考等级

SELECT Students.Sname,Students.Sno,Class.Cno,ApplyTable.Tname FROM baoKao,Students,Class,ApplyTable
WHERE baoKao.Sno=Students.Sno AND Students.Scno=Class.Cno AND baoKao.Tno=ApplyTable.Tno

2、查询四六级报名情况，显示学号、姓名、班级、报考等级。包括未报考的学生
把第一小题的查询语句创建为视图
CREATE VIEW Subaokao as
SELECT Students.Sname,Students.Sno,Class.Cname,ApplyTable.Tname FROM baoKao,Students,Class,ApplyTable
WHERE baoKao.Sno=Students.Sno AND Students.Scno=Class.Cno AND baoKao.Tno=ApplyTable.Tno
在视图的基础上，通过左连接和Students，表中全体学生信息进行匹配，显示全体Students表中的记录:
select Students.Sno,Students.Sname,Cname ,Tname FROM Students  LEFT OUTER JOIN Subaokao ON(Students.Sno=Subaokao.Sno)

3、请按班级统计报考人数
在第题的基础上创建全体报学生报考情况视图学生报考情况
CREATE VIEW 学生报考情况 as
select Students.Sno,Students.Sname,Cname ,Tname FROM Students  LEFT OUTER JOIN Subaokao ON(Students.Sno=Subaokao.Sno)

select Cname, COUNT(Cname)as 班级报考人数  from 学生报考情况
where Cname is not null
group by Cname

4、请根据报考等级统计报考人数
同3题一样，也是在视图学生报考情况的基础上查询，
select Tname,  COUNT(Tname)as 该项报考人数 from 学生报考情况
where Tname is not null
group by Tname

注意：以上问题中，注意使用外连接

提示：使用范式理论分解