How to delete duplicate rows using analytic functions?

delete from emp
where rowid in
(select rid
from (select rowid rid, row_number() over
( partition by ENAME order by rowid ) rn from emp
)
where rn <> 1
);

This is much more fast and efficient way of removing duplicates compared to
standard approach.

Oracle SQL

How to find the nth largest salary?

There are many ways to do this out of many few possible are listed below:

1.
Select ename , sal , rank
from
( select x.ename , x.sal , rownum rank
from
( select e.ename, e.sal
from
emp e order by e.sal desc
) x
)
where rank = N

2.
select ename, salfrom (select ename, sal, rownum() (partition by ename order by sal desc) rank from emp)where rank = N

3.
Select * from emp where sal =(select min(sal) from emp A where &n >= (select count(*) from empwhere sal >= a.sal))

4.
SELECT * FROM (SELECT sal,ROWNUM RN FROM (Select distinct salfrom emp order by sal desc) WHERE ROWNUM < &n )WHERE RN = &n-1;

5.
select t.salfrom(select sal, rank() over (order by sal desc) rnkfrom emp) twhere t.rnk =&n;

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