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
