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

①查询最低的工资

  1. select min(salary) from employees;

②查询last_name,salary，要求salary=①

select last_name,salary from employees where salary=(select min(salary) from employees);

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

方式一：

①各部门的平均工资

select department_id,avg(salary) from employees group by department_id;

②查询①结果上的最低平均工资

select min(ag) from (select department_id,avg(salary) ag from employees group by department_id) ag_dep

③查询哪个部门的平均工资=②

select avg(salary),department_id from employees group by department_id having avg(salary)=(
    select min(ag) from (select department_id,avg(salary) ag from employees group by department_id) ag_dep
)

④查询部门信息

select d.* from departments d where d.department_id=(
select department_id from employees group by department_id having avg(salary)=(
    select min(ag) from (select department_id,avg(salary) ag from employees group by department_id) ag_dep
)
)

方式二

①各部门的平均工资

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

②求出最低平均工资的部门编号

SELECT department_id FROM employees GROUP BY department_id ORDER BY AVG(salary) LIMIT 1;

③查询部门信息

select d.* from departments d where d.department_id=(
SELECT department_id FROM employees GROUP BY department_id ORDER BY AVG(salary) LIMIT 1
)ag_dep
ON d.`department_id`=ag_dep.department_id;

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

①各部门的平均工资

select avg(salary),department_id from employees group by department_id;

②求出最低平均工资的部门编号

select avg(salary),department_id from employees group by department_id order by avg(salary) limit 1

③查询部门信息

select d.*,ag from departments d join (select avg(salary) ag,department_id from employees group by department_id order by avg(salary) limit 1) ag_dep on d.department_id=ag_dep.department_id

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

①查询最高的job的平均工资

select avg(salary),job_id from employees group by job_id order by avg(salary) desc limit 1;

②查询job信息

select j.* from jobs j where j.job_id=(select job_id from employees group by job_id order by avg(salary) desc limit 1);

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

①查询平均工资

select avg(salary) from employees;

②查询每个部门的平均工资

select avg(salary),department_id from employees group by department_id;

③筛选②结果集，满足平均工资>①

select avg(salary),department_id from employees group by department_id having avg(salary)>(select avg(salary) from employees);

6. 查询出公司中所有 manager 的详细信息.

①查询所有manager的员工编号

select distinct manager_id from employees;

②查询详细信息，满足employee_id=①

select * from employees where employee_id=Any(select distinct manager_id from employees);

7.各个部门中 最高工资中最低的那个部门的 最低工资是多少

①查询各部门的最高工资中最低的部门编号

select department_id from employees group by department_id order by max(salary)  limit 1;

②查询①结果的那个部门的最低工资

select min(salary),department_id from employees where department_id=(select department_id from employees group by department_id order by max(salary)  limit 1)

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

①查询平均工资最高的部门编号

select department_id from employees group by department_id order by avg(salary) desc  limit 1

②将employees和departments连接查询，筛选条件是①

SELECT last_name, d.department_id, email, salary  from employees e inner join departments d on d.manager_id=e.manager_id where d.department_id=(select department_id from employees group by department_id order by avg(salary) desc  limit 1);