USE mytools;

-- 1) drop, recreate, and populate table account:
DROP TABLE IF EXISTS account;
CREATE TABLE account (acct_num INT, amount DECIMAL(10,2));
INSERT INTO account VALUES(1000,1.00);
INSERT INTO account VALUES(1000,2.00);
INSERT INTO account VALUES(1000,3.00);
SELECT * FROM account;

-- 2) drop, recreate, and populate table average_val:
DROP TABLE IF EXISTS average_val;
CREATE TABLE average_val (average double);
INSERT INTO average_val VALUES(1.00);
SELECT * FROM average_val;

-- 3) drop and redefine trigger inserted_sum:
DROP TRIGGER IF EXISTS inserted_sum;
CREATE TRIGGER update_avg_val AFTER INSERT ON account 
FOR EACH ROW SET @sum = @sum + NEW.amount;
UPDATE average_val SET average = (SELECT AVG(amount) FROM account);
SELECT * FROM average_val;

