SQL练习5 - SELECT（嵌套查询EXISTS、集合查询、基于派生表的查询）

一、带有EXISTS谓词的子查询
EXISTS谓词：存在量词

带有EXISTS谓词的子查询不返回任何数据，只产生逻辑真值“true”或逻辑假值“false”。
①、若内层查询结果非空，则外层的WHERE子句返回真值
②、若内层查询结果为空，则外层的WHERE子句返回假值

由EXISTS引出的子查询，其目标列表达式通常都用 * ，因为带EXISTS的子查询只返回真值或假值，给出列名无实际意义。
NOT EXISTS谓词：
①、若内层查询结果非空，则外层的WHERE子句返回假值
②、若内层查询结果为空，则外层的WHERE子句返回真值
（同EXISTS谓词相反）

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

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

这段代码的意思是，在SC表中是否存在选修了课程号为1的学生学号，如果存在，则在Student表中找到与该学号对应的学生姓名。

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

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

如果存在选修了1 号课的学生学号，因为是NOT EXISTS，所以返回假值；如果没有选修1 号课，返回真值，同时显示出与没有选修1号课的学生学号对应的学生姓名。

一些带EXISTS或NOT EXISTS谓词的子查询不能被其他形式的子查询等价替换
所有带IN谓词、比较运算符、ANY和ALL谓词的子查询都能用带EXISTS谓词的子查询等价替换，就是说，EXSTS 更高级一些。
[例 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
                                      )
                       );

这段代码一层一层来分析，先看第一个学生是否选修了全部的课程，假设全部课程从1到5，如果选修了1 号课，最内层返回F，没有选修返回T，以此类推，如果这5 轮中都返回F，那么说明该学生选修了全部课程，然后是第二名学生…分析方法相同。

用EXISTS/NOT EXISTS实现逻辑蕴涵（难点）

[例 3.63]查询至少选修了学生201215122选修的全部课程的学生号码。
（该题目的意思是学生201215122选的课，某学生都选了，而且还可能选了别的课，也就是说，不存在这样的课程y，学生201215122选修了y，而学生x没有选。）
用NOT EXISTS谓词表示：

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));
                                 

在SCX表中选中第一个学号假定为201215011，在SCY中选择201215122选修的地门课的学号，最内层的意思是，如果201215122选的课程201215011也选了，那么返回F，反之返回T，以此类推，将所得结果取并集如果均为F，那么201215011符合条件，如果有一个T，则不符合条件。
二、集合查询
集合操作的种类
并-UNION
交-INTERSECT
差-EXCEPT
参加集合操作的各查询结果的列数必须相同
对应项的数据类型必须相同
[例 3.64] 查询计算机科学系的学生及年龄不大于19岁的学生。

SELECT *
    FROM Student
    WHERE Sdept= 'CS'
    UNION
    SELECT *
    FROM Student
    WHERE Sage<=19;

以上二者是或的关系，满足其中之一即可。

UNION：将多个查询结果合并起来时，系统自动去掉重复元组
UNION ALL：将多个查询结果合并起来时，保留重复元组
[例 3.65] 查询选修了课程1或者选修了课程2的学生。

 SELECT Sno
    FROM SC
    WHERE Cno=' 1 '
    UNION
    SELECT Sno
    FROM SC
    WHERE Cno= ' 2 ';

[例3.66] 查询计算机科学系的学生与年龄不大于19岁的学生 的交集。

SELECT *
FROM Student
WHERE Sdept='CS' 
INTERSECT
SELECT *
FROM Student
WHERE Sage<=19 

[例 3.67]查询既选修了课程1又选修了课程2的学生。

 SELECT Sno
    FROM SC
    WHERE Cno=' 1 ' 
    INTERSECT
    SELECT Sno
    FROM SC
    WHERE Cno='2 ';

[例 3.68] 查询计算机科学系的学生与年龄不大于19岁的学生的差集。

 SELECT *
    FROM Student
    WHERE Sdept='CS'
    EXCEPT
    SELECT  *
    FROM Student
    WHERE Sage <=19;

三、基于派生表的查询
子查询不仅可以出现在WHERE子句中，
还可以出现在FROM子句中，
这时子查询生成的临时派生表成为主查询的查询对象。
[例3.57]找出每个学生超过他自己选修课程平均成绩的课程号

  SELECT Sno, Cno
    FROM SC, (SELECT  Sno, Avg(Grade) 
                        FROM   SC 
    		           GROUP BY   Sno)
                        AS   Avg_sc(avg_sno,avg_grade)
    WHERE SC.Sno = Avg_sc.avg_sno
                        and SC.Grade >=Avg_sc.avg_grade

将每个学生的平均成绩放在一个新形成的临时表中，重新命名，然后再把SC表中的每个学生的成绩和平均成绩进行比较，如果比平均成绩大，符合条件显示出来该同学的学号以及该门课的课程号。

如果子查询中没有聚集函数，派生表可以不指定属性列，子查询SELECT子句后面的列名为其缺省属性。
[例]查询所有选修了1号课程的学生姓名，可以用如下查询完成：

 SELECT Sname
    FROM     Student,  
                   (SELECT Sno FROM SC WHERE Cno=' 1 ') AS SC1
    WHERE  Student.Sno=SC1.Sno;

通过对嵌套查询（带有EXISTS）、 集合查询、派生表的查询等操作，集合查询比较简单，带有EXISTS的查询比较难理解、可以先从整体理解，再从细节理解，派生表相当于一个中介，相对容易理解，总的来说，还需要多多练习。