INTERVIEW QUESTIONS ORACLE SQL IN ORACLE DETAILS

Question: Find out nth highest salary from emp table

Answer: SELECT DISTINCT (a.sal) FROM EMP A WHERE &N = (SELECT COUNT (DISTINCT (b.sal)) FROM EMP B WHERE a.sal<=b.sal);
For Eg:-
Enter value for n: 2
SAL
---------
3700

SELECT DISTINCT (a.sal) FROM EMP A WHERE &N = (SELECT COUNT (DISTINCT (b.sal)) FROM EMP B WHERE a.sal<=b.sal);
For Eg:-
Enter value for n: 2
SAL
---------
3700 Source: CoolInterview.com

select min(sal) from (select DISTINCT sal from emp order by sal desc) where rownum <=&n
/ Source: CoolInterview.com

Select * from ( select rank() over (partition by sal order by sal desc NULLS LAST) rn from tablename)
where rn = &N; Source: CoolInterview.com

select max(sal) from emp;
we want to deptno wise;


select deptno,max(sal) from emp group by deptno; Source: CoolInterview.com

Select * from ( select rank() over (partition by sal order by sal desc NULLS LAST) rn from tablename)
where rn = &N;


Source: CoolInterview.com

select ename,sal,deptno,job from emp where sal=(select max(sal) from emp where level = &levelno
connect by prior sal>sal
group by level) Source: CoolInterview.com

select rownum,sal from (select rownum,sal from emp order by sal desc )group by rownum,sal having rownum=&N
/
Source: CoolInterview.com

SELECT * FROM(SELECT EMPNO,ENAME,DEPTNO,SAL,RANK()
OVER(ORDER BY SAL) TOPSAL
FROM EMP)
WHERE topsal=&nth; Source: CoolInterview.com

SELECT * FROM
(SELECT ENAME,SAL,
DENSC_RANK()
OVER(ORDER BY SAL DESE)MAXRANK
WHERE MAXRANK<=&GIVENRANK Source: CoolInterview.com

for nth highest salary:

select distinct salary ,last_name from employees e where &n =(select count(distinct salary) from employees e1 where e1.salary>e.salary)

when n=0,it gives highest salary,
n=1 ,it gives 2nd highest salary and so on Source: CoolInterview.com

select max sal from emp where sal=select max(sal) from emp;
Source: CoolInterview.com

SELECT DISTINCT a.sal
FROM emp a,
(SELECT ROWNUM AS cnt, a.*
FROM (SELECT DISTINCT sal
FROM emp
ORDER BY sal DESC) a) b
WHERE a.sal = b.sal AND b.cnt = :a Source: CoolInterview.com

select max(sal)from emp; Source: CoolInterview.com

If you have the better answer, then send it to us. We will display your answer after the approval.

Rules to Post Answers in CoolInterview.com:-

• There should not be any Spelling Mistakes.
• There should not be any Gramatical Errors.
• Answers must not contain any bad words.
• Answers should not be the repeat of same answer, already approved.
• Answer should be complete in itself.

Name :*
Email Id :*
Answer :*
Verification Code	Not readable? Load New Code
Enter the above shown code: *
	Inform me about updated answers to this question