###练习:
1. 每个部门的人数,根据人数排序
select deptno,count(*) c from emp
group by deptno order by c;
2. 每个部门中,每个主管的手下人数
select deptno,mgr,count(*) from emp where mgr is not null group by deptno,mgr;
select d.deptno,e.mgr,count(e.ename)
from emp e right join dept d
on e.deptno=d.deptno
where e.mgr is not null
group by d.deptno,e.mgr;
- 查询每个部门的人数(结果中包含40号部门)
select d.deptno,count(e.ename)
from emp e right join dept d
on e.deptno=d.deptno
group by d.deptno;
3. 每种工作的平均工资
select job,avg(sal) from emp group by job;
4. 每年的入职人数
select extract(year from hiredate) y,count(*) from emp group by y;
5. 少于等于3个人的部门信息
select d.*,count(e.ename) c
from emp e right join dept d
on e.deptno=d.deptno
group by d.deptno
having c<=3;
6. 拿最低工资的员工信息
select * from emp where sal=(select min(sal) from emp);
7. 只有一个下属的主管信息
-查询只有一个下属的主管编号
select mgr from emp
where mgr is not null group by mgr
having count(*)=1;
-通过得到的每个主管编号查询主管的详情
select * from emp where empno in (select mgr from emp
where mgr is not null group by mgr
having count(*)=1);
8. 平均工资最高的部门编号
-得到最高的平均工资
select avg(sal) a from emp group by deptno order by a desc limit 0,1;
-通过平均工资值去查询对应的部门编号
select deptno from emp group by deptno
having avg(sal)=(select avg(sal) a from emp group by deptno order by a desc limit 0,1);
9. 下属人数最多的人,查询其个人信息
-得到最多的下属人数
select count(*) c from emp group by mgr order by c desc limit 0,1;
-通过人数查询对应的领导编号
select mgr from emp group by mgr having count(*)=(select count(*) c from emp group by mgr order by c desc limit 0,1);
-通过领导编号得到个人信息
select * from emp where empno in(select mgr from emp group by