实验六 嵌套查询

一、实验名称：数据库嵌套查询

二、实验目的

1. 掌握嵌套查询的表示及使用；

2. 掌握使用IN、比较符；

3. 掌握ANY或ALL；

4. 掌握EXISTS操作符进行嵌套查询操作。

三、实验环境

已安装SQL Server 2005 企业版的计算机；

具有局域网环境,有固定IP;

四、实验学时

2学时

五、实验要求

1. 了解连接查询的表示及使用；

2. 完成实验报告。

实验内容及步骤：

在WHERE子句中包含一个形如SELECT-FROM-WHERE的查询块，此查询块称为子查询或嵌套查询，包含子查询的语句称为父查询或外部查询。

一、返回一个值的子查询

当子查询的返回值只有一个时，可以使用比较运算符（=, >, <, >=, <=, !=）将父查询和子查询连接起来。

【例1】 查询与李明教师职称相同的教师号、姓名。

SELECT TNO,TN

FROM T

WHERE PROF=(SELECT PROF

FROM T

WHERE TN='李明')

1

二、返回一组值的子查询

如果子查询的返回值不止一个，而是一个集合时，则不能直接使用比较运算符，可以在比较运算符和子查询之间插入ANY或ALL。

1. 使用ANY

【例2】查询讲授课程号为C5的教师姓名。

SELECT TN

FROM T

WHERE TNO=ANY(SELECT TNO

FROM TC

WHERE CNO='C5')

2. 使用IN

可以使用IN代替“=ANY”。

【例3】查询讲授课程号为C5的教师姓名

SELECT TN

2

FROM T

WHERE TNO IN(SELECT TNO

FROM TC

WHERE CNO='C5')

3. 使用ALL

ALL的含义为全部。

【例4】查询其他系中比电力系所有教师工资都高的教师的姓名和工资。

SELECT TN,SAL

FROM T

WHERE SAL>ALL(SELECT SAL

FROM T

WHERE DEPT='电力')

AND DEPT!= ‘电力’

3

4. 使用EXISTS

EXISTS表示存在量词，带有EXISTS的子查询不返回任何实际数据，它只得到逻辑值“真”或“假”。当子查询的的查询结果集合为非空时，外层的WHERE子句返回真值，否则返回假值。

【例5】查询讲授课程号为C5的教师姓名

SELECT TN

FROM T

WHERE EXISTS(SELECT *

FROM TC

WHERE TNO=T.TNO AND CNO='C5')

综合实例：

【实例1】在数据库Student_test中完成以下查询：

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

select *

4

from student

where sdept in (select sdept

from student

where sname='刘晨')

2. 查询选修了课程名为’信息系统’ select no, sname

from student

where sno in(select sno from sc

where cno in (select cno from course

where cname='信息系统'))

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

5

select sno

from student

where sno in (select sno

from sc

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

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

select *

from student

where sage< (select sage

from student

where sname='刘晨')

5. 查询其他系中比信息系(IS)某一学生年龄小的学生姓名和年龄

select sname, sage

6

from student

where sage from student

where sdept='IS')

and sdept<>'IS'

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

select sname, sage

from student

where sage from student

where sdept='IS')

and sdept<>'IS'

7. 查询与计算机系(CS)系所有学生的年龄均不同的学生学号, 姓名和年龄

7

select sno,sname,sage

from student

where sage<>all(select sage

from student

where sdept='CS')

8. 查询与其他所有学生年龄均不同的学生学号, 姓名和年龄

select sno,sname,sage

from student A

where not exists(select *

from student B

where A.sage=B.sage and A.sno<>B.sno)

9. 查询所有选修了1号课程的学生姓名

select sname

8

from student

where exists(select *

from sc

where sno=student.sno and cno='1')

10. 查询没有选修了1号课程的学生姓名

select sname

from student

where not exists(select *

from sc

where sno=student.sno and cno='1')

11. 查询选修了全部课程的学生姓名

select sname

from student

9

where not exists(select *

from course

where not exists( select *

from sc

where sno=student.sno and cno=course.cno))

11. 查询至少选修了学生95002选修的全部课程的学生的学号

select distinct sno

from sc A

where not exists (select *

from sc B

where sno='95002' and not exists(select *

from sc C

where sno=A.sno and cno=B.cno))

10

12. 求没有人选修的课程号cno和cnamecname

select cno,cname

from course C

where not exists(select *

from sc

where sc.cno=C.cno )

【实例2】在数据库School_will中完成以下查询：

1. 查询考分在600分及以上的考生志愿

Select *

From Ewill

Where Exno in（select Exno

From Examine

Where Exgrade>=600）

11

2. 查询考分大于考号为05140200271233的考生的考生信息

Select *

From Examine

Where Exgrade>（select Exgrade

From Examine

Where Exno=’ 05140200271233’）

3. 查询平均分大于校代码为10459的学校信息

Select *

From School

Where average>all（select average

From School

Where Sccode=’ 10459’）

4. 查询报考志愿为10730的考生信息

12

Select *

From Examine

Where exists（select *

From Ewill

Where Exno=Examine.Exno and Sccode=’ 10730’）

实验习题：

【习题1】在School_Will中，用Transact-SQL语句实现下列查询操作。

1) 查看所有考生的信息；

2) 查看每位考生的考分；

3) 查看每位考生的所在地；

4) 查看每位考生的民族；

5) 查看招生学校的基本信息；

6) 查看招生学校的实录人数；

13

7) 查看考分超过500分的考生；

8) 查看所有男考生的考分及志愿学校；

9) 查看所有超过500分女考生的志愿学校，并按考号排序；

10) 查看考生志愿不是10730的考生信息。

14