[例3.55] 査询与“刘桑”在同一个系学习的学生。
在SQL语言中,一个select – from – where语句称为一个査询块。将一个査询块嵌套在另一个査询块的 where子句 或 having短语 的条件中的査询称为嵌套査询 ,此题目中,我们先分步来完成此査询,然后再构造嵌套査询:
1.先确定“刘晨”所在系名,代码如下:
select Sdept
from Student
where Sname = '刘桑';
2.查找所有在CS系学习的学生,代码如下:
select Sno , Sname , Sdept
from Student
where Sdept = 'CS';
将第一步查询嵌套进第二步查询中,代码如下:
select Sno , Sname , Sdept
from Student
where Sdept in
(
select Sdept
from Student
where Sname = '刘晨'
);
[例3.56] 查询选修了课程名为“信息系统”的学生学号和姓名
select Sno , Sname
from Student
where Sno in
(
select Sno
from SC
where Cno in
(
select Cno
from course
where Cname = '信息系统'
)
);
本题使用连接查询效果一样:
select Student.Sno , Sname
from Student , SC , Course
where Student.Sno = SC.Sno
and SC.Cno = Course.Cno
and Course.Cname = '信息系统';
事实证明有的时候连接查询更简便,不一定非用难的
[例3.57] 找出每个学生超过(或等于)他自己选修课程平均成绩的课程号
select Sno , Cno
from SC x
where Grade >=
(
select avg(Grade)
from SC y
where y.Sno = x.Sno
);
[例3.58] 査询非计算机科学系中比计算机科学系任意一个学生年龄小的学生姓名和年龄
select Sname , Sage
from Student
where Sage < any
(
select Sage
from Student
where Sdept = 'CS'
)
and Sdept <> 'CS';
[例3.59] 査询非计算机科学系中比计算机科学系所有学生年龄都小的学生姓名及年龄
select Sname , Sage
from Student
where Sage < all
(
select Sage
from Student
where Sdept = 'CS'
)
and Sdept<> 'CS';;
这题可以用聚集函数写:
select Sname , Sage
from Student where Sage <
(
select min(Sage)
from Student
where Sdept = 'CS'
)
and Sdept <> 'CS';
[例3.60] 査询所有选修了 1号课程的学生姓名
select Sname
from Student
where exists
(
select *
from SC
where Sno = Student.Sno
and Cno = '1'
);
[例3.61] 査询没有选修1号课程的学生姓名
select Sname
from Student
where not exists
(
select *
from SC
where Sno = Student.Sno
and Cno = '1'
);
[例3.62] 査询选修了全部课程的学生姓名
select Sname
from Student
where not exists
(
select *
from Course
where not exists
(
select *
from SC
where Sno = Student.Sno
and Cno = Course.Cno
)
);
[例3.63] 査询至少选修了学生201215122选修的全部课程的学生号码。
嵌套查询作为老师口中的山顶,恰巧赶上考试周,还没好好回味,只是过了一遍例题,感觉需要好好领悟嵌套的思想,做题就会简单很多