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

海星星hktk發表於2016-04-27
Oracle
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));

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29475508/viewspace-2089324/，如需轉載，請註明出處，否則將追究法律責任。

相關文章