Create a stored procedure named prc_inv_amounts to update the INV_SUBTOTAL, INV_TAX, and INV_TOTAL. The procedure takes the invoice number as a parameter. The INV_SUBTOTAL is the sum of the LINE_TOTAL amounts for the invoice, the INV_TAX is the product of the INV_SUBTOTAL and the tax rate (8 percent), and the INV_TOTAL is the sum of the INV_SUBTOTAL and the INV_TAX.

Create a stored procedure named prc_inv_amounts to update the INV_SUBTOTAL, INV_TAX, and INV_TOTAL. The procedure takes the invoice number as a parameter. The INV_SUBTOTAL is the sum of the LINE_TOTAL amounts for the invoice, the INV_TAX is the product of the INV_SUBTOTAL and the tax rate (8 percent), and the INV_TOTAL is the sum of the INV_SUBTOTAL and the INV_TAX.

A Guide to SQL

9th Edition

ISBN:9781111527273

Author:Philip J. Pratt

Publisher:Philip J. Pratt

Chapter8: Sql Functions And Procedures

Section: Chapter Questions

Problem 6CAT

See similar textbooks

Similar questions

Create a stored procedure that will list all customers (name and number) represented by rep 15.
"Create a stored procedure named spPriceRange that accepts two optional parameters. Theprocedure should return a result set consisting of Product Id, Product Name, ProductDescription, and List Price from the product and product description tables for each productwithin the price range, sorted with largest price first. The parameter @PriceMin and @PriceMaxare parameters used to specify the requested range of prices. If the minimum price is notprovided use the minimum non-zero price in the product table. If the maximum price is notprovided use the maximum price in the product table."
code must be in SQL SSMS format /*29. Write a trigger to update the CUST_BALANCE ON CUSTOMER TABLE when an invoice is deleted (INV_NUM) FROM INVOICE TABLE. Namethe trigger trg_updatecustbalance2.*/ 30. Write a procedure to delete an invoice (INV_NUM) FROM INVOICE TABLE giving the invoice number as a parameter.Name the procedure prc_inv_delete. Test the procedure by deleting invoices 8005and 8006. TABLE STRUCTURE & CURRENT INV TABLE ARE ATTACHED
Q1. Create a stored procedure that will list all customers (name and number) represented by rep 15. (SQL)
Write a SELECT statement returns these columns from the orders table: The invoice_id column as Invoice_ID The invoice_date column as Invoice_Date The invoice_due_date column as Invoice_Due_Date The payment_date column in the format 'Mon/DD/YY' as Formatted_Payment_Date The invoice_due_date column in the format 'Weekday, Month Day of month with suffix, YYYY' (ex: 'Tuesday, April 23rd, 2019') as Long_Invoice_Due_Date The invoice_due_date plus 60 days as Expected_Payment_Date (Column must remain a date type) The number of days between the invoice_due_date and the payment_date as Time_To_Pay
Create a procedure named prc_cus_balance_update that will take the invoice number as a parameter and update the customer balance.
Task 8: Create the UPDATE_INVOICE procedure to change the date of the invoice whose number is stored in I_INVOICE_NUM to the date currently found in I_INVOICE_DATE. Expected Results INVOICE_NUM INVOICE_DATE CUST_ID 00001 2021-11-21 294
Write a stored procedure named prPriceDoubles based on the Products table: The columns in the Products table are named ProductID, ProductName, Price, and ProductDescription. The ProductID column is the primary key of the table. The store procedure checks if the average price of a product is less than N$ 200, then doubles the product prices and then selects the maximum product price. If the maximum product price is less than or equal to N$ 300, it doubles the product prices again and continues doubling the product prices until the maximum product price is greater than N$ 300, and then prints a message “Too much for the student pocket!!!”.