SQL Queries

--=====================Nth HIGHEST SALARY=======================
--Highest salary
select max(salary) from employees;
--2nd highest salary
select max(salary) from employees where salary < (select max(salary) from employees);
--Nth Highest Salary
SELECT EMPLOYEE_ID,FIRST_NAME, LAST_NAME,salary
FROM EMPLOYEES e1
WHERE 3 = (SELECT COUNT(DISTINCT(SALARY)) FROM
EMPLOYEES e2 WHERE e2.SALARY > e1.SALARY);
--===============================================================
--===================DELETE DUPLICATE ROWS=======================
--===============================================================
delete from emp where rowid in(
select rowid from(
select k.*,ROW_NUMBER() OVER(partition by id order by id)R ,rowid
from emp k )
where R!=1);
--Explanation
select * from emp; --13 rows
select k.* from emp k; --13 rows
select id, count(1) from emp group by id; -- to get duplicates
select id, count(1) from emp group by id having count(*)>2; --to get duplicates>2
select id, count(1) from emp group by id;
select distinct * from emp; -- 3 rows
--provide row number to it , OK
select k.*,ROW_NUMBER() OVER(order by id) from emp k; --provided row number to each rows
--Now partition table based on id
select k.*,ROW_NUMBER() OVER(partition by id order by id) from emp k; --partition of table based on id
--Here when we partition for each new ID counting starts from 1.
--select rownum, rowid
select k.*,ROW_NUMBER() OVER(partition by id order by id)R ,rowid from emp k;
---
delete from emp where rowid in(
select rowid from(
select k.*,ROW_NUMBER() OVER(partition by id order by id)R ,rowid
from emp k )
where R!=1);
--===============================================================
--===========find employees hired in last n months ==============
--===============================================================

select first_name,hire_date from employees where
to_char(hire_date,'DD')<15 and to_char(hire_date,'MM')<may;

--===============================================================
--===================*********************=======================
--===============================================================



--===============================================================
--===================*********************=======================
--===============================================================



--===============================================================
--===================*********************=======================
--===============================================================