SQL Queries

Query to display middle records (i.e., drop first 5, last 5 records in emp table)?             Q).  select * from emp where rownum<=(select count(*)-5 from emp) minus select * from emp where rownum<=5;

Continue reading about Query to display middle records drop first 5 last 5 records in emp table

admin on September 26th, 2011

Query to display first N records? Q). select * from(select * from emp order by rowid) where rownum<=&n;

Continue reading about Query to display first N records

admin on September 26th, 2011

Query to display odd records only?             Q).  select * from emp where (rowid,1) in (select rowid,mod (rownum,2) from emp);

Continue reading about Query to display odd records only

admin on September 26th, 2011

Query to display even records only?             Q.)  select * from emp where (rowid,0) in (select rowid,mod (rownum,2) from emp);

Continue reading about Query to display even records only

admin on September 26th, 2011

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

Continue reading about How to display duplicate rows in a table

admin on September 26th, 2011

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

Continue reading about Query to display 3rd highest and 3rd lowest salary

admin on September 26th, 2011

 Query to display Nth record from the table?            Q). select * from emp where rownum<=&n minus select * from emp where  rownum<&n;

Continue reading about Query to display Nth record from the table

admin on September 26th, 2011

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 * […]

Continue reading about Query to display the records from M to N

admin on September 26th, 2011

Query to delete the duplicate records?              Q). delete from dup where rowid not in(select max(rowid)from dup group by eno);

Continue reading about Query to delete the duplicate records

admin on September 26th, 2011

Query to display the duplicate records?             Q).  select * from dup where rowid not in(select max(rowid)from dup group by eno);

Continue reading about Query to display the duplicate records