OCP-1Z0-051-Question Analysis-8th question

8. View the Exhibit and examine the structure of the MERs table.
Which two tasks wowould require subqueries or joins to be executed in a single statement? (Choose two .)

(Question: A MERs table is provided for the question, asking which two tasks need to use subqueries or connection statements to execute in one statement .)

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

Answer: DE

A: List customers with no credit limit and A birthdate greater than 1980.

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

B: calculate the number of married customers in each city.

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

C: calculate the average credit line of male customers in 'cide' and 'sydonnel' cities

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

D: List customers with the same credit line in Tokyo.

Select * from MERs Cust1, Customers Cust2

Where Cust1.Cust _ Credit_Limit = Cust2.Cust _ Credit_Limit andCust2.Cust_City = 'Tokyo ';

E: count the number of customers whose credit limit is greater than the average credit limit in each city.

Select Cust_City, Count (*) from MERs

Where Cust_Credit_Limit> (Select avg (Cust_Credit_Limit) from MERs mers)

Group by Cust_City;

As shown in the preceding figure, the connection statements and subqueries used by answer DE are respectively