OCP-1Z0-051 第138题 not操作,IN、ANY、ALL的应用

来源：互联网发布：功夫英语知乎编辑：程序博客网时间：2024/05/01 17:48

一、原题

Which statement is true regarding subqueries?
A. The LIKE operator cannot be used with single- row subqueries.
B. The NOT IN operator is equivalent to IS NULL with single- row subqueries.
C. =ANY and =ALL operators have the same functionality in multiple- row subqueries.
D. The NOT operator can be used with IN, ANY, and ALL operators in multiple- row subqueries.

答案：D

二、题目翻译

关于子查询哪句话是正确的？
A.LIKE操作符不能用在单行子查询中。
B.NOT IN操作符相当于单选子查询的IS NULL。
C.多行子查询中的=ANY and =ALL操作符具有相同的功能。
D.NOT操作可以用在多行子查询中的IN,ANY和ALL操作符上。

三、测试
D选项，进行如下测试：

SQL> select ename,sal
2 from emp
3 where not sal IN (select sal from emp where ename='SCOTT' or ename='FORD');

ENAME                       SAL
-------------------- ----------
SMITH                       800
ALLEN                      1600
WARD                       1250
JONES                      2975
MARTIN                     1250
BLAKE                      2850
CLARK                      2450
KING                       5000
TURNER                     1500
ADAMS                      1100
JAMES                       950
MILLER                     1300

12 rows selected.

SQL> select ename,sal
2 from emp
3 where sal not IN (select sal from emp where ename='SCOTT' or ename='FORD');

ENAME                       SAL
-------------------- ----------
SMITH                       800
ALLEN                      1600
WARD                       1250
JONES                      2975
MARTIN                     1250
BLAKE                      2850
CLARK                      2450
KING                       5000
TURNER                     1500
ADAMS                      1100
JAMES                       950
MILLER                     1300

12 rows selected.

SQL> select ename,sal
2 from emp
3 where not sal=ANY(select sal from emp where ename='SCOTT' or ename='FORD');

ENAME                       SAL
-------------------- ----------
SMITH                       800
ALLEN                      1600
WARD                       1250
JONES                      2975
MARTIN                     1250
BLAKE                      2850
CLARK                      2450
KING                       5000
TURNER                     1500
ADAMS                      1100
JAMES                       950
MILLER                     1300

12 rows selected.

SQL> select ename,sal
2 from emp
3 where not sal=ALL(select sal from emp where ename='SCOTT' or ename='FORD');

ENAME                       SAL
-------------------- ----------
SMITH                       800
ALLEN                      1600
WARD                       1250
JONES                      2975
MARTIN                     1250
BLAKE                      2850
CLARK                      2450
KING                       5000
TURNER                     1500
ADAMS                      1100
JAMES                       950
MILLER                     1300

12 rows selected.

0 0