Microsoft Interview Question for Software Engineer / Developers






Comment hidden because of low score. Click to expand.
5
of 5 vote

SELECT salary, id FROM employee_salary WHERE salary=(SELECT MAX(salary) FROM employee_salary WHERE salary < (SELECT MAX(salary) FROM employee_salary))

- Anonymous December 01, 2009 | Flag Reply
Comment hidden because of low score. Click to expand.
0
of 0 votes

second highest sal

- vk August 03, 2015 | Flag
Comment hidden because of low score. Click to expand.
1
of 1 vote

My Previous answer will only return second highest record... More general solution is...

select a.sal from salary a where "N" = (select count(distinct b.sal) from salary b where a.sal <= b.sal)

Change N to 2 to get second highest... change it to 3 to get 3rd highest and so on.

- gagdeep November 27, 2009 | Flag Reply
Comment hidden because of low score. Click to expand.
0
of 0 votes

change select a.sal from sa... to select * from sa...

I was testing it so forgot to change to *

- gagdeep November 27, 2009 | Flag
Comment hidden because of low score. Click to expand.
1
of 1 vote

extending from dinesh's idea.. this is more generic and handles duplicate rows with same salary. need to run on sql though.

SELECT salary ,id FROM employee where salary = (SELECT distinct(sal) FROM employee WHERE ROWNUM ==N ORDERBY SALARY DESC)

- pavan c January 27, 2010 | Flag Reply
Comment hidden because of low score. Click to expand.
0
of 0 vote

select * from salary where sal = (select max(a.sal) from (select * from salary where sal <> (select max(sal) from salary)) a)

- Anonymous November 27, 2009 | Flag Reply
Comment hidden because of low score. Click to expand.
0
of 0 vote

select * from employee where sal = (select max(a.sal) from (select * from employee where sal <> (select max(sal) from employee)) a)

- Anonymous November 27, 2009 | Flag Reply
Comment hidden because of low score. Click to expand.
0
of 0 vote

select * from employee where sal = (select max(a.sal) from (select * from employee where sal <> (select max(sal) from employee)) a)

- gagdeep November 27, 2009 | Flag Reply
Comment hidden because of low score. Click to expand.
0
of 0 vote

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

- Anonymous November 27, 2009 | Flag Reply
Comment hidden because of low score. Click to expand.
0
of 0 votes

this query only selects second highest salary...the questions asks for both second highest salary and the corresponding Employee Id

- Anonymous November 27, 2009 | Flag
Comment hidden because of low score. Click to expand.
0
of 0 votes

No this is not a correct solution

- Anonymous February 03, 2010 | Flag
Comment hidden because of low score. Click to expand.
0
of 0 vote

SELECT salary ,id FROM (SELECT * FROM employee ORDERBY SALARY DESC) WHERE ROWNUM ==2.

- dinesh January 25, 2010 | Flag Reply
Comment hidden because of low score. Click to expand.
0
of 0 votes

dinesh i think it should be
SELECT distinct salary ,id FROM (SELECT * FROM employee ORDERBY SALARY DESC) WHERE ROWNUM ==2.

because there could 2 employees having max salary

- snehal May 05, 2010 | Flag
Comment hidden because of low score. Click to expand.
0
of 0 vote

select salary,empid from employee where salary=(select max(salary) from employee where salary<(select max(salary) from employee))

- raj April 13, 2010 | Flag Reply
Comment hidden because of low score. Click to expand.
0
of 0 vote

if we consider there are no duplicate record,
SELECT *
FROM employee_salary t1
WHERE &N=(SELECT COUNT(DISTINCT salary)FROM employee_salary t2 WHERE t2.salary >= t1.salary)

- Anonymous May 12, 2010 | Flag Reply
Comment hidden because of low score. Click to expand.
0
of 0 vote

SELECT TOP 2 salary,empid from employee
ORDER BY salary DESC
MINUS
SELECT TOP 1 salary,empid from employee
ORDER BY salary DESC

- Harish October 31, 2010 | Flag Reply
Comment hidden because of low score. Click to expand.
0
of 0 vote

select * from employee_salary where salary =
(
select t2.salary
from
(select distinct(salary) from employee_salary) t1
inner join
(select distinct(salary) from employee_salary) t2
on t1.salary > t2.salary
group by t2.salary
having count(t1.salary) = 2
order by count(t1.salary) asc
);

- ypliu November 24, 2010 | Flag Reply
Comment hidden because of low score. Click to expand.
0
of 0 vote

In Oracle, this is the query

SELECT salary_amount
FROM	(select salary2.*, rownum rnum from
(select * from salary ORDER BY salary_amount DESC) salary2
where rownum <= 2 )
WHERE rnum >= 2;

- Anonymous April 04, 2011 | Flag Reply
Comment hidden because of low score. Click to expand.
0
of 0 vote

Folks, if there are TWO people with the top salary, most of the above would return the 3rd highest salary and not the second. Try this:

SELECT TOP 2 Employee_ID,Salary FROM Employee order by salary desc
EXCEPT
SELECT TOP 1 Employee_ID,Salary FROM Employee order by salary desc

- Anonymous July 23, 2011 | Flag Reply
Comment hidden because of low score. Click to expand.
0
of 0 vote

SELECT Employee_ID, Salary FROM Employee E1 WHERE 2 = (
    SELECT COUNT(DISTINCT E2.Salary)
    FROM Employee E2
    WHERE E2.Salary >= E1.Salary
);

- tianchu1987 December 20, 2011 | Flag Reply
Comment hidden because of low score. Click to expand.
0
of 0 vote

Without Analytical functions
{
select empno,ename,sal from emp
where sal=(select max(sal) from emp where sal<(select max(sal) from emp));
}

Efficient way using analytical function
{
select empno,ename,sal from
(select empno,ename,sal, dense_rank() over (order by sal desc) r from emp)
where r=2;
}

- Santhosh Devunuri February 22, 2012 | Flag Reply
Comment hidden because of low score. Click to expand.
0
of 0 vote

select max(salary),m.id
from (select max(salary) ,id from employee_salary) m, employee_salary es
where es.id !=m.id

- Ashish July 08, 2012 | Flag Reply
Comment hidden because of low score. Click to expand.
0
of 0 vote

SELECT employee_id, salary
FROM employee_salary
ORDER BY salary desc
LIMIT 1,1;

- Zack April 30, 2014 | Flag Reply
Comment hidden because of low score. Click to expand.
0
of 0 vote

select max(salary), emp_id from employee_salary where salary ! =(select max(salary) from employee_salary)

- Sahil September 30, 2014 | Flag Reply


Add a Comment
Name:

Writing Code? Surround your code with {{{ and }}} to preserve whitespace.

Books

is a comprehensive book on getting a job at a top tech company, while focuses on dev interviews and does this for PMs.

Learn More

Videos

CareerCup's interview videos give you a real-life look at technical interviews. In these unscripted videos, watch how other candidates handle tough questions and how the interviewer thinks about their performance.

Learn More

Resume Review

Most engineers make critical mistakes on their resumes -- we can fix your resume with our custom resume review service. And, we use fellow engineers as our resume reviewers, so you can be sure that we "get" what you're saying.

Learn More

Mock Interviews

Our Mock Interviews will be conducted "in character" just like a real interview, and can focus on whatever topics you want. All our interviewers have worked for Microsoft, Google or Amazon, you know you'll get a true-to-life experience.

Learn More