Due Date: April, 09th, 2013
IDE: SQL Developer
1.Write a script that creates a cursor consisting of vendor_name, invoice_number, and balance_due for each invoice with a balance due that’s greater than or equal to $5,000. The rows in this cursor should be sorted in descending sequence by balance due. Then, for each invoice, display the balance due, invoice number, and vendor name so it looks something like this:
$19,351.18 P-0608 Malloy Lithographing Inc
2. Enhance your solution to exercise 3 so it shows the invoice data in three groups based on the balance due amounts with these headings:
$20,000 or More
$10,000 to $20,000
$5,000 to $10,000
Each group should have a heading followed by the data for the invoices that fall into that group. Also, the groups should be separated by one blank line.
3. Enhance your solution to exercise 3 so it uses a substitution variable to set a bind variable that you use to determine what the minimum balance due should be for the invoices that the SELECT statement is going to retrieve. You should also use this bind variable to display a heading like this before the list of invoices:
Invoice amounts greater than or equal to $2,000
where 2,000 is the value of the bind variable.
4. Create a trigger named invoices_before_update_payment for the Invoices table that raises an application error whenever the payment total plus the credit total becomes larger than the invoice total in a row. Then, test this trigger with an appropriate UPDATE statement. (Note that you could code a check constraint to accomplish the same task.)
5. Create a trigger named invoices_after_update_payment for the Invoices table that displays the vendor name, invoice number, and payment total in the output window whenever the payment total is increased. Then, test this trigger with an appropriate UPDATE statement. (A trigger like this could be modified so it stores the data in an audit table.)
Here is a Link that contains the details of the columns of all the tables to be able to write queries in SQL Developer.