第一天--简单的sql语句

--1.查询所有的员工的部门名称
SELECT
    e.ename AS 员工,
    d.dname AS 部门 
FROM
    emp e
    LEFT JOIN dept d ON e.deptno = d.deptno;

--2.查询现有员工都有哪些职位
SELECT DISTINCT
    ( job ) AS 职位 
FROM
    emp;
    
--3.查询每个部门下员工的数量
SELECT
    ( SELECT d.dname FROM dept d WHERE d.deptno = e.deptno ) AS 部门,
    count( e.empno ) AS 人数 
FROM
    emp e 
GROUP BY
    e.deptno;

--4.查询每个职位的最高薪资
SELECT
    job AS 职位,
    max( sal ) AS 最大薪资 
FROM
    emp 
GROUP BY
    job;
    
--5.查询员工表中的经理，去掉为NULL的信息
SELECT
    b.ename AS 经理 
FROM
    ( SELECT DISTINCT ( mgr ) FROM emp WHERE mgr IS NOT NULL ) a
    LEFT JOIN emp b ON a.mgr = b.empno;
    
--6.查询每个经理管理的员工数量
SELECT
    b.ename AS 经理,
    a.num 
FROM
    ( SELECT mgr, count( empno ) AS num FROM emp WHERE mgr IS NOT NULL GROUP BY mgr ) a
    LEFT JOIN emp b ON a.mgr = b.empno;
    
--7.查询薪资级别最高的员工
SELECT
    ename AS 姓名,
    sal AS 薪资 
FROM
    emp 
WHERE
    sal > ( SELECT losal FROM SALGRADE WHERE grade = 5 ) 
    AND sal < ( SELECT hisal FROM SALGRADE WHERE grade = 5 );

--8.查询每个职位的员工数量
SELECT
    job AS 职位,
    count( empno ) AS 数量 
FROM
    emp 
GROUP BY
    job;

--9.分别统计每个部门的薪资成本。
SELECT
    d.dname AS 部门,
    a.total AS 薪资成本 
FROM
    ( SELECT deptno, sum( sal ) AS total FROM emp GROUP BY deptno ) a
    LEFT JOIN dept d ON d.deptno = a.deptno;

--10.统计一下总裁管理的员工数量。
SELECT
    count( ename ) AS 总裁管理的员工数量 
FROM
    emp 
WHERE
    mgr = 7839;