OCP-1Z0-051 补充题库 第6题 SELECT查询语句

一、原题

See the Exhibit and examine the structure of the CUSTOMERS table:

Using the CUSTOMERS table, you need to generate a report that shown 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');

答案: C

二、题目翻译

下看面的CUSTOMERS表的结构
使用CUSTOMERS表，生成一个报告，显示WASHINGTON和NEW YORK这两个城市的平均credit_limit。
哪个SQL会生成所需要的结果?

三、题目解析

根据题目的要求，需要按城市分组，然后求平均值。
AB选项不仅按城市分组，也按cust_credit_limit分组了，不合题目要求。
D选项求的是所有城市的平均值。