Some Nice Oracle Queries

 
To fetch Duplicate Rows

 Select * from emp 
   where rowid notin (select max(rowid) from emp group by empno);
 
Delete nth Row

delete from emp 
  where empno = (select empno from 
    (select empno,rownum from emp group by empno,rownum 
       having rownum = &n));

Select alternate rows

  select * from emp where rowid in 
    (select decode(mod(rownum,2),0,rowid) from emp);

To find last 3 rows

  select * from emp e1 where 3 > 
   (select count(rowid) from emp e
    where e.rowid < e1.rowid);

To find first 3 rows 

 select * from emp where rownum <=3 
 
Select nth row

 select * from emp where rowid in 
       (select decode(rownum,&n,rowid) from emp);
or
     select * from emp where (rowid,&n) in 
       (select rowid,rownum from emp);

Select nth and mth row

   select * from emp where rowid in
   (select decode(rownum,&m,rowid,&n,rowid) from emp);

Find out last n rows in table

  select * from emp where rowid not in
   (select rowid from emp group by rownum,rowid 
     having rownum <= (select count(*) - &n from emp);

Find row between m & n including m & n

  select * from emp where rowid in 
   (select rowid from emp group by rownum having rownum > &n 
    and rownum < &m);

Find last row of table

  select * from emp where rowid in
   (select max(rowid) from emp);

Rename column

 alter table emp rename column empno to eno;

Rename table 

 rename emp to employee;

Employee name starting with s,p,a

  select * from emp where substr(empname,1,1) in ('s','p','a');

Display 2nd highest sal department wise

 select * from emp a where 1 = 
  (select count(distinct sal) from emp b where a.sal < b.sal and a.dept=b.dept);

To create table from existing table without data

 create table emp1 as (select * from emp where 1=2);

Display max sal along with all details

  select * from emp where sal in (select max(sal) from emp);

Last n row

  select * from emp minus select * from emp 
   where rownum <= (select max(rownum)-&n from emp);
 or
  select * from emp e1 where &n > (select count(rowid) from emp e2
   where e1.rowid < e2.rowid );

Delete n row

 Delete form emp where empno = (select empno from (select empno,rownum from emp 
   group by empno,rownum having rownum = &n));

Convert sal into string
 
 select tochar(to_date(sal,'J'),'JSP') salinWord from emp;

Find out null & total nul column

  Note: comm and mgr are the column of table emp

  select sum(decode(comm,null,1,0)) comm_null, sum(decode(mgr,null,1,0)) mgr_null
   sum(comm_null + mgr_null) Total_null from emp;

Find no. of employee working in each dept. & total no. of employee

  select sum(decode(deptno,10,1,1) dept_10, sum(decode(deptno,20,1,1) dept_20,
   sum(decode(deptno,30,1,1) dept_30, sum(decode(deptno,40,1,1) dept_40,
   count(*) total from emp;
 or
  select deptno,count(empno) from emp group by deptno;

No. of employee hired in one month

 select to_char(hiredate,'Mon'),count(hiredate) from emp 
  group by to_char(hiredate,'Mon');

Total no. of column in the table

  select count(*) from col where tname = &tablename; 

To find employee joined on same date

  select * from emp e1 where 2 <= (select count(*) from emp e2 
    where e1.hiredate = e2.hiredate);

To find the no. of employee working under each manager or employee

  select mgr,count(empno) from emp where mgr is not null group by mgr;

Maximun no. of employee working under which employee/manager

  select * from (select mgr,count(empno) No from emp where mgr is not null
   group by mgr) where No = (select max(No) from (select mgr,count(empno) from emp
   where mgr is not null group by mgr));

No. of employee hired on same date

  select hiredate,No from (select hiredate,count(empno) No from emp group by hiredate);

Nth Highest Salary 

  Generic
  SELECT name, salary FROM employee e1 WHERE N-1 = (SELECT COUNT(DISTINCT salary) 
  FROM employee e2 WHERE e2.salary > e1.salary)
  Sql Server 
  SELECT TOP 1 salary FROM 
  ( SELECT DISTINCT TOP N salary FROM employee ORDER BY salary DESC ) AS temp 
  ORDER BY salary
  My Sql
  SELECT salary FROM employee ORDER BY salary DESC LIMIT N-1, 1

  postgres - need to set limit and offset properly
  SELECT salary FROM employee ORDER BY salary DESC LIMIT N-1 OFFSET 1

please try at your end....