1. 查询工资最低的员工信息：last_name，salary

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

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

-- 方法1
-- 1.查各部门平均工资
SELECT AVG(salary),department_id
FROM employees
GROUP BY department_id;
-- 2:查询1中最低的工资
SELECT MIN(ag)
FROM (
SELECT AVG(salary) ag,department_id
FROM employees
GROUP BY department_id
) dev_ag
-- 3.查询哪个部门的平均工资=步骤2
SELECT AVG(salary) ag,department_id
FROM employees
GROUP BY department_id
HAVING AVG(salary)=(
SELECT MIN(ag)
FROM (
SELECT AVG(salary) ag,department_id
FROM employees
GROUP BY department_id
) dev_ag
)
-- 4.查询部门信息
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 AVG(salary) ag,department_id
FROM employees
GROUP BY department_id
) dev_ag
)
)

-- 方法2
-- 1.查各部门平均工资
SELECT AVG(salary),department_id
FROM employees
GROUP BY department_id;
-- 2求出最低平均工资的部门编号
SELECT department_id
FROM employees
GROUP BY department_id
ORDER BY AVG(salary) ASC
LIMIT 1;

-- 3查询部门信息
SELECT *
FROM departments
WHERE department_id=(
SELECT department_id
FROM employees
GROUP BY department_id
ORDER BY AVG(salary) ASC
LIMIT 1
)

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

-- 方法1
SELECT departments.* ,dev_ag.ag
FROM departments
INNER JOIN (
SELECT AVG(salary) ag,department_id
FROM employees
GROUP BY department_id
) dev_ag
WHERE departments.department_id=(
SELECT department_id
FROM employees
GROUP BY department_id
ORDER BY AVG(salary) ASC
LIMIT 1
)
AND departments.department_id=dev_ag.department_id;

-- 方法2

SELECT d.* ,dev_ag.ag
FROM departments d
JOIN (
SELECT AVG(salary) ag,department_id
FROM employees
GROUP BY department_id
ORDER BY AVG(salary) ASC
LIMIT 1
) dev_ag
ON d.department_id=dev_ag.department_id;

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

SELECT jobs.*
FROM jobs
INNER JOIN (
SELECT AVG(salary),job_id
FROM employees
GROUP BY job_id
ORDER BY AVG(salary) DESC
LIMIT 1
) dev_ag
WHERE jobs.job_id =dev_ag.job_id;

-- 方法2 ：如果不用查AVG(salary)，可以不用连接查询
SELECT jobs.*
FROM jobs
WHERE jobs.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) aa,ee.department_id
FROM employees ee
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的详细信息

-- 1.查询所有mannager涉及到到员工编号
SELECT manager_id
FROM employees;
-- 2
SELECT *
FROM employees
WHERE employee_id IN( -- =ANY
SELECT DISTINCT manager_id
FROM employees
);

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

SELECT MIN(salary)
FROM employees
WHERE department_id=(
SELECT department_id
FROM employees
GROUP BY department_id
ORDER BY MAX(salary)
LIMIT 1
);

8. 查询平均工资最高的部门的manager的详细信息：last_name，departmant_id，email，salary

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

SELECT e.last_name,d.department_id,e.email,e.salary
FROM employees e
INNER JOIN departments d
ON d.manager_id=e.employee_id
WHERE d.department_id=(
SELECT department_id
FROM employees
GROUP BY department_id
ORDER BY AVG(salary) DESC
LIMIT 1
);