HIVE-SQL员工练习

13．列出在每个部门工作的员工数量、平均工资和平均服务期限。

select  DEPTNO,count(ENAME) as name ,avg(sal) as avg_sal,
avg(datediff(current_timestamp,HIREDATE)) as avg_day from emp group by DEPTNO; 

14．列出所有员工的姓名、部门名称和工资。

select e.ENAME,d.DEPTNAME,e.sal from emp e left join dept d on e.DEPTNO=d.DEPTNO;

15．列出所有部门的详细信息和部门人数。

select * from dept d 
left join (select DEPTNO,count(ENAME) as cn from emp group by DEPTNO ) as e on e.DEPTNO=d.DEPTNO;

16．列出各种工作的最低工资。

select job,min(sal)  from emp group by job;

17．列出各个部门的MANAGER（经理）的最低薪金。

select DEPTNO,min(SAL)  FROM emp where job ='MANAGER' group by DEPTNO;

18．列出所有员工的年工资,按年薪从低到高排序。

select * from (select ENAME,sal*12+NVL(BONUS,0) as year_sal from emp) e order by e.year_sal asc;

19．列出每个部门薪水前两名最高的人员名称以及薪水。

select ename,sal,deptno from 
(select ename,sal,deptno,row_number() over(partition by deptno order by sal desc) as num from emp ) as m where m.num <=2;

20．列出每个员工从受雇开始到2018-12-12 为止共受雇了多少天。

select ENAME,datediff("2018-12-12",HIREDATE) as t from emp order by t;