Reference no: EM131282025

Using MySQL commands answer the questions listed below usingthe TinyVideoschema. Submit via the D2L dropbox the final SQL scripts for each problem. You must answer all parts of the question before receiving any partial credit. For example, do not expect any points if all you answer is part b of question 3. Each part builds upon the correct answer of the previous part.

Question 1.

a) Create a view called customer_with_balances that has the following columns: customer_number, customer_lname and customer_balance. The view should only include those customers where the customer balance is greater than zero.

b) Write the SELECT statement to show all records from the customer_with_balances view.

customer number      customer_lname        customer balance

102                            Dawson                    11.00

103                            Knight                      6.00

105                            Mcclain                     15.00

107                            Elliott                       5.00

109                            Ochoa                      10.00

111                            Mann                       8.00

112                            Trujillo                     3.00

Question 2.

a) Create a stored function called get_customer_balance which will return a customer's balance from the membership table by passing in a membership number.

b) Write a single SELECT statement to show the customer balance for member 102 by using the get_customer_balance function.

 

customer number      get_customer_balance

102                            11.00

Question 3.

a) Create a trigger called membership_balance_updates that will capture any updates made to the balance column in membership. The trigger should only capture those transactions in which the member's balance actually changes. The membership_id, old balance, new balance, user, transaction date should be placed into a membership_balance_audit table.
Use the following script to create the membership_balance_audit table.
CREATE TABLE IF NOT EXISTS

membership_balance_audit

(mem_num                   INTEGER,

 old_mem_balance     DECIMAL(10,2),

 new_mem_balance  DECIMAL(10,2),

 transaction_date       TIMESTAMP,

 transaction_user       VARCHAR(50));

b) Run the following update statements after the trigger has been created.

UPDATE membershipSET balance = 10.00WHERE membership_id = 104;

UPDATE membership SET first_name = 'Dan' WHERE membership_id = 103;

c) Write the SELECT statement to show all records in the membership_balance_audit table. (Note: The transaction date will be different for your result set.)

mem_num        old_mem_balance          new_mem_balance                 transaction_date            transaction_user

104                     0.00                               10.00                                     2012-04-1200:00:00     root@localhost