程序员冯帅前脚更把学生系统理顺，组长看他 sql 写的不错，于是给他安 排跟着一个新项目，项目是为公司的财务部门进行财务管理的。下面是项目所 需的数据表和功能需求 数据表: 雇员表(employee):雇员编号(empid,主键)，姓名(name)，性别(sex)， 职称(title)，出生日期(birthday)，所属部门(depid) 部门(department):部门编号(depid,主键)，部门名称(depname) 工资表(salary):雇员编号(empid)，基本工资(basesalary)，职务工 资(titlesalary)，扣除(deduction) 需求:
修改表结构，在部门表中添加部门简介字段将李四的职称改为“工程师”,并将她的基本工资改成 2000，职务工资 为 700删除人事部门的部门记录查询出每个雇员的雇员编号，实发工资，应发工资查询姓张且年龄小于 40 的员工记录查询雇员的雇员编号，姓名，职称，部门名称，实发工资查询销售部门的雇员姓名，工资统计各职称的人数统计各部门的部门名称，实发工资总和，平均工资查询比销售部门所有员工基本工资都高的雇员姓名 建表： -- 雇员表 CREATE TABLE employee( empid int auto_increment PRIMARY key, ename VARCHAR(4), sex enum('男','女'), title VARCHAR(5), birthday date, depid int ) ENGINE = INNODB DEFAULT charset = utf8mb4; -- 部门 CREATE TABLE department( depid int PRIMARY key, depname VARCHAR(10) )ENGINE = INNODB DEFAULT charset = utf8mb4; -- 工资表 CREATE TABLE salary( empid int , basesalary VARCHAR(10), titlesalary VARCHAR(10), deduction VARCHAR(10) )ENGINE = INNODB DEFAULT charset = utf8mb4;习题：
-- 1. 修改表结构，在部门表中添加部门简介字段 ALTER TABLE department add COLUMN info varchar(20); -- 2. 将李四的职称改为“工程师”,并将她的基本工资改成 2000，职务工资 为 700 UPDATE employee AS e1 SET e1.title = '工程师' WHERE e1.empid = ( SELECT a.empid from ( SELECT em.empid FROM employee AS em WHERE em.ename = '李四' ) as a); UPDATE salary AS s1 SET s1.basesalary = 2000,s1.titlesalary = 700 WHERE s1.empid = ( SELECT a.empid from ( SELECT em.empid FROM employee AS em WHERE em.ename = '李四' ) as a); SELECT em.* FROM salary AS em; -- 3. 删除人事部门的部门记录 DELETE from department as dp where dp.depname = '人事部'; SELECT * from department; -- 4. 查询出每个雇员的雇员编号，实发工资，应发工资 SELECT sa.empid as '编号', (sa.basesalary + sa.titlesalary - sa.deduction) as '实发工资', (sa.basesalary + sa.titlesalary) as '应发工资' from salary as sa -- 5. 查询姓张且年龄小于 40 的员工记录 SELECT em.* FROM employee AS em WHERE TIMESTAMPDIFF( YEAR, em.birthday, CURDATE()) < 40; -- em.empid = ( SELECT TIMESTAMPDIFF( YEAR, em.birthday, CURDATE( ) ) AS age FROM employee AS em WHERE age < 40 ); -- 6. 查询雇员的雇员编号，姓名，职称，部门名称，实发工资 SELECT em.empid,em.ename,em.title,dp.depname, (sa.basesalary + sa.titlesalary - sa.deduction) as '实发工资' from employee as em LEFT join department as dp on em.depid = dp.depid LEFT join salary as sa on em.empid = sa.empid; -- 7. 查询销售部门的雇员姓名，工资 SELECT em.ename,sa.basesalary from employee as em, salary as sa,department as dp where dp.depname = '销售部' and em.depid = dp.depid and em.empid = sa.empid; -- 8. 统计各职称的人数 SELECT em.title,count(*) from employee as em GROUP BY em.title; -- 9. 统计各部门的部门名称，实发工资总和，平均工资 SELECT dp.depname, sum( ( sa.basesalary + sa.titlesalary - sa.deduction ) ) AS '实发工资总和', AVG( sa.basesalary + sa.titlesalary - sa.deduction ) as '平均工资' FROM department AS dp, employee AS em, salary AS sa where em.depid = dp.depid and em.empid = sa.empid GROUP BY dp.depname; -- 10. 查询比销售部门所有员工基本工资都高的雇员姓名 SELECT em.ename from employee as em where em.empid = ( SELECT sa.empid from salary as sa where sa.basesalary > ( SELECT max(sa.basesalary) from employee as em,salary as sa where em.empid = sa.empid and em.depid =( SELECT dp.depid from department as dp where dp.depname = '销售部')));