表 复合条件连接 检索选修课程号‘2且成绩在90分以上的所有学生 SELECT SS# SN FROMS.SC WHERE SS#= SC S# AND SC. C#=2. AND SC GR>=90 检索每个学生选修的课程名及其成绩 SELECT SS#SN. C. CN.SC gr from Sscc WHERE SS#= SC S# AND SC C#=CC#
➢ 复合条件连接 检索选修课程号‘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.33嵌套查询 表 带Ⅰ谓词的子査询 检索与“刘晨”同在一系的学生信息 SELECT S#SNSD FROM S WHERE SD IN ( SELECT SD FROM S WHERE SN=‘刘晨’) 本例可以通过自连接来实现 SELECT SLS# SlSN SlSD froMSs. ss2 WHERE SISD=s2 SD AND2SN=2刘晨
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 CNEMA)) 本例同样可以用连接来实现 SELECT S# SN FROMS SCc WHERE S S#= SCS# AND SC C#=CC# 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的子查询(子查询返回多值时用 检索其他系中比IS系任一学生年龄小的学生名单 SELECT S# SN FROM S WHERE SA < ANY (SELECT SA FROM S WHERE SD=IS) AND SDIS 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
➢ 带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