OCP-1Z0-051-V9.02-69题

69. You need to generate a list of all customer last names with their credit limits from the CUSTOMERS

table. Those customers who do not have a credit limit should appear last in the list. 

Which two queries would achieve the required result? (Choose two.)

A. SELECT cust_last_name, cust_credit_limit

FROM customers

ORDER BY cust_credit_limit DESC ;

B. SELECT cust_last_name, cust_credit_limit

FROM customers

ORDER BY cust_credit_limit;

C. SELECT cust_last_name, cust_credit_limit

FROM customers

ORDER BY cust_credit_limit NULLS LAST;

D. SELECT cust_last_name, cust_credit_limit

FROM customers

ORDER BY cust_last_name, cust_credit_limit NULLS LAST;

Answer: BC

 答案解析：

参考： http://blog.csdn.net/rlhua/article/details/12837221

对于升序排序，空值显示在最后，对于降序排序，则显示在最前面

who do not have a credit limit should appear last in the list

题目要求空值在最后面

要么升序排列，要么指定nulls last

D先按照cust_last_name排序，在按照 cust_credit_limit空值最后排序，不符合题意。

实验验证：

B,

scott@TESTDB> select * from emp order by comm;

 

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO

---------- ---------- --------- ---------- --------- ---------- ---------- ----------

      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30

      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30

      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30

      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30

      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20

      7839 KING       PRESIDENT            17-NOV-81       5000                    10

      7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20

 C,

scott@TESTDB> select * from emp order by comm nulls last;

 

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO

---------- ---------- --------- ---------- --------- ---------- ---------- ----------

      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30

      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30

      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30

      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30

      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20

      7839 KING       PRESIDENT            17-NOV-81       5000                    10

      7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20

 

14 rows selected.

 D,

scott@TESTDB> select * from emp order by ename,comm nulls last;

 

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO

---------- ---------- --------- ---------- --------- ---------- ---------- ----------

      7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20

      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30

      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30 

      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30

      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30

      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30

 

14 rows selected.