Question

Write a SELECT statement that returns these four columns:

vendor_name-The vendor_name column from the Vendors table
invoice_number The invoice_number column from the Invoices table
invoice_date The invoice date column from the Invoices table
balance due The invoice total column minus the payment total and credit total columns from the Invoices table

Use these aliases for the tables: v for Vendors and i for Invoices.

Return one row for each invoice with a non-zero balance.
This should return 11 rows. Sort the result set by vendor_name in ascending order

Answer 1

SELECT vendor_name, invoice_number, invoice_date,

(invoice_total - payment_total - credit_total) AS balance_due

FROM vendors AS v JOIN invoices AS i

ON vendors.vendor_id = Invoices.vendor_id

WHERE invoice_total - payment_total - credit_total > 0

ORDER BY vendor_name;

Step-by-step explanation:

The query statement returns joint records of vendor name, invoice number, invoice date, and balance due from the vendor and invoice tables represented with aliases v and i respectively.

It returns an output with the WHERE clause condition to only return balance due greater than zero and the output is returned in ascending order of the vendor's names.