-- 创建EmpManager数据库
create database EmpManager;
alter database EmpManager character set utf8;
use EmpManager;
-- 创建depart表
create table depart(
	departid Char(10) primary key comment '部门编号',
	departname Char(50) unique comment '部门名称',
	departlocation Char(50) default '北京' comment '部门所在地'
);
-- 增加列
-- alter table depart add COLUMN departid Char(10) primary key comment '部门编号';
-- 修改列名
-- alter table depart change jobid departid Char(10);

-- 创建job表
create table job(
	jobid Char(10) primary key comment '职位编号',
	jobname Char(50) not null comment '职位名称',
	joblevel Int not null default 1 comment '职位级别'
);
-- 创建employee  TODO check()
create table employee(
	empid Char(10) primary key comment '员工编号',
	empname Char(50) not null comment '员工姓名',
	empsex Char(4) default '男' check(empsex='男' || empsex='女'),
	empjob Char(20) comment '职位',
	empmgr Char(10) comment '上级领导编号',
	hiredate Date comment '入职时间',
	Salary Decimal(7,2) check(Salary>0) comment '工资',
	departid Char(10) comment '部门编号',
	foreign key(empjob) references job(jobid),
	foreign key(departid) references depart(departid)
);
-- 插入数据
insert into depart values('01', '总裁办', '北京'), ('02', '财务处', '上海'), ('03', '研发部', '武汉'), ('04', '销售部', '深圳');
insert into job values('01', '董事长', 20), ('02', '总经理', 18), ('03', '经理', 16), ('04', '销售员', 11), ('05', '分析师', 14), ('06', '文员', 10), ('07', '会计', 12);
insert into employee values
('10001','Judy','女','01',null,'2001-09-01',99999,'01'),
('10002','刘备','男','02','10001','2005-10-01',50000,'01'),
('10003','关羽','男','03','10002','2006-05-01',30000,'04'),
('10004','张飞','男','03','10002','2006-05-01',30000,'04'),
('10005','诸葛亮','男','05','10002','2012-07-13',45000,'03'),
('10006','赵云','男','03','10002','2008-04-24',25000,'02'),
('20003','孙尚香','女','06','10006','2016-09-26',5500,'03'),
('10008','黄月英','女','07','10005','2014-01-12',13500,'02'),
('30010','徐庶','男','06','10006','2007-11-11',28000,'02'),
('30035','周仓','男','04','10003','2015-03-12',5800,'04'),
('40001','马超','男','04','10004','2018-08-22',7900,'04'),
('50001','孟获','男','04','10002','2021-02-13',4500,'04');

-- 1、给马超升职为经理(修改其职位为03)，领导变成10002，工资涨3000；（5分）
update employee  set empjob='03', empmgr='10002', salary=salary+3000 where empname='马超';
-- 2、查询员工编号，姓名，性别，职位，入职时间，基本工资，要求使用列别名；（5分）
select empid '员工编号', empname '姓名', empsex '性别', empjob '职位', diredate '入职时间', salary '基本工资' from employee;
-- 3、查询02，03，04部门入职超过10年的员工信息；（5分）
select * from employee where departid in('02','03','04') and DATE(hiredate)<DATE_SUB(CURRENT_DATE, INTERVAL 10 YEAR);
-- 4、查询员工编号以1开头，且工资在30000及以上的员工信息；（5分）
select * from employee where empid like '1%' and salary>30000;
-- 5、查询工资最高的前3-8名的员工信息；（6分）
select * from employee order by salary desc limit 2,6;
-- 6、查询04部门的人数，及平均工资；（6分)
select departid, count(*), avg(salary) from employee group by departid having departid='04';
-- 7、查询每个部门的人数，平均工资，最高工资，最低工资，并显示人数在3人及以上的信息；（8分）
select departid, count(*) total, avg(salary), max(salary), min(salary) from employee group by departid having total>=3;
-- 8、查询职位级别在13以上的员工编号，员工姓名，员工性别，职位名称，入职时间，部门名称和工作地点（使用多表连接查询）（8分）
select empid,empname,empsex, jobname,hiredate,departname,departlocation from employee, job, depart where empjob=jobid and depart.departid=employee.departid  and joblevel>13;
-- 9、查询赵云所领导的员工信息（使用子查询）（8分）
select * from employee where empmgr=(select empid from employee where empname='赵云');
-- 10、显示非销售人员职位名称以及从事同一工作雇员的月工资的总和,并且要满足从事同一工作的雇员的月工资合计大于50000,输出结