Question { CTS, 57302 }

Query to get max and second max in oracle in one query ?

Answer

select sal from (select dense_rank() over(order by sal) rank
from emp) where rank in(1,2);

In above query you will get both highest and second highest
salary details even there are more people who are getting
highest and second highest salary.

Question { ABC, 21406 }

how to delete all duplicate records from a table using
subquery?

Answer

delete emp where empno not in(select distinct empno from emp);

For example we have duplicates in empno column then write
query like above...whatever column u want u can write..

Question { Verizon, 6629 }

what is the querry to get first 10 records from the emp
table?(emp no is the primary key)

Answer

select * from emp where rownum<=10;

or

select * from (select rowid ri,e.* from emp e order by ri)
where ri<=10;

Question { Oracle, 11958 }

How to Select second Maximum salary in a Table ?

Answer

select max(sal) from table_name where
sal<(select max(sal) from table_name);

or

select sal from(select sal,dense_rank() over(order by sal)
as rnk from table_name) where rnk=2;

Question { TCS, 7938 }

I have one table :EID,Depid,ename,sal
I want to have max(sal) in each department.

Answer

select depid,max(sal) from group by depid;

Is This Answer Correct ?

33 Yes

4 No