Query to display
middle records drop first 5 last 5 records in emp table
select * from emp
where rownum<=(select count(*)-5 from emp) - select * from emp where
rownum<=5;
Query to display
first N records
select * from(select * from emp order by rowid) where
rownum<=&n;
Query to display odd
records only?
Q). select * from emp where (rowid,1) in (select
rowid,mod (rownum,2) from emp);
Query to display even
records only?
Q.) select * from emp where (rowid,0) in (select
rowid,mod (rownum,2) from emp);
How to display
duplicate rows in a table?
Q). select * from emp
where deptno=any
(select deptno from emp having count(deptno)>1 group by
deptno);
Query to display 3rd
highest and 3rd lowest salary?
Q). select * from emp
e1 where 3=(select count(distinct sal)
from emp e2 where e1.sal<=e2.sal)
union
select * from emp e3 where 3=(select count(distinct sal)
from emp e4 where e3.sal>=e4.sal);
Query to display Nth record from the table?
Q). select
* from emp where rownum<=&n minus select * from emp where rownum<&n;
Query to display the records from M to N;
Q.) select ename from
emp group by rownum,ename having rownum>1 and rownum<6;
select
deptno,ename,sal from emp where rowid in(select rowid from emp
where
rownum<=7 minus select rowid from emp where rownum<4);
select *
from emp where rownum<=7 minus select * from emp where rownum<5;
Query to delete the duplicate records?
Q).
delete from dup where rowid not in(select max(rowid)from dup group by eno);
Query to display the duplicate records?
Q). select * from dup where rowid not in(select
max(rowid)from dup group by eno);
Query for joining two tables(OUTER JOIN)?
Q). select
e.ename,d.deptno from emp e,dept d where e.deptno(+)=d.deptno order by
e.deptno;
select
empno,ename,sal,dept.* from emp full outer join dept on emp.deptno=dept.deptno;
Right Outer Join:
select empno,ename,sal,dept.* from emp right outer join dept on
emp.deptno=dept.deptno;
Left
Outer Join:
select
empno,ename,sal,dept.* from emp left
outer join dept on emp.deptno=dept.deptno
Query for joining table it self(SELF JOIN)?
Q). select e.ename
“employee name”,e1.ename “manger name” from emp e,emp e1 where e.mgr=e1.empno;
Query for combining two tables(INNER JOIN)?
select emp.empno,emp.ename,dept.deptno from emp,dept where
emp.deptno=dept.deptno;
By using aliases:
select e.empno,e.ename,d.deptno from emp e,dept d where e.deptno=d.deptno;
select empno,ename,sal,dept.* from emp join dept on
emp.deptno=dept.deptno:
Find the particular employee salary?
for maximum:
select * from emp where sal in(select min(sal)from
(select sal from emp group by sal order by sal desc)
where rownum<=&n);
select * from emp a where &n=(select count(distinct(sal)) from emp b where a.sal<=b.sal);
for minimum:
select * from emp where sal in(select max(sal) from(select
sal from emp group by sal order by sal asc) where rownum<=&n);
select * from emp a where &n=(select count(distinct(sal)) from emp b where
a.sal>=b.sal)
Find the lowest 5 employee salaries?
Q). select * from
(select * from emp order by sal asc) where rownum<6;
Find the top 5 employee salaries queries
select * from (select * from emp order by sal desc) where
rownum<6;
Find lowest salary queries
select * from emp where sal=(select min(sal) from emp);
Find highest salary queries
select * from emp where sal=(select max(sal) from emp);
