Question

Problem 44 Write a query to display the employee number, last name, first name, and sum of invoice totals for all employees who completed an invoice. Sort the output by employee last name and then by first name (Partial results shown in Figure P7.44).

Answer 1

Solution

Problem 44

Explanation

we need to display employee number,last name, first name and sum of invoice totals
for that we can use SELECT statement

employee number E.EMP_NUM AS EMP_NUM
last name E.EMP_LNAME AS EMP_LNAME
first name E.EMP_FNAME AS EMP_FNAME
sum of invoice totals SUM(INV_TOTAL)

we can round it to 2 decimal placces using ROUND function

for grouping
We are using GROUP BY EMP_NUM;
for sorting the name
We are using ORDER BY

SQL Code

SELECT E.EMP_NUM AS EMP_NUM,
E.EMP_LNAME AS EMP_LNAME,
E.EMP_FNAME AS EMP_FNAME,
ROUND(SUM(INV_TOTAL),2) AS TOTALINVOICES
FROM LGEMPLOYEE E, LGINVOICE I
WHERE E.EMP_NUM=I.EMPLOYEE_ID
GROUP BY EMP_NUM,EMP_LNAME,EMP_FNAME
ORDER BY EMP_LNAME,EMP_FNAME;

SCREENSHOT

---

Please check the sql query and let me know if it is working or not

if you have any doubt, please mention it, love to help

all the best