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