#查询每个工种的最高工资 select max(salary), job_id from employees group by job_id;
#查询每个位置的部门个数 select count(*), location_id 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 select count(*), department_id from employees group by department_id having count(*) > 2;
#查询每个工种有奖金的员工的最高工资>12000的工种编号和最高工资 select job_id, max(salary) from employees where commission_pct is not null group by job_id having max(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 length(concat(first_name, last_name)) 长度, count(*) from employees group by length(concat(first_name, last_name)) having count(*) > 5;
#查询每个部门,每个工种员工的平均工资 select avg(salary), department_id, job_id from employees group by department_id, job_id;
#查询每个部门,每个工种员工的平均工资,平均工资大于10000,并且按平均工资的高低显示出来 select avg(salary), department_id, job_id from employees where department_id is not null group by department_id, job_id having avg(salary) > 10000 order by avg(salary) desc;
|