See the Exhibit and examine the structure of the PROMOTIONS table:
Using the PROMOTIONS table, you need to find out the average cost for all promos in the range
$0-2000 and $2000-5000 in category A.
You issue the following SQL statements:
What would be the outcome?
A.
It generates an error because multiple conditions cannot be specified for the WHEN clause
B.
It executes successfully and gives the required result
C.
It generates an error because CASE cannot be used with group functions
D.
It generates an error because NULL cannot be specified as a return value
Explanation:
CASE Expression
Facilitates conditional inquiries by doing the work of an
IF-THEN-ELSE statement:
CASE expr WHEN comparison_expr1 THEN return_expr1
[WHEN comparison_expr2 THEN return_expr2
WHEN comparison_exprn THEN return_exprn
ELSE else_expr]
END
it will execute but doesn’t give the required result, the second expression excludes 2000
You’re wrong:
the value of
expr1 BETWEEN expr2 AND expr3
is the value of the boolean expression:
expr2 <= expr1 AND expr1 <= expr3
https://docs.oracle.com/cd/B28359_01/server.111/b28286/conditions011.htm#SQLRF52147
I think D is correct, because returns_expressions and else_exp must not be NULL