We will use a simple database composed of the following tables: CUSTOMER, INVOIC

ID: 3735493 • Letter: W

Question

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

Explanation / Answer

Please see the below SQL queries. As there are so many questions in a single post, so I am not adding explanation for each and ever question. If you have any doubt in any of SQL query, you can ask in comments.

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

select count(*) from CUSTOMER where CUS_BALANCE>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.

select sum(CUS_BALANCE) from CUSTOMER where CUS_BALANCE>250 and CUS_AREACODE=615;

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

select count(*) from CUSTOMER where CUS_BALANCE>(select avg(CUS_BALANCE) from CUSTOMER);

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)

select
   C.CUS_CODE,
   I.INV_NUMBER,
   I.INV_DATE,
   P.P_DESCRIPT,
   L.LINE_UNITS,
   L.LINE_PRICE
FROM
   CUSTOMER C, INVOICE I, LINE L, PRODUCT P
WHERE
C.CUS_CODE=I.CUS_CODE AND
I.INV_NUMBER=L.INV_NUMBER AND
L.P_CODE=P.P_CODE
ORDER BY C.CUS_CODE

select
   C.CUS_CODE,
   I.INV_NUMBER,
   P.P_DESCRIPT,
   L.LINE_UNITS AS "Unit Bought",
   L.LINE_PRICE as "Unit Price",
   L.LINE_UNITS*L.LINE_PRICE as "Subtotal"
FROM
   CUSTOMER C, INVOICE I, LINE L, PRODUCT P
WHERE
C.CUS_CODE=I.CUS_CODE AND
I.INV_NUMBER=L.INV_NUMBER AND
L.P_CODE=P.P_CODE
ORDER BY C.CUS_CODE

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

select
   C.CUS_CODE,
   SUM(L.LINE_UNITS*L.LINE_PRICE) as "Total Purchases"
FROM
   CUSTOMER C, INVOICE I, LINE L
WHERE
   C.CUS_CODE=I.CUS_CODE AND
   I.INV_NUMBER=L.INV_NUMBER
GROUP BY C.CUS_CODE
ORDER BY C.CUS_CODE

select
   C.CUS_CODE,
   SUM(L.LINE_UNITS*L.LINE_PRICE) as "Total Purchases",
   COUNT(*) AS "Number Of Purchases"
FROM
   CUSTOMER C, INVOICE I, LINE L
WHERE
   C.CUS_CODE=I.CUS_CODE AND
   I.INV_NUMBER=L.INV_NUMBER
GROUP BY C.CUS_CODE
ORDER BY C.CUS_CODE

select
   C.CUS_CODE,
   SUM(L.LINE_UNITS*L.LINE_PRICE) as "Total Purchases",
   COUNT(*) AS "Number Of Purchases",
   AVG(SUM(L.LINE_UNITS*L.LINE_PRICE)) as "Average Purchase Amount"
FROM
   CUSTOMER C, INVOICE I, LINE L
WHERE
   C.CUS_CODE=I.CUS_CODE AND
   I.INV_NUMBER=L.INV_NUMBER
GROUP BY C.CUS_CODE
ORDER BY C.CUS_CODE

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

select
   I.INV_NUMBER,
   SUM(L.LINE_UNITS*L.LINE_PRICE) AS "Invoice Total"
from
   INVOICE I, LINE L
where
   I.INV_NUMBER=L.INV_NUMBER
GROUP BY I.INV_NUMBER
ORDER BY I.INV_NUMBER

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.

SELECT
   SUM(CUS_BALANCE) AS "Total Balances",
   MIN(CUS_BALANCE) AS "Minimum Balance",
   MAX(CUS_BALANCE) AS "Maximum Balance",
   AVG(CUS_BALANCE) AS "Average Balance"
from
   CUSTOMER

Navigate

We will use a simple database composed of the following tables: CUSTOMER, INVOIC

We will use a tube model for the circulatory system of an average man. This is a

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.

We will use a simple database composed of the following tables: CUSTOMER, INVOIC

Question

Explanation / Answer

Related Questions

Navigate