数据库系统原理实验作业七：嵌套查询

一，嵌套查询

1.带有IN谓词的子查询

[例 3.55] 查询与“刘晨”在同一个系学习的学生。

此查询为不相关子查询

SELECT Sno,Sname,Sdept
FROM Student
WHERE Sdept IN
(SELECT Sdept
FROM Student
WHERE Sname='刘晨')

[例 3.56]查询选修了课程名为“信息系统”的学生学号和姓名

① 首先在Course关系中找出信息系统”的课程号，为3号
② 然后在SC关系中找出选修了3号课程的学生学号
③ 最后在Student关系中取出Sno和Sname

此查询为不相关子查询

SELECT Sno,Sname
FROM Student
WHERE Sno IN
(
SELECT Sno
FROM SC
WHERE CNO IN
(
SELECT Cno
FROM Course
WHERE CNAME='信息系统'
)
)

2.带有比较运算符的子查询

❖ 当能确切知道内层查询返回单值时，可用比较运算符（>，<，=，>=，<=，!=或< >）。

[例 3.57 ]找出每个学生超过他选修课程平均成绩的课程号。

SELECT Sno,Cno
FROM SC x
WHERE Grade>=(SELECT AVG(Grade)
FROM SC y
WHERE y.Sno=x.Sno
)

3.带有ANY（SOME）或ALL谓词的子查询

使用ANY或ALL谓词时必须同时使用比较运算
语义为：
ANY 大于子查询结果中的某个值
ALL 大于子查询结果中的所有值
< ANY 小于子查询结果中的某个值
< ALL 小于子查询结果中的所有值
= ANY 大于等于子查询结果中的某个值
= ALL 大于等于子查询结果中的所有值

[例 3.58] 查询非计算机科学系中比计算机科学系任意一个学生年龄小的学生姓名和年龄

SELECT Sname,Sage
FROM Student
WHERE Sage<ANY(
SElECT Sage
FROM Student
WHERE Sdept='CS' 
)
AND Sdept <> 'CS'

执行过程：
（1）首先处理子查询，找出CS系中所有学生的年龄，构成一个集合（20,19）
（2）处理父查询，找所有不是CS系且年龄小于20 或 19的学生

[例 3.59] 查询非计算机科学系中比计算机科学系所有学生年龄都小的学生姓名及年龄

SELECT Sname,Sage
FROM Student
WHERE Sage<ALL(
SElECT Sage
FROM Student
WHERE Sdept='CS' 
)
AND Sdept <> 'CS'

4.带有EXISTS谓词的子查询

EXISTS谓词
1.存在量词 ∃
2.带有EXISTS谓词的子查询不返回任何数据，只产生逻辑真值“true”或逻辑假值“false”。
⚫ 若内层查询结果非空，则外层的WHERE子句返回真值
⚫ 若内层查询结果为空，则外层的WHERE子句返回假值
3.由EXISTS引出的子查询，其目标列表达式通常都用 *

[例 3.60]查询所有选修了1号课程的学生姓名。

SELECT Sname
FROM Student
WHERE EXISTS
(
SELECT *
FROM SC
WHERE Sno=Student.Sno AND Cno='1'
)

[例 3.61] 查询没有选修1号课程的学生姓名

SELECT Sname
FROM Student
WHERE NOT EXISTS
(
SELECT *
FROM SC
WHERE Sno=Student.Sno AND Cno='1'
)

※用EXISTS/NOT EXISTS实现全称量词

[例 3.62] 查询选修了全部课程的学生姓名。

没有一门课程是他不选修的

执行过程：

SELECT Sname
FROM Student
WHERE NOT EXISTS
	(
	SELECT *
	FROM Course
	WHERE NOT EXISTS(
					SELECT *
					FROM SC
					WHERE Sno=Student.Sno
					AND Cno=Course.Cno
					)
	)

※用EXISTS/NOT EXISTS实现逻辑蕴涵

[例 3.63]查询至少选修了学生201215122选修的全部课程的学生号码。

① 用P表示谓词 “学生201215122选修了课程y”
② 用q表示谓词 “学生x选修了课程y”
③ 则上述查询为: （y） p → q

语义转换：不存在这样的课程y，学生201215122选修了y，而学生x没有选。

执行过程：

SELECT DISTINCT Sno
FROM SC SCX
WHERE NOT EXISTS
	(SELECT *
	FROM SC SCY
	WHERE SCY.Sno = ' 201215122 ' AND
		NOT EXISTS
		(SELECT *
		FROM SC SCZ
		WHERE SCZ.Sno=SCX.Sno AND
			  SCZ.Cno=SCY.Cno))

总结

带EXIST的查询比较绕，ppt写的比较详细多看看能看懂。