多表查询（mysql）

-- 1.列出至少有4个员工的部门名称
SELECT 
    dept.deptno AS 部门编号,
    dept.dname AS 部门名称
FROM 
    dept
JOIN 
    emp ON dept.deptno = emp.deptno
GROUP BY 
    dept.deptno, dept.dname
HAVING 
    COUNT(emp.empno) >= 4;

-- 2.列出薪金比 “smith”多的所有员工
SELECT 
    empno AS 雇员编号,
    ename AS 姓名,
    sal AS 工资
FROM 
    emp
WHERE 
    sal > (SELECT sal FROM emp WHERE ename = 'Smith');

-- 3.列出所有员工的姓名以及其直接上级的姓名
SELECT
    e1.ename AS 员工姓名,
    e2.ename AS 上级姓名
FROM
    emp e1
LEFT JOIN
    emp e2 ON e1.mgr = e2.empno;
-- 4.列出受雇日期早于其直接上级的所有员工的编号、姓名、部门名称	
SELECT
    e1.empno AS 员工编号,
    e1.ename AS 员工姓名,
    d.dname AS 部门名称
FROM
    emp e1
LEFT JOIN
    emp e2 ON e1.mgr = e2.empno
JOIN
    dept d ON e1.deptno = d.deptno
WHERE
    e1.hiredate < e2.hiredate;
-- 	5.列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门
SELECT
    d.deptno AS 部门编号,
    d.dname AS 部门名称,
    e.empno AS 员工编号,
    e.ename AS 员工姓名,
    e.job AS 职位,
    e.sal AS 工资,
    e.hiredate AS 入职日期
FROM
    dept d
LEFT JOIN
    emp e ON d.deptno = e.deptno
ORDER BY
    d.deptno;
-- 6.列出所有从事“clerk”工作的雇员姓名及部门名称、部门人数
-- 方法1
SELECT
    e.ename AS 员工姓名,
    d.dname AS 部门名称,
    COUNT(e.empno) OVER (PARTITION BY d.deptno) AS 部门人数
FROM
    emp e
LEFT JOIN
    dept d ON e.deptno = d.deptno
WHERE
    e.job = 'CLERK';

-- 方法2
SELECT
    e.ename AS 员工姓名,
    d.dname AS 部门名称,
    (
        SELECT COUNT(*)
        FROM emp e2
        WHERE e2.job = 'CLERK' AND e2.deptno = e.deptno
    ) AS 部门人数
FROM
    emp e
LEFT JOIN
    dept d ON e.deptno = d.deptno
WHERE
    e.job = 'CLERK';

-- 7.列出最低薪金大于1500的各种工作及从事此工作的全部雇员人数
SELECT
    job AS 工作,
    COUNT(empno) AS 雇员人数
FROM
    emp
GROUP BY
    job
HAVING
    MIN(sal) > 1500;
		
-- 8.列出在部门'sales'(销售部)工作的员工的姓名，假定不知道销部的部门编号
SELECT
    e.ename AS 员工姓名
FROM
    emp e
JOIN
    dept d ON e.deptno = d.deptno
WHERE
    d.dname = '销售部';

-- 9列出薪金高于公司平均薪金的雇员姓名、所部门名称、领导姓名、雇员的工资等级
SELECT
    e1.ename AS 员工姓名,
    d.dname AS 部门名称,
    e2.ename AS 领导姓名,
    e1.sal AS 工资,
    sg.grade AS 工资等级
FROM
    emp e1
LEFT JOIN
    emp e2 ON e1.mgr = e2.empno
JOIN
    dept d ON e1.deptno = d.deptno
JOIN
    salgrade sg ON e1.sal BETWEEN sg.losal AND sg.hisal
WHERE
    e1.sal > (SELECT AVG(sal) FROM emp);
-- 10.列出与"smith"从事相同工作的所有员工的部门名称
SELECT DISTINCT
    d.dname AS 部门名称
FROM
    emp e
JOIN
    dept d ON e.deptno = d.deptno
WHERE
    e.job = (SELECT job FROM emp WHERE ename = 'SMITH');
-- 11.列出薪金等于部门30中员工的薪金的所有员工的姓名和薪金
SELECT
    ename AS 员工姓名,
    sal AS 薪金
FROM
    emp
WHERE
    sal IN (SELECT sal FROM emp WHERE deptno = 30);

-- 12.列出薪金高于部门30所有员工薪金的员工姓名、薪金和部门名称
SELECT
    e.ename AS 员工姓名,
    e.sal AS 薪金,
    d.dname AS 部门名称
FROM
    emp e
JOIN
    dept d ON e.deptno = d.deptno
WHERE
    e.sal > (SELECT MAX(sal) FROM emp WHERE deptno = 30);
-- 13.列出每个部门工作的员工数量、平均工资
SELECT
    d.deptno AS 部门编号,
    d.dname AS 部门名称,
    COUNT(e.empno) AS 员工数量,
    AVG(e.sal) AS 平均工资
FROM
    dept d
LEFT JOIN
    emp e ON d.deptno = e.deptno
GROUP BY
    d.deptno, d.dname;
-- 14.列出所有员工的姓名、部门名称和工资
SELECT
    e.ename AS 员工姓名,
    d.dname AS 部门名称,
    e.sal AS 工资
FROM
    emp e
LEFT JOIN
    dept d ON e.deptno = d.deptno;
-- 15.列出所有部门的详细信息和部门人数
SELECT
    d.deptno AS 部门编号,
    d.dname AS 部门名称,
    d.loc AS 地点,
    COUNT(e.empno) AS 部门人数
FROM
    dept d
LEFT JOIN
    emp e ON d.deptno = e.deptno
GROUP BY
    d.deptno, d.dname, d.loc;
-- 16.列出每种工作的最低工资及从事此工作的雇员姓名
SELECT
    e.job AS 工作,
    MIN(e.sal) AS 最低工资,
    e.ename AS 雇员姓名
FROM
    emp e
GROUP BY
    e.job, e.ename
HAVING
    e.sal = MIN(e.sal);
-- 17.列出各个部门的经理的最低薪金
SELECT
    d.deptno AS 部门编号,
    d.dname AS 部门名称,
    MIN(e.sal) AS 经理最低薪金
FROM
    dept d
JOIN
    emp e ON d.deptno = e.deptno
WHERE
    e.job = 'MANAGER'
GROUP BY
    d.deptno, d.dname;
-- 18.列出所有员工的年薪，按年薪从低到高进行排序
SELECT
    e.ename AS 员工姓名,
    e.sal * 12 AS 年薪
FROM
    emp e
ORDER BY
    年薪;
-- 19.列出所有员工的领导信息，要求领导的薪水要超过3000
SELECT
    e1.ename AS 员工姓名,
    e2.ename AS 领导姓名,
    e2.sal AS 领导薪水
FROM
    emp e1
LEFT JOIN
    emp e2 ON e1.mgr = e2.empno
WHERE
    e2.sal > 3000;
-- 20.求出部门名称中，带“S”字符的部门员工的工资总和、部门人数
SELECT
    d.dname AS 部门名称,
    SUM(e.sal) AS 工资总和,
    COUNT(e.empno) AS 部门人数
FROM
    dept d
LEFT JOIN
    emp e ON d.deptno = e.deptno
WHERE
    d.dname LIKE '%S%'
GROUP BY
    d.dname;