一：将下列数据加载hive表。

员工信息表emp：

字段：员工id,员工名字,工作岗位,部门经理,受雇日期,薪水,奖金,部门编号

英文名：EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,BONUS,DEPTNO

CREATE TABLE emp2 (
    empno INT,
    ename STRING,
    job STRING,
    mgr INT,
    hiredate STRING,
    sal INT,
    bonus INT,
    deptno INT
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ',';

load data local inpath '/opt/datas/emp2.txt' into table emp2;

create table dept2(

DEPTNO int,

DEPTNAME string,

DEPTADDR string

)

row format delimited

fields terminated by ',';

 load data local inpath '/opt/dept2.txt'overwrite into table dept2;

二：使用HQL完成下面需求：（参考答案，也可以自行思考完成）

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

SELECT 
    t1.DEPTNO,
    t2.DEPTNAME,
    t2.DEPTADDR,
    t1.sum_DEPTNO
FROM (
    SELECT 
        DEPTNO,
        COUNT(DEPTNO) AS sum_DEPTNO
    FROM emp2
    GROUP BY DEPTNO
) t1 
JOIN dept2 t2 ON t1.DEPTNO = t2.DEPTNO;

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

SELECT t1.EMPNO, t1.ENAME, t1.sal_bonus
FROM (
    SELECT EMPNO, ENAME, 
           12 * COALESCE(SAL, 0) + COALESCE(BONUS, 0) AS sal_bonus, 
           1 AS num_id 
    FROM emp2
) t1 
JOIN (
    SELECT 12 * COALESCE(SAL, 0) + COALESCE(BONUS, 0) AS sal_bonus, 
           1 AS num_id 
    FROM emp2
    WHERE ENAME = 'SMITH'
) t2 ON t1.num_id = t2.num_id
WHERE t1.sal_bonus > t2.sal_bonus;

3． 列出所有员工的姓名及其直接上级的姓名。(自己连接自己)

SELECT t1.ENAME, t2.ENAME
FROM (
    SELECT ENAME, MGR
    FROM emp2
) t1 
JOIN (
    SELECT EMPNO, ENAME
    FROM emp2
) t2 ON t1.MGR = t2.EMPNO;

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

SELECT t1.ENAME, t1.HIREDATE, t2.ENAME AS mgr_ENAME, t2.HIREDATE
FROM emp2 t1 
JOIN emp2 t2 ON t1.MGR = t2.EMPNO
WHERE t1.HIREDATE < t2.HIREDATE;

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

select  distinct t1.DEPTNO

        ,t1.DEPTNAME

        ,t2.ENAME

from  dept2 t1

left join emp2 t2

on t1.DEPTNO=t2.DEPTNO;

6． 列出所有“CLERK”（办事员）的姓名及其部门名称.

SELECT  distinct t1.ENAME,t1.JOB,t2.DEPTNAME

FROM (select ENAME,JOB,DEPTNO

      from emp2

      where JOB='CLERK')

t1 join dept2 t2

on t1.DEPTNO=t2.DEPTNO;

7． 列出最低薪金大于1500的各种工作。

select job,min(SAL) AS MIN_sal
    > from emp2 group by job
    > having min_sal>1500;

8． 列出在部门“SALES”（销售部）工作的员工的姓名，假定不知道销售部的部门编号

select  ENAME

from emp2

where DEPTNO in (select DEPTNO

                 from dept2

                 where DEPTNAME='SALES');

select t1.ENAME
FROM emp2
t1 join(
select DEPTNO
from dept2
where DEPTNAME='SALES')
2 on t1.DEPTNO=t2.DEPTNO;

9． 列出薪金高于公司平均薪金的所有员工。

select  t1.ENAME,t1.sal_bonus,t2.avg_sal_bonus

from (select  ENAME,12*SAL+nvl(BONUS,0) as sal_bonus,1 as num

      from emp2) t1

join (select  round(avg(12*SAL+nvl(BONUS,0)),2) as avg_sal_bonus,1 as num  

      from emp2)

t2 on t1.num=t2.num

where t1.sal_bonus>t2.avg_sal_bonus;

#扩展

#列出薪金高于部门平均薪金的所有员工。

SELECT t1.ENAME, t1.sal_bonus, t2.avg_sal_bonus, t1.DEPTNO
FROM (
    SELECT ENAME, 12*SAL + nvl(BONUS,0) as sal_bonus, DEPTNO
    FROM emp2
) t1
JOIN (
    SELECT DEPTNO, round(avg(12*SAL + nvl(BONUS,0)), 2) as avg_sal_bonus
    FROM emp2
    GROUP BY DEPTNO
) t2 ON t1.DEPTNO = t2.DEPTNO
WHERE t1.sal_bonus > t2.avg_sal_bonus;

10．列出与“SCOTT”从事相同工作的所有员工。

select  t1.ENAME

from emp2 t1

join  (select JOB

        from emp2 

        where ENAME='SCOTT')

t2 on t1.JOB=t2.JOB

where ENAME!='SCOTT';

11．列出薪金等于部门30中员工的薪金的所有员工的姓名和薪金。

select  t1.ENAME,t1.sal_bonus

from (

    select  ENAME,12*SAL+nvl(BONUS,0) as sal_bonus

    from emp2 

    where DEPTNO!='30'

)t1 join (

    select 12*SAL+nvl(BONUS,0) as sal_bonus

    from emp2

    where DEPTNO='30'

) t2 on t1.sal_bonus=t2.sal_bonus;

#拓展

#列出薪水等于部门30中员工的薪水的所有员工的姓名和薪水。

select  t1.ENAME,t1.SAL

from emp2 t1

where DEPTNO!='30'

and SAL in (

            select  SAL

            from  emp2 

            where DEPTNO='30'

);

12．列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金。

select  t1.ENAME,t1.sal_bonus

from (

    select  ENAME,12*SAL+nvl(BONUS,0) as sal_bonus,1 as num

    from emp2 

    where DEPTNO!='30'

)t1 join (

    select max(12*SAL+nvl(BONUS,0)) as max_sal_bonus,1 as num

    from emp2

    where DEPTNO='30'

) t2 on t1.num=t2.num

where t1.sal_bonus>t2.max_sal_bonus;

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

select  DEPTNO

        ,count(EMPNO) as cnt

        ,round(avg(SAL+nvl(BONUS,0)),2) as avg_sal_bonus

        ,round(avg(datediff(current_date(),HIREDATE)),2) as avg_date

from emp2

group by DEPTNO;

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

select distinct t1.ENAME,t2.DEPTNAME,SAL+nvl(BONUS,0) as sal_bonus

from emp2 t1 join dept2 t2

on t1.DEPTNO=t2.DEPTNO;

select  t1.ENAME,t1.SAL,t2.DEPTNAME,t2.DEPTNO

from emp2 t1 join dept2 t2

on t1.DEPTNO = t2.DEPTNO;

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

select  t1.DEPTNO,t1.DEPTNAME,t1.DEPTADDR,t2.cnt_empno

from dept2 t1 left join

    (select  DEPTNO,count(EMPNO) as cnt_empno

     from emp2

     group by DEPTNO)

t2 on t1.DEPTNO=t2.DEPTNO;

select  t2.DEPTNO,t2.DEPTNAME,t2.DEPTADDR,t1.cnt

from (

    select  DEPTNO,count(distinct EMPNO) as cnt

    from emp2

    group by DEPTNO

) t1 right join dept2 t2

on t1.DEPTNO = t2.DEPTNO;

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

select  JOB,min(SAL) as min_sal

from emp2

group by JOB;

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

SELECT t1.DEPTNO, MIN(t1.sal_bonus)
FROM (
    SELECT DEPTNO, 12*SAL + nvl(BONUS,0) as sal_bonus
    FROM emp2
    WHERE JOB = 'MANAGER'
) t1
GROUP BY t1.DEPTNO;

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