mysql 查询练习题

1. 每个部门的人数，根据人数降序排序
   select deptno,count(*) c from emp
   group by deptno order by c desc;

2. 每个部门中，每个主管的手下人数
   select deptno,mgr,count(*) from emp where mgr is not null
   group by deptno,mgr;

3. 每种工作的平均工资
   select job,avg(sal) from emp group by job;

4. 每年的入职人数
   select extract(year from hiredate) year,count() from emp
   group by year;
   select extract(year from hiredate) e ,count() from emp group by e;

5. 少于等于3个人的部门信息(考虑40号部门)

   select d.*
   from emp e right join dept d
   on e.deptno=d.deptno
   group by d.deptno
   having count(*)<=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