-- 创建表
drop table if exists emp;
create table emp(
empno int,
ename varchar(50),
job varchar(50),
mgr int,
hiredate date,
sal decimal(7,2),
comm decimal(7,2),
deptno int
)engine=innodb default charset=utf8;

drop table if exists dept;
create table dept(
deptno int,
dname varchar(14),
loc varchar(13)
)engine=innodb default charset=utf8;

INSERT INTO emp VALUES(7369,'SMITH','CLERK',7902,'1980-12-17',800,NULL,20);
INSERT INTO emp VALUES(7499,'ALLEN','SALESMAN',7698,'1981-02-20',1600,300,30);
INSERT INTO emp VALUES(7521,'WARD','SALESMAN',7698,'1981-02-22',1250,500,30);
INSERT INTO emp VALUES(7566,'JONES','MANAGER',7839,'1981-04-02',2975,NULL,20);
INSERT INTO emp VALUES(7654,'MARTIN','SALESMAN',7698,'1981-09-28',1250,1400,30);
INSERT INTO emp VALUES(7698,'BLAKE','MANAGER',7839,'1981-05-01',2850,NULL,30);
INSERT INTO emp VALUES(7782,'CLARK','MANAGER',7839,'1981-06-09',2450,NULL,10);
INSERT INTO emp VALUES(7788,'SCOTT','ANALYST',7566,'1987-04-19',3000,NULL,20);
INSERT INTO emp VALUES(7839,'KING','PRESIDENT',NULL,'1981-11-17',5000,NULL,10);
INSERT INTO emp VALUES(7844,'TURNER','SALESMAN',7698,'1981-09-08',1500,0,30);
INSERT INTO emp VALUES(7876,'ADAMS','CLERK',7788,'1987-05-23',1100,NULL,20);
INSERT INTO emp VALUES(7900,'JAMES','CLERK',7698,'1981-12-03',950,NULL,30);
INSERT INTO emp VALUES(7902,'FORD','ANALYST',7566,'1981-12-03',3000,NULL,20);
INSERT INTO emp VALUES(7934,'MILLER','CLERK',7782,'1982-01-23',1300,NULL,10);

INSERT INTO dept VALUES(10, 'ACCOUNTING', 'NEW YORK');
INSERT INTO dept VALUES(20, 'RESEARCH', 'DALLAS');
INSERT INTO dept VALUES(30, 'SALES', 'CHICAGO');
INSERT INTO dept VALUES(40, 'OPERATIONS', 'BOSTON');

-- 1、按员工编号升序排列不在10号部门工作的员工信息
SELECT *
FROM emp
WHERE deptno != 10
ORDER BY empno;
-- 2、查询姓名第二个字母不是”A”且薪水大于800元的员工信息，按薪水降序排列
SELECT *
FROM emp
where ename not LIKE "_A%" and CEIL(sal) > 800;
-- 3、求每个部门的平均薪水
SELECT deptno, avg(sal)
FROM emp
GROUP BY deptno;
-- 4、求各个部门的最高薪水
SELECT deptno, max(sal)
FROM emp
GROUP BY deptno;
-- 5、求每个部门每个职位的最高薪水
SELECT deptno, job, MAX(sal)
FROM emp
group by deptno, job
-- 6、求平均薪水大于2000的部门编号
SELECT deptno, avg(sal) avgSal
from emp
group by deptno
HAVING avgSal > 2000;
-- 7、将员工薪水大于1200且部门平均薪水大于1500的部门编号列出来，按部门平均薪水降序排列
select deptno, avg(sal) avgSal
from emp
where sal > 1200
GROUP BY deptno
HAVING avgSal > 1500
ORDER BY avgSal desc;

-- 8、求最高薪水的员工信息
SELECT *
from emp
where sal = (
SELECT max(sal)
FROM emp
)
-- 9、求多于平均薪水的员工信息
SELECT *
FROM emp
where sal > (
SELECT AVG(sal)
FROM emp
)

-- 10、求各个部门薪水最高的员工信息 显示部门名称
SELECT dept.dname, t2.*
FROM dept,(
select emp.*
from emp, (
SELECT deptno, MAX(sal) maxSal
FROM emp
GROUP BY deptno) t1
where emp.deptno = t1.deptno AND emp.sal = t1.maxSal) t2
where dept.deptno = t2.deptno


-- 11. 查询和Scott相同部门的员工姓名和雇用日期

select ename, hiredate
FROM emp
WHERE ename != "SCOTT" and deptno = (
select deptno
from emp
where ename = "SCOTT");
-- 12.	查询工资比公司平均工资高的员工的员工号，姓名和工资。
SELECT empno, ename, sal
FROM emp
WHERE sal > (
select AVG(sal)
FROM emp);
-- 13.	查询各部门中工资比本部门平均工资高的员工的员工号, 姓名和工资

-- 13.1
SELECT empno, ename, sal
FROM emp,(
SELECT deptno, avg(sal) avgSal
from emp 
GROUP BY deptno
) t1
where emp.deptno = t1.deptno and emp.sal > t1.avgSal;

-- 13.2
select empno, ename, sal
FROM emp inner join (
SELECT deptno, avg(sal) avgSal
FROM emp
GROUP BY deptno
) t1
on emp.deptno = t1.deptno and emp.sal > t1.avgSal

-- 14.	查询和姓名中包含字母u的员工在相同部门的员工的员工号和姓名
SELECT empno, ename
FROM emp
WHERE deptno in (
SELECT deptno
FROM emp
WHERE ename LIKE "%u%"
)

-- 15. 	查询在部门的loc为 DALLAS 的部门工作的员工的员工号
SELECT empno
from emp
WHERE deptno in (
SELECT deptno
FROM dept
WHERE loc = "DALLAS");


-- 16.	查询所有部门的名字，loc，员工数量 和 工资平均值 ()
-- 16.1
select dept.dname, dept.loc, t1.*
from dept, (
select avg(sal), count(empno), deptno
from emp
group by deptno
) t1
where t1.deptno = dept.deptno;

-- 16.2
select d.*, count(e.empno), avg(sal)
from dept d, emp e
where d.deptno = e.deptno
GROUP BY d.deptno, d.dname, d.loc


-- 17.查询平均工资最低的部门信息
-- 17.1
select *
from dept
where deptno = (
select deptno
from emp
GROUP BY deptno
order by avg(sal)
limit 1)

-- 17.2
select *
from dept
where deptno = (
select deptno
from emp
group by deptno
having avg(sal) like (
select min(t1.avgSal)
from (
select deptno, avg(sal) avgSal
from emp
GROUP BY deptno
) t1
))

-- 18.查询平均工资最低的部门信息和该部门的平均工资
select dept.*, avgSal
from dept, (
select deptno, avg(sal) avgSal
from emp
group by deptno) t1
where dept.deptno = t1.deptno


-- 19.查询平均工资高于公司平均工资的部门有哪些?
select deptno
from emp
group by deptno
having avg(sal) > (
select avg(sal) avgSal
from emp
)

-- 20. 各个部门中 最高工资中最低的那个部门的 最低工资是多少	
select min(sal)
from emp
where deptno = (
select deptno
from emp
group by deptno
order by max(sal) asc
limit 1
)
-- 21.查询部门号大于20 或者 姓名中包含 a的 员工信息
-- 21.1
select *
from emp
where deptno > 20 or ename Like "%a%";

-- 21.2
select *
from emp 
where deptno > 20
union
select *
from emp
where ename like "%a%";