Refer to the tblCustomer table below. As a data analyst, you have been requested
ID: 3712573 • Letter: R
Question
Refer to the tblCustomer table below. As a data analyst, you have been requested to select rows that meet the following requirements: For each region, extract the average balance for the region. Include only those regions where the average balance is greater than $300. Order the rows in descending order of average balance. Part 1: Build your SELECT statement by choosing the certain clauses from the list below and putting the clauses in the correct order: tblCustomer: CustID (PK) LastName FirstName Phone City Region Overdue Status* Preferred Income NumOrders Balance Birthdate 10 Smith Bob 123-4567 Orlando North Paid Y 10000 5 100 1/1/1978 12 Ramirez Jose 422-1234 Apex South Paid Y 50000 16 600 2/8/1970 13 Washington Terrell 951-1234 Sujour East [null] Y 20000 25 700 3/5/1985 15 Jones Bill 963-7894 AxeCrossing West Unpaid Y 16900 2 500 12/1/1990 17 Morales Sandy 951-1234 Orlando North Paid N 85000 90 400 8/3/1986 21 Principe Michelle 963-4561 Apex South Paid N 94000 85 300 7/2/1974 23 Mavis John 147-4561 Sujour East Unpaid Y 25000 26 600 3/5/1989 *Overdue Status indicates customers with a balance that is 30 or more days overdue SELECT Region, Avg(Balance) GROUP BY Balance WHERE Balance > 300 GROUP BY Region ORDER BY Region ORDER BY Avg(Balance) HAVING Avg(Balance) > 300 ORDER BY Region FROM tblCustomer HAVING Balance > 300 Part 2: Did you select any filter clause(s)? (WHERE, HAVING) Justify your choice of filter(s).
Explanation / Answer
--Build your SELECT statement by choosing the certain clauses from the list below and putting the clauses in the correct order: tblCustomer
SELECT Region,Avg(Balance) FROM tblCustomer
GROUP BY Region HAVING Avg(Balance)>300
ORDER BY Avg(Balance) DESC;
--Did you select any filter clause(s)? (WHERE, HAVING) Justify your choice of filter(s).
THe above select query has filter clause. Here we are filter data based on Avg(Balance). In order to filter we are using having clause.
If we didn't use having clause query will pull out data even Avg(Balance) is lesser than 300
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.