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分別使用的串連語句和子查詢