设教学数据库Education有三个关系

CT *Р FROM SCР WHERE SC.Sno=S.Sno o=‘C2’); Р例33:查询所有未选修C2课程的学生姓名。Р SELECT SnameР FROM SР WHERE NOT EXISTS Р ( SELECT *Р FROM SCР WHERE SC.Sno=S.Sno o=‘C2’); Р [NOT]EXISTS 实际上是一种内、外层互相关的嵌套查询,只有当内层引用了外层的值,这种查询才有意义。Р例34:查询与“张三”在同一个系学习的学生学号、Р姓名和系别。Р SELECT Sno,Sname,Sdept Р FROM S AS S1Р WHERE EXISTS Р (SELECT *Р FROM S AS S2РWHERE S2.Sdept=S1. Sdept AND S2.Sname=‘张三’);Р相关子查询Р例35:查询选修了全部课程的学生姓名。Р在表S中找学生,要求这个学生学了全部课程。换言之,在S表中找学生,在C中不存在一门课程,这个学生没有学。Р SELECT Sname Р FROM S Р WHERE NOT EXISTSР (SELECT *Р FROM CР WHERE NOT EXISTSР (SELECT *Р FROM SCР WHERE SC.Sno=S.Sno AND o=o));Р例36:查询所学课程包含学生S3所学课程的学生学号Р分析:不存在这样的课程Y,学生S3选了Y,而其他学生没有选。РSELECT DISTINCT Sno Р FROM SC AS XР WHERE NOT EXISTSР (SELECT *Р FROM SC AS YР WHERE Y.Sno=‘S3’ AND NOT EXISTSР (SELECT *Р FROM SC AS ZР WHERE Z.Sno=X.Sno AND o=o));