>带ANY和ALL的子香询（子查询返回多值时用检索其他系中比IS系任一学生年龄小的学生名单SELECT S#,SN FROM S WHERE SA < ANY(SELECT SA FROM S WHERE SD= ‘IS')AND SD<>'ISORDER BY SA DESC等价于SELECT S#, SNFROMS WHERE SA<(SELECT MAX (SA)FROMS WHERE SD=‘IS')AND SD>“ISORDERBYSADESO
➢ 带ANY和ALL的子查询（子查询返回多值时用） 检索其他系中比IS系任一学生年龄小的学生名单 SELECT S＃,SN FROM S WHERE SA < ANY (SELECT SA FROM S WHERE SD＝‘IS’) AND SD<>‘IS’ ORDER BY SA DESC 等价于 SELECT S＃，SN FROM S WHERE SA < (SELECT MAX（SA） FROM S WHERE SD＝‘IS’) AND SD <> ‘IS’ ORDER BY SA DESC
检索其他系中比IS系所有学生年龄都小的学生名单SELECT S#, SNFROM S WHERE SA < ALI(SELECT SA FROM S WHERE SD='IS')AND SD<>“IS等价于SELECT S#, SN FROM S WHERE SA <(SELECTMIN （SA) FROM S WHERE SD='IS')AND SD<>“IS"ORDERBYSADESC
检索其他系中比IS系所有学生年龄都小的学生名单 SELECT S＃，SN FROM S WHERE SA < ALL (SELECT SA FROM S WHERE SD＝‘IS’) AND SD<>‘IS’ 等价于 SELECT S＃，SN FROM S WHERE SA < (SELECT MIN（SA） FROM S WHERE SD＝‘IS’) AND SD <> ‘IS’ ORDER BY SA DESC
>带EXISTS的子查询（不返回任何数据，只返回Ture和False)检索所有选修了课程号为‘1'的学生姓名SELECTSNFROM S WHEREEXISTS(SELECT*FROM SCWHERE S#=S.S#AND C#=“1°)一注意：此例中子查询的查询条件依赖于外层父查询：称此类查询为相关子查询（corelatedsubquery）。等价连接实现：SELECT SN FROM S, SC WHERE S.S# = SC.S# AND C#=“1’
➢ 带EXISTS的子查询（不返回任何数据，只返回 Ture和False） 检索所有选修了课程号为‘1’的学生姓名 SELECT SN FROM S WHERE EXISTS (SELECT * FROM SC WHERE S# = S.S# AND C# = ‘1’) –注意：此例中子查询的查询条件依赖于外层父查询， 称此类查询为相关子查询(corelated subquery)。 等价连接实现： SELECT SN FROM S,SC WHERE S.S# = SC.S# AND C# = ‘1’
口SQL中没有(Vx)p，故须转换为~(3x(~p))如检索选修了全部课程的学生，即没有一门课没有选的学生SELECTSNFROMSWHERENOTEXISTS(SELECT * FROM C WHERE NOT EXISTS(SELECT * FROM SCWHERE C#= C.C# AND S#= S.S#))口p->q应被等价为-pVq如检索至少选修了学生S001选修的全部课程的学生令p=‘学生S001选修了y'q=‘学生x选修了y(Vy) (p->q) =-y(-(p->q)= y(-(p Vq))=~y(p^q)即不存在这么一门课程，学生S001选修了而x没有选修SELECT SN FROM S WHERE NOT EXISTS(SELECT * FROM SC SC2 WHERE S# = ‘SOO1’ ANDNOT EXISTS (SELECT ＊ FROM SC WHERE C# = SC2.C# ANDS# = S. S#))
❑SQL中没有(x)p，故须转换为¬(x(¬p)) 如检索选修了全部课程的学生,即没有一门课没有选的学生 SELECT SN FROM S WHERE NOT EXISTS (SELECT * FROM C WHERE NOT EXISTS (SELECT * FROM SC WHERE C#＝ C.C# AND S# = S.S#)) ❑p->q应被等价为¬p∨q 如检索至少选修了学生S001选修的全部课程的学生 令p=‘学生S001选修了y’ q＝‘学生x选修了y’ (y)（p->q）=¬y(¬(p->q))= ¬y(¬(¬p∨q))= ¬y(p∧¬q)) 即不存在这么一门课程，学生S001选修了而x没有选修 SELECT SN FROM S WHERE NOT EXISTS( SELECT * FROM SC SC2 WHERE S# = ‘S001’ AND NOT EXISTS (SELECT * FROM SC WHERE C# = SC2.C# AND S# = S.S#))
3.3.4集合查询>使用交、并、差的集合运算概念，INTERSECTUNION, MINUS口检索计算机科学系及年龄不大于19岁的学生SELECT * FROM S WHER SD='CSUNION SELECT*FROMSWHERE SA<=19等价于：SELECT * FROM S WHERE SD=‘CS' OR SA <=19口检索选修了课程号为CO1或C02的学生学号SELECT S# FROM SCWHERE C# =‘CO1UNION SELECTS#FROMSCWHEREC# =‘CO2等价于：SELECT S# FROM SC WHERE C# IN （‘CO1’，‘C02')
3.3.4集合查询 ➢ 使用交、并、差的集合运算概念，INTERSECT ， UNION，MINUS ❑检索计算机科学系及年龄不大于19岁的学生 SELECT * FROM S WHER SD=’CS’ UNION SELECT * FROM S WHERE SA<＝19 等价于： SELECT * FROM S WHERE SD＝‘CS’ OR SA <=19 ❑检索选修了课程号为C01或C02的学生学号 SELECT S# FROM SC WHERE C＃＝‘C01’ UNION SELECT S# FROM SC WHERE C＃＝‘C02’ 等价于： SELECT S# FROM SC WHERE C# IN (‘C01’，‘C02’)