8. Write and run a SELECT statement that returns four columns from the Invoices

Question

8. Write and run a SELECT statement that returns four columns from the Invoices table named Number, Total, Credits, and Balance Due. These columns should include this data: Number The invoice_number column Total The invoice_total column Credits Sum of the payment_total and credit_total columns Balance Due Invoice_total minus the sum of payment_total and credit_total Next, filter for invoices with a balance due that’s greater than or equal to $500. Then, sort the result set by balance due in descending sequence. Last, use the ROWNUM pseudo column so the result set contains only the rows with the 10 largest balance dues.

Explanation / Answer

Order of SQL statement should be

SELECT ...

FROM ...

WHERE ...

ORDER BY ...

SELECT statement will be :

SELECT invoice_number AS 'Number', invoice_total AS 'Total' , (payment_total+ credit_total) AS 'Credits' ,

invoice_total -(payment_total+credit_total) AS 'Balance Due'

FROM Invoices

WHERE (invoice_total -(payment_total+credit_total) ) >= 500 AND ROWNUM<=10

ORDER BY "Balance Due" DESC;

ROWNUM select first 10 rows

ORDER BY arranges result returned in descending order of Balance Due

AS is used to rename Column

Related Questions

Navigate

• Browse (All)
• Browse 8
• Subjects

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