SQL语言复杂查询 - θ Some和 θ All子查询

1.θ some 和 θ all 子查询的语法

  (1)表达式  θ some (子查询)

  (2)表达式  θ all (子查询)

  (3)说明:θ 是比较运算符,包括 >, <, >=, <=, =, <>

  (4)语义:将表达式的值与子查询的结果进行比较

    1)如果表达式的值至少与子查询结果的某一个值相比较满足 theta 关系,则“表达式 theta some (子查询)” 的结果为真(存在量词为真)

    2)如果表达式的值与子查询结果的所有值相比较都满足 theta 关系,则“表达式 theta all (子查询)” 的结果为真(全称量词为真)

 

2.举例

  (1)找出工资最低的教师姓名

    Select Tname From Teacher

    Where Salary <= all ( Select Slary From Teacher );

  (2)找出 001 号课成绩不是最高的所有学生的学号

    Select S# From SC

    Where C# = '001' and Score < some ( Select Score From SC Where C# = '001' );

  (3)找出所有课程都不及格的学生姓名

    Select Sname From Student 

    Where 60 > all ( Select Score From SC Where Student.S# = S# );

  (4)找出 001 号课成绩最高的所有学生的学号 

    Select S# From SC

    Where C# = '001' and Score >= all ( Select Score From SC Where C# = '001' );

  (5)找出 98030101 号同学成绩最低的课程号

    Select C# From SC 

    Where S# = '98030101' and Score <= all ( Select Score From SC Where S# = '98030101' ); 

  (6)找出张三同学成绩最低的课程号

    Select C# From SC, Student 

    Where Sname = "张三" and SC.S# = Student.S# and Score <= all ( Select Score From SC Where Student.S# = S# );

 

3.等价性变换问题

  (1)表达式  = some (子查询)

           表达式  in (子查询)

  (2)表达式 <> all (子查询)

      表达式  not in (子查询)

上一篇:socket和粘包现象


下一篇:SQL语言复杂查询-IN/Not IN子查询