首页 > 代码库 > OCP-1Z0-051-题目解析-第6题

OCP-1Z0-051-题目解析-第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:

(题意:题目给了一个发货表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, DECODE2 (SIGN(MONTHS_BETWEEN(po_date,shipment_date)),1 3 TO_CHAR((shipment_date - po_date) * 20), No Penalty) PENALTY4 FROM shipments;