Assignment #3, SQL Procedures, Functions, and Triggers Name_________________________________ User ID (abc123) _______________ Section____
Bear in mind that you are bound by Smeal’s code of academic integrity. All work should be your own. If you see suspicious behavior by a classmate, please notify the instructor. You are not allowed to discuss this exam outside of the classroom. Please sign the statement below:
I ____________________________ affirm that I have not and will not give or receive unauthorized aid on this deliverable and I will complete this work honestly and according to the instructor’s guidelines.
Instructions:
Follow the instructions for the five problems below. Use Windows snipping tool to screenshot what is requested in the directions, paste in Word, and print. There should be one procedure code and 6 screenshots when completed. Staple this page(s) to this assignment front page and turn in.
This is an individual assignment and not to be completed in teams or with others. Also, you must be logged into MySQL using your account.
Procedures:
1. Create a procedure named ProductPurchase which takes productcode as the input and returns the product name, product line, MSRP, and priceEach for each time the product was purchased.
Required: apply if else condition that returns the above data if the product has been sold. If otherwise, return an error message showing “the product has not been sold or does not exist”. Note, for your input variable, name it productnum or something just different than productcode.
**Type the command “show create procedure ProductPurchase” and screenshot the output of this command. Also, copy your code and paste to your answer sheet.
Functions:
2.User defined function:
Step 1) Create a function which will take two inputs and outputs the sum of the two inputs and then multiplies the sum by the first input. You will need parenthesis to force the order of operations.
Step 2) Input these two numbers 8, 6;
**Take a screenshot of the output of Step 2.
3. Write a user input function which uses the CONCAT function. In this function you need to concatenate two strings and put an exclamation point at the end. There must be a space between the words, note there is not a space in the two strings. For example, if you input into your function the words What’s and up, the output would be What’s up!
When completed, use your function with the following: String 1 = “Happy” and String 2 = “Halloween”.
**Take a screenshot of the output.
**Type the command “show function status”, and screenshot the output for the first 4 columns
(db, name, type, definer) This should show your two functions plus any others you may have.
Triggers:
Step 1: Create this table:
CREATE TABLE ProductLineCount (
productLine VARCHAR(100), TotalCount INTEGER) engine = InnoDB;
Step 2: Add 6 rows of data and update by using these two commands (recommend you make sure you have no spelling errors prior to inserting):
Insert into ProductLineCount values (‘Classic Cars’,0),
(‘Motorcycles’,0),
(‘Planes’,0),
(‘Ships’,0),
(‘Trains’,0),
(‘Trucks and Buses’,0), (‘Vintage Cars’,0);
UPDATE ProductLineCount set TotalCount = (select count(*) from CM_products where productLine = ProductLineCount.productLine);
4. Create a trigger to update your table on any insert in the CM_products table. This will be part of your trigger:
UPDATE ProductLineCount SET TotalCount = (select count(*) from CM_products where productLine=NEW.productLine) where productLine=NEW.productLine;
Now insert a product into the CM_products table by typing the following,
INSERT INTO CM_products VALUES (‘S00_1000′,’2018 Drone’,’Planes’, ‘1:10′,’PSU Planes’, ‘Flying drone’,12,88.98, 250.00);
5. Similar to #4, create trigger to update the TotalCount in ProductLineCount when a row is deleted in CM_products table. Instead of NEW.productline it will be OLD.productline. Also, when it’s an insert we use After Insert, with a delete we will also use After Delete
Now delete a classic car record from the table by typing the following, DELETE FROM CM_products WHERE productcode=’S18_3233′;
**Query the ProductLineNumbers table where category equals Planes or Classic Cars only. Screenshot the command and the output.
**Type the command “show triggers” and screenshot the output through the first 8 columns, Trigger, Event, Table, Statement, Timing, Created, sql_mode, and Definer.
Note: You can ignore this error (you might face this when you enter your code): #1235 – This version of MySQL doesn’t yet support ‘multiple triggers with the same action time and event for one table’