一、嵌套查询
1 带有比较运算符的嵌套查询
【SY4-05】将SY4-01改为带有比较运算符的嵌套查询。
use fsyxscj
select Student.*
from Student
where Dno =
(select Department.Dno
from Student,Department
where Student.Dno=Department.Dno
and Student.Sname='李勇'
)
3 带谓词ANY或ALL的嵌套查询
【SY4-06】查询其它系中比计算机系(DX01)某一学生年龄小的学生。
use fsyxscj
select Sname
from Student a
where Sage <any
(select Sage
from Student b
where Dno='DX01'
)
【SY4-07】查询其他系中比计算机系(DX01)学生年龄都小的学生。
use fsyxscj
select Sname
from Student a
where Sage < all
(select Sage
from Student b
where Dno='DX01'
)
4 带谓词EXISTS的嵌套查询
【SY4-08查询所有选修了编号为“1”课程的学生姓名(Sname)。
use fsyxscj
select Sname
from Student
where exists
(select Sno
from SC
where Cno='1'
)
【SY4-09查询所有选修了编号为“2”课程的学生姓名(Sname)。
where后面的exists覆盖了自然连接
use fsyxscj
select Sname
from Student
where exists
(select Sno
from SC
where Sno =Student.Sno
and Cno='2'
)
【SY4-10询与“李勇”在同一个系学习的学生信息。将【SY4-01】改为带谓词EXISTS的查询,其SQL语句如下:
use fsyxscj
select Student.*
from Student
where exists
(select Department.Dno
from Student,Department
where Student.Dno=Department.Dno
and Student.Sname='李勇'
)
【SY4-11查询选修了所有课程的学生姓名。
use fsyxscj
select Student.SnO
from Student
where NOT exists
(select *
from Course
where NOT exists(
select *
from SC
where SC.Sno=Student.Sno
AND SC.Cno=Course.Cno
))