#1.查询平均工资最低的部门
mysql中聚合函数是不能嵌套使用的
所以,这样的代码是错误的,无法执行
SELECT MIN(AVG(salary)) FROM employees GROUP BY department_id
先查询到最低的部门平均工资
- 方法:在FROM中声明子查询(查询的部门平均工资生成新的表,然后再select ...from(新生成的表),即可对新生成的表进行查询)
SELECT MIN(avg_salary) FROM( SELECT AVG(salary) avg_salary #需要给AVG(salary)字段生成别名,因为不能在MIN里面再套AVG FROM employees GROUP BY department_id ) dep_avg_salary #新生成的表的别名
再接着,查询出平均工资最低的部门
在HAVING中使用子查询
- 方法一:
SELECT department_id FROM employees GROUP BY department_id HAVING AVG(salary)=( SELECT MIN(avg_salary) FROM( SELECT AVG(salary) avg_salary FROM employees GROUP BY department_id ) dep_avg_salary )
- 方法二:用ALL关键字,就不用找出最低的部门平均工资。
SELECT department_id FROM employees GROUP BY department_id HAVING AVG(salary)<=ALL( SELECT AVG(salary) FROM employees GROUP BY department_id )
#2.查询员工中工资大于所在部门平均工资的员工的last_name,salary和其department_id
- 方法一:使用相关子查询,用WHERE关键字
SELECT last_name,department_id,salary FROM employees e1 WHERE salary>( SELECT AVG(salary) FROM employees e2 WHERE e2.`department_id`=e1.`department_id` )
- 方法二:在FROM中声明子查询
from型的子查询,子查询是作为from的一部分,子查询要用()引起来,并且要给这个子查询取别名,把它当成一张“临时的虚拟的表”来使用
(由于FROM后面有两个表,里面生成的那个新表也含有字段department_id,所以在查询的时候要声明一下departemnt_id是查询的哪个表里面的)
SELECT last_name,e1.department_id,salary FROM employees e1,( SELECT AVG(salary) avg_sal,department_id FROM employees GROUP BY department_id)e2 WHERE e1.`department_id`=e2.`department_id` AND e1.`salary`>e2.`avg_sal`
#3.查询员工的id,salary,按照depertment_name排序(department_name在departments表中)
在ODER BY后面跟子查询
SELECT e.employee_id,e.salary,c.department_name FROM employees e,departments c WHERE e.`department_id`=c.`department_id` ORDER BY( SELECT department_name FROM departments d WHERE e.`department_id`=d.`department_id` )ASC
#4.若employees 表中employee_id与job_history表中的employee_id相同的数目不小于2,输出这些相同id的员工的employee_id,last_name和其job_id
SELECT employee_id,last_name,job_id FROM employees e WHERE 2<=( SELECT COUNT(*) FROM job_history WHERE employee_id=e.`employee_id` )
#5.查询公司管理者的employee_id,和last_name信息
- 方法一:WHERE关键字过滤
SELECT DISTINCT mgr.employee_id,mgr.last_name FROM employees emp,employees mgr WHERE emp.`manager_id`=mgr.`employee_id`
- 方法二:自连接
SELECT DISTINCT mgr.employee_id,mgr.last_name FROM employees emp JOIN employees mgr ON emp.`manager_id`=mgr.`employee_id`
- 方法三:子查询
SELECT employee_id,last_name FROM employees WHERE employee_id IN( SELECT DISTINCT manager_id FROM employees )
- 方法四:EXISTS关键字
SELECT mgr.employee_id,mgr.last_name FROM employees e1 WHERE EXISTS( SELECT *#这块查询什么其实不重要,只要查询得到那么就返回TRUE FROM employees e2 WHERE e1.`employee_id`=e2.`manager_id` )
#6.查询departments表中,不存在于employees表中的部门的department_id和department_name
- 方法一:外连接
SELECT d.department_id,d.department_name FROM departments d LEFT JOIN employees e ON d.`department_id`=e.`department_id` WHERE e.`department_id` IS NULL
- 方法二:NOT EXISTS关键字
SELECT department_id,department_name FROM departments d WHERE NOT EXISTS( SELECT * FROM employees e WHERE d.`department_id`=e.`department_id` #如果存在这样的记录那么就返回false,不要这条记录,如果不存在这样的记录那么就返回TRUE,这条记录就是要查询的记录 );