Chapter 15 How to code stored procedures, functions, and triggers Exercises 1. Write a script that creates and calls a stored procedure named spInsertCategory. First, code a statement that creates a procedure that adds a new row to the Categories table. To do that, this procedure should have one parameter for the category name. Code at least two EXEC statements that test this procedure. (Note that this table doesn’t allow duplicate category names.) 2. Write a script that creates and calls a function named fnDiscountPrice that calculates the discount price of an item in the OrderItems table (discount amount subtracted from item price). To do that, this function should accept one parameter for the item ID, and it should return the value of the discount price for that item. 3. Write a script that creates and calls a function named fnItemTotal that calculates the total amount of an item in the OrderItems table (discount price multiplied by quantity). To do that, this function should accept one parameter for the item ID, it should use the DiscountPrice function that you created in exercise 2, and it should return the value of the total for that item. 4. 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. This stored procedure should set the Description column to an empty string, and it should set the DateAdded column to the current date. 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. Code at least two EXEC statements that test this procedure. 5. 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. 21 My Guitar Shop exercises.docx Murach’s SQL Server 2012 6. 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 an appropriate UPDATE statement. 7. Create a trigger named Products_INSERT that inserts the current date for the DateAdded column of the Products table if the value for that column is null. Test this trigger with an appropriate INSERT statement. 8. Create a table named ProductsAudit. This table should have all columns of the Products table, except the Description column. Also, it should have an AuditID column for its primary key, and the DateAdded column should be changed to DateUpdated. Create a trigger named Products_UPDATE. This trigger should insert the old data about the product into the ProductsAudit table after the row is updated. Then, test this trigger with an appropriate UPDATE statement.

A password consist of three digits 0-9 followed by three letters from an alphabet having 26 letters. If repetition is not allowed for digits or letters

Milton conpanys trading securities 2012 and 2013

gg

gg

Vintage Cellars manufactures a 1,000-bottle wine storage system that maintains optimum temperature (55-57 °F) and humidity (50-80%) for aging wines. The system has a backup battery for power failures and can store red and white wines at different temperatures. The following table depicts how average cost varies with the number of units manufactured and sold (per month):

Required: Prepare a table that computes the total cost and marginal cost for each quantity between 1 and 10 units. What is the relation between average cost and marginal cost? What is the opportunity cost of producing one more unit if the company is currently producing and selling four units? Vintage Cellars sells the units for $9,000 each. This price does not vary with the number of units sold. How many units should Vintage manufacture and sell each month?

How do you Find all the points having an x-coordinate of 3 whose distance from the point (-2, -1) is 13.?

Which president earned a spot on the dime by leading the country during World War Two?

(-8-6i)-(5+3i)

E1-1 Reporting Amounts on the Four Basic Financial Statements [LO2] Using the following table and the equations underlying each of the four basic financial statements, show (a) that the balance sheet is in balance, (b) that net income is properly calculated, (c) what caused changes in the retained earnings account, and (d) what caused changes in the cash account. (Negative amounts should be indicated by a minus sign. Omit the "$" sign in your response.) Assets $ 19,260 Liabilities 14,540 Stockholders' Equity 4,720 Revenue 10,530 Expenses 9,280 Net income 1,250 Dividends 680 Beginning Retained Earnings 3,620 Ending Retained Earnings 4,190 Cash Flows from Operating Activities 1,670 Cash Flows from Investing Activities (1,460 ) Cash Flows from Financing Activities (870 ) Beginning Cash 1,170 Ending Cash 510 Required: a. Assets = + = $ + $ Assets reported = $ b. Net income = – = $ – $ Net income reported = $

c. Ending Retained Earnings = + – = $ + $ – $ = $

d. Ending Cash = + + + = $ + $ + $ + $ = $

The Derivcos Entertainment Company is a video game production company that specializes in educational video games for kids.

The company’s R&D department is always looking for great ideas for new games.

On average, the R&D department generates about 25 new ideas a week.

To go from idea to approved product, the idea must pass through the following stages:

Paper screening (a 1--‐page document describing the idea and giving a rough sketch of

The design), prototype development, testing, and a focus group.

At the end of each stage, successful ideas enter the next stage.

All other ideas are dropped. The following chart depicts this process, and the probability of succeeding at each stage.

 

 

The paper screening for each idea takes 2 hours of a staff member’s time. After that, there is a stage of designing and producing a prototype. A designer spends 4 hours designing the game in a computer--‐aided--‐design (CAD) package. The actual creation of the mock--‐up is outsourced to one of many suppliers with essentially limitless capacity. It takes 4 days to get the prototype programmed, and multiple prototypes can be created simultaneously. A staff member of the testing team needs 2 days to test an idea. Running the focus group takes 2 hours of a staff member’s time per idea, and only one game is tested in each focus group. Finally, the management team meets for 3 hours per idea to decide if the game should go into

production.

 

Available working hours for each staff member are 8 hours per day, 5 days a week.

The current staffing plan is as follows:

A.     

Paper screening: 3 staff members.

B.Design and Production: 4 staff members.

C. Testing: 6 staff members.

D. Focus Group: 1 staff member.

E. Final Decision: 1 management team.

 

A1.

How many new ideas would Gamer Co. approve for production per week if it had unlimited capacity (staff) in its R&D process?

A2.

Which stage is the bottleneck according to the current staffing plan?

A3.

With the current staffing plan, how many new ideas will be put into production per week?