员工练习一

员工信息表emp：
字段：员工id,员工名字,工作岗位,部门经理,受雇日期,薪水,奖金,部门编号
英文名：EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,BONUS,DEPTNO

部门信息表dept：
字段：部门编号,部门名称,部门地点
英文名：DEPTNO,DEPTNAME,DEPTADDR

hive> create database test7;
OK
Time taken: 0.041 seconds
hive> use test7;
OK
Time taken: 0.01 seconds
hive> create table emp(
    >     EMPNO int
    >     ,ENAME string
    >     ,JOB string
    >     ,MGR int
    >     ,HIREDATE string
    >     ,SAL int
    >     ,BONUS int
    >     ,DEPTNO int
    > ) 
    > row format delimited
    > fields terminated by ',';
OK
Time taken: 0.06 seconds
hive> create table dept(
    >     DEPTNO int
    >     ,DEPTNAME string
    >     ,DEPTADDR string
    > ) 
    > row format delimited
    > fields terminated by ',';
OK
Time taken: 0.033 seconds
hive> load data local
    > inpath '/usr/local/soft/data/emp.txt' into table emp;
Loading data to table test7.emp
Table test7.emp stats: [numFiles=1, totalSize=676]
OK
Time taken: 0.553 seconds
hive> load data local
    > inpath '/usr/local/soft/data/dept.txt' into table dept;
Loading data to table test7.dept
Table test7.dept stats: [numFiles=1, totalSize=106]
OK
Time taken: 0.517 seconds

hive> select * from emp;
OK
7369	SMITH	CLERK	7902	1980-12-17	800	NULL   20
7499	ALLEN	SALESMAN	7698	1981-02-20	1600   300	30
7521	WARD	SALESMAN	7698	1981-02-22	1250   500	30
7566	JONES	MANAGER	7839	1981-04-02	2975	NULL   20
7654	MARTIN	SALESMAN	7698	1981-09-28	1250   1400	30
7698	BLAKE	MANAGER	7839	1981-05-01	2850	NULL   30
7782	CLARK	MANAGER	7839	1981-06-09	2450	NULL   10
7788	SCOTT	ANALYST	7566	1987-04-19	3000	NULL   20
7839	KING	PRESIDENT	NULL	1981-11-17	5000   NULL	10
7844	TURNER	SALESMAN	7698	1981-09-08	1500   30
7876	ADAMS	CLERK	7788	1987-05-23	1100	NULL   20
7900	JAMES	CLERK	7698	1981-12-03	950	NULL   30
7902	FORD	ANALYST	7566	1981-12-03	3000	NULL   20
7934	MILLER	CLERK	7782	1982-01-23	1300	NULL   10
Time taken: 0.029 seconds, Fetched: 14 row(s)
hive> select * from dept;
OK
10	ACCOUNTING	NEW YORK
10	ACCOUNTING	shanghai
20	RESEARCH	DALLAS
30	SALES	CHICAGO
40	OPERATIONS	BOSTON
Time taken: 0.022 seconds, Fetched: 5 row(s)

薪水 = SAL 
薪金 = 12*SAL加奖金
年工资 = 年薪 = 12*SAL

 1． 列出至少有一个员工的所有部门。

hive> select t1.DEPTNO
    > ,t1.DEPTNAME
    > ,t1.DEPTADDR
    > from dept t1
    > join(
    >   select DEPTNO
    > ,count(EMPNO)as cnt
    > from emp
    > group by DEPTNO
    > having cnt>=1
    > )t2 on t1.DEPTNO=t2.DEPTNO
    > ;
OK
10	ACCOUNTING	NEW YORK
10	ACCOUNTING	shanghai
20	RESEARCH	DALLAS
30	SALES	    CHICAGO
Time taken: 40.087 seconds, Fetched: 4 row(s)

hive> select t1.DEPTNO
    > ,t1.DEPTNAME
    > ,t1.DEPTADDR
    > from dept t1
    > where DEPTNO in
    > (
    >     select DEPTNO
    > from emp
    > group by DEPTNO
    > having count(EMPNO)>=1
    > );
OK
10	ACCOUNTING	NEW YORK
10	ACCOUNTING	shanghai
20	RESEARCH	DALLAS
30	SALES	    CHICAGO
Time taken: 38.016 seconds, Fetched: 4 row(s)

