案例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 );
