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

最新推荐文章于 2018-08-16 18:14:53 发布

原创最新推荐文章于 2018-08-16 18:14:53 发布 · 4k 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#OCP-1Z0-051 #AVG #聚合函数 #11g #GROUP BY

OCP-1Z0-051 专栏收录该内容

220 篇文章

订阅专栏

本文介绍了一道关于SQL查询的题目，需要从CUSTOMERS表中筛选出居住在WASHINGTON和NEWYORK两个城市的客户，并计算这两个城市的平均信用额度。通过对比四个选项，解析了正确答案并解释了原因。

一、原题

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子句分组。