OCP-1Z0-051 第111题 GROUP BY子句

最新推荐文章于 2023-05-04 09:36:45 发布

原创最新推荐文章于 2023-05-04 09:36:45 发布 · 3.7k 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#OCP-1Z0-051 #GROUP BY #MAX #11g

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

220 篇文章

订阅专栏

本文详细介绍了如何通过SQL查询来找出CUSTOMERS表中每个城市、每个收入水平下的最高信用额度。通过对比不同选项，最终确定了正确的查询语句。了解此技巧对于数据库管理及数据分析工作至关重要。

一、原题

View the Exhibit and examine the structure of the CUSTOMERS table.

Which statement would display the highest credit limit available in each income level in each city in the CUSTOMERS table?
A. SELECT cust_city, cust_income_level, MAX(cust_credit_limit )
       FROM customers
GROUP BY cust_city, cust_income_level, cust_credit_limit;

B. SELECT cust_city, cust_income_level, MAX(cust_credit_limit)
        FROM customers
GROUP BY cust_city, cust_income_level;

C. SELECT cust_city, cust_income_level, MAX(cust_credit_limit)
        FROM customers
GROUP BY cust_credit_limit, cust_income_level, cust_city ;

D. SELECT cust_city, cust_income_level, MAX(cust_credit_limit)
        FROM customers
GROUP BY cust_city, cust_income_level, MAX(cust_credit_limit);

答案：B

二、题目翻译

查看CUSTOMERS表的结构，
哪条语句显示每个城市中每个收入水平的最高信用额度？

三、题目解析

AC选项不正确，按照题目，应该按城市和收入水平分组，这两个都不正确。
D选项不正确，报错，因为GROUP BY子句中不能使用MAX聚合函数。