1600 1250 2850 1500 950
部门信息表dept：
字段：部门编号,部门名称,部门地点
英文名：DEPTNO,DEPTNAME,DEPTADDR
create table dept(
DEPTNO int
,DEPTNAME string
,DEPTADDR string
)
row format delimited
fields terminated by ',';
10,ACCOUNTING,NEW YORK
10,ACCOUNTING,shanghai
20,RESEARCH,DALLAS
30,SALES,CHICAGO
40,OPERATIONS,BOSTON
二：使用HQL完成下面需求：
1． 列出至少有一个员工的所有部门。
select t1.deptno,t1.dname,t1.loc from dept t1 join (select deptno,count(distinct deptno) as cnt from emp group by deptno) t2 on (t1.deptno=t2.deptno);
2． 列出薪金比“SMITH”多的所有员工。薪资+津贴 12*sal+COMM
在Hive中，where条件后面不支持不等式的子查询，可以使用join的方式实现
select t1.empno,t1.ename,t1.sal_comm from (select empno,ename,12*sal+nvl(comm,0) as sal_comm,1 as temp_id from emp) t1 join (select 12*sal+nvl(comm,0) as sal_comm,1 as temp_id from emp where ename='SMITH') t2 on (t1.temp_id=t2.temp_id) where t1.sal_comm>t2.sal_comm;
美化后的sql:
SELECT t1.empno, t1.ename, t1.sal_comm
FROM (
SELECT empno, ename
, 12 * sal + nvl(comm, 0) AS sal_comm
, 1 AS temp_id
FROM emp
) t1
JOIN (
SELECT 12 * sal + nvl(comm, 0) AS sal_comm
, 1 AS temp_id
FROM emp
WHERE ename = 'SMITH'
) t2
ON t1.temp_id = t2.temp_id
WHERE t1.sal_comm > t2.sal_comm;
3． 列出所有员工的姓名及其直接上级的姓名。
select t1.ename,t2.ename as leader_name from emp t1 join emp t2 on (t1.mgr=t2.empno);
4． 列出受雇日期早于其直接上级的所有员工。
select t1.ename,t1.hiredate,t2.ename as leader_name,t2.hiredate as leader_hiredate from emp t1 left join emp t2 on (t1.mgr=t2.empno) where t1.hiredate1500;
select * from (select job,min(sal) as min_sal from emp group by job) t1 where t1.min_sal>1500;
8． 列出在部门“SALES”（销售部）工作的员工的姓名，假定不知道销售部的部门编号
select empno,ename from emp where deptno in (select deptno from dept where dname='SALES');
9． 列出薪金高于公司平均薪金的所有员工。 12*sal+comm
select t1.empno,t1.ename,t1.sum_sal_comm,t2.avg_sal_comm from (select empno,ename,12*sal+nvl(comm,0) as sum_sal_comm,1 as temp_id from emp) t1 join (select round(avg(12*sal+nvl(comm,0)),2) as avg_sal_comm,1 as temp_id from emp) t2 on(t1.temp_id=t2.temp_id) where t1.sum_sal_comm>t2.avg_sal_comm;
10．列出与“SCOTT”从事相同工作的所有员工。
select empno,t1.ename,job from emp t1 where job in (select job from emp where ename='SCOTT') and t1.ename!='SCOTT';
11．列出薪资等于部门30中员工的薪资的所有员工的姓名和薪资。
select t1.ename,t1.sal from emp t1 where t1.sal in (select sal from emp where deptno=30) and t1.deptno!=30;
12．列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金。
select max(12*sal+nvl(comm,0)) from emp where deptno=30;
select 12*sal+nvl(comm,0) from emp where deptno!=30;
select t1.ename,t1.sum_sal_comm,t2.max_sal_comm from (select ename,12*sal+nvl(comm,0) as sum_sal_comm,1 as temp_id from emp where deptno!=30) t1 join (select max(12*sal+nvl(comm,0)) as max_sal_comm,1 as temp_id from emp where deptno=30) t2 on (t1.temp_id=t2.temp_id) where t1.sum_sal_comm>t2.max_sal_comm;
13．列出在每个部门工作的员工数量、平均工资和平均服务期限。
select deptno,count(empno) as nop,round(avg(12*sal+nvl(comm,0)),2) as avg_sal_comm,floor(avg(datediff(current_date(),hiredate))) as avg_work_days from emp group by deptno;
14．列出所有员工的姓名、部门名称和工资。
select t1.ename,t2.dname,t1.sal from emp t1 join dept t2 on (t1.deptno=t2.deptno);
15．列出所有部门的详细信息和部门人数。
select t2.deptno,t2.dname,t2.loc,if(t1.pcount is null,0,t1.pcount) from (select deptno,count(distinct empno) as pcount from emp group by deptno) t1 right join dept t2 on (t1.deptno=t2.deptno);
select t2.deptno,t2.dname,t2.loc,nvl(t1.pcount,0) from (select deptno,count(distinct empno) as pcount from emp group by deptno) t1 right join dept t2 on (t1.deptno=t2.deptno);
16．列出各种工作的最低工资。
select min(sal) from emp group by job;
17．列出各个部门的MANAGER（经理）的最低薪金。
select t1.deptno,min(12*sal+nvl(comm,0)) from (select deptno,sal,comm from emp where job='MANAGER') t1 group by t1.deptno;
18．列出所有员工的年工资,按年薪从低到高排序。
select empno,ename,12*sal+nvl(comm,0) as year_sal from emp order by year_sal;
19. 列出每个部门薪水前两名最高的人员名称以及薪水。（思考：用开窗函数解决）
select ename,deptno,sal,rows_number() over(partition by denptno order by sal desc) from emp;
select w.empno,w.ename,w.sal,w.deptno,w.rn from (select empno,ename,sal,deptno,row_number() over(partition by deptno order by sal desc) as rn from emp) w where w.rn