1.where子句可否使用组函数进行过滤?
不能使用where子句,应该使用having子句
2.查询公司员工工资的最大值,最小值,平均值,总和
SELECT MIN(salary),MAX(salary),SUM(salary),AVG(salary)
FROM employees;
3.查询各job_id的员工工资的最大值,最小值,平均值,总和
SELECT job_id,MIN(salary),MAX(salary),SUM(salary),AVG(salary)
FROM employees
GROUP BY job_id;
4.选择具有各个job_id的员工人数
SELECT job_id,COUNT(*)
FROM employees
GROUP BY job_id;
5.查询员工最高工资和最低工资的差距(DIFFERENCE)
SELECT MAX(salary),MIN(salary),MAX(salary)-MIN(salary) DIFFERENCE
FROM employees;
6.查询各个管理者手下员工的最低工资,其中最低工资不能低于6000,没有管理者的员工不计算在内
SELECT manager_id,MIN(salary)
FROM employees
WHERE manager_id IS NOT NULL
GROUP BY manager_id
HAVING MIN(salary) >6000;