/*
作业内容
a. Give each manager of “Netease” a 10 percent raise unless the salary becomes greater than $100000; in such cases, give only a 3 percent raise.
b. Delete all tuples in the works relation for employees of “Tensent”.
/
show variables like ‘autocommit’;
SET autocommit
= FALSE;
/
题目a
*/
SAVEPOINT init_status;
SELECT * FROM works WHERE company_name = ‘Netease’;
SELECT * FROM manages WHERE employee_name IN (SELECT employee_name FROM works WHERE company_name = ‘Netease’);
UPDATE manages SET salary = salary*1.1 WHERE employee_name IN (SELECT employee_name FROM works WHERE company_name = ‘Netease’);
UPDATE manages SET salary = (salary/1.1)*1.03 WHERE employee_name IN (SELECT employee_name FROM works WHERE company_name = ‘Netease’) AND salary >100000;
SELECT * FROM manages WHERE employee_name IN (SELECT employee_name FROM works WHERE company_name = ‘Netease’);
ROLLBACK TO init_status;
commit;
/*
题目b
*/
SAVEPOINT init_status2;
SELECT * FROM works WHERE company_name = ‘Tensent’;
DELETE FROM works WHERE company_name = ‘Tensent’;
SELECT * FROM works WHERE company_name = ‘Tensent’;
ROLLBACK TO init_status2;
commit;
测试结果