数据库数据查询——嵌套查询(续)
增加了数据库中对于带有ANY或ALL谓词的子查询的使用例题和代码
例题:
1.检索所学课程包含学生‘刘晨’所学课程的学生学号、姓名;
select *from student
where sname != ’刘晨’and sno in(select sno
from sc, course
where sc.cno=course.cno and sc.cno in(select cno
from sc, student
where sname='刘晨'and student.sno=sc. sno)group by sc.sno
having count(*)>=(select count(cno)
from sc,student where sname=’刘晨’and student. sno=sc.sno))
2.使用嵌套查询查询其它系中年龄小于CS系的某个学生的学生姓名、年龄和院系
select sname, sage, sdeptfrom student
where sage<anyOP-NR4786DB\TAN_PC)(select sage
from student
where sdept='CS’)and sdept!='cs'
3.使用嵌套查询查询其它系中年龄小于CS系所有学生年龄的学生;
select *from studentwhere sage<all
(select sagefrom student
where sdept='CS')and sdept!cS’ ;