We will use a simple database composed of the following tables: CUSTOMER, INVOICE, LINE, PRODUCT, and VENDOR. This database model is shown in Figure 6.1.

The associated tables CUSTOMER, INVOICE, LINE, PRODUCT, and VENDOR are shown below:

a) Write the SQL code to return the number of customers whose balance is greater than $250

b) Write the SQL code to return the total balance of the customer whose balance is greater than $250 and whose area phone code is 615.

c) Write the SQL code to return the number of customers whose balance is greater than the average balance.

d) Write the SQL code to generate a listing of all purchases made by the customers, producing the output as shown in Figure 6-2. (Hint: ORDER BY CUS_CODE in the command)

e) Write the SQL code to generate a listing of customer purchases, including the subtotals for each of the invoice line numbers, producing the output as shown in the Figure 6-3. (Hint: Modify the query format used to produce the listing of customer purchases in the part (d), delete the INV_DATE column, then add the derived (computed) attribute LINE_UNITS*LINE_PRICE to calculate the subtotal.)

f) Now modify the query used in part 6-20 to produce the summary shown in Figure 6-4. (Hint: Use GROUP BY command)

g) Modify the query in part (f) to include the number of individual product purchases made by each customer. (Hint: Each row in Figure 2 corresponds to one individual purchase). The output produced as shown in Figure 6-5.

h) Modify the query in part (g) to add a column that contains the average purchase amount per product made by each customer. Note that the Average Purchase Amount is equal to the Total Purchases divided by the Number of purchases. The output produced as shown in Figure 6-6.

i) Write a query to produce the total purchase per invoice, to generate the results shown in Figure 6-7.

j) Write a query to find the customer balance characteristics for all customers, including the total of the outstanding balances. The results of this query are shown in Figure 6-8.

INVOICE LINE CUSTOMER CUS CODE CUS LNAME CUS FNAME CUS INITIAL CUS AREACODE CUS PHONE CUS BALANCE PRODUCT P CODE P DESCRIPT P INDATE P ONHAND P MIN P PRICE CUS CODE INV DATE LINE NMBER P CODE LINE UNITS LINE PRICE VENDOR Y CODE V_CODE V CONTACT V AREACODE PHONE VSTATE V ORDER - Figure 1 - The database model