数据库作业8：SQL练习5

4.带有EXISTS谓词的子查询

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

NOT EXISTS谓词（与EXISTS谓词相反）
1）若内层查询结果非空，则外层的WHERE子句返回假值
2）若内层查询结果为空，则外层的WHERE子句返回真值

查询所有选修了1号课程的学生姓名。
思路分析：
1）本查询涉及Student和SC关系
2）在Student中依次取每个元组的Sno值，用此值去检查SC表
3）若SC中存在这样的元组，其Sno值等于此Student.Sno值，并且其Cno=‘1’，则取此Student.Sname送入结果表

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

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

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

不同形式的查询间的替换
（1）一些带EXISTS或NOT EXISTS谓词的子查询不能被其他形式的子查询等价替换
（2）所有带IN谓词、比较运算符、ANY和ALL谓词的子查询都能用带EXISTS谓词的子查询等价替换
查询与“刘晨”在同一个系学习的学生。

SELECT Sno,Sname,Sdept
     FROM Student S1
      WHERE EXISTS
             　   (SELECT *
                   FROM Student S2
                   WHERE S2.Sdept = S1.Sdept AND
                         S2.Sname = '刘晨');

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

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

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

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

EXISTS的使用总结：
1、谓词EXISTS只会返回逻辑真值true或者逻辑假值false。
2、EXISTS若内层查询结果为非空，则外层WHERE子句返回真值；若为空，则返回假值；NOT EXISTS谓词相反。
3、一些EXISTS或NOT EXISTS谓词的子查询不能被其他形式的子查询等价替换，但所有带有IN谓词、比较运算符、ANY和ALL谓词的子查询都能用带EXISTS谓词的子查询等价替换。
三、集合查询

集合操作的种类
1、并-UNION
2、交-INTERSECT
3、差-EXCEPT
（1）参加集合操作的各查询结果的列数必须相同
（2）对应项的数据类型必须相同
查询计算机科学系的学生及年龄不大于19岁的学生。

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

查询选修了课程1或者选修了课程2的学生。

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

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

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

四、基于派生表的查询
子查询不仅可以出现在WHERE子句中，
还可以出现在FROM子句中，
这时子查询生成的临时派生表成为主查询的查询对象。

找出每个学生超过他自己选修课程平均成绩的课程号

 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

查询所有选修了1号课程的学生姓名，可以用如下查询完成：

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