Sql queries

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);