Homework #2 Due Date: Wednesdav. Feb. 20 in Canvas 1. Log into your Oracle account and run the script Procedures_Tables.sg/to refresh tables for the coffee tore database. 2. (a) Create a PL SQL procedure that calculates sales tax on an order. Sales tax varies from state to state as listed in the bb_tax table. Name this procedure as your own last name. This procedure takes two input (IN) parameters: name of the state and order subtotal. The procedure has one output (OUT) parameter for the amount of sales tax calculated on the order. The procedure calculates amount of sales tax for the given subtotal using the given state 's tax rate (state rates are listed in BB_TAX table) and displays tax amount (b) Compile the procedure and make sure it compiles successfully. Make a screenshot of the complete procedure, its compilation message, and your account name showing in the screenshot. Paste the screenshot in a Word document. (An example of procedure created message is shown below-your screenshot should show the procedure, compilation message, and your account name.) Procedure created. 3. Test the procedure by calling it in a PL SQL block, such as the following (your procedure name would be different). Make a screenshot of the PL SQL block that you used for testing and its output. Your account name should also be visible in the screenshot Paste the screenshot in the same Word document as in step 2 abore. Worksheet Query Buider 1 DECLARE tax anount NUMBER (S,2) 3BEGIN 4Shirani_tax_calc'VA',100, tax_amount) 5DBMS OUTPUT. PUT LINE (tax amount) END script Output Homework #2 Due Date: Wednesdav. Feb. 20 in Canvas 1. Log into your Oracle account and run the script Procedures_Tables.sg/to refresh tables for the coffee tore database. 2. (a) Create a PL SQL procedure that calculates sales tax on an order. Sales tax varies from state to state as listed in the bb_tax table. Name this procedure as your own last name. This procedure takes two input (IN) parameters: name of the state and order subtotal. The procedure has one output (OUT) parameter for the amount of sales tax calculated on the order. The procedure calculates amount of sales tax for the given subtotal using the given state 's tax rate (state rates are listed in BB_TAX table) and displays tax amount (b) Compile the procedure and make sure it compiles successfully. Make a screenshot of the complete procedure, its compilation message, and your account name showing in the screenshot. Paste the screenshot in a Word document. (An example of procedure created message is shown below-your screenshot should show the procedure, compilation message, and your account name.) Procedure created. 3. Test the procedure by calling it in a PL SQL block, such as the following (your procedure name would be different). Make a screenshot of the PL SQL block that you used for testing and its output. Your account name should also be visible in the screenshot Paste the screenshot in the same Word document as in step 2 abore. Worksheet Query Buider 1 DECLARE tax anount NUMBER (S,2) 3BEGIN 4Shirani_tax_calc'VA',100, tax_amount) 5DBMS OUTPUT. PUT LINE (tax amount) END script Output
