In SQL, 1. Create a view named OPEN_ITEMS, that shows the invoices that haven\'t

Question

In SQL,

1. Create a view named OPEN_ITEMS, that shows the invoices that haven't been paid.
This view should return four columns from the vendors and invoices table:
vendor_name, invoice_number, invoice_total, and balance_due (invoice_total - payment_total - credit_total).

2. Write a SELECT statement that returns all the columns in the OPEN_ITEMS view that you created in exercise 1, with one row for each invoice that has a balance due of $1000 or more.

3. Create a view named OPEN_ITEMS_SUMMARY that returns ONE summary row for each vendor that has invoices that haven't been paid.
Each row should include vendor_name, open_item_count (the number of invoices with a balance due), and open_item_total (the total of the balance due amounts).

4. Write a SELECT statement that returns just the 5 rows from the open_items_summary view that you created in exercise 3.

Explanation / Answer

1) CREATE OR REPLACE VIEW open_items AS SELECT vendor_name, invoice_number, invoice_total,

invoice_total - payment_total - credit_total AS balance_due FROM vendors JOIN invoices

ON vendors.vendor_id = invoices.vendor_id WHERE invoice_total - payment_total - credit_total > 0

ORDER BY vendor_name;

3)

create or replace view open_items_summery as select vendor_name,count(*) as open_item_count, sum(invoice_total-credit_total-payment_total) as open_item_total from vendors join invoices on vendors.vendor_id=invoices.vendor_id where invoice_total-credit_total-payment_total>0 group by vendor_name

Related Questions

Navigate

• Browse (All)
• Browse I
• Subjects

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