These questions are the most frequently asked
in interviews.
1. Select Bottom n rows from oracle
table?
Sql>select * from (select * from
emp order by rowid desc) where rownum<=&n order by rowid;
2. Select records where count>1 ?
Sql>select col1 from abc group by col1
having count(col1)>1
3. Count vowels in
ename from emp table (not for all vowel strings i.e AAA, AEIOU column data)?
Sql>SELECTENAME,NVL(LENGTH(ENAME)-LENGTH(REPLACE(TRANSLATE(UPPER(ENAME),'AEIOU','A'),'A')),0) COUNT FROM EMP
4. Select records having avg sal>sal group by
deptno?
Sql>select empno,sal,deptno from
(select empno,sal,deptno ,avg(sal) over(partition by deptno) asal
where sal >asal
5. To fetch ALTERNATE records from a table. (EVEN
NUMBERED)
Type-1: select * from emp where rowid in (select
decode(mod(rownum,2),0,rowid, null) from emp);
Type-1: select * from emp where rowid in (select decode(mod(rownum,2),0,null
,rowid) from emp);
type-1: select distinct sal from emp e1 where
3 = (select count(distinct sal) from emp e2 where e1.sal <= e2.sal);
select distinct sal from emp e1 where 3 =
(select count(distinct sal) from emp e2where e1.sal >= e2.sal);
select * from emp where rownum <= &n;
select * from emp minus select * from emp
where rownum <= (select count(*) - &n from emp);
select * from dept where deptno not in
(select deptno from emp);
alternate solution: select * from dept a where not exists
(select * from emp b where a.deptno = b.deptno);
altertnate solution: select empno,ename,b.deptno,dname from emp a,
dept b where a.deptno(+) = b.deptno and empno is null;
Type-1: select * from (select sal from emp order by sal desc) where
rownum<=3;
select distinct sal from emp a where 3
>= (select count(distinct sal) from emp b where a.sal >= b.sal);
Type-1:
select * from emp a where rowid =
(select max(rowid) from emp b where a.empno=b.empno);
delete from emp a where rowid != (select
max(rowid) from emp b where a.empno=b.empno);
select count(EMPNO), b.deptno, dname from emp
a, dept b where a.deptno(+)=b.deptno group by b.deptno,dname;
Type-2: select * from (select empno,ename,sal,rownum rn
from emp order by empno) where mod(rn,2)=0;
Type-3: select * from emp where (rowid,0) in (select
rowid,mod(rownum,2) from emp);
6. To select ALTERNATE records from a table. (ODD
NUMBERED)?
Type-2: select * from (select empno,ename,sal,rownum rn from
emp order by empno) where mod(rn,2)<>0;
Type-3: select * from emp where (rowid,1) in (select
rowid,mod(rownum,2) from emp);
7. Find the 3rd MAX salary in the emp table.
Sql>select distinct sal from emp e1 where
3 = (select count(distinct sal) from emp e2 where e1.sal <= e2.sal);
8. Find the 3rd MIN salary in the emp table.
Sql>select distinct sal from emp e1 where 3 =
(select count(distinct sal) from emp e2where e1.sal >= e2.sal);
9. Select FIRST n records from a table.
Sql>select * from emp where rownum <= &n;
10. Select LAST n records from a table?
Sql>select * from emp minus select * from emp
where rownum <= (select count(*) - &n from emp);
11. List dept no., Dept name for all the
departments in which there are no employees in the department.
Sql>select * from dept where deptno not in
(select deptno from emp);
alternate solution: select * from dept a where not exists
(select * from emp b where a.deptno = b.deptno);
altertnate solution: select empno,ename,b.deptno,dname from emp a,
dept b where a.deptno(+) = b.deptno and empno is null;
12. How to get 3 Max salaries ?
Type-1: select * from (select sal from emp order by sal desc) where
rownum<=3;
Type-2: select distinct sal from emp a where 3 >=
(select count(distinct sal) from emp b where a.sal <= b.sal) order by a.sal
desc;
Type-3: select * from (select ename,sal,rank() over
(order by sal desc) ranking from emp) where ranking<=3;
Type-4: select * from (select ename,sal,dense_rank() over
(order by sal desc) ranking from emp) where ranking<=3;
Type-5: select * from (select ename,sal,row_number() over
(order by sal desc) ranking from emp) where ranking<=3;
13. How to get 3 Min salaries ?
Sql>select distinct sal from emp a where 3
>= (select count(distinct sal) from emp b where a.sal >= b.sal);
14. How to get nth max salaries ?
Type-1:
select distinct sal from
emp a where &n = (select count(distinct sal) from emp b where a.sal <=
b.sal);
or
select min(sal) from (select distinct sal from
emp order by sal desc) where rownum<=&n;
Type-2:
select * from emp e1
where (&N-1) = (select
count(distinct(e2.sal)) from emp e2 where e2.sal>e1.sal)
Type-3: using dense_rank()
function // this will show all rows with that same salary
SELECT empno,ename,sal,deptno FROM (SELECT e1.*,
DENSE_RANK () OVER (ORDER BY sal DESC) rnk FROM emp e1) WHERE
rnk = 3
select
* from (select ename,sal,dense_rank() over (order by sal desc) ranking from
emp) where ranking=&n;
select
* from (select ename,sal,rank() over (order by sal desc) ranking from emp)
where ranking=&n;
Type-4: using
row_number() function // this will not give multiple records if there are
employees with same salaries.
select
* from (select ename,sal,row_number() over (order by sal desc) ranking from emp
order by sal desc) where ranking=&n;
Type-5: using
rownum // this will not give multiple records if there are employees with same salaries.
Note:--- This will show which record you want in
the table records list. Not by sorting.
Sql>select * from (select ename,sal,rownum ranking from emp
order by sal desc) where ranking=&n;
15. Select DISTINCT RECORDS from emp table.
Sql>select * from emp a where rowid =
(select max(rowid) from emp b where a.empno=b.empno);
16. Select Duplicate records/rows in emp table?
Sql>Select * from
(select d.*, count(*) over (partition
by empno) cnt from emp d) where cnt>1;
17. How to delete duplicate rows in a table?
Sql>delete from emp a where rowid != (select
max(rowid) from emp b where a.empno=b.empno);
Sql>delete from emp a where
rowid > (select min(rowid) from emp b where a.empno=b.empno);
18. Count of number of employees in
department wise.
Sql>select count(EMPNO), b.deptno, dname from emp
a, dept b where a.deptno(+)=b.deptno group by b.deptno,dname;
