分组前筛选

SELECT last_name , job_id , salary AS sal
FROM employees;

SELECT * FROM employees;


SELECT employee_id , last_name,
salary * 12 'ANNUAL SALARY'
FROM employees;

SELECT* FROM departments 

SELECT  DISTINCT job_id 
FROM employees 


SELECT CONCAT(job_id,',',last_name) AS out_put
FROM employees


SELECT  CONCAT(last_name,'-',first_name) ,salary
FROM  employees
WHERE salary<12000

SELECT department_id,(salary*12) AS '年薪'
FROM employees
WHERE  employee_id=176


# 查询每个工作的最高工资
SELECT MAX(salary) ,job_id
FROM employees
GROUP BY job_id


# 查询每个位置上的部门个数
SELECT  location_id, COUNT(*)
FROM departments
GROUP BY location_id



# 查询每个部门的平均工资，邮箱中包含A字符的
SELECT AVG(salary) ,department_id,email
FROM employees
WHERE email LIKE '%a%'
GROUP BY department_id


# 查询有奖金的每个领导手下员工的最高工资

SELECT MAX(salary),`manager_id`
FROM employees
WHERE commission_pct IS NOT NULL
GROUP BY  manager_id


#添加复杂的筛选条件

#案例：查询那个部门的员工个数大于2  HAVING
SELECT COUNT(department_id),department_id   
FROM employees
GROUP BY  department_id
HAVING  COUNT(department_id)>2


#案例2：查询每个工种有奖金的员工的最高工资》12000的工种编号和最高工资
SELECT job_id,salary
FROM employees
WHERE commission_pct IS NOT NULL
GROUP BY job_id
HAVING salary>12000


#查询领导编号》102的每个领导手下的最低工资》5000的领导编号，以及最低工资

SELECT manager_id,MIN( salary)
FROM employees
WHERE manager_id>102
GROUP BY  manager_id
HAVING MIN( salary)>5000


#按照员工姓名的长度进行分组，查询每一组的员工个数，筛选员工个数>5的有哪些

SELECT COUNT(*),LENGTH(last_name)
FROM employees
GROUP BY LENGTH(last_name)
HAVING COUNT(*)>5

#查询每个部门，每个工种的员工的平均工资,而且按照工资排序
SELECT AVG(salary),department_id,job_id
FROM employees
GROUP BY  department_id, job_id
ORDER BY  AVG(salary) DESC