表>复合条件连接检索选修课程号‘2'且成绩在90分以上的所有学生SELECT S.S# ,SN FROM S,SCWHERE S.S# = SC.S# AND SC.C#='2' AND SC.GR>=90检索每个学生选修的课程名及其成绩SELECT S.S#,SN,C.CN,SC.GR from S,SC,CWHERES.S#=SC.S# ANDSC.C#=C.C#
➢ 复合条件连接 检索选修课程号‘2’且成绩在90分以上的所有学生 SELECT S.S# ,SN FROM S,SC WHERE S.S# = SC.S# AND SC.C#=’2’ AND SC.GR>=90 检索每个学生选修的课程名及其成绩 SELECT S.S#,SN,C.CN,SC.GR from S,SC,C WHERE S.S# = SC.S# AND SC.C# = C.C# 表
表3.3.3嵌套查询>带IN谓词的子查询检索与“刘晨”同在一系的学生信息SELECT S#,SN.SD FROM S WHERE SD IN(SELECTSDFROM SWHERE SN=‘刘晨’）本例可以通过自连接来实现SELECT s1.S#, s1.SN, s1.SD FROM S s1, S s2WHERE s1.SD = s2.SD AND s2.SN='刘晨
3.3.3嵌套查询 ➢ 带IN谓词的子查询 检索与“刘晨”同在一系的学生信息 SELECT S#,SN,SD FROM S WHERE SD IN (SELECT SD FROM S WHERE SN＝‘刘晨’) 本例可以通过自连接来实现 SELECT s1.S#, s1.SN, s1.SD FROM S s1, S s2 WHERE s1.SD = s2.SD AND s2.SN=’刘晨’ 表
表检索选修了课程名的为‘MA'的学生学号和姓名SELECT S#. SN FROM S WHERE S# IN(SELECT S#FROM SC WHERE C# IN(SELECT C# FROM C WHERE CN='MA'))本例同样可以用连接来实现SELECT S#,SN FROM S ,SC,CWHERE S.S#= SC.S# AND SC.C#=C.C#AND C.CN='MA
检索选修了课程名的为‘MA’的学生学号和姓名 SELECT S#, SN FROM S WHERE S# IN (SELECT S# FROM SC WHERE C# IN (SELECT C# FROM C WHERE CN=’MA’) ) 本例同样可以用连接来实现 SELECT S#,SN FROM S ,SC,C WHERE S.S# = SC.S# AND SC.C# = C.C# AND C.CN=’MA’ 表
>带比较运算的子香询当确定子查询的返回值是唯一时，可以使用比较运算符（注意子查询在比较符后）SELECT S#.SNFROM S WHERE SD-(SELECTSDFROMSWHERECN='刘晨'）
➢ 带比较运算的子查询 当确定子查询的返回值是唯一时，可以使用比较运算 符(注意子查询在比较符后) SELECT S#,SN FROM S WHERE SD= (SELECT SD FROM S WHERE CN=’刘晨’)
>带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