数据库MySQL实验二数据更新

最新推荐文章于 2023-10-28 16:32:22 发布

小小唯有

最新推荐文章于 2023-10-28 16:32:22 发布

阅读量200

点赞数

分类专栏： MySQL初学文章标签： mysql 数据库

本文链接：https://blog.csdn.net/weixin_54697940/article/details/120878226

版权

MySQL初学专栏收录该内容

1 篇文章 0 订阅

订阅专栏

题目一

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.

给“网易”的每位经理加薪10%，除非工资超过10万美元；在这种情况下，只增加3%。

SELECT * FROM works WHERE employee_name IN (SELECT employee_name FROM manages WHERE employee_name = manager_name) AND  company_name = 'Netease';

/* 查询修改前网易经理的工资*/



UPDATE works SET salary = salary*1.1 WHERE employee_name IN (SELECT employee_name FROM manages WHERE employee_name = manager_name) AND  company_name = 'Netease'  AND salary <=100000;

/* 工资不高于100000的经理工资*1.1*/



UPDATE works SET salary = salary*1.03 WHERE employee_name IN (SELECT employee_name FROM manages WHERE employee_name = manager_name) AND company_name = 'Netease' AND salary >100000;

/* 工资高于100000的经理工资*1.03*/



SELECT * FROM works WHERE employee_name IN (SELECT employee_name FROM manages WHERE employee_name = manager_name) AND  company_name = 'Netease';

/*查询修改后的网易经理工资*/

题目二

Delete all tuples in the works relation for employees of “Tensent”.

删除“Tensent”员工工作关系中的所有元组。

DELETE FROM employee WHERE employee_name IN (SELECT employee_name FROM works WHERE company_name = 'Tensent');

/*删除员工*/



DELETE FROM manages WHERE employee_name IN (SELECT employee_name FROM works WHERE company_name = 'Tensent');

/*删除经理关系*/



DELETE FROM works WHERE company_name = 'Tensent';

/*删除工资*/



DELETE FROM company WHERE company_name = 'Tensent';

/*删除公司*/