5.经典例题

#1.查询工资最低的员工信息：last_name,salsry

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

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

①SELECT DISTINCT department_id
FROM employees
GROUP BY department_id
ORDER BY AVG(salary)
LIMIT 1;
②SELECT *
FROM departments
WHERE department_id=(
SELECT DISTINCT department_id
FROM employees
GROUP BY department_id
ORDER BY AVG(salary)
LIMIT 1
);

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

①SELECT DISTINCT department_id
FROM employees
GROUP BY department_id
ORDER BY AVG(salary)
LIMIT 1;
②SELECT DISTINCT department_id,AVG(salary)
FROM employees
GROUP BY department_id
ORDER BY AVG(salary)
LIMIT 1

③SELECT d.*,ag
FROM departments d
JOIN (
SELECT DISTINCT department_id,AVG(salary) ag
FROM employees
GROUP BY department_id
ORDER BY AVG(salary)
LIMIT 1
)ag_dep
ON d.department_id= ag_dep.department_id

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

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

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

SELECT e.*
FROM employees e
WHERE e.employee_id IN( #或者=any（
SELECT DISTINCT manager_id
FROM employees)

#6.各部门中 最高工资中的最低的那个部门的 最低工资

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

#7.查询平均工资最高的部门的 manager的详细信息：last_name，department_id,email,salary

①SELECT department_id
FROM employees
GROUP BY department_id
ORDER BY AVG(salary) DESC
LIMIT 1
② SELECT last_name,d.department_id,email,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);