1Z0-051 QUESTION 8 子查询和JION的用法

最新推荐文章于 2025-06-17 23:15:28 发布

原创最新推荐文章于 2025-06-17 23:15:28 发布 · 551 阅读

0 ·

CC 4.0 BY-SA版权

OCP之路专栏收录该内容

37 篇文章

订阅专栏

本文探讨了在执行特定SQL查询任务时需要使用子查询或联接的情况，例如找出信用额度与特定城市顾客相同的人或统计各城市中信用额度超过平均值的顾客数量。

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

Which two tasks would require subqueries or joins to be executed in a single statement? (Choose two.)

A. listing of customers who do not have a credit limit and were born before 1980
B. finding the number of customers, in each city, whose marital status is 'married'
C. finding the average credit limit of male customers residing in 'Tokyo' or 'Sydney'
D. listing of those customers whose credit limit is the same as the credit limit of customers residing in the
city 'Tokyo'
E. finding the number of customers, in each city, whose credit limit is more than the average credit limit of
all the customers

答案为：DE

解析：

--A选项不需要用到子查询或者jion：
select * from customers where cust_credit_limit='' and cust_year_of_birth<1980;
--B选项不需要用到子查询或者jion：
select cust_city,count(*) from customers where cust_maritial_status='married' group by cust_city; 
--C选项不需要用到子查询或者jion：
select * from customers where cust_credit_limit=avg(cust_credit_limit) and cust_gender='1' and cust_city in('Tokyo','Sydney')
--假设cust_gender=1为male
--D选项需要用到子查询或者jion：
select * from customers where cust_credit_limit=
 （select cust_credit_limit from customers where cust_city ='Tokyo')
--E选项需要用到子查询或者jion：
select cust_city,count(*) from customers where cust_id=
（select custid from customers where cust_credit_limit>=avg(cust_credit_limit)) group by cust_city