1. (TCO 2) Refer to the tblCustomer table below. As a data analyst, you have been requested to select rows that meet the following requirements: For each city, list the city name in all upper case characters along with the exchange part of the customer phone number (the first 3 digits). Include only preferred customers in the list. Order the rows in alphabetical order of city. Be sure to give the calculated columns an alias.
tblCustomer:
*Overdue Status indicates customers with a balance that is 30 or more days overdue
Part 1: Write the complete SQL statement needed to return the rows that meet these requirements.
Part 2: What rows will be returned from this query? List the customer ID only of the rows to be returned.
Part 3: What function did you use to extract the three-digit exchange? Why did you select this function? Rewrite the SQL using a different function to extract these characters. (Points : 25)
2. Refer to the tblCustomer table below. As a data analyst, you have been requested to select rows that meet the following requirements. For each city, list the name of the city, and the number of customers in that city. Include only those cities that have more than 1 customer in them. Order the data by city in descending order.
tblCustomer:*Overdue Status indicates customers with a balance that is 30 or more days overdue
Part 1: Build your SELECT statement by choosing the certain clauses from the list below and putting the clauses in the correct order:
SELECT City, Count(*)
ORDER BY 2
WHERE Count(*) > 1
SELECT Region, Count(*)
GROUP BY City
FROM tblCustomer
ORDER BY Count(*)
HAVING Count(*) > 1
GROUP BY Region
ORDER BY 2 DESC
Part 2: Did you select any filter clause(s)? (WHERE, HAVING) Justify your choice of filter(s)