关联，分组练习

--查询出各个区域名和设在此区域的部门数量
select r.id,r.name,count(d.id) from s_dept d right join s_region r on d.region_id = r.id group by r.id,r.name;

--查询出客户及客户的订单数
select c.id,c.name,count(o.id) from s_ord o right join s_customer c on o.customer_id = c.id group by c.id,c.name;

--查询出订单数超过一个的客户编号，客户名称和电话
select c.id,c.name,c.phone from s_customer c left join s_ord o on o.customer_id = c.id group by c.id,c.name,c.phone having count(o.id) > 1;

--查询平均工资超过1400的部门编号
select dept_id from s_emp group by dept_id having avg(salary) > 1400;

--统计本公司的员工数
select count(*) from s_emp;

--统计本公司的职位个数
select count(distinct title) from s_emp;

--统计各个部门的员工数，按照员工数降序排列
select dept_id,count(id) from s_emp group by dept_id order by count(id) desc;

--统计各个部门的名称和员工数，按照员工数降序排列
select d.id,d.name,count(e.id) from s_emp e join s_dept d on e.dept_id = d.id group by d.id,d.name order by count(e.id) desc;

--查询出各个区域以及本区域工作的员工数，并且按照员工数的降序排列
select r.id,r.name,count(e.id) from s_emp e join s_dept d on e.dept_id = d.id join s_region r on d.region_id = r.id group by r.id,r.name order by count(e.id) desc;

--查询出各职位的员工数量
select title,count(e.id) from s_emp group by title;

--查询出各个部门中工资超过1200的员工数量
select dept_id,count(id) from s_emp where salary > 1200 group by dept_id;

--查询出人数超过3个员工的部门编号和部门名称
select d.id,d.name,count(*) from s_dept d join s_emp e on e.dept_id = d.id group by d.id,d.name having count(e.id) > 3;