Answer / nusrath sultana

select * from emp
where &n=(select count(distinct(sal)) from emp b
where emp.sal<=b.sal)

Answer / hari kanth

select sal from(select sal,rank() over(order by sal desc)
rnk from emp) e
where e.rnk=2;
(OR)
select rownum,sal from emp group by sal,rownum
having rownum=2;
(OR)
select rn,sal from( select rownum rn,sal from emp group by
sal,rownum order by sal desc)
where rn=2;

Answer / aravind

select * from empdetails where sal=(select max(sal) from
empdetails where sal<(select max(sal)from empdetails));

Answer / anand

SELECT SALARY FROM (SELECT SALARY FROM emp WHERE ROWNUM < 3
ORDER
BY salary) WHERE ROWNUM < 2

Answer / sikindar

SELECT DISTINCT (a.sal) FROM EMP A WHERE &N = (SELECT COUNT (DISTINCT (b.sal)) FROM EMP B WHERE a.sal<=b.sal)

Answer / amulya kumar panda

select max(salary) from employee where
salary<(select max(salary) from employee)

Answer / raunaq

to all the people who are using rownum in their queries...

This query will not work if you have more than 1 person
having the second highest salary

Answer / akash

select emp_id,sal from ( select emp_id,sal from emp where
sal < ( select max(sal) from emp) order by sal desc ) where
rownum < 2;

Answer / nagaraju

SELECT LEVEL,MAX(SAL)
FROM EMP
WHERE LEVEL IN(2,3)
CONNECT BY PRIOR SAL>SAL
GROUP BY LEVEL;

Answer / madan kumar

select *from (select empno,deptno,min(sal) from emp
group by empno,deptno,sal
order by sal desc)
where rownum<=1

List out the types of joins.

0 Answers  

How to insert a record into a table?

0 Answers  

What is an oracle tablespace?

0 Answers  

How to lock and unlock a user account in oracle?

0 Answers  

Difference between an implicit & an explicit cursor.?

2 Answers  

what is primary key?

9 Answers  

what is the syntax of CREATE command?

8 Answers  

Can we write insert statement in function in oracle?

0 Answers  

How do I find the database name in oracle?

0 Answers  

wirte the syntax of update query?

3 Answers   IBM,

How to write a query with a right outer join in oracle?

0 Answers  

i can create a view with two columns from emp table,, later i need to add one more emp column to existing view.. what is query similarly add one more column to existing primary key constraint.. please give me the solutions

4 Answers   CGI,