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
City 'Tokyo'
E. Finding the number of MERs, in each city, whose credit limit is more than the average credit limit
All 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.

SelectCust_city,Count (*) from MERs whereCust_maritial_status = 'married'Group by cust_city;

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

SelectCust_city,AVG (Cust_credit_limit)From MERs where cust_gender = 'male' andCust_city in ('Tokyo ', 'sydney ');

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 andCust_city = 'Tokyo ';

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

SelectCust_city,Count (*) from MERs

WhereCust_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