nested select and joins in hive

 from(                                        
    > SELECT ename,sal,loc from emp111)            
    > e                                            
    > select e.ename,e.sal,e.loc where e.sal > 25000;




-------------------------------------------------------------------




select ename,sal ,                      
    > CASE                                    
    > WHEN sal < 10000 THEN 'low'              
    > WHEN sal >10000 AND sal < 20000 THEN 'mid'
    > WHEN sal > 20000 AND sal < 27000 THEN 'high'
    > ELSE 'VERY HIGH'                          
    > END AS bracket from emp111;  

-------------------------------------------------------
hive> select ename,loc,
    > case
    > when loc='chennai' then 'ur in chennai'
    > when loc='noida' then 'ur in noida'
    > when loc in ('hyd','bangalore') then 'ur in hydban'
    > else 'welcome'
    > end as bracket from emp111;
--------------------------------------------------------



query2:

hive> select e.ename  from emp222 e join dept222 d
 on e.dname=d.dname where e.loc in ('chennai','noida');



OK
aaa
aa
aassa
Time taken: 20.321 seconds

hive> s[training@localhost ~]$
[training@localhost ~]$ hive;
Hive history file=/tmp/training/hive_job_log_training_201702281252_1346521375.txt
hive> use bdps;
OK
Time taken: 2.714 seconds

hive> select * from emp222;
OK
1       aaa     12000   hr      chennai
2       bb      15000   fin     hyd
3       cc      12000   mark    noida
4       aa      12000   hr      chennai
5       aea     12000   hr      bangalore
6       beb     15000   fin     hyd
7       cdc     12000   mark    noida
8       aassa   12000   hr      chennai


Time taken: 0.862 seconds

hive> select * from dept222;
OK
1       hr      noida
2       fin     hyd
3       sales   goa
4       accounts        chennai
Time taken: 0.239 seconds





query3:
hive> select e.ename  from emp222 e join dept222 d on
e.dname=d.dname where e.sal > 12000;