IMPORTANT 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)
10)TO FIND SECOND MAXIMUM
SAL
SQL> select max(sal) from emp where sal not
in(select max(sal)
from emp);
No comments:
Post a Comment