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);
Do you think this Article is useful?
Subscribe to:
Post Comments (Atom)
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.
2 Responses to “ORACLE SQL TUTORIAL: USEFUL QUERIES”
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.
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