Friday, October 28, 2011

ORACLE SQL TUTORIAL: USEFUL QUERIES




1)    To find the nth row of a table

SQL> Select *from emp where rowid = (select max(rowid) from emp where rownum <= 4);
Or
   SQL> Select *from emp where rownum <= 4 minus select *from emp where rownum <= 3;

2)    To find duplicate rows

SQL> Select *from emp where rowid in (select max(rowid) from emp group by empno,          
         ename, mgr, job, hiredate, comm, deptno, sal);
Or
 SQL> Select empno,ename,sal,job,hiredate,comm , count(*) from emp group by
         empno,ename,sal,job,hiredate,comm  having count(*) >=1;

3)    To delete duplicate rows

      SQL> Delete emp where rowid in (select max(rowid) from emp group by
              empno,ename,mgr,job,hiredate,sal,comm,deptno);

4)    To find the count of duplicate rows

      SQL> Select ename, count(*) from emp group by ename having count(*) >= 1;

5)    How to display alternative rows in a table?

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

6)    Getting employee details of each department who is drawing maximum sal?

       SQL> select *from emp where (deptno,sal) in
             ( select deptno,max(sal)  from emp group by deptno);

7)    How to get number of employees in each department  , in which department is having more than 2500 employees?

       SQL> Select deptno,count(*) from emp group by  deptno having count(*) >2500;

8)    To reset the time to the beginning of the day


                  SQL> Select to_char(trunc(sysdate),’dd-mon-yyyy hh:mi:ss am’) from dual;


9)    To find nth maximum sal

  SQL> Select *from emp where sal in (select max(sal) from (select *from emp order by sal)
          where rownum <= 5);




2 Responses to “ORACLE SQL TUTORIAL: USEFUL QUERIES”

Michael Coughlin said...
June 18, 2012 at 11:09 AM

I have written a couple articles which you may want to link to:

This is SQL/PL/SQL related:
http://www.michaelcoughlin.net/blog/index.php/2011/09/ora-00054/

This is Apps 11i related:
http://www.michaelcoughlin.net/blog/index.php/2011/07/oracle-text-output-not-ie/

I would be happy to contribute more if desired.


Team search said...
September 14, 2012 at 11:36 AM

Hi Michael,

If you would like to share your knowledge, we welcome you as a Guest Author.

Regards,
Team Search


Post a Comment

Disclaimer

The ideas, thoughts and concepts expressed here are my own. They, in no way reflect those of my employer or any other organization/client that I am associated. The articles presented doesn't imply to any particular organization or client and are meant only for knowledge Sharing purpose. The articles can't be reproduced or copied without the Owner's knowledge or permission.