Solved by: AllAcademicHelp.com
| April 30, 2019
Write my research paperUse the given MyGuitarShop database to answer these questions. Stored Procedure with Data Validation Write a script that creates and calls a stored procedure named spInsertProduct that inserts a row into the Products table. This stored procedure should accept five parameters. One parameter for each of these columns: CategoryID, ProductCode, ProductName, ListPrice, and DiscountPercent. If the value for the ListPrice column is a negative number, the stored procedure should raise an error that indicates that this column doesn’t accept negative numbers. Similarly, the procedure should raise an error if the value for the DiscountPercent column is a negative number.When inserting data into the Products table, set the Description column to an empty string and set the DateAdded column to the current date. Code at least two EXEC statements that test this procedure (one successful, one failure)Write a script that creates and calls a stored procedure named spUpdateProductDiscount that updates the DiscountPercent column in the Products table. This procedure should have one parameter for the product ID and another for the discount percent.If the value for the DiscountPercent column is a negative number, the stored procedure should raise an error that indicates that the value for this column must be a positive number.Code at least two EXEC statements that test this procedure.TriggerCreate a trigger named Products_INSERT that sets DateAdded column of the Products table to current date if the value for that column is null. Test this trigger with the followingINSERT INTO Products (CategoryID, ProductCode, ProductName, Description, ListPrice, DiscountPercent) VALUES (1, ‘G5122’, ‘Gretsch G5122 Double Cutaway Hollowbody’, ”, 999.99, 32); Check and make sure the date was inserted with a SELECT statement. Create a trigger named Products_UPDATE that checks the new value for the DiscountPercent column of the Products table. This trigger should raise an appropriate error if the discount percent is greater than 100 or less than 0. If the new discount percent is between 0 and 1, this trigger should modify the new discount percent by multiplying it by 100. That way, a discount percent of .2 becomes 20. Test this trigger with the following UPDATE ProductsSET DiscountPercent = .25WHERE ProductID = 1; Check to make sure the DiscountPercent is 25 with a SELECT statement. Cursors Write a script that creates a cursor for a SELECT query that consists of the ProductName and ListPrice columns for each product with a list price that’s greater than $700. The rows in this result set should be sorted in descending sequence by list price. Then, the script should print the product name and list price for each product so it looks something like this: Gibson SG, $2517.00 Gibson Les Paul, $1199.00 (10 points) Write a script to declare and use a cursor for the following SELECT statement. Use a WHILE loop to fetch each row in the result set. Fetch each row into a set of local variables. Use the PRINT statement to return each row in the format “Name, $0.00” to the Messages tab. SELECT LastName, AVG(ShipAmount) AS ShipAmountAvg FROM Customers JOIN Orders ON Customers.CustomerID = Orders.CustomerID GROUP BY LastName; SubmissionPlace all the sql queries in a single sql file (.sql) and upload to Blackboard under Assignments-> HW 3. Purchase the answer to view it©Copyright 2000-2018. All Rights Reserved. TermPaperChampions.com: The most reliable provider of custom academic papers. Our writers are here to help you complete papers from all disciplines and academic levels . You can always trust us to deliver.
Get a 15 % discount on an order above $ 50Use the following coupon code :tpc15
READY TO PLACE AN ORDER