Sunday, July 17, 2016

Advance SQL Queries

Select 10th highest salary


SELECT DISTINCT TOP (10) Salary FROM Employee ORDER BY Salary DESC

Altertative:

SELECT Salary from Employee limit 10 order by Salary DESC


Combine more than two tables using union all, avoid duplicates

SELECT * FROM mytable WHERE a=X UNION ALL SELECT * FROM mytable WHERE b=Y AND a!=X

Find values from one table exclude value from another table without using not in

Select id from table_a
except
Select id from table_b

Alternative:

Select a.id from table_a a left outer join table_b b on a.id = b.id
where a.id <> b.id

Oracle MERGE - Use case - when the conditoin meets, update/delete record otherwise insert the record. Useful in ETL process while syncing two different datasources. Where the updated rows from source table are updated in datawarehouse table and new rows are inserted in the table.

MERGE INTO dw_employee D
USING (SELECT employee_id, salary, department_id FROM employees
ON (D.employee_id = S.employee_id)
WHEN MATCHED THEN UPDATE SET d.salary = S.salary
WHEN NOT MATCHED THEN INSERT (D.employee_id, D.Salary, D.department_id)
VALUES (S.employee_id, S.salary, S.department_id)

For performance use parallel hint for e.g in above query 

merge /*+ parallel (dw_employee,6) */ 

Reference : https://docs.oracle.com/cd/E11882_01/server.112/e25523/parallel007.htm#i1009257

Saturday, July 16, 2016

Hive queries

Combining multiple hive tables into one

INSERT OVERWRITE TABLE FINAL_TBL
SELECT main_union.id, main_union.output_string
FROM (
select id, concat_ws("|",col1,col2,col3) output_string from table1

UNION ALL

select id, concat_ws("|",col1,col2,col3) output_string from table2

UNION ALL

select id, concat_ws("|",col1,col2,col3) output_string from table3

UNION ALL

select id, concat_ws("|",col1,col2,col3) output_string from table4

) main_union

CLUSTER BY main_union.id;

Solution in hive instead of exists clause

for e.g
Employee = individual works at institute, it has many departments.
joe works at institute1 - dept1
joe works at institute2 - dept3
jason works at institute1 - dept1
jason works at institute1 - dept2


Query:

Select individual from individual ind join ind_related ir on ind.id = ir.id
join institute ins on ir.relatedkey = inst.instituteid 
left semi join
dept d on d.deptid=ir.relatedkey