SQL 语句(四)--------------数据查询(嵌套查询)

/**********************************嵌套查询******************************************/


--在SQL语言中，一个SELECT-FROM-WHERE语句称为一个“查询块”
--将一个查询块嵌套在另一个查询块的WHERE子句或HAVING短语的条件中的查询称为“嵌套查询”
--SQL语言允许多层嵌套，即一个子查询中还可以嵌套其他子查询
--子查询的SELECT语句中不能使用ORDER BY子句，ORDER BY子句只能对最终查询结果排序


/**********************************带有IN谓词的子查询******************************************/
--查询与“宋杰”在同一个系学习的学生
--第一步 查‘宋杰’所在的系名
SELECT 院系号
FROM 学生选课.学生
WHERE 学生.姓名='宋杰';
--结果为 06001


--第二步 查该院系的学生
SELECT 学生.学号,姓名,名称
FROM 学生选课.学生,学生选课.院系
WHERE 学生.院系号='06001' AND 院系.院系号=学生.院系号;


--第三步 嵌套：
SELECT 学生.学号,姓名,名称
FROM 学生选课.学生,学生选课.院系
WHERE 学生.院系号 IN
(SELECT 院系号
FROM 学生选课.学生
WHERE 学生.姓名='宋杰') 
AND 院系.院系号=学生.院系号;




--查询选修了课程名为'数据库系统' 的学生学号和姓名
SELECT 学号,姓名
FROM 学生选课.学生
WHERE 学号 IN
(SELECT 学号
FROM 学生选课.选课
WHERE 课程号 IN
(SELECT 课程号
FROM 学生选课.课程
WHERE 名称='数据库系统'
)
);


--查询涉及多个关系时,用嵌套查询逐步求解，层次清楚，易于构造，具有结构化程序设计的优点


--子查询的查询条件不依赖于父查询，这类子查询称为不相关子查询
--如果子查询的查询条件依赖于父查询，这类子查询称为相关子查询,整个查询语句称为相关嵌套查询




/**********************************带有比较运算符的子查询******************************************/
--找出每个学生超过他选修课程平均成绩的课程号
SELECT 学号,课程号
FROM 学生选课.选课 x
WHERE 成绩 >
(SELECT AVG(成绩)
FROM 学生选课.选课 y
WHERE x.学号=y.学号
);


/*******************************带有ANY(SOME)或ALL谓词的子查询***************************************/
--子查询返回单值时可以用比较运算符，但返回多值时要用ANY（有的系统用SOME）或ALL谓词修饰符
--使用ANY或ALL谓词时必须同时使用比较运算符


--查询其他学院中比经济学院某一学生年龄小的学生姓名和年龄
SELECT 姓名,年龄
FROM 学生选课.学生 x
WHERE x.年龄 < ANY
(SELECT 年龄
FROM 学生选课.学生 y,学生选课.院系 z
WHERE z.名称='经济学院' AND y.院系号=z.院系号
)
AND 院系号 != 
(SELECT 院系号
FROM 学生选课.院系
WHERE 院系.名称='经济学院'
);


--用聚集函数实现子查询通常比直接用ANY或ALL查询效率要高！


/*******************************带有EXISTS谓词的子查询***************************************/
--带有EXISTS谓词的子查询不返回任何数据,只产生逻辑真true假false


--查询所有选修了0001号课程的学生姓名
SELECT 姓名
FROM  学生选课.学生 x
WHERE EXISTS 
(SELECT *
FROM 学生选课.选课
WHERE 选课.学号=x.学号 AND 选课.课程号='0001'
);
--本题的思路是遍历学生表，取出每一个元组，如果子查询返回true，则为目标元组
--由EXISTS引出的子查询，其目标列表达式通常都用 * ,因为带EXISTS的子查询只返回真值或假值，给出列名无实际意义


--查询没有选修0003号课程的学生姓名
SELECT 姓名
FROM  学生选课.学生 x
WHERE NOT EXISTS 
(SELECT *
FROM 学生选课.选课
WHERE 选课.学号=x.学号 AND 选课.课程号='0003'
);


SELECT 姓名
FROM 学生选课.学生 x
WHERE x.学号 NOT IN
(SELECT 学号
FROM 学生选课.选课
WHERE 选课.课程号='0003'
);




--查询选修了全部课程的学生姓名
--注：sql中没有全程量词，需要把带有全称量词的谓词转换为等价的带有存在量词的谓词---没有很好的理解！！！！！！！！！
--没有一门课是他没有选修的！


SELECT 姓名,学号
FROM 学生选课.学生 s
WHERE NOT EXISTS
(SELECT *
FROM 学生选课.课程 x
WHERE NOT EXISTS
(SELECT *
FROM 学生选课.选课
WHERE x.课程号=选课.课程号 AND s.学号=选课.学号
)
);


--查询至少选修了学生020002选修的全部课程的学生学号
--不存在这样的课程y,学生020002选修了y，而学生x没有选 
SELECT 学号
FROM 学生选课.选课 x
WHERE NOT EXISTS
(SELECT *
FROM 学生选课.选课 y
WHERE y.学号='020002' AND NOT EXISTS
(SELECT *
FROM 学生选课.选课 z
WHERE z.学号=x.学号 AND z.课程号=y.课程号
)
);