OCP 1Z0 051 102

102. Examine the structure of the TRANSACTIONS table: 
name            Null            Type 
TRANS_ID        NOT NULL        NUMBER(3) 
CUST_NAME                      VARCHAR2(30) 
TRANS_DATE                     TIMESTAMPTRANS_AMT   NUMBER(10,2) 
You want  to display  the date,  time, and  transaction amount of  transactions  that where done before 12 
noon. The value zero should be displayed  for  transactions where  the  transaction amount has not been 
entered. 
Which query gives the required result? 
A.  SELECT TO_CHAR(trans_date,'dd-mon-yyyy hh24:mi:ss'), 
TO_CHAR(trans_amt,'$99999999D99') 
FROM transactions 
WHERE TO_NUMBER(TO_DATE(trans_date,'hh24')) < 12 AND COALESCE(trans_amt,NULL)<>NULL; 
B.  SELECT TO_CHAR(trans_date,'dd-mon-yyyy hh24:mi:ss'),  
NVL(TO_CHAR(trans_amt,'$99999999D99'),0) 
FROM transactions 
WHERE TO_CHAR(trans_date,'hh24') < 12; 
C.  SELECT TO_CHAR(trans_date,'dd-mon-yyyy hh24:mi:ss'),  
COALESCE(TO_NUMBER(trans_amt,'$99999999.99'),0) 
FROM transactions 
WHERE TO_DATE(trans_date,'hh24') < 12; 
D.  SELECT TO_DATE (trans_date,'dd-mon-yyyy hh24:mi:ss'),  
NVL2(trans_amt,TO_NUMBER(trans_amt,'$99999999.99'), 0) 
FROM transactions 
WHERE TO_DATE(trans_date,'hh24') < 12; 

转为字符串要用to_char()
to_date加格式符只能转为date用

SQL> SELECT TO_DATE(SYSDATE,'hh24') FROM dual;
SELECT TO_DATE(SYSDATE,'hh24') FROM dual
ORA-01850: 小时值必须介于 0 和 23 之间

SQL> SELECT TO_DATE(12,'hh24') FROM dual;
TO_DATE(12,'HH24')
------------------
2014-5-1 12:00:00
1 row selected

null 不能用作大小比较<> = 均返回为空

SQL> select count(*) from emp where comm <> null;
  COUNT(*)
----------
         0
1 row selected

SQL> select count(*) from emp where comm = null;
  COUNT(*)
----------
         0
1 row selected


SQL> select count(*) from emp where comm is null;
  COUNT(*)
----------
        10
1 row selected


SQL> select count(*) from emp where comm is not null;
  COUNT(*)
----------
         4
1 row selected

Answer: B