OCP-1Z0-051-题目解析-第8题

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.)

（题意：题目给出了一个Customers表，问哪两个任务在一条语句中执行需要用到子查询或者连接语句。）

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 ofall the customers

Answer: DE

A：列出没有信用额度且出生日期大于1980的客户。

Select * from customers Where Cust_Credit_Limit is NULL and Cust_Year_Of_Birth >1980;

B:分别统计每个城市已婚客户的数量。

Select Cust_City, Count(*) from Customers Where  Cust_Maritial_Status='married' group by Cust_City ;

C:统计'Tokyo' 和'Sydney'两个城市男性客户信用额度的平均值

Select Avg(Cust_Credit_Limit) from Customers Where Cust_gender='male' and Cust_City in('Tokyo' ,'Sydney');

D:列出Tokyo城市里信用额度相等的客户。

Select * from Customers Cust1,Customers Cust2

where Cust1.Cust_Credit_Limit=Cust2.Cust_Credit_Limit and Cust2.Cust_City='Tokyo';

E:统计每个城市信用额度大于平均信用额度客户的数量。

Select Cust_City, Count(*) from Customers

Where  Cust_Credit_Limit>(Select avg(Cust_Credit_Limit) from Customers)

group by Cust_City;

由上可见，答案DE分别使用的连接语句和子查询