OCP-1Z0-051 第110题 AVG的使用_avg这个题怎么做-CSDN博客

本文链接：https://blog.csdn.net/holly2008/article/details/25345437

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

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

一、原题

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