hive练习题_查询至少有4个员工的部门的部门名称

一 将empdata.txt数据插入hive表。
EMPNO\ENAME\JOB\MGR\HIREDATE \SAL\COMM\DEPTNO
字段中文名字依次是：工号，姓名，工作岗位，部门经理，受雇日期，薪金，奖金，部门编号

CREATE TABLE emp(
id string,
name string,
job string,
mgr string,
hiredate date,
sal double,
comm double,
deptid string)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ‘,’ ;
将dept.txt数据插入表字段（DEPTNO、DNAME、LOC）
10,ACCOUNTING,NEW YORK
10,ACCOUNTING,shanghai
20,RESEARCH,DALLAS
30,SALES,CHICAGO
40,OPERATIONS,BOSTON

create table dept(
deptno string,
dname string,
loc string
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ‘,’;
然后查询（sql和运行结果截图）：
1． 列出至少有4个员工的所有部门编号和名称。
create table join1 as
select e.*,d.dname,d.loc from (select * from emp) e left join (select * from dept) as d on e.deptid=d.deptno;

select join1.deptid,join1.dname,count() as cnt from join1 group by deptid,dname;
select deptind,dname,distinct(if(count(deptid)>=4,deptid,null)) from join1;
select e.deptid,e.dname, e.cnt from (select join1.deptid,join1.dname,count() as cnt from join1 group by deptid,dname )as e where e.cnt>=4;
2． 列出薪金比“SMITH”多的所有员工。
select e.name,e.sal from
(select name,sal,1 as eid from emp) as e left join (select 1 as did,sal from emp where name=“SMITH”) as e2
on e.eid=e2.did where e.sal>e2.sal;
3． 列出所有员工的姓名及其直接上级的姓名。
select e.name,e2.name from (select name,mgr from emp) as e left join (select id,name from emp) as e2 on e.mgr=e2.id;
4． 列出受雇日期早于其直接上级的所有员工。
select e.name from (select name,mgr,hiredate from emp) as e left join (select id,name,hiredate from emp) as e2 on e.mgr=e2.id where e.hiredate<e2.hiredate;
5． 列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门。
select * from (select * from dept) as d left join (select * from emp) as e on d.deptno=e.deptid;
6． 列出所有“CLERK”（办事员）的姓名及其部门名称。
select name,dname from join1 where job=“CLERK”;
7． 列出最低薪金大于1500的各种工作。
select job from join1 where sal>1500 group by job;
8． 列出在部门“SALES”（销售部）工作的员工的姓名，假定不知道销售部的部门编号
select name from join1 where dname=“SALES”;
9． 列出薪金高于公司平均薪金的所有员工。
select e.name from (select name,sal,1 as eid from emp) as e left join (select avg(sal) as ag,1 as e2id from emp) as e2 on e.eid=e2.e2id where e.sal>e2.ag;
10．列出与“SCOTT”从事相同工作的所有员工。
select e.name from (select name,job,1 as eid from emp) as e left join (select job, 1 as e2id from emp where name=“SCOTT”) as e2 on e.eid=e2.e2id where e.job=e2.job and name!=“SCOTT”;
11．列出薪金等于部门30中员工的薪金的所有员工的姓名和薪金。
select e.name,e.sal from emp e left join (select distinct sal from emp where deptid=‘30’) as e2 where e.sal=e2.sal;
12．列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金。
select e.name,e.sal from (select name,sal,1 as eid from emp) as e left join (select max(sal) as m,1 as e2id from emp where deptid=‘30’) as e2 on e.eid=e2.e2id where e.sal>e2.m;
13．列出在每个部门工作的员工数量、平均工资和平均服务期限。
select deptid,count(deptid),avg(sal),round(avg(datediff(current_date,hiredate)),0) from emp group by deptid;
14．列出所有员工的姓名、部门名称和工资。
select distinct name,dname,sal from join1 ;
15．列出所有部门的详细信息和部门人数。
select deptid,dname,loc,count(deptid) from join1 group by deptid,dname,loc;
16．列出各种工作的最低工资。
select job,min(sal) from emp group by job;
17．列出各个部门的MANAGER（经理）的最低薪金。
select deptid,min(sal) from emp group by deptid;
18．列出所有员工的年工资,按年薪从低到高排序。
select e.name,e.sum from (select name, if(comm!=null,comm12+sal,sal12) as sum from emp) e order by sum asc;
思考： 列出每个部门薪水前两名最高的人员名称以及薪水
select e.deptid,e.sal,e.number from (select deptid,sal,row_number() over(partition by deptid order by sal desc) as number from emp) as e where e.number<=2;