-- 1.查询出部门编号为BM01的所有员工
SELECT
*
FROM
emp e
WHERE
e.deptno = 'BM01';
-- 2.所有销售人员的姓名、编号和部门编号。
SELECT
e.empname,
e.empno,
e.deptno
FROM
emp e
WHERE
e.empstation = "销售人员";
-- 3.找出奖金高于工资的员工。
SELECT
*
FROM
emp2
WHERE
sal < comm;
-- 4.找出奖金高于工资60%的员工。
SELECT * FROM emp2 WHERE comm > ( sal * 0.6 );
-- 5.找出部门编号为10中所有经理，和部门编号为20中所有销售员的详细资料。
select
*
from emp2
where (deptno = 10 and job = 'MANAGER') or (deptno = 20 and job = 'SALESMAN');
-- 6.找出部门编号为10中所有经理，部门编号为20中所有销售员，还有即不是经理又不是销售员但其工资大或等于20000的所有员工详细资料。
select
*
from emp2
where (deptno = 10 and job = 'MANAGER') or (deptno = 20 and job = 'SALESMAN')
or (job not in ('MANAGER','SALESMAN') and sal >= 2000);
-- 7.无奖金或奖金低于1000的员工。
select * from emp2 where comm is null or comm < 1000;
-- 8. 查询名字由三个字组成的员工。
-- 注意：一个汉字占三个字节
select * from emp2 e where e.ename like '___';
-- 9.查询2023年入职的员工。
select * from emp2 where YEAR(hiredate) like '2023%';
-- 10. 查询所有员工详细信息，用编号升序排序
select * from emp2 order by empno asc;
-- 11. 查询所有员工详细信息，用工资降序排序，如果工资相同使用入职日期升序排序
select * from emp2 e order by e.sal desc,e.hiredate;
-- 12.查询每个部门的平均工资
select avg(e.sal) d.deptname from emp2 e,dept d
group by d.deptno
on e.deptno = d.deptno;
select avg(sal),deptno,dept from emp2
group by deptno;
-- 13.查询每个部门的雇员数量
select
deptno,count(deptno)
from emp2
group by deptno;
-- 14.查询每种工作的最高工资、最低工资、人数
select max(sal),min(sal),count(job)
from emp2
group by job;
-- 18.列出工资比ALLEN高的所有员工
select * from emp2 where sal > (select sal from emp2 where ename = 'ALLEN');
-- 19.列出所有员工的姓名及其直接上级的姓名
select
e1.ename,
e2.ename
from emp2 e1,emp2 e2
where e1.mgr = e2.empno
-- 20.列出受雇日期早于直接上级的所有员工的编号、姓名、部门名称
select
e1.ename,
e2.ename
from emp2 e1,emp2 e2
where e1.mgr = e2.empno and e1.hiredate < e2.hiredate;
-- 21.列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门
select deptname,e.* from dept d,emp2 e where count(dept) is null;
-- 22.列出所有文员(CLERK)的姓名及其部门名称，部门的人数
select e.ename,d.deptname,d.deptno
from emp2 e
join dept d
where e.job = 'CLERK';
-- 23.列出最低薪金大于1500的各种岗位及从事此岗位的员工人数
SELECT e.job,min(sal) min_salary,count(e.empno)
from emp2 e
group by e.job
having min_salary > 1500;
-- 25.列出薪金高于公司平均薪金的所有员工信息，所在部门名称，上级领导，工资等级
-- select d.deptname,e1.ename e2.ename as leadername,
-- where emp2 e1 join dept12 d on e.deptno = d.deptno
-- left join emp2 b on e.mgr = b.empno#解决员工与领导的一个关联问题
-- join sa
# （1）公司平均薪资
select avg(sal) avgsal from emp;
+-------------+
| avgsal |
+-------------+
| 2073.214286 |
+-------------+
# （2）第一个连接：部门名字
# 第二个左连接：emp表看成两张表，列出员工对应的上级领导
# 第三个连接：工资等级
select d.dname,e.ename,b.ename as leadername,grade
from emp e join dept d on e.deptno=d.deptno
left join emp b on e.mgr=b.empno
join salgrade s on e.sal between s.losal and s.hisal
where e.sal>(select avg(sal) avgsal from emp);
+------------+-------+------------+-------+
| dname | ename | leadername | grade |
+------------+-------+------------+-------+
| RESEARCH | FORD | JONES | 4 |
| RESEARCH | SCOTT | JONES | 4 |
| ACCOUNTING | CLARK | KING | 4 |
| SALES | BLAKE | KING | 4 |
| RESEARCH | JONES | KING | 4 |
| ACCOUNTING | KING | NULL | 5 |
-- 26.列出与SMITH从事相同工作的所有员工及部门名称
select d.dname,e.*
from emp2 e
join dept12 d
on e.deptno = d.deptno
where e.job = (select job from emp2 where ename = "SMITH");
-- 27.列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金、部门名称
# （1）部门30中的员工最高工资
select max(sal) from emp2 where deptno=30;
+----------+
| max(sal) |
+----------+
| 2850.00 |
+----------+
# （2）高于最高工资的其他员工
select deptno,ename,sal from emp2
where sal>(select max(sal) from emp where deptno=30)
and deptno!=30;
+--------+-------+---------+
| deptno | ename | sal |
+--------+-------+---------+
| 20 | JONES | 2975.00 |
| 20 | SCOTT | 3000.00 |
| 10 | KING | 5000.00 |
| 20 | FORD | 3000.00 |
+--------+-------+---------+
# （3）连接dept表
select d.dname,t.ename,t.sal
from (select deptno,ename,sal from emp2
where sal>(select max(sal) from emp where deptno=30)
and deptno!=30) t
join dept12 d
on t.deptno=d.deptno;
+------------+-------+---------+
| dname | ename | sal |
+------------+-------+---------+
| RESEARCH | JONES | 2975.00 |
| RESEARCH | SCOTT | 3000.00 |
| ACCOUNTING | KING | 5000.00 |
| RESEARCH | FORD | 3000.00 |
+------------+-------+---------+
-- 28.列出在每个部门工作的员工数量、平均工资
select d.deptno,count(ename) num,avg(sal)
from emp2 e
right join dept12 d
on e.deptno = d.deptno
group by deptno;
