表 >复合条件连接 检索选修课程号‘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#
➢ 复合条件连接 检索选修课程号‘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嵌套查询 表 >带N谓词的子查询 检索与“刘晨”同在一系的学生信息 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=刘晨
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.C WHERE 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#.SN FROM S WHERE SD= (SELECT SD FROM S WHERE CN='刘晨)
➢ 带比较运算的子查询 当确定子查询的返回值是唯一时，可以使用比较运算 符(注意子查询在比较符后) SELECT S#,SN FROM S WHERE SD= (SELECT SD FROM S WHERE CN=’刘晨’)
>带ANY和ALL的子查询（子查询返回多值时用） 检索其他系中比S系任一学生年龄小的学生名单 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 FROMS WHERE SA< (SELECT MAX (SA)FROMS WHERE SD=IS) AND SD◇IS' ORDER BY SA DESC
➢ 带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