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的用法
![](//img.blog.itpub.net/blog/attachment/201604/27/29475508_1461737089lB9G.jpg?x-oss-process=style/bb)
通过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;
![](//img.blog.itpub.net/blog/attachment/201604/27/29475508_146173783629r9.jpg?x-oss-process=style/bb)
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;
![](//img.blog.itpub.net/blog/attachment/201604/27/29475508_1461738057b3QJ.jpg?x-oss-process=style/bb)
题目中给出的两个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));
![](//img.blog.itpub.net/blog/attachment/201604/27/29475508_1461738387T912.jpg?x-oss-process=style/bb)
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函数:
![](http://img.blog.itpub.net/blog/attachment/201604/27/29475508_1461734891K15W.jpg?x-oss-process=style/bb)
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;
![](http://img.blog.itpub.net/blog/attachment/201604/27/29475508_1461736538Jnjh.jpg?x-oss-process=style/bb)
SCOTT@PROD>select hiredate,sysdate,months_between(sysdate,hiredate)
2 from emp where empno=7788;
![](http://img.blog.itpub.net/blog/attachment/201604/27/29475508_1461736554Lx0x.jpg?x-oss-process=style/bb)
结合题目要求,shipment_date 比 po_date 晚一个月,可以表示为 months_between(shipment_date,po_date )>1
故第二条sql语句months_between部分错误,ADE选项错,排除ADE
接下来看case 和 decode的用法
![](http://img.blog.itpub.net/blog/attachment/201604/27/29475508_1461737089lB9G.jpg?x-oss-process=style/bb)
![](http://img.blog.itpub.net/blog/attachment/201604/27/29475508_1461737110rjj4.jpg?x-oss-process=style/bb)
通过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;
![](http://img.blog.itpub.net/blog/attachment/201604/27/29475508_14617375224zgG.jpg?x-oss-process=style/bb)
【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;
![](http://img.blog.itpub.net/blog/attachment/201604/27/29475508_146173783629r9.jpg?x-oss-process=style/bb)
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;
![](http://img.blog.itpub.net/blog/attachment/201604/27/29475508_1461738057b3QJ.jpg?x-oss-process=style/bb)
题目中给出的两个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));
![](http://img.blog.itpub.net/blog/attachment/201604/27/29475508_1461738387T912.jpg?x-oss-process=style/bb)
![](http://img.blog.itpub.net/blog/attachment/201604/27/29475508_1461738586K14c.jpg?x-oss-process=style/bb)
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29475508/viewspace-2089324/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/29475508/viewspace-2089324/