1. Create a view that defines a view named open_items that shows the invoices th
ID: 3548095 • Letter: 1
Question
1. Create a view that defines a view named open_items that shows the invoices that havent been paid. This view should return four colums from the vendors and Invoices Table: vendor_name, invoice_number, invoice_total, and balance_due (invoice_total - payment_total - credit_total). However a row should only be returned when the balance due is greater than zero, and the rows should be in sequence by vendor_name. Then, run the script to create the view, and use SQL developer to review the data it returns.
2. Create a view named open_items_summary that returns one summary row for each vendor that contains invoices with unpaid balances dues. 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), and the rows should be sorted by the open item totals in decending sequence. Then, run the script to see what it returns in SQL developer.
3. Write a SELECT statement that returns just the first 5 rows in the open_items_summary view you created in excercise 2.
Explanation / Answer
CREATE VIEW open_items AS
SELECT v.vendor_name, i.invoice_number, i.invoice_total, (i.invoice_total - i.payment_total - i.credit_total) balance_due
FROM vendors v, Invoices i
WHERE v.vendor_id = i.vendor_id
AND i.invoice_total - i.payment_total - i.credit_total > 0
ORDER BY v.vendor_name
;
CREATE VIEW open_items_summary AS
SELECT *
FROM (
SELECT i.vendor_name, SUM(i.invoice_total) open_item_count, SUM(balance_due) open_item_total
FROM open_items i
GROUP BY i.vendor_name) a
ORDER BY a.open_item_total;
SELECT * FROM open_items_summary WHERE rownum < 6;
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.