1Z0-51-6题

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:
SQL> SELECT po_id, CASE
WHEN MONTHS_BETWEEN (shipment_date,po_date)>1 THENTO_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
答案解析：
显示PO_ID和支付的罚款总金额，SHIPMENT_DATE与PO_DATE进行比较，SHIPMENT_DATE如果比PO_DATE晚一个月，则每天罚款$20。

SQL> CREATE TABLE SHIPMENTS(PO_ID NUMBER(3) NOT NULL,PO_DATE DATE NOT NULL,SHIPMENT_DATE DATE NOT NULL,SHIPMENT_MODE VARCHAR2(30),SHIPMENT_COST NUMBER(8,2));

Table created.

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;

no rows selected