2． 列出薪金比“SMITH”多的所有员工。

-- 在Hive的where条件中的不等式（> < >= <=）比较不可以使用子查询

-- MySQL 把nvl 换成 if 加 isnull 即可实现
select  EMPNO
        ,ENAME
        ,12*SAL + if(isnull(BONUS),0,BONUS) as sal_bonus
from emp
where ENAME != 'SMITH'
and 12*SAL + if(isnull(BONUS),0,BONUS) > (
    select  12*SAL + if(isnull(BONUS),0,BONUS)
    from emp
    where ENAME = 'SMITH'
);

hive> select  t1.EMPNO
    >         ,t1.ENAME
    >         ,t1.sal_bonus
    > from(
    >     select  EMPNO
    >             ,ENAME
    >             ,12*SAL + nvl(BONUS,0) as sal_bonus
    >             ,1 as tmp_id
    >     from emp
    >     where ENAME != 'SMITH'
    > ) t1 join(
    >     select  12*SAL + nvl(BONUS,0) as sal_bonus
    >             ,1 as tmp_id
    >     from emp
    >     where ENAME = 'SMITH'
    > ) t2 on t1.tmp_id = t2.tmp_id
    > where t1.sal_bonus > t2.sal_bonus;
OK
7499	ALLEN	19500
7521	WARD	15500
7566	JONES	35700
7654	MARTIN	16400
7698	BLAKE	34200
7782	CLARK	29400
7788	SCOTT	36000
7839	KING	60000
7844	TURNER	18000
7876	ADAMS	13200
7900	JAMES	11400
7902	FORD	36000
7934	MILLER	15600
Time taken: 18.485 seconds, Fetched: 13 row(s)

3． 列出所有员工的姓名及其直接上级的姓名。

hive> select  t1.ENAME
    >         ,t2.ENAME
    > from emp t1
    > left join emp t2
    > on t1.MGR = t2.EMPNO;
OK
SMITH	FORD
ALLEN	BLAKE
WARD	BLAKE
JONES	KING
MARTIN	BLAKE
BLAKE	KING
CLARK	KING
SCOTT	JONES
KING	NULL
TURNER	BLAKE
ADAMS	SCOTT
JAMES	BLAKE
FORD	JONES
MILLER	CLARK
Time taken: 17.98 seconds, Fetched: 14 row(s)

  4.列出受雇日期早于其直接上级的所有员工。

hive> select  t1.ENAME
    >         ,t1.HIREDATE
    >         ,t2.ENAME
    >         ,t2.HIREDATE
    > from emp t1
    > left join emp t2
    > on t1.MGR = t2.EMPNO
    > where t1.HIREDATE < t2.HIREDATE;
OK
SMITH	1980-12-17	FORD	1981-12-03
ALLEN	1981-02-20	BLAKE	1981-05-01
WARD	1981-02-22	BLAKE	1981-05-01
JONES	1981-04-02	KING	1981-11-17
BLAKE	1981-05-01	KING	1981-11-17
CLARK	1981-06-09	KING	1981-11-17
Time taken: 18.101 seconds, Fetched: 6 row(s)

 5． 列出部门名称和这些部门的员工信息，同时列出哪些没有员工的部门。 

hive> select  t1.DEPTNO
    >         ,t1.DEPTNAME
    >         ,t2.EMPNO
    >         ,t2.ENAME
    > from (
    >     select  distinct
    >             DEPTNO
    >             ,DEPTNAME
    >     from dept
    > ) t1 left join emp t2
    > on t1.DEPTNO = t2.DEPTNO;
OK
10	ACCOUNTING	7782	CLARK
10	ACCOUNTING	7839	KING
10	ACCOUNTING	7934	MILLER
20	RESEARCH	7369	SMITH
20	RESEARCH	7566	JONES
20	RESEARCH	7788	SCOTT
20	RESEARCH	7876	ADAMS
20	RESEARCH	7902	FORD
30	SALES	    7499	ALLEN
30	SALES	    7521	WARD
30	SALES	    7654	MARTIN
30	SALES	    7698	BLAKE
30	SALES	    7844	TURNER
30	SALES	    7900	JAMES
40	OPERATIONS	NULL	NULL
Time taken: 38.376 seconds, Fetched: 15 row(s)