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
网友评论