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);
No comments:
Post a Comment