THE RELATIONAL MODEL 2: SQL - Concepts of Database Management - page 111

EXAMPLE 19

Find the number of customers and the total of their balance.

111

SELECT COUNT(*), SUM(Balance)
FROM Customer
;

EXAMPLE 20

Find the total number of customers and the total of their balances. Change the column names for the number
of customers and the total of their balances to CustomerCount and BalanceTotal, respectively.

SELECT COUNT(*) AS CustomerCount, SUM(Balance) AS BalanceTotal
From Customer
;

EXAMPLE 21

List the order number for each order that contains an order line for a part located in warehouse 3.

SELECT OrderNum
FROM OrderLine
WHERE PartNum IN
(SELECT PartNum
FROM Part
WHERE Warehouse= ' 3 ' )
;

EXAMPLE 22

For each sales rep, list the rep number, the number of customers assigned to the rep, and the average bal-
ance of the rep's customers. Group the records by rep number and order the records by rep number.

SELECT RepNum, COUNT(*), AVG(Balance)
FROM Customer
GROUP BY RepNum
ORDER BY RepNum
;

EXAMPLE 23

For each sales rep with fewer than four customers, list the rep number, the number of customers assigned to
the rep, and the average balance of the rep

s customers. Rename the count of the number of customers and
the average of the balances to NumCustomers and AverageBalance, respectively. Order the groups by rep
number.

'

SELECT RepNum, COUNT(*) AS NumCustomers, AVG(Balance)
AS AverageBalance
FROM Customer
GROUP BY RepNum
HAVING COUNT(*) < 4
ORDER BY RepNum
;