6.Examine the structure of the SHIPMENTS table:

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 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.
答案:C
解析:这里考察的是decode和case的用法，decode不能这样使用,因此只留下了B，C选项，题目中说的是如果SHIPMENT_DATE比PO_DATE晚了1个月的话，那么每天20$
所以MONTHS_BETWEEN (shipment_date,po_date)>1这个是正确的，shipment_date - po_date这个是相差的天数，因此正确
MONTHS_BETWEEN(date1,date2) : 相等结果为0，date1大于date2为正，小于为负

SELECT MONTHS_BETWEEN
       (TO_DATE('02-02-1995','MM-DD-YYYY'),
        TO_DATE('01-01-1995','MM-DD-YYYY') ) "Months"
  FROM DUAL;

    Months
----------
1.03225806

SELECT product_id,
       DECODE (warehouse_id, 1, 'Southlake', 
                             2, 'San Francisco', 
                             3, 'New Jersey', 
                             4, 'Seattle',
                                'Non domestic') "Location" 
  FROM inventories
  WHERE product_id < 1775
  ORDER BY product_id, "Location";