Write a trigger to insert some information into employee_audit table (which have four fields: employee_id, salary_after, edit_time, and action) every time, when an INSERT happens into employee table.
Write a trigger to insert some information into log_employee table (which have five fields employee_id, salary_before, salary_after, edit_time, and action) every time, after an UPDATE happen into employee table.
(3). Create an index “IndLastFirstName” with columns LAST_NAME and FIRST_ NAME on employee table. (See document for table pic)

(4). List the query which will use the index “IndLastFirstName”

a) Select * from employee where FIRST_ NAME=’Joe’

b) Select * from employee where LAST_NAME =’LEE’

c) Select * from employee where LAST_NAME =’LEE’ and FIRST_ NAME=’Joe’

d) Select * from employee where LAST_NAME =’LEE’ orFIRST_ NAME=’Joe’

(a) Create a Store Procedure to list CustomerName, ContactName and phone for a given City as input parameter. (10 points)
The structure of the table: Customers

(b) Can you create a function to implement the above, if “yes”, write the function, if “no” explain the reason?

Write Output after executing the following SQL statements

Session 1:

mysql> CREATE TABLE t (i INT, PRIMARY KEY (i)) ENGINE = InnoDB;
mysql> INSERT INTO t (i) VALUES(1),(2),(3);
mysql> START TRANSACTION;
mysql> SELECT * FROM t WHERE i = 2 FOR UPDATE;
The following SQL statements shows an example of using savepoint, rollback, and commit in the transaction.

Table: STUDENT_MAST:

Student_ID

ST_CLASS

2

12

3

14

4

15

create table t_test (id int not null primary key) engine=InnoDB;

Start transaction;

insert into t_test values(1);

insert into t_test values(3);

savepoint tran1;

insert into t_test values(2);

UPDATE STUDENT_MAST SET ST_CLASS=10 WHERE STUDENT_ID=2;

rollback to tran1;

UPDATE STUDENT_MAST SET ST_CLASS=17 WHERE STUDENT_ID=3;

UPDATE STUDENT_MAST SET ST_CLASS=18 WHERE STUDENT_ID=4;

Commit;

After executing the SQL transactions above, please answer:

What’s the output to run the query: select * from t_test
What’s the New Value of ST_CLASS corresponding each Student_ID(5 points)