员工练习三

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

hive> select t1.DEPTNO
    > ,t1.DEPTNAME
    > ,t1.DEPTADDR
    > ,t2.cnt
    > from dept t1
    > left join(
    >   select DEPTNO
    >   ,count(distinct EMPNO)as cnt
    >   from emp
    >   group by DEPTNO
    > )t2 on t1.DEPTNO=t2.DEPTNO;
OK
10	ACCOUNTING	NEW YORK	3
10	ACCOUNTING	shanghai	3
20	RESEARCH	DALLAS	    5
30	SALES	    CHICAGO    	6
40	OPERATIONS	BOSTON	    NULL
Time taken: 37.12 seconds, Fetched: 5 row(s)

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

hive> select  JOB
    >         ,min(SAL) as min_sal
    > from emp
    > group by JOB;
OK
ANALYST	    3000
CLERK	    800
MANAGER	    2450
PRESIDENT	5000
SALESMAN	1250
Time taken: 20.941 seconds, Fetched: 5 row(s)

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

hive> select  DEPTNO
    >         ,min(12*SAL + nvl(BONUS,0)) as min_sal_bonus
    > from emp
    > where JOB = 'MANAGER'
    > group by DEPTNO;
OK
10	29400
20	35700
30	34200
Time taken: 21.305 seconds, Fetched: 3 row(s)

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

hive>  select  EMPNO
    >         ,ENAME
    >         ,12*SAL as year_sal
    > from emp
    > order by year_sal;
OK
7369	SMITH	9600
7900	JAMES	11400
7876	ADAMS	13200
7521	WARD	15000
7654	MARTIN	15000
7934	MILLER	15600
7844	TURNER	18000
7499	ALLEN	19200
7782	CLARK	29400
7698	BLAKE	34200
7566	JONES	35700
7788	SCOTT	36000
7902	FORD	36000
7839	KING	60000
Time taken: 20.282 seconds, Fetched: 14 row(s)

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

hive> select  t1.ENAME
    >         ,t1.SAL
    > from (
    >     select  ENAME
    >             ,SAL
    >             ,row_number() over(partition by DEPTNO order by SAL desc) as rn
    >     from emp
    > ) t1 where t1.rn <=2;
OK
KING	5000
CLARK	2450
SCOTT	3000
FORD	3000
BLAKE	2850
ALLEN	1600
Time taken: 21.046 seconds, Fetched: 6 row(s)

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

hive> select  EMPNO
    >         ,ENAME
    >         ,datediff('2018-12-12',HIREDATE) as hire_days
    > from emp;
OK
7369	SMITH	13874
7499	ALLEN	13809
7521	WARD	13807
7566	JONES	13768
7654	MARTIN	13589
7698	BLAKE	13739
7782	CLARK	13700
7788	SCOTT	11560
7839	KING	13539
7844	TURNER	13609
7876	ADAMS	11526
7900	JAMES	13523
7902	FORD	13523
7934	MILLER	13472
Time taken: 0.032 seconds, Fetched: 14 row(s)