美文网首页数据库
黑猴子的家:mysql 子查询巩固练习

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

作者: 黑猴子的家 | 来源:发表于2019-02-27 08:44 被阅读1次
    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
    

    相关文章

      网友评论

        本文标题:黑猴子的家:mysql 子查询巩固练习

        本文链接:https://www.haomeiwen.com/subject/abmayqtx.html