V.IMP SQL used in INFORMATICA

1) display duplicate rows?

sql> select deptno from emp group by deptno having count(*)>1;

  2) delete duplicate rows?

sql> delete from emp e1 where rowid>(select min(rowid) from emp e2 where e1.deptno=e2.deptno);

  3) update emp sal based on hiredate?

sql> update emp set sal=sal+500 where hiredate in(select hiredate from emp having count(*)>1 group by hiredate);

  4) display top n max sal?

sql> select rownum,sal from (select sal from emp order by sal desc)where rownum<=5;

  5) display the 5th record of the table?

sql> select *from emp where empno=(select empno from emp where rownum<5 minus select empno from emp where rownum<4);

  6) select nth max salary?

sql> select min(sal) from (select distnict (sal) from emp order by sal desc) where rownum<='&n';

  7) display rownum with records?

sql> select rownum,emp. *from emp;

  8) Display the records between two range?

sql> select rownum,ename,empno from emp where rowid in(select rowid from emp where rownum <=&upto minus select rowid from emp where rownum<&Start);

  9) Odd number of records?

sql> select * from emp where (rowid,1) in (select rowid, mod(rownum,2) from emp);

  10) Even number of records?

sql> select * from emp where (rowid,0) in (select rowid, mod(rownum,2) from emp);
  
  11) How To Display last 5 records in a table?

sql> select * from (select rownum r, emp.* from emp) where r between (Select count(*)-5 from emp)
      and
     (Select count(*) from emp);