公司员工数据查询（SQL）

drop table if exists Emp;
drop table if exists Dept;

#部门表
create table Dept
(
dno int primary key,		#部门编号
dname varchar(20) not null,	#部门名称
loc varchar(50)		#部门所在地
);

#员工表
create table Emp
(
eno int primary key,		#员工编号
ename varchar(20) not null,	#员工姓名
job varchar(10) not null,	#员工职位
mgr int references tb_emp(eno),	#员工主管编号
sal int not null,		#员工月薪
comm int,			#员工月补贴
dno int				#员工所在部门
);

alter table Emp add constraint fk_dno foreign key (dno) references Dept(dno) on delete set NULL;


#插入部门数据
insert into Dept values (10, '会计部', '北京海淀区');
insert into Dept values (20, '研发部', '上海浦东区');
insert into Dept values (30, '销售部', '重庆江北区');
insert into Dept values (40, '运维部', '四川成都');

#插入员工数据
insert into Emp values (7800, 'AA', '总裁', null, 9000, 1500, 20);
insert into Emp values (2056, 'BB', '分析师', 7800, 5000, 1500, 20);
insert into Emp values (3088, 'CC', '设计师', 2056, 3500, 1200, 20);
insert into Emp values (3211, 'DD', '程序员', 2056, 3200, null, 20);
insert into Emp values (3233, 'EE', '程序员', 2056, 3400, 1000, 20);
insert into Emp values (3251, 'FF', '程序员', 2056, 4000, null, 20);
insert into Emp values (5566, 'GG', '会计师', 7800, 4000, null, 10);
insert into Emp values (5234, 'HH', '出纳', 5566, 2000, null, 10);
insert into Emp values (3344, 'II', '销售主管', 7800, 3000, 500, 30);
insert into Emp values (1359, 'JJ', '销售员', 3344, 1800, 800, 30);
insert into Emp values (4466, 'KK', '销售员', 3344, 2500, 300, 30);
insert into Emp values (3244, 'LL', '程序员', 3088, 3200, 500, 20);
insert into Emp values (3577, 'MM', '会计', 5566, 2200, null, 10);
insert into Emp values (3588, 'NN', '会计', 5566, 2500, 200, 10);<pre name="code" class="sql">#0. 查询最高工资及其对应员工姓名
select ename, sal from emp
where sal=(select max(sal) from emp);

#1. 计算每位员工的年薪
select ename as 姓名, (sal+if(comm is null, 0, comm))*12 as 年薪 
from emp order by 年薪 DESC;

#2. 统计有员工的部门的人数
select dname as 部门名称, t1.部门人数 from
(select dno, count(dno) as 部门人数 from emp group by dno) as t1,
dept as t2
where t1.dno = t2.dno;

#3. 求挣最高薪水的员工(boss除外)的姓名
select ename as 最高薪水员工 from emp 
where sal = (select max(sal) from emp where job <> '总裁');

#4. 查询薪水超过平均薪水的员工的姓名和工资
select ename as 员工姓名, sal as 员工工资 from emp 
where sal > (select avg(sal) from emp);

#5. 查询薪水超过其所在部门平均薪水的员工的姓名、部门名称和工资
select ename, dname, t3.sal from
(select eno, t1.dno, sal from emp as t1,
(select dno, avg(sal) as avgSal from emp group by dno) as t2
where t1.dno=t2.dno and sal>avgSal) as t3, emp as t4, dept as t5 
where t3.eno=t4.eno and t5.dno=t3.dno;

#6. 查询部门中薪水最高的人姓名、工资和所在部门名称
select t5.ename, t5.sal, t6.dname from
(select t4.ename, t3.sal, t3.dno from 
(select eno, sal, t1.dno from emp as t1 inner join
(select dno, max(sal) as maxSal from emp group by dno) as t2
on t1.dno = t2.dno and sal = maxSal) as t3,
emp as t4 where t4.eno = t3.eno) as t5,
dept as t6 where t5.dno = t6.dno;

#7. 哪些人是主管
select * from emp 
where eno in 
(select distinct mgr from emp);

select * from emp 
where eno=any(select distinct mgr from emp);

#8. 求平均薪水最高的部门的名称和平均工资
select dname as 部门名称, avgSal as 平均工资 from
(select dno, avgSal
from (select dno, avg(sal) as avgSal from emp
group by dno) t1 
where avgSal=(select max(avgSal) from 
(select dno, avg(sal) as avgSal from emp group by dno) as t2)) as t3
INNER JOIN dept as t4 on t3.dno=t4.dno;

#9. 求薪水最高的前3名雇员
select * from emp ORDER BY sal desc limit 0,3;

#10. 求薪水排在第4-6名雇员
select * from emp ORDER BY sal desc limit 3,3;
select * from emp order by sal desc limit 10,5;

#11. 求薪水最低的部门经理所在部门的名称
select dname as 最低薪水部门经理 from
(select dno, min(sal) from
(select * from emp 
where eno in (select distinct mgr from emp)) as t1) as t2,
dept as t3 where t2.dno = t3.dno