案例1：查询工资最低的员工信息：last_name，salary（where后标量子查询）
首先查询出最低工资，然后使用该工资进行筛选
SELECT last_name, salary FROM employees WHERE salary =( SELECT min( salary ) FROM employees );
案例2：查询平均工资最低的部门信息（from后表子查询，where后标量子查询）
先查询出部门的平均工资，然后在查哪个部门的平均工资最低，再查哪个部门的平均工资等于最低工资，最后查询部门信息
SELECT
*
FROM
departments
WHERE
department_id =(
SELECT
department_id
FROM
( SELECT department_id, avg( salary ) avg FROM employees GROUP BY department_id ) ad
WHERE
avg =(
SELECT
min( avg )
FROM
( SELECT department_id, avg( salary ) avg FROM employees GROUP BY department_id ) ad
));案例3：查询平均工资最低的部门信息和该部门的平均工资
SELECT
*
FROM
(
SELECT
*
FROM
departments
WHERE
department_id =(
SELECT
department_id
FROM
( SELECT department_id, avg( salary ) avg FROM employees GROUP BY department_id ) ad
WHERE
avg =(
SELECT
min( avg )
FROM
( SELECT department_id, avg( salary ) avg FROM employees GROUP BY department_id ) ad
))) a
JOIN ( SELECT department_id, avg( salary ) avg FROM employees GROUP BY department_id ) b ON a.department_id = b.department_id;案例4：查询平均工资最高的job信息
先查询出平均工资最高的job_id是什么，然后再去job表中查询
SELECT
*
FROM
jobs
WHERE
job_id = (
SELECT
job_id
FROM
employees
GROUP BY
job_id
ORDER BY
avg( salary ) DESC
LIMIT 1)案例5：查询平均工资高于公司平均工资的部门有哪些？
首先查询出公司的平均工资，然后再查询出各部门的平均工资，然后使用having进行筛选
SELECT
department_id,
avg( salary ) ag
FROM
employees
GROUP BY
department_id
HAVING
ag >(
SELECT
avg( salary ) ag
FROM
employees)案例6：查询出公司中所有manager的详细信息
首先查询出公司中所有的manager的id，然后使用in去做遍历
SELECT
*
FROM
employees
WHERE
employee_id IN (
SELECT DISTINCT
manager_id
FROM
employees
WHERE
manager_id IS NOT NULL)案例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，department_id，email，salary
SELECT
last_name,
department_id,
email,
salary
FROM
employees
WHERE
employee_id IN ( SELECT DISTINCT manager_id FROM employees WHERE manager_id IS NOT NULL )
AND department_id = ( SELECT department_id FROM employees GROUP BY department_id ORDER BY avg( salary ) DESC LIMIT 1 );
