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
SHIPMENT_DATE is later than one month from the PO_DATE. The penalty is $20 per day.
Evaluate the following two queries:
(Question: The Shipments table is assigned with the PO_DATE and SHIPMENT_DATE fields. If SHIPMENT_DATE is one month later than PO_DATE, a penalty of 20 will be incurred every day, please comment on the two SQL statements below)
SQL> SELECT po_id, CASEWHEN MONTHS_BETWEEN (shipment_date,po_date)>1 THENTO_CHAR((shipment_date - po_date) * 20) ELSE 'No Penalty' END PENALTYFROM 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
The syntax of the decode function is: decode (condition, value 1, return value 1, value 2, return value 2,... value n, return value n, default value)
Comparison: The preceding decode usage error. The syntax of Case... When is correct, and the statement is written correctly. The correct result can be obtained.
The second decode syntax can be changed in this way, and the size is determined using the sign function.
1 SELECT po_id, DECODE2 (SIGN(MONTHS_BETWEEN(po_date,shipment_date)),1 3 TO_CHAR((shipment_date - po_date) * 20), 'No Penalty') PENALTY4 FROM shipments;