OCP-1Z0-051-题目解析-第6题

gqinvs

　　6. Examine the structure of the SHIPMENTS table:
　　name                    Null       　　Type
　　PO_ID          　　   NOT NULL    NUMBER(3)
　　PO_DATE     　　   NOT NULL    DATE
　　SHIPMENT_DATE   NOT NULL    DATE
　　SHIPMENT_MODE             　　  VARCHAR2(30)
　　SHIPMENT_COST           　　    NUMBER(8,2)
　　You want to generate a report that displays the PO_ID and the penalty amount to be paid if the
　　SHIPMENT_DATE is later than one month from the PO_DATE. The penalty is $20 per day.
　　Evaluate the following two queries:
　　(题意：题目给了一个发货表Shipments，其中有PO_DATE和SHIPMENT_DATE 字段，如果SHIPMENT_DATE比PO_DATE迟一个月，则每多一天罚款20，对此，请评价下面给出的两个sql语句)
　　SQL> SELECT po_id, CASE
　　WHEN MONTHS_BETWEEN (shipment_date,po_date)>1 THEN
　　TO_CHAR((shipment_date - po_date) * 20) ELSE 'No Penalty' END PENALTY
　　FROM shipments;
　　SQL>SELECT po_id, DECODE
　　(MONTHS_BETWEEN (po_date,shipment_date)>1,
　　TO_CHAR((shipment_date - po_date) * 20), 'No Penalty') PENALTY   
　　FROM shipments;
　　Which statement is true regarding the above commands?
　　A. Both execute successfully and give correct results.
　　B. Only the first query executes successfully but gives a wrong result.
　　C. Only the first query executes successfully and gives the  correct result.
　　D. Only the second query executes successfully but gives a wrong result.
　　E. Only the second query executes successfully and gives the correct result.
　　Answer: C
　　decode函数的语法是，decode(条件,值1,返回值1,值2,返回值2,...值n,返回值n,缺省值)
　　对比，上面decode使用错误。Case...When的语法是正确的，并且语句写的也是正确的，可以得到正确的结果。
　　第二个decode语法可以这样改，使用sign函数判断大小。



1 SELECT po_id, DECODE
2 (SIGN(MONTHS_BETWEEN(po_date,shipment_date)),1
3 TO_CHAR((shipment_date - po_date) * 20), 'No Penalty') PENALTY
4 FROM shipments;