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 表示式,month_between函式
關鍵字：the first correct   (case)

答案解析：
根據題意，要求出一個報表顯示po_id以及總的罰款金額。罰款計算方法：如果shipment_date 比 po_date晚一個月，則每天罰金$20.

給出的sql語句，第一個用了case ,第二個用了decode.兩條語句都用了months_between函式，且函式中引數順序不同。
先來看months_between函式：

months_between 函式
months_between(date1,date2) 返回兩個日期date1和data2之間間隔的月數。
如果date1 比 date2 晚，則返回正數；如果date1 比 date2 早，則返回負數。

SCOTT@PROD>select hiredate,sysdate,months_between(hiredate,sysdate)
2 from emp where empno=7788;

SCOTT@PROD>select hiredate,sysdate,months_between(sysdate,hiredate)
2 from emp where empno=7788;

結合題目要求，shipment_date　比　po_date 晚一個月，可以表示為 months_between(shipment_date,po_date)>1
故第二條sql語句months_between部分錯誤，ADE選項錯，排除ADE

接下來看case 和 decode的用法

透過scott使用者中emp表簡單演示同一個查詢需求，decode和case的不同
查詢需求：根據不同的部門號，對員工進行漲工資。10號部門員工工資漲100,20號部門員工工資漲200，其餘的漲300（30號部門）
【decode】:
SCOTT@PROD>select ename,deptno,sal,
2 decode ( deptno,10,sal+100,
3 20,sal+200,
4 sal+300 ) as new_sal
5 from emp order by deptno;

【case】: case可以有兩種寫法，第一種與decode類似
SCOTT@PROD>select ename,deptno,sal,
2 case deptno when 10 then sal+100
3         when 20 then sal+200
4         else     sal+300 end as new_sal
5 from emp order by deptno;

case第二種寫法與decode不同，可以寫明具體的條件
SCOTT@PROD>select ename,deptno,sal,
2 case when deptno=10 then sal+100
3    when deptno=20 then sal+200
4   else         sal+300 end as new_sal
5 from emp order by deptno;

題目中給出的兩個sql ,第一條語法正確，也符合題目要求。答案C正確
第二條decode的不僅mouths_between部分錯誤，語法上也有錯誤。

建立測試表測試
SCOTT@PROD>create table shipments(
2 po_id number(3) not null,
3 po_date date not null,
4 shipment_date date not null,
5 shipment_mod varchar2(30),
6 shipment_cost number(8,2));

【OCP】Oracle 11g OCP 1Z0-051 006

相關文章