DROP DATABASE IF EXISTS demo;
CREATE DATABASE demo DEFAULT CHARACTER SET utf8;
USE demo;

DROP TABLE IF EXISTS emp;
DROP TABLE IF EXISTS dept;

-- 部门表
CREATE TABLE DEPT(
  deptno INT PRIMARY KEY AUTO_INCREMENT, -- 部门编号
  dname  VARCHAR(14), -- 部门名称
  loc    VARCHAR(13) -- 部门地址
);

-- 员工表
CREATE TABLE EMP( 
  empno    INT PRIMARY KEY AUTO_INCREMENT, -- 员工编号
  ename    VARCHAR(10), -- 员工姓名
  job      VARCHAR(9), -- 职务
  mgr      INT, -- 所属经理的员工编号
  hiredate DATETIME, -- 入职时间 
  sal      DOUBLE, -- 月薪
  comm     DOUBLE, -- 奖金
  deptno   INT -- 所属部门的编号
);

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

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

/*

	select * from emp;
	select * from dept;
	
*/

/*
1. 使用两种函数, 计算员工的实发工资.
   结果集要求显示: 员工编号, 员工姓名, 工资, 奖金, 实发工资, 部门名称
*/

 SELECT empno 员工编号,ename 员工姓名 
    ,sal 工资, comm 奖金
    ,IFNULL (sal+comm,sal) 实发工资1
    ,IF(comm,sal+comm,sal) 实发工资2
    ,d.dname 部门名称
 FROM emp e
 LEFT JOIN  dept d ON e.deptno =d.deptno ; 
 
 /*
 2. 统计每个部门的平均工资, 总工资, 总人数, 最高工资, 最低工资.
   结果集要求显示: 部门名称, 总人数, 平均工资, 总工资, 总人数, 最高工资, 最低工资
 */
 
SELECT d.dname 部门名称,COUNT(e.empno) 总人数,
    AVG(IFNULL(e.sal+e.comm,e.sal)) 平均工资,
    SUM(IFNULL(e.sal+e.comm,e.sal)) 总工资,
    MAX(IFNULL(e.sal+e.comm,e.sal)) 最高工资,
    MIN(IFNULL(e.sal+e.comm,e.sal)) 最低工资
FROM emp e  
LEFT JOIN  dept d ON e.deptno =d.deptno
GROUP BY d.dname; 

/*
3. 查询员工的直属经理
   结果集要求显示: 员工编号, 员工姓名, 部门名称, 经理姓名, 经理编号, 经理部门名称
*/

SELECT e.empno 员工编号,e.ename 员工姓名,
    d.dname 部门名称, m.ename 经理姓名,
    m.empno 经理编号, d.dname 经理部门名称
FROM emp e     
LEFT JOIN dept d ON e.deptno =d.deptno
LEFT JOIN emp m ON e.mgr=m.empno;

/*
4. 查询实发工资总排名前十的员工, 将十人按照部门分组排名显示
   结果集要求显示: 员工编号, 员工姓名, 部门名称, 实发工资
*/
/* 将查询结果作为数据表使用 */
SELECT t.*
FROM(
    SELECT e.empno 员工编号,e.ename 员工姓名,
        d.dname 部门名称,IFNULL (sal+comm,sal) 实发工资
    FROM emp e
    LEFT JOIN dept d ON e.deptno=d.deptno
    ORDER BY 实发工资 DESC
    LIMIT 0,10
)t
ORDER BY t.部门名称;