INTERVIEW QUESTIONS DATABASE SQL DETAILS

Question: How we get second highest salary in
database. Please show me this coding.

Answer: select top 1 from (select top 2 from (select salary from employee order by salary) order by salary dsc)

select top 1 from (select top 2 from (select salary from employee order by salary) order by salary dsc) Source: CoolInterview.com

select max(salary) from employee where salary!=(select max(salary) from employee); Source: CoolInterview.com

select top 1 salary from (select top 2 salary Order by salary DESC) Source: CoolInterview.com

select max(salary) from employee where salary in (select distinct Top 2 salary from employee order by salary desc) Source: CoolInterview.com

select min(sal) from (select * from table orderby desc sal) where rownum <=2 Source: CoolInterview.com

select e.salary from
(select salary from employees order by salary desc)e
where
rownum =2; Source: CoolInterview.com

Select salary from emplyees in (select distinct top 2 salary from employees order by

salary desc) where rownum = 2
Source: CoolInterview.com

Select r, salary, last_name from (select rownum r, salary, last_name from (select salary, last_name from employees order by salary desc)) where r=2

u can replace value of 'r' for 'n'th person having highest salary. Source: CoolInterview.com

Select top 1 salary from employee where salary in (select top 2 salary from employee order by salary desc) order by salary asc Source: CoolInterview.com

To retrieve the second max salary from database run the following query:-

Taking EMPLOYEE as a database table and SALARY as a field where we have to find second max salary.

SELECT MAX(SALARY) FROM EMPLOYEE WHERE SALARY NOT IN (SELECT MAX(SALARY) FROM EMPLOYEE)
Source: CoolInterview.com

Select max(salary)from emp_salary where salary < (select max(salary) from emp-salary) Source: CoolInterview.com

Select max(salary)from emp_salary where salary < (select max(salary) from emp-salary) Source: CoolInterview.com

select salary from emp e where &n=(select count(distinct(salary)) from emp where salary>e.salary); Source: CoolInterview.com

select * from emp where sal=(select max(sal) from emp where sal not in(select max(sal) from emp)) Source: CoolInterview.com

select MIN(salary) from table where salary in (select top 2 salary from table order by salary DESC) Source: CoolInterview.com

select e1.esalary from employee e1 where 1=(select count(distinct e2.esalary) from employee e2 where e2.esalary>e1.esalary) Source: CoolInterview.com

Select a.sal from table a where 2= (select count(distinct b.sal) from table b where a.sal <= b.sal) Source: CoolInterview.com

Select top 1 Salary from salary_tbl where salary
IN (Select top 2 Salary from salary_tbl order by salary desc)
order by sala asc Source: CoolInterview.com

The following query retrieves the nth maximum salary from emp table.

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

If you want to retrieve the nth minimum salary from emp table, you can give the following query

select e.sal from emp e where &n=(select count(distinct(b.sal)) from emp b where e.sal>=b.sal) Source: CoolInterview.com

select top 1 salary from
(select top 2 salary from employeemaster order by salary desc) as temp1
order by salary Source: CoolInterview.com

To find only the second highest salary:

SELECT TOP 1 salary FROM
(SELECT DISTINCT TOP 2 salary FROM employee
ORDER BY salary desc) employee
ORDER BY salary

To find the record of the employee getting second highest salary:

SELECT TOP 1 * FROM employee where salary in
(SELECT DISTINCT TOP 2 salary FROM employee ORDER BY salary desc)
ORDER BY salary Source: CoolInterview.com

select min(sal) from emp where
sal in(select distinct sal from emp
order by sal desc)
and rownum <=2 Source: CoolInterview.com

Select b.Salary FROM (Select DISTINCT(Salary) FROM Table1) AS A,
(Select DISTINCT(Salary) FROM Table1) AS B
WHERE A.Salary >= B.Salary Group By b.Salary HAVING Count(B.Salary) =2

Value 2 will return Second Highest. Pass 3,4 for getting 3rd,4th heighest value respectively. Source: CoolInterview.com

SELECT salary FROM `table` ORDER BY `salary` DESC LIMIT 1 , 1;

It will return 2nd highest salary Source: CoolInterview.com

select sal from
(select rownum r,sal from (select distinct sal from emp order by sal desc))
where r = 2; Source: CoolInterview.com

SQL> select max(sal) from emp where sal<(select max(sal) from emp);

MAX(SAL)
----------
3000 Source: CoolInterview.com

select sal
from
(
select e.sal as sal,
rank() over (order by e.sal desc) as salrank
from emp e
)
where salrank = 2
Source: CoolInterview.com

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

Select salary
from employee a
Where 2 =
(select count(salary) From employee b Where b.salary >= a.salary);
Source: CoolInterview.com

select max(salary)
from employees
where salary < (select max(salary)
from employees); Source: CoolInterview.com

select max(salary) from employee where salary<(select max(salary) from employee); Source: CoolInterview.com

select sal from (select sal , dense_rank() over (order by sal desc) as rnk from emp)
where rnk=2 Source: CoolInterview.com

Select * from emp where sal in(select s from(select rownum r,s from(select distinct sal s from emp where order by sal desc))where r=&no);

The above query gives the o/p of 2nd,3rd,4th etc., whatever u want. Source: CoolInterview.com

select top 1 empsal from emp where empsal in (select distinct Top 2 empsal from emp order by empsal desc)order by empsal Source: CoolInterview.com

select max(salary) from table
where salary in(select salary from table where salary betbeen max(salary)and min(salary)) Source: CoolInterview.com

example; table name emp

sal
9000
3000
4000
6000
find second highest salary?

select min(sal) from(select sal from emp where sal is not null order by desc)where rownum<3

mean order by desc
sal
9000
6000
4000
3000
where rownum<3
mean
sal
9000
6000
& selact min(sal) that is 6000(it is also second highaest salary.

Thank you Source: CoolInterview.com

SELECT TOP 1 salary FROM ( SELECT TOP 2 salary FROM employee_table ORDER BY salary DESC)ORDER BY salary; Source: CoolInterview.com

SELECT TOP (1) SAL
FROM (SELECT TOP (2) SAL
FROM SALARY
ORDER BY SAL DESC) AS a
ORDER BY SAL Source: CoolInterview.com

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

This is the best query to find out nth highest salary

SELECT *
FROM
(
SELECT E.ENAME,E.SAL,E.DEPTNO, DENSE_RANK () OVER ( ORDER BY SAL DESC) RANK
FROM EMP E
)
WHERE RANK=2
Source: CoolInterview.com

select distinct(a.salary) from emp a where 2 = (select count(distinct(b.salary)) from emp b where a.salary <= b.salary) Source: CoolInterview.com

select min(salary) from tab where salary in (select distinct Top 2 salary from table order by salary desc) Source: CoolInterview.com

forget the logic

Empsal table data
700
500
100
900
400
200
600
750


query to find second highest salary from table Empsal

mysql>select distinct(max(e.salary)) from Empsal e
>where e.salary in(
>select e1.salary from Empsal e1 where e1.salary<(select max(salary) from Empsal));

Output=750

Source: CoolInterview.com

Empsal table data
700
500
100
900
400
200
600
750
query to find the third highest salary

mysql>select distinct(max(e.salary)) from Empsal e
>where e.salary in(
>select e1.salary from Empsal e1 where e1.salary<
>(select max(e1.salary) from Empsal e1
>where e1.salary IN(
>select e2.salary from Empsal e2 where
>e2.salary<(select max(salary) from Empsal))));
Output=700
Source: CoolInterview.com

Empsal table data
700
500
100
900
400
200
600
750
query to find third highest
mysql>select distinct(max(e.salary)) from Empsal e
>where e.salary in(
>select e1.salary from Empsal e1 where e1.salary<
>(select max(e1.salary) from Empsal e1
>where e1.salary IN(
>select e2.salary from Empsal e2 where
>e2.salary<(select max(salary) from Empsal))));
Output=700

Source: CoolInterview.com

for oracle query is:

select min(d.sal) from (select e.sal from (select sal from emp order by sal desc) e where rownum < 3) d Source: CoolInterview.com

select max(sal) from EMP A
where 2=(select count (sal) from EMP B where B.sal >= A.sal) Source: CoolInterview.com

Select distinct sal from emp where rownum = 2 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