黑猴子的家：mysql 子查询巩固练习

1、查询工资最低的员工信息: last_name, salary

（1）查询公司的最低工资

SELECT MIN(salary)
FROM employees

（2）查询员工信息，满足 salary=①

SELECT last_name,salary
FROM employees
WHERE salary=(
    SELECT MIN(salary)
    FROM employees
);

2、查询平均工资最低的部门信息

（1）查询每个部门的平均工资

SELECT AVG(salary),department_id
FROM employees
GROUP BY department_id

（2）查询①结果中avg(salary)字段中的最低值

SELECT MIN(ag_sal)
FROM (
    SELECT AVG(salary) ag_sal,department_id
    FROM employees
    GROUP BY department_id
) ag_dep

（3）查询部门编号，满足平均工资=②结果

SELECT department_id,AVG(salary)
FROM employees
GROUP BY department_id
HAVING AVG(salary)=(
    SELECT MIN(ag_sal)
    FROM (
        SELECT AVG(salary) ag_sal,department_id
        FROM employees
        GROUP BY department_id
    ) ag_dep
)

（4）查询部门信息，满足 department_id=③

SELECT d.*
FROM departments d
INNER JOIN (
    SELECT department_id,AVG(salary)
    FROM employees
    GROUP BY department_id
    HAVING AVG(salary)=(
        SELECT MIN(ag_sal)
        FROM (
            SELECT AVG(salary) ag_sal,department_id
            FROM employees
            GROUP BY department_id
        ) ag_dep
    )
)  m ON d.department_id = m.department_id

3、查询平均工资最低的部门信息和该部门的平均工资

SELECT d.*,m.ag
FROM departments d
INNER JOIN (
    SELECT department_id,AVG(salary) ag
    FROM employees
    GROUP BY department_id
    HAVING AVG(salary)=(
        SELECT MIN(ag_sal)
        FROM (
            SELECT AVG(salary) ag_sal,department_id
            FROM employees
            GROUP BY department_id

        ) ag_dep
    )
)  m ON d.department_id = m.department_id

4、查询平均工资最高的 job 信息

（1）查询每个job的平均工资

SELECT  AVG(salary),job_id
FROM employees
GROUP BY job_id

（2）查询①结果中的 avg(salary)的最高值

SELECT MAX(ag_sal)
FROM (
    SELECT  AVG(salary) ag_sal,job_id
    FROM employees
    GROUP BY job_id
) ag_job

（3）查询每个工种的平均工资，满足 平均工资=②

SELECT  AVG(salary) ag_sal,job_id
    FROM employees
    GROUP BY job_id
    HAVING ag_sal=(
        SELECT MAX(ag_sal)
        FROM (
            SELECT  AVG(salary) ag_sal,job_id
            FROM employees
            GROUP BY job_id
        ) ag_job
    )

（4）查询工种表和③连接

SELECT j.*,ag.ag_sal
FROM jobs j,(
        SELECT  AVG(salary) ag_sal,job_id
        FROM employees
        GROUP BY job_id
        HAVING ag_sal=(
            SELECT MAX(ag_sal)
            FROM (
                SELECT  AVG(salary) ag_sal,job_id
                FROM employees
                GROUP BY job_id
            ) ag_job
        )
)ag
WHERE ag.job_id=j.job_id;

5、查询平均工资高于公司平均工资的部门有哪些?

（1）查询公司的平均工资

SELECT AVG(salary)
FROM employees

（2）查询每个部门的平均工资，并且平均工资>①

SELECT AVG(salary),department_id
FROM employees
GROUP BY department_id
HAVING AVG(salary)>(
    SELECT AVG(salary)
    FROM employees
)

6、查询平均工资最高的部门的 manager 的详细信息: last_name, department_id, email, salary

（1）查询平均工资最高的部门编号

SELECT department_id,AVG(salary) ag
    FROM employees
    GROUP BY department_id
    HAVING AVG(salary)=(
        SELECT MAX(ag_sal)
        FROM (
            SELECT AVG(salary) ag_sal,department_id
            FROM employees
            GROUP BY department_id
        ) ag_dep
    )

三表连接查询方式实现

SELECT last_name, e.department_id, email, salary
FROM employees e,departments d,(
    SELECT department_id,AVG(salary) ag
    FROM employees
    GROUP BY department_id
    HAVING AVG(salary)=(
        SELECT MAX(ag_sal)
        FROM (
            SELECT AVG(salary) ag_sal,department_id
            FROM employees
            GROUP BY department_id
        ) ag_dep
    )
) a
WHERE e.employee_id = d.manager_id
AND d.department_id = a.department_id