OCP-1Z0-051 第110题 AVG的使用

来源：互联网发布：软件开发热门发展方向编辑：程序博客网时间：2024/05/16 14:41

一、原题

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');

答案：C

二、题目翻译

查看CUSTOMERS表的结构
使用CUSTOMERS表的数据生成一个报表，显示居住在WASHINGTON和NEW YORK的客户的平均credit limit
哪条SQL语句给出所需结果？

三、题目解析

AB选项不正确，因为GROUP BY里的分组的列不对，这里应该按cust_city分组。
D选项不正确，因为要根据城市求平均值，所以需要使用GROUP BY子句分组。

0 0