Amazon Interview Question for Web Developers






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

select * from employee_salary order by salary desc limit 6,1

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

nice :) it works in MYSQL too :)

- addy August 18, 2009 | Flag
Comment hidden because of low score. Click to expand.
0
of 0 votes

what is primary key

- ramesh.chettu20 December 23, 2009 | Flag
Comment hidden because of low score. Click to expand.
0
of 0 vote

To find the Nth Maximum Salary, you can use the following correlated query:

select * from employee_salary E1 where
(N-1) = (select Count(Distinct(E2.Salary))
from employee_salary E2 where E2.Salary > E1.Salary );

However, the time taken would be quadratic in nature.

- AM May 30, 2009 | Flag Reply
Comment hidden because of low score. Click to expand.
0
of 0 vote

SELECT name, salary FROM Employees
ORDER BY salary, desc
)LIMIT 6,1

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

select name from employee
where salary = ( select salary from ( select distinct(salary) from employee order by salary)
where rownum =7)
)

- anand.gallant June 01, 2009 | Flag Reply
Comment hidden because of low score. Click to expand.
0
of 0 vote

sorry for the above !!

select name from employee where salary =
(select salary from (select salary from employee order by salary desc)
where rownum=7);

- anand.gallant June 01, 2009 | Flag Reply
Comment hidden because of low score. Click to expand.
0
of 0 vote

one comment: the employee with the 7th highest salary may be more than 1 person.
That is all.

- dam775 June 07, 2009 | Flag Reply
Comment hidden because of low score. Click to expand.
0
of 0 vote

select emp, sal
from
( select dense_rank() over (order by sal desc NULLS LAST) ds
from emp1) t1
where t1.ds = 7;

- ammk June 13, 2009 | Flag Reply
Comment hidden because of low score. Click to expand.
0
of 0 vote

SQL> select min(temp.sal) from (select distinct sal from emp order by sal desc) temp where rownum <= 7;

- DG October 16, 2009 | Flag Reply
Comment hidden because of low score. Click to expand.
0
of 2 vote

SELECT * FROM employee_salary WHERE salary = (SELECT salary FROM employee_salary ORDER BY salary DESC LIMIT 7,1);

This will work, tested, OK

- John October 27, 2009 | Flag Reply
Comment hidden because of low score. Click to expand.
1
of 1 vote

You will need to include a distinct in the sub-query to account for duplicate salaries.

select name, salary from employee_salary where salary = (
select distinct salary from employee_salary order by salary desc limit 6,1)

- todd August 09, 2012 | Flag
Comment hidden because of low score. Click to expand.
0
of 0 vote

<code>

-- in Oracle
select id, name, salary
  from (
            select id, name, salary, rownum as row_num
               from (
                        select id, name, salary
                          from employee_salary
                         order by salary desc
                     )
              where rownum < 8
       )
 where row_num = 7;

</code>

- Garbage Monster August 09, 2012 | Flag Reply
Comment hidden because of low score. Click to expand.
0
of 0 vote

-- in Oracle using analytic function
select id, name, salary
  from (
          select id, name, salary,
                 rank() over (order by salary desc, rownum asc) rn
            from employee_salary
       )
 where rn = 7;

- Garbage Monster August 09, 2012 | Flag Reply
Comment hidden because of low score. Click to expand.
0
of 0 vote

select *
from employee
where salary=(
select min(salary)
from (
select e.eid, e.salary
from employee e
order by e.salary)
where rownum<=7)
;

- aileen April 21, 2013 | Flag Reply
Comment hidden because of low score. Click to expand.
0
of 0 vote

select b.*from employee_salary e1
inner join (SELECT e.id,e.name,sum(e.salary)over()as total,e.salary,DENSE_RANK() over (order by e.salary) as emprank
FROM employee_salary E
) b on b.id = e1.id
where b.emprank = 3

- Sai June 06, 2013 | Flag Reply
Comment hidden because of low score. Click to expand.
0
of 0 vote

Sorry I meant employee rank = 7.This query worked for me in SQL Server 2008 R2

- Sai June 06, 2013 | Flag Reply
Comment hidden because of low score. Click to expand.
0
of 0 vote

select * from employee where salary = (select distinct(salary) from employee order by salary desc limit 6,1)

- Anonymous July 02, 2013 | Flag Reply
Comment hidden because of low score. Click to expand.
0
of 0 votes

I agree with you.

- sabellaobama March 25, 2015 | Flag
Comment hidden because of low score. Click to expand.
0
of 0 vote

I know there's wording issues in the question, but the 7th highest salary should refer to 7th highest pay scale.

Lets say Bob A, Bob B, Bob C, Bob D, Bob E, Bob F, Bob G are all at salary cap. Using the queries in other examples, Bob G would be returned as having the 7th highest salary. Even though he's as max salary which would be False. It would also only return Bob G because of where his name lies alphabetically.

This query would return all employees who are at the 7th highest salary as the question asks.

SELECT name
FROM employee_salary
WHERE salary = ( 
SELECT DISTINCT salary
FROM employee_salary
ORDER BY salary DESC 
LIMIT 6 , 1 )

- blahblahblah July 10, 2013 | Flag Reply
Comment hidden because of low score. Click to expand.
0
of 0 vote

select name, salary from employees
order by salary, desc
)LIMIT 6,1

- sabellaobama March 25, 2015 | Flag Reply
Comment hidden because of low score. Click to expand.
0
of 0 vote

Try this.

select name, salary from employees 
order by salary, desc 
)limit 6,1

- sabellaobama March 25, 2015 | 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