please write code for me... it must be work tho Chapter 11 1. Create a view that

Question

please write code for me...

it must be work tho

Chapter 11

1. Create a view that defines 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 tables: 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 that it returns. (You may have to click on the Refresh button in the Connections window after you click on the Views node to show the view you just created.)

2. Write a SELECT statement that returns all of 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 contains invoices with unpaid balance 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 descending sequence. Then, run the script to create the view, and use SQL Developer to review the data that it returns.

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

5. Create an updatable view named vendor_address that returns the vendor_id, both address columns, and the city, state, and zip code columns for each vendor. Then, use SQL Developer to review the data in this view.

6. Write an UPDATE statement that changes the address for the row with vendor ID 4 so the suite number (

Explanation / Answer

1 ) Create a view that defines 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 tables: 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 that it returns.

SOLN:

CREATE VIEW open_items AS SELECT V.VENDOR_NAME,I.INVOICE_NUMBER,I.INVOICE_TOTAL.I.BALANCE_DUE FROM VENDOR V JOIN ON INVOICE I WHERE V.VENDOR_NAME=I.VENDOR_NAME AND BALANCE_DUE > 0 ORDER BY VENDOR_NAME DESC

2) Write a SELECT statement that returns all of 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.

SOLN:

SELECT DISTINCT * FROM OPEN_ITEMS WHERE BALANCE_DUE >=1000

3) Create a view named open_items_summary that returns one summary row for each vendor that contains invoices with unpaid balance 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 descending sequence. Then, run the script to create the view, and use SQL Developer to review the data that it returns.

SOLN:

CREATE VIEW OPEN_ITEMS_SUMMARY AS SELECT VENDOR_NAME,OPEN_ITEM_COUNT,OPEN_ITEM_TOTAL FROM VENDOR,INVOICE WHERE BALANCE_DUE>0 ORDER BY OPEN_ITEMS_COUNT DESC.

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

SOLN:

SELECT * FROM OPEN_ITEMS_SUMMARY WHERE ROWNUM<6

5) Create an updatable view named vendor_address that returns the vendor_id, both address columns, and the city, state, and zip code columns for each vendor. Then, use SQL Developer to review the data in this view.

SOLN:

CREATE VIEW VENDOR_ADRESS AS SELECT VENDOR_ID,ADDRESS1,ADDRESS2,CITY,STATE,ZIP

WITH CHECK OPTION

6)Write an UPDATE statement that changes the address for the row with vendor ID 4 so the suite number (

) is stored in vendor_address2 instead of vendor_address1. Then, use SQL Developer to verify the change (you may need to click the Refresh button at the top of the Data tab to see the change). If this works correctly, go back to the tab for the UPDATE statement and click the Commit button to commit the change.

SOLN:

UPDATE VENDOR_ADDRESS SET VENDOR_ADDRESS2='STE 260' WHERE VENDOR_ID=2

Related Questions

Navigate

• Browse (All)
• Browse P
• Subjects

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