数据库实验2

/*
作业内容
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;

测试结果