基于SqlServer 连接、嵌套和集合查询

(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=‘数据结构’