IT360 Lab 8: Triggers, Stored Procedures, and Views

 

DUE: March 17, 2014, 2359 (paper copy BEFORE start of lab next day)

 

This lab should get you familiarized with creating and using stored procedures, triggers, and views. We will use MySQL for this lab. Use your notes, the slides, and online documentation to solve the exercises in the lab.

 

Preliminaries

 

a)For this lab you will be using your MySQL database on cardhu.cs.usna.edu. Connect to your database on cardhu using MySQL Workbench

 

b)      If the Product table in your database does not have a “QuantityInStock” column or something similar, modify the table to add the column:

ALTER TABLE Product add QuantityInStock integer default 0;

 

You should have now some tables similar with these (you should have created them in lab 6):

 

Product (BarCode, PName, Price, QuantityInStock)

Sale (SaleID, DeliveryAddress, CreditCard)

SaleItem (SaleID, BarCode, Quantity)

 

c)      Create a file called yourLastName_yourfirstName_Lab8.sql to contain all the questions as answers for this lab. Place the question as a comment before each answer. Make sure you save the file on your X drive and not on the local machine. If the answer is not an SQL statement, place the answer between /* … */.

 

 

Write SQL code queries to accomplish the following tasks. All answers should be saved in yourLastName_yourFirstName_Lab8.sql file.

 

PART 1: TRIGGERS

 

Write SQL code/queries to accomplish the following tasks. Add all answers to the yourLastName_yourFirstName_Lab8.sql file. If the answer is not an SQL statement, place the answer between /* … */.

 

  1. Create a trigger called updateAvailableQuantity that updates the quantity in stock in the Product table, for every product sold. The trigger should be executed after each insert operation on the SaleItem table: for the product with the given barcode (the one inserted into SaleItem), update the available quantity in Product table to be the old quantity minus the sold quantity.

a)      Write and execute the code to create the trigger.

b)      Insert a row in the Sale table and then insert a row in the SaleItem table for the existing sale. Was the quantity in the Product table updated? Why? Write your answers in the yourLastName_yourFirstName_Lab8.sql file.

 

PART 2: STORED PROCEDURES

 

  1. Create a stored procedure called spInsertProduct that inserts a new product into the database, under some conditions. The stored procedure has as input parameters the barcode, the product name, price, and quantityInStock. The stored procedure should insert a row in the Product table only if the price is greater than 0 and the quantity is greater or equal to 0. If the conditions are not satisfied, the stored procedure just terminates (no errors generated)

a)      Write and execute the code to create the stored procedure. Do not forget to change the default delimiter before and after the procedure. You should see the spInsertProduct stored procedure in your database under “routines” if you right-click on your database name and select “refresh”.

b)      Try to insert a product with price < 0 into the database by invoking the stored procedure. Write your invocation code to yourLastName_yourFirstName_Lab8.sql file. Was the product inserted? Try now to insert a product with price > 0. Was this product inserted? Write your answers in the yourLastName_yourFirstName_Lab8.sql file.

 

PART 3: Queries and Views

 

Write SQL queries to accomplish the following tasks. Add all answers to the yourLastName_yourFirstName_Lab8.sql file. Write the question as a comment /*…*/ before each answer. If the answer is not an SQL statement, place the answer between /* … */.

 

  1. List all products, with barcode and name, and the sale id and quantity for the sales containing that product, if any (products that were never sold should still be listed in your result)  Hint: use a left join

 

  1. Write a SQL statement to create a view called AllProductsSales based on the query in the previous exercise.

 

Run the SQL statement to create the view.

Check in the database (right-clickàRefresh) that the view was created. 

 

  1. A virtual table or view can be used as if it is a table in the database.  Now write a SQL query against the AllProductsSales view as if it was a table: select everything from the AllProductsSales, ordered by BarCode and SaleID.

 

  1. IMPORTANT: The view does not store any data. The data is stored just in the tables used in the definition of view. When a query that uses AllProductsSales is executed, the system first evaluates the query that defines AllProductsSales, and then performs further evaluation of the query that uses the view.

 

To demonstrate this, do the following:

 

a) Insert a row with value for PName = ‘Bread’ in Product table (use INSERT INTO … VALUES …). (You can change the product name to fit your store)

 

b) Execute the query from exercise 5 again.

 

Did the results changed?  Why? Write your answers in the yourLastName_yourFirstName_Lab8.sql file.

 

  1.  (Updates on views)  

 

Run the following query (or the similar query based on your data):

UPDATE AllProductsSales

SET PName = 'French Bread'

WHERE PName = 'Bread'

 

Does the query succeed? Why? Write your answers in the yourLastName_yourFirstName_Lab8.sql file.

 

Execute “SELECT * FROM Product” and check whether the name of ‘Bread’ product was changed to ‘French Bread’.

 

One reason to use the views: Hide complexity and simplify queries when frequently using the results of a complex query.

 

Example: In C++, would you rather write the code to compute a square root instead of using sqrt()?  Of course not, and the same idea applies to SQL.  Hide complexity and present a simple “replacement” of the complex query.

 

  1. (Extra credit) Create a view called ProductProfit to show the barcode, the name of the product, and total_profit. total_profit represents the total profit for that product, which is based on the fraction of the product price that represents the profit margin (assume a fixed 10%), and the quantity of products sold (found in the SaleItem table). 

Hint: you can do calculations in the SELECT statement. For example, the following query will display the barcode and the total inventory value for each product:

SELECT BarCode, Price*QuantityInStock AS TotalValue

FROM Product

 

  1. (Extra credit) SQL SELECT statement for MySQL has a “LIMIT n” clause that allows you to limit the number of rows returned in the result of a select query. For example “SELECT * FROM Product LIMIT 5” will display first 5 rows from Products table.

 

Write the SQL query on the ProductProfit view to show the top 5 products based on total profit in order of highest profit to lowest profit.

 

  1. (Extra credit)Write the SQL query to achieve the same result as above, but without using the ProductProfit view (you can still use LIMIT n). This query should look more complicated than the query in the previous exercise.

 

  1. (Extra credit): Write the SQL statement to update the total_profit for ‘Bread’ to be $10,000, using the view. Run the query.

 

Does the query succeed? Why? Write your answers in the yourLastName_yourFirstName_Lab8.sql file.

 

 

 

Turn in ( BOTH electronic and paper submissions are required):

Electronic (due before 2359 on March 17, 2014):

  1. Upload the file yourLastName_yourFirstName_Lab8.sql with all your answers to the Lab 8 assignment on the blackboard.

 

Hard-copies (due before start of lab on March 18, 2014):

  1. The completed assignment coversheet. Your comments will help us improve the course.
  2. A hard copy of the file created for this assignment: yourLastName_yourFirstName_Lab8.sql. Do not forget the question as a comment before the answer for each exercise.