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

一、原題

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.