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

实验三：数据库的嵌套查询实验

实验目的：

加深对嵌套查询语句的理解。

实验容：

使用IN、比较符、ANY或ALL和EXISTS操作符进行嵌套查询操作。

实验步骤：

一. 使用带IN谓词的子查询

1. 查询与’晨’在同一个系学习的学生的信息:

比较select * from student where sdept in

(select sdept from student where sname='晨')

与: select * from student where sdept =

(select sdept from student where sname='晨') 的异同

比较: select * from student where sdept =

(select sdept from student where sname='晨') and sname <> ‘晨’

与: select S1.* from student S1, student S2 where S1.sdept=S2.sdept and S2.sname='晨'的异同

2. 查询选修了课程名为’信息系统’ 的学生的学号和:

比较select sno, sname from student where sno in

(select sno from sc whereo in (selecto from course whereame='信息系统'))

与: select sno, sname from student where sno in

(select sno from sc, course where https://www.wendangku.net/doc/fb14762719.html,o=https://www.wendangku.net/doc/fb14762719.html,o andame='信息系统')

3. 查询选修了课程’1’和课程’2’的学生的学号:

select sno from student where sno in (select sno from sc whereo='1') and sno in (select sno from sc whereo='2')

比较: 查询选修了课程’1’或课程’2’的学生的sno:

select sno from sc whereo='1' oro='2'

比较连接查询:

select A.sno from sc A, sc B where A.sno=B.sno and https://www.wendangku.net/doc/fb14762719.html,o='1' and https://www.wendangku.net/doc/fb14762719.html,o='2'

二. 使用带比较运算的子查询

4. 查询比’晨’年龄小的所有学生的信息:

select * from student where sage<

(select sage from student where sname='晨')

三. 使用带Any, All谓词的子查询

5. 查询其他系中比信息系(IS)某一学生年龄小的学生和年龄; select sname, sage from student where sage

(select sage from student where sdept='IS') and sdept<>'IS'

6. 查询其他系中比信息系(IS)学生年龄都小的学生和年龄:

select sname, sage from student where sage

(select sage from student where sdept='IS') and sdept<>'IS'