please answere these questions Using MySQL commands answer the questions listed

Question

please answere these questions

Using MySQL commands answer the questions listed below using the Tiny Video schema. Submit via the D2L drop box 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

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.

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

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.

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

Create a trigger called membership_balance_updates that will capture any updates made to the mem_balance column in membership. The trigger should only capture those transactions in which the member’s balance actually changes. The mem_num, old mem_balance, new mem_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));

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

UPDATE membership SET mem_balance = 10.00 WHERE mem_num = 104;

UPDATE membership SET mem_fname = 'Dan' WHERE mem_num = 103;

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.)

Explanation / Answer

there are multiple parts to this question. Please post 1 more question for the same.

the view will be as shown below:

create view customer_with_balances AS

select customer_number, customer_lname, customer_balance

from customer where customer_balance > 0

function will be:

CREATE FUNCTION get_customer_balance (@number INTEGER) RETURNS INTEGER
AS
BEGIN
DECLARE @retval INTEGER

SELECT customer_balance
FROM customer
WHERE customer_balance = @number

RETURN @retval
END

select * from customer where customer_balance = get_customer_balance(102)

Related Questions

Navigate

• Browse (All)
• Browse P
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.