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