then promo_cost

ELSE null END)"CAT_2000A",

AVG(CASE

WHEN promo_cost BETWEEN2001 AND 5000 AND promo_category='A'

THEN promo_cost

ELSE null END)"CAT_5000A"

FROM promotions;

What would be theoutcome

A. It executessuccessfully and gives the required result.

B. It generates an errorbecause NULL cannot be specified as a return value.

C. It generates an errorbecause CASE cannot be used with group functions.

D. It generates an errorbecause multiple conditions cannot be specified for the WHEN clause.

Answer: A

解析：

找到在平均 promo_cost在$-2000和$2000-5000范围类，并且属于A的

AVG(CASE

WHEN promo_cost BETWEEN 0 AND 2000 AND promo_category='A'

then promo_cost

ELSE null END)"CAT_2000A",

满足条件，输出 promo_cost

否则输出空

同理：

AVG(CASE

WHEN promo_cost BETWEEN 2001 AND 5000 AND promo_category='A'

THEN promo_cost

ELSE null END)"CAT_5000A"

100. View the Exhibitand examine the structure of the PROMOTIONS table.

Which SQL statements areva lid (Choose all that apply.)

A. SELECT promo_id,DECODE(NVL(promo_cost,0), promo_cost,

promo_cost * 0.25, 100)"Discount"

FROM promotions;

B. SELECT promo_id,DECODE(promo_cost, 10000,

DECODE(promo_category,'G1', promo_cost *.25, NULL),

NULL)"Catcost"

FROM promotions;

C. SELECT promo_id,DECODE(NULLIF(promo_cost, 10000),

NULL, promo_cost*.25,'N/A') "Catcost"

FROM promotions;

D. SELECT promo_id,DECODE(promo_cost, >10000, 'High',

<10000, 'Low')"Range"

FROM promotions;

Answer: AB

解析：

这里主要考察decode的用法，引用官方文档：

DECODE compares expr toeach search value one by one. If expr is equal to a

search, then OracleDatabase returns the corresponding result. If no match is

found, then Oraclereturns default. If default is omitted, then Oracle returns null.

The arguments can be anyof the numeric types (NUMBER, BINARY_FLOAT, or

BINARY_DOUBLE) orcharacter types.

For example:
SELECT product_id,

DECODE (warehouse_id, 1,'Southlake',

2, 'San Francisco',

3, 'New Jersey',

4, 'Seattle',

'Non domestic')"Location"

FROM inventories

WHERE product_id <1775

ORDER BY product_id,"Location";

101. Examine the data inthe PROMO_BEGIN_DATE column of the PROMOTIONS table:

PROMO_BEGIN _DATE

04-jan-00

10-jan-00

15-dec-99

18-oct-98

22-aug-99

You want to display thenumber of promotions started in 1999 and 2000.

Which query gives thecorrect output

A. SELECTSUM(DECODE(SUBSTR(promo_begin_date,8),'00',1,0)) "2000",

SUM(DECODE(SUBSTR(promo_begin_date,8),'99',1,0))"1999"

FROM promotions;

B. SELECT SUM(CASETO_CHAR(promo_begin_date,'yyyy') WHEN '99' THEN 1

ELSE 0 END)"1999",SUM(CASE TO_CHAR(promo_begin_date,'yyyy') WHEN '00' THEN 1

ELSE 0 END)"2000"

FROM promotions;

C. SELECT COUNT(CASETO_CHAR(promo_begin_date,'yyyy') WHEN '99' THEN 1

ELSE 0 END)"1999",COUNT(CASE TO_CHAR(promo_begin_date,'yyyy') WHEN '00' THEN 1

ELSE 0 END)"2000"

FROM promotions;

D. SELECTCOUNT(DECODE(SUBSTR(TO_CHAR(promo_begin_date,'yyyy'), 8), '1999', 1, 0))"1999",

COUNT(DECODE(SUBSTR(TO_CHAR(promo_begin_date,'yyyy'),8),'2000', 1,

0)) "2000"

FROM promotions;

Answer: A

解析：题目意思要求分别统计1999年和2000的数量

首先得区分1999和2000才能分别进行统计

SUM(DECODE(SUBSTR(promo_begin_date,8),'00',1,0))

SUM(DECODE(SUBSTR(promo_begin_date,8),'99',1,0))

截取后面两位数字，如果是00就是2000如果是99就是1999

102. Examine thestructure of the TRANSACTIONS table:

name Null Type

TRANS_ID NOT NULLNUMBER(3)

CUST_NAME VARCHAR2(30)