-- 1.查询基本工资高于smith的员工
SELECT
    *
FROM
    emp
WHERE
    sal > (SELECT
            sal
        FROM
            emp
        WHERE
            ename = \'smith\');

select * from emp;
-- 2.查询clerk的姓名及其所属部门名称和部门人数
SELECT
    e.ename, d.dname, job, COUNT(e.empno)
FROM
    emp AS e
        LEFT JOIN
    dept AS d ON d.deptno = e.deptno
WHERE
    e.empno IN (SELECT
            empno
        FROM
            emp
        WHERE
            job = \'clerk\')
GROUP BY d.deptno;

-- 3.查询各部门最高工资的员工：empno,ename,sal,deptno
SELECT
    *
FROM
    emp AS e
        LEFT JOIN
    dept AS d ON d.deptno = e.deptno
WHERE
    e.sal IN (SELECT
            MAX(sal)
        FROM
            emp AS e
                LEFT JOIN
            dept AS d ON d.deptno = e.deptno
        GROUP BY e.deptno)
;
select * from emp as e left join dept as d on d.deptno=e.deptno;
-- 4.工资高于同职位的平均工资的员工信息

SELECT
    *
FROM
    emp AS e1
        LEFT JOIN
    (SELECT
        AVG(e2.sal) AS avgsal, job
    FROM
        emp AS e2
    GROUP BY job) AS t ON e1.job = t.job
WHERE
    e1.sal > t.avgsal;
-- 5.查询各部门平均工资等级：deptno,平均工资,grade,dname

SELECT
    d.deptno,平均工资,grade,dname
FROM
    (SELECT
        AVG(sal) AS 平均工资, deptno
    FROM
        emp
    GROUP BY deptno) t
        LEFT JOIN
    salgrade AS s ON t.平均工资 BETWEEN s.losal AND s.hisal
    left join dept as d on d.deptno=t.deptno;


-- 6.查询平均工资等级最高的部门名称
SELECT
   d.deptno, d.dname
FROM
    (SELECT
    deptno,max(grade)
FROM
    (SELECT
        AVG(sal) AS 平均工资, deptno
    FROM
        emp
    GROUP BY deptno) t
        LEFT JOIN
    salgrade AS s ON t.平均工资 BETWEEN s.losal AND s.hisal
    ) AS b
left join dept as d on d.deptno=b.deptno;

-- 7.查询工资等级最低的员工的所属部门
SELECT
    d.dname, e.ename, s.grade
FROM
    emp AS e
        LEFT JOIN
    dept AS d ON e.deptno = d.deptno
        LEFT JOIN
    salgrade AS s ON sal BETWEEN s.losal AND s.hisal
WHERE
    s.grade IN (SELECT
            MIN(grade)
        FROM
            salgrade);
-- 8.查询基本工资比普通员工最高工资还要高的管理者
SELECT
    *
FROM
    emp
WHERE
    sal > (SELECT
            MAX(sal)
        FROM
            emp
        WHERE
            empno NOT IN (SELECT DISTINCT
                    mgr
                FROM
                    emp
                WHERE
                    mgr IS NOT NULL))
        AND empno IN (SELECT
            empno
        FROM
            emp
        WHERE
            empno IN (SELECT DISTINCT
                    mgr
                FROM
                    emp
                WHERE
                    mgr IS NOT NULL));


-- 9.查询基本工资高于chicago地区所有员工的员工姓名及其所在地区
SELECT
    ename, loc
FROM
    emp
        LEFT JOIN
    dept ON emp.deptno = dept.deptno
WHERE
    sal > ALL (SELECT
            sal
        FROM
            emp
                LEFT JOIN
            dept ON emp.deptno = dept.deptno
        WHERE
            loc = \'chicago\');


-- 10.查询不同职位的最低工资及从事该工作的员工姓名
SELECT
    *
FROM
    emp
WHERE
    (job , sal) IN (SELECT
            job, MIN(sal)
        FROM
            emp
        GROUP BY job);