View 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 ranges
$0-2000 and $2000-5000 in category A
You issue the following SQL statement:
SQL>SELECT AVG(CASE
WHEN promo_cost BETWEEN 0 AND 2000 AND promo_category=’A’
then promo_cost
ELSE null END) “CAT_2000A”,
AVG(CASE
WHEN promo_cost BETWEEN 2001 AND 5000 AND promo_category=’A’
THEN promo_cost
ELSE null END) “CAT_5000A”
FROM promotions;
What would be the outcome?
A.
It executes successfully and gives the required result.
B.
It generates an error because NULL cannot be specified as a return value.
C.
It generates an error because CASE cannot be used with group functions.
D.
It generates an error because multiple conditions cannot be specified for the WHEN clause.
hey..according to me in this query the answer should be option b because i have studied that we can not specify the literal null in the return expression and else conditions.
so what is the correct answer?
@Nikita,
You must have confused with a different RDBMS or other programming languages. Below is the copy & paste from Oracle Ref on Nulls in SQL functions as FYI.
– – – – – – – – – – –
Nulls in SQL Functions
All scalar functions (except REPLACE, NVL, and CONCAT) return null when given a null argument. You can use the NVL function to return a value when a null occurs. For example, the expression NVL(commission_pct,0) returns 0 if commission_pct is null or the value of commission_pct if it is not null.
Most aggregate functions ignore nulls. For example, consider a query that averages the five values 1000, null, null, null, and 2000. Such a query ignores the nulls and calculates the average to be (1000+2000)/2 = 1500.
what is the correct answer ??
According to my search answer is A
Good day I am so happy I found your blog page, I really found you by mistake, while I was researching on Aol for something else, Anyways I am here now and would just like to say many thanks for a marvelous post and a all round interesting blog (I also love the theme/design), I don’t have time to browse it all at the moment but I have saved it and also added in your RSS feeds, so when I have time I will be back to read a lot more, Please do keep up the excellent job.
http://wayneweiglexx74.livejournal.com/1334.html
Great blog you have got here.. It’s hard to find excellent writing like yours nowadays. I seriously appreciate people like you! Take care!!