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

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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29475508/viewspace-2089324/