SQL 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 having count(*)>1);
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;
9) 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;
10) 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);
