数据库的嵌套查询实验报告.doc

实验三：数据库的嵌套查询实验
实验目的：
加深对嵌套查询语句的理解。
实验内容：
使用IN、比较符、ANY或ALL和EXISTS操作符进行嵌套查询操作。
实验步骤：
一. 使用带IN谓词的子查询
1. 查询与’X晨’在同一个系学习的学生的信息:
比较select * from student where sdept in
(select sdept from student where sname='X晨')
与: select * from student where sdept =
(select sdept from student where sname='X晨') 的异同
比较: select * from student where sdept =
(select sdept from student where sname='X晨') andsname<>‘X晨’
与: select S1.* from student S1, student S2 where = and ='X晨'的异同
2. 查询选修了课程名为’信息系统’ 的学生的学号和某:
比较select sno, sname from student where sno in
(select sno from sc where o in (select o from course where ame='信息系统'))
与: select sno, sname from student where sno in
(select sno from sc, course where = ame='信息系统')
3. 查询选修了课程’1’和课程’2’的学生的学号:
select sno from student where sno in (selectsnofrom sc where o='1')
and sno in (select sno from sc where o='2')
比较: 查询选修了课程’1’或课程’2’的学生的sno:
select sno from sc where o='1' or o='2'
比较连接查询:
select from sc A, sc B where = and ='1' and ='2'
二. 使用带比较运算的子查询
4. 查询比’X晨’年龄小的所有学生的信息:
select * from student where sage<
(select sage from student where sname='X晨')
三. 使用带Any, All谓词的子查询
5. 查询其他系中比信息系(IS)某一学生年龄小的学生某和年龄;
select sname, sage from student where sage <Any
(select sage from student where sdept='IS') and sdept<>'IS'
6. 查询其他系中比信息系(IS)学生年龄都小的学生某和年龄:
select sname, sage from student where sage <ALL
(select sage from student where sdept='IS') and sdept<>'IS'
7. 查询与计算机系(CS)系所有学生的年龄均不同的学生学号, 某和年龄:
select sno,sname,sage from student where sage<>all
(select sage from student where sdept='CS')
四. 使用带Exists谓词的子查询和相关子查询
8. 查询与其他所有学生年龄均不同的学生学号, 某和年龄:
select sno,sname,sage from student A where not exists
(select * from student B where = and <>)
9. 查询所有选修了1号课程的学生某:
select sname from student where exists
(select * from sc where sno= and o='1')
10. 查询没有选修了1号课程的学生某:
select sname from