INSY 3432 Homework 7 Fall 2016

This is a homework assignment for the INSY 3432 Data Base Systems course, focusing on SQL and stored procedures. The assignment includes two main questions. The first question involves modifying a stored procedure to categorize movies by duration and update a database table. The second question requires using a cursor to process sales transactions and update inventory levels in the AllApplianceCo database. Students are required to submit SQL scripts, results, and before/after images of the inventory table.

INSY 3432 Data Base Systems
Home Work 7 Fall 2016
Q1: In the class, we did an exercise where each movie in the database was given a label, Short, Medium,
or Long. This output was simply printed to the screen. Rewrite this procedure to the following.
1. In the movies database, create a new Column called Duration (which table?)
2. Modify the stored procedure. Do not print the message to the screen. Instead, write the label
Short, Medium, or Long into that column of the table. Execute this procedure with appropriate
parameters.
3. Next write an SQL query to display all the short film names, their run time in minutes and their
duration label. This is just to check that your stored procedure worked.
As always, for each part of the question, submit the query by snipping and pasting into a word
document. Snip and paste the results as well. If the results of 3 exceed 10 lines, copy and paste only
the first 10 lines of output, but mention how many lines of output was returned.
Q2: For this question, you need to use the AllApplianceCo .mdf database available on BB. You need
some preparatory work before you can do the question.
1. First of all you need to execute the stored procedure sp_discount that we wrote in class. If you
already executed this in class, there is no need to repeat this process. After it executes, make
sure that the discount table has discount specified for quantities from 0 thru 1000. Unless the
discount table is updated thus, you will not be able to change any inventory levels in the
Inventory table.
2. The idea of this question is that some sales transactions have taken place, and you want to
process all these transactions to update your inventory levels for each product in each store.
You are provided two SQL scripts. 1) a SQL script to create a table (Sales) in the AllApplianceCo
database. Study the script to understand the table. Create the table by executing the script.
While the table is created, it is still empty. You need to populate the table with actual sales data.
3. You are provided a second script to insert data into the sales table. Execute this script as well
and you will have the sales table, with salesID as the primary Key.
Once steps 1 thru 3 are completed, you are ready to begin the work on the question.
1

Write a stored procedure (sp_Sales) that takes the transactions in the sales table one by one and
processes them to update the inventory levels in the INVENTORY table. You need to USE a CURSOR
to process the sales transactions one by one. Carefully study the problem to decide what data
elements you need in the CURSOR. In this stored procedure the input is coming from a table in the
database and output is stored to a table in the database.
Submit
1) Your SQL script for the stored procedure copied and pasted into a word file,
2) a copy of the INVETORY table before you process any transactions, and after you process all
transactions, also copied and pasted as images into the same word document. Submit the entire
INVENTORY table before and after the stored proc execution.
Script for creating the Sales Table is provided below. This is not at all difficult. I am giving the script
just to make sure that everyone uses the same script. I have already provided you a template for
writing a stored procedure with a CURSOR. The same template is attached to this HW.
USE AllApplianceCo
CREATE TABLE Sales(
FirstName VARCHAR(55) NOT NULL,
LastName VARCHAR(55) NOT NULL,
StreetAddress VARCHAR(55) NULL,
City VARCHAR(55) NOT NULL,
State VARCHAR(55) NOT NULL,
Zipcode CHAR(5) NULL,
StoreID INT NOT NULL,
ProductID INT NOT NULL,
Quantity INT NOT NULL,
SalesID INT NOT NULL
CONSTRAINT [Salespk] PRIMARY KEY CLUSTERED
)
2

CREATE PROCEDURE sp_Proc_Name (param1 datatype, param2 data type……)
AS
BEGIN
-- Declare local variables and Cursors if needed
Declare @variable1 datatype, @variable2 datatype, @variable3 datatype, ………….
Declare NameOfYourCursor CURSOR
FOR (select ----------------------------------------------- from tblyour)
OPEN NameOfYourCursor
-- Read values from the first record into local variables
FETCH NEXT FROM NameOfYourCursor INTO @variable1, @variable2, ………….
--is it end of file? @@FETCH_STATUS = 0 means, a record has been obtained
WHILE @@FETCH_STATUS = 0
BEGIN
-- Here you insert your Pseudo code and then add SQL to accomplish the tasks
-- Read values from the next record into local variables
FETCH NEXT FROM NameOfYourCursor INTO @variable1, @variable2, ……….
END
CLOSE NameOfYourCursor
DEALLOCATE NameOfYourCursor
END
Note that there is one FETCH NEXT statement before the While loop,
which gets the very first record from your CURSOR. After processing a
record, we get the next record by using the FETCH NEXT again, as the
last statement of the While loop. If you forget to write the FETCH
NEXT statement inside the while loop, you end up creating an
INFINITE loop, since you will end up processing the very first record
again and again. So be WARNED!
3