SQL

Lab 8, Procedural SQL

In Lab 8, we will practice implementing indexes and writing Procedural SQL. Please be sure to see the SQL PowerPoints for reference.

The deliverable for the lab is your SQL file.

Part 1, Lab Setup:

1. Connect to the SQL lab environment and launch SQL Server Management Studio (SSMS).

2. Retrieve the Lab_8_Setup.sql file found in the Week 10 module.

3. Change the first 3 lines to have your name in the database name.

a. Example: Lab8JacobCase

4. Run the script to create the database for the lab. It will take a few seconds to run.

5. Refresh the Databases menu on the left. Your Lab8StudentName database should be created.

6. Notice the Employees table has a new column called SSN, and the Customers table has a new column called PIN.

7. Create a new .sql file by clicking the icon on the menu at the top. Type & run the command to USE your Lab 8 database, and then save the file as Lab_8_YourName.sql. This is the file you will submit for credit.

Part 2, Indexes & SQL Programming:

Use the below ERD for reference while writing your procedural SQL scripts.

Notice the SSN column in the Employees table and the PIN column in the Customers table.

1- Create necessary indexes on tables with Surrogate Primary Keys. (4 points)

Whenever Surrogate Primary Keys are used, it’s important to evaluate if it’s necessary to enforce uniqueness on the table by creating secondary unique index. Let’s evaluate tables with Surrogate Primary Keys and decide if they should have a secondary unique index:

Here is the syntax to create the unique composite index on the Customers table.

Here is the syntax to create the unique single-column unique index on the Employees table.

Notice they both use the UNIQUE keyword, give the index a meaningful name, and specify the index columns in parentheses.

Refer to the screenshots above and the PowerPoint from week 13 to create the indexes on the Customers & Employees tables, and then use the same format to create the indexes on the Statuses, Vendors, and Products tables. Be sure to use the UNIQUE keyword and give them meaningful names in case they show up in error messages.

The deliverable for Question 1 is the 5 CREATE INDEX commands.

2- Create a View called SalesPerProductPerDay (4 points).

The SalesPerProductPerDay view should return:

· ProductID

· ProductName

· OrderDate

· UnitsSold (count ProductID)

· SalesVolume (sum Price * Quantity from OrderLines)

Hints:

a. Start by SELECTing ProductID and ProductName FROM Products.

b. JOIN into OrderLines.

c. JOIN into Orders.

d. Add a column called UnitsSold that COUNTs ProductID.

e. Add a column called SalesVolume that SUMs Price * Quantity.

f. Add the appropriate GROUP BY clause.

g. Add: CREATE VIEW SalesPerProductPerDay in front of your SELECT query.

h. Run the command to create & store the View in the database.

i. Test the view by running the query: SELECT * FROM SalesPerProductPerDay.

3- Create a View called CustomerInfo (4 points):

The CustomerInfo view should return:

· CustomerID

· FullName (concatenate FirstName, MiddleName, & LastName with spaces in between)

· Address (concatenate StreetAdddress, City, State, & ZipCode with spaces in between)

· PrimaryEmail (If customer has no email, show NULL for the customer’s email address)

Hints:

a. Start by SELECTing CustomerID and concatenating FullName & Address from Customers.

b. JOIN into CustomerEmails.

· We need the view to show Customers who don’t have emails, so the join type is important here.

· We also only want the Primary Email, so only include those rows from the CustomerEmails table.

· Please keep in mind, you can have more than 1 predicate in joins. The ON clause might be a good place to specify you only want Primary Email rows from the CustomerEmails table.

4- Create a stored procedure called GetCustomerInfo & test it. (4 points)

The GetCustomerInfo sproc should take a parameter called @CustomerEmail and SELECT all columns from the CustomerInfo view from Question 3.

It should:

· Have 1 parameter called @CustomerEmail.

i. Make sure the @CustomerEmail parameter has the same data type as the EmailAddress column in the CustomerEmails table.

· Declare a variable called @CustomerID.

i. Make sure the @CustomerID variable has the same data type as the CustomerID column in the Customers table.

· Set the @CustomerID variable to the result of a subquery that reads from the CustomerEmails table WHERE EmailAddress = @CustomerEmail.

· SELECT all columns from the CustomerInfo view WHERE CustomerID = @CustomerID.

Hints:

a- Retrieve the Stored Procedure Template from the Lab 8 files.

b- Change the name to be GetCustomerInfo.

c- Replace the single-line comment in the parameters section with a parameter called @CustomerEmail with a data type of VARCHAR(254).

d- Inside the sproc body, declare a variable called @CustomerID with a data type of INT.

e- Set the @CustomerID variable to the result of a subquery that SELECTs CustomerID from the CustomerEmails table WHERE EmailAddress = @CustomerEmail.

f- SELECT all columns from the CustomerInfo view WHERE CustomerID = @CustomerID.

g- Run the CREAT PROCEDURE command to create the stored procedure.

h- Test the stored procedure by running:

EXECUTE GetCustomerInfo @CustomerEmail = ‘[email protected]’;

5- Create a stored procedure called UpdateProductPrice and test it. (4 points)

The UpdateProductPrice sproc should take 2 input parameters, ProductID and Price

Create a Stored Procedure that can be used to update the SalesPrice of a Product. Make sure the stored procedure also adds a row to the ProductPriceHistory table to maintain price history. For full credit, the sproc must use a transaction.

Hints:

a- Retrieve the Stored Procedure Template from the Lab 8 files.

b- Change the name to be UpdateProductPrice

c- Add 2 parameters. Call the first one @ProductID with a data type of SMALLINT and the second one @SalesPrice with a data type of DECIMAL(7,2).

d- Type BEGIN TRANSACTION;, hit enter a few times, and type COMMIT TRANSACTION;

e- Inside the Transaction, write the command to UPDATE the SalesPrice column in the Products table equal to the value of the @SalesPrice parameter.

f- Next, write the command to INSERT the historical row into the ProductPriceHistory table. Set the PriceChangeDate column equal to CURRENT_DATE().

g- Run the CREAT PROCEDURE command to create the stored procedure.

h- Test the sproc by executing it and passing a ProductID & Price of your choice.

Extra Credit Questions (1 point each)

1- Describe what Abstraction is and provide some examples.

2- How do Stored Procedures & Views provide an Abstraction Layer in database development?

3- Think of a hypothetical use case that hasn’t been covered in class and describe how a stored procedure or view can be used to implement an Abstraction Layer.

Your extra credit answers can be added as comments to the end of the .sql file you submit.

Lab 8 Deliverable:

Place all your commands to create views & stored procedures in a single .sql file called Lab_8_YourName.sql and submit it on Canvas.

For example:

-- Question 1

CREATE INDEX …

-- Question 2

CREATE VIEW SalesPerProductPerDay …

-- Question 3

CREATE VIEW CustomerInfo …

-- Question 4

CREATE PROCEDURE GetCustomerInfo …

-- Question 5

CREATE PROCEDURE UpdateProductPrice …

-- Extra Credit

-- Your answers