CUSTOMER INVOICE PK CUS CODE contains PK,FK1 INY NUMBER PK CUS LNAME CUS FNAME C
ID: 3720202 • Letter: C
Question
CUSTOMER INVOICE PK CUS CODE contains PK,FK1 INY NUMBER PK CUS LNAME CUS FNAME CUS INITIAL CUS_ AREACODE CUS PHONE CUS BALANCE FK1 CUS.CODE INV DATE PK2 P CODE LINE UNITS LINE PRICE I CUS LNAME I TOTAL UNITS |GRAND TOTAL I 444.00 I Dunne l Farriss I o'Brian I Orlando l Smith 16 I 12 I 70.44 I 34·97? 422.77 153.85 5 rows in set (0.00 sec) 31. Provide the MySQL query that summarizes the following values over all customer's invoices to produce that table below INCLUDINOG · Joins: CUSTOMER, INVOICE and LINE (see ER diagram above) » Includes attributes: CUS_LNAME, TOTAL UNITS, GRAND TOTAL » Derived attribute TOTAL UNITS: is derived from summing the LINE_UNITS over all customer's invoices Derived attribute GRAND TOTAL: is derived from summing LINE_UNITS multiplied by LINE_PRICE over all of a customer's invoices. Note that GRAND TOTAL has been rounded to two decimal places. » » Sorted by: CUSLNAMEExplanation / Answer
Below is the SQL query in MySQL that gives the given result.
As mentioned 3 tables are joined and aggregate function SUM() is used to find the sum of line units. Group by is used with aggregate function to group the result by customer last name. Order by clause is used to sort the result by customer last name.
Query-
SELECT c.CUS_LNAME, SUM(l.LINE_UNITS) AS 'TOTAL UNITS', ROUND(SUM(l.LINE_UNITS * l.LINE_PRICE),2) AS 'GRAND TOTALS'
FROM CUSTOMER c
INNER JOIN INVOICE inv
ON inv.CUS_CODE = c.CUS_CODE
INNER JOIN LINE l
ON l.INV_NUMBER = inv.INV_NUMBER
GROUP BY c.CUS_LNAME
ORDER BY c.CUS_LNAME;