Question

Based on the tables below, which of the following SQL commands would return the average customer balance grouped by SalesRepNo?

GENERAL SALES DATABASE:

SALESREP

SalesRepNo - RepName - HireDate
654 - Jones - 01/02/2005
734 - Smith - 02/03/2007
345 - Chen - 01/25/2018
434 - Johnson - 11/23/2004

CUSTOMER

CustNo - CustName - Balance - SalesRepNo
9870 - Winston - 500 - 345
8590 - Gonzales - 350 - 434
7840 - Harris - 800 - 654
4870 - Miles - 100 - 345

a) SELECT AVG (Balance)
FROM CUSTOMER
ORDER BY SalesRepNo;

b) SELECT AVG (Balance)
FROM CUSTOMER
WHERE SalesRepNo;

c) SELECT AVG (Balance)
FROM CUSTOMER
GROUP BY SalesRepNo;

d) SELECT AVG (Balance)
FROM CUSTOMER, SALESREP
WHERE SALESREP.SalesRepNo = CUSTOMER.SalesRepNo;

Answer 1

The correct SQL command to return the average customer balance grouped by SalesRepNo is an option (c): SQL SELECT AVG(Balance) FROM CUSTOMER GROUP BY SalesRepNo.

Step-by-step explanation:

In SQL, the SELECT AVG(column) statement is used to calculate the average value of a specific column. In this case, we want to find the average balance for each Sales Representative (SalesRepNo) from the CUSTOMER table. The GROUP BY clause is essential here as it groups the results by SalesRepNo, providing the average balance for each unique SalesRepNo.

Option (a) is incorrect because using ORDER BY without GROUP BY does not provide the average balance per Sales Representative.

Option (b) is incorrect because using WHERE without GROUP BY provides a single average balance for the entire table, not grouped by Sales Representative.

Option (d) is incorrect because the use of both tables (CUSTOMER and SALESREP) in the FROM clause without proper JOIN conditions is unnecessary and can lead to incorrect results. The correct way is to use the GROUP BY clause as shown in option (c).