1. Write a query to count the number of customers with a balance of more than $5

Question

1. Write a query to count the number of customers with a balance of more than $500.

2. Create a query to produce the total purchase per invoice sorted by invoice number. The invoice total is the sum of the product purchases in the LINE that corresponds to the INVOICE.

3. List the balances of customers who have made purchases during the current invoice cycle—that is, for the customers who appear in the INVOICE table. The results of this query are sorted by customer code.

FIGURE P7.9 THE CH07 SALECO DATABASE Relational diagram CUSTOMER LINE ENDOR PK INVOICE V Name V AreaCode V State an-FName K3 Cun Code Cus Phone Cus Balance Line Price Price Discount Order K V Code Table name: VENDOR Table name: CUSTOMER On 215 8095 1344 Oome Bro Orteg 2946 K 22-167 2-1810 501 6002 Table name: INVOICE Table name: LINE Table name: PRODUCT 3-D-17318149 344 100 00 344 000 2 $477 -2 003 003 1004 1004 09 s 02 SH-1827 0o 344 100 008 0023 1109 1000 19

Explanation / Answer

Solution:

1)

SELECT COUNT(CUS_CODE) FROM CUSTOMER WHERE CUS_BALANCE > 500

2)

SELECT SUM(Line_Unit*Line_Price) AS Total FROM INVOICE INNER JOIN LINE ON INVOICE.Inv_Number = LINE.Inv_Number GROUP BY Inv_number

3)

SELECT CUS_BALANCE FROM CUSTOMER INNER JOIN INVOICE ON CUSTOMER.Cus_Code= INVOICE.Cus_Code ORDER BY Cus_Code

I hope this helps if you find any problem. Please comment below. Don't forget to give a thumbs up if you liked it. :)

Related Questions

Navigate

• Browse (All)
• Browse 1
• Subjects

---

---

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