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

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