ocp-1z0-051-Topic Analysis-Question 8th

8. View the Exhibit and examine the structure of the CUSTOMERS table.
Which both tasks would require subqueries or joins to being executed in a single statement? (Choose.)

(Test instructions: The topic gives a Customers table.) Ask which two tasks to run in a statement requires a subquery or a connection statement. ）

A. Listing of customers who does not has 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 credits limit of male customers residing in ' Tokyo ' or ' Sydney '
D. Listing of those customers whose credit limit is the same as the credits limit of customers residing in theCity ' Tokyo '
E. Finding the number of customers, in each city, whose credits limit is more than the average credits limit ofAll the customers

Answer:de

A: List customers with no credit limit and a birth date greater than 1980.

Select * FROM Customers Where Cust_credit_limit is NULL and Cust_year_of_birth >1980;

B: Count the number of married clients in each city separately.

Select cust_city,  count (*) from Customers Where  cust _maritial_status= ' married '   group by cust_city;

C: Statistics ' Tokyo ' and ' Sydney ' Two cities average male customer credit limit

Select avg (cust_credit_limit)   from Customers Where cust_gender= ' male ' and 

D: List the customers with the same credit limit in Tokyo city.

Select * from Customers cust1,customers Cust2

where Cust1.< Span style= "margin:0px; padding:0px ">cust_credit_limit=cust2. cust_credit_limit and  Cust2. cust_city= ' Tokyo ';

E: Count the number of customers per city with a credit limit greater than the average credit limit.

Select cust_city, Count (*) from Customers

where  cust_credit_limit> ( Select avg (cust_credit_limit) from Customers)

Group BY Cust_city;

Visible from above. Answer the connection statements and subqueries used by the de respectively

ocp-1z0-051-Topic Analysis-Question 8th