View the Exhibit and examine the structure of the CUSTOMERS table.
Using the CUSTOMERS table, you need to generate a report that shows the average credit limit for
customers in WASHINGTON and NEW YORK.
Which SQL statement would produce the required result?
A.
SELECT cust_city, AVG(cust_credit_limit)
FROM customers
WHERE cust_city IN (‘WASHINGTON’,’NEW YORK’)
GROUP BY cust_credit_limit, cust_city;
B.
SELECT cust_city, AVG(cust_credit_limit)
FROM customers
WHERE cust_city IN (‘WASHINGTON’,’NEW YORK’)
GROUP BY cust_city,cust_credit_limit;
C.
SELECT cust_city, AVG(cust_credit_limit)
FROM customers
WHERE cust_city IN (‘WASHINGTON’,’NEW YORK’)
GROUP BY cust_city;
D.
SELECT cust_city, AVG(NVL(cust_credit_limit,0))
FROM customers
WHERE cust_city IN (‘WASHINGTON’,’NEW YORK’);
Yes, C.
WRONG! “D” is correct. The question does not ask for the average credit_limit by city. It asks for the average credit limit for customers in “ALL/BOTH” of the cities (New York and Washington). If it wanted the average credit limit for customers in “EACH OF” the cities (New York and Washington).
This in another example, of how poor the quality of questions both Oracle lists in its tests and how important it is to be extremely well versed in the English language. This test does not honestly test ones abilities in SQL… to perform properly for any organization one MUST be able to ask the TRUE intentions of the requester/customer. So, if Oracle truly wants to test ability they must be FAR MORE ARTICULATE with their questions.
You’re wrong. You’ll get “ORA-00937: not a single-group group function” in D. So, correct answer is C.
Donald is wrong. The answer given is correct: C
That’s because it doesn’t make sense to use: (NVL(cust_credit_limit,0)) – that’s because cust_city has a NOT NULL constraint. The data is not supposed to contain null values.
C.
SELECT cust_city, AVG(cust_credit_limit)
FROM customers
WHERE cust_city IN (‘WASHINGTON’,’NEW YORK’)
GROUP BY cust_city;