-1

Consider the following SQL database:

enter image description here

I have two questions that I'm struggling on. I'm not really sure where to start.

  1. Select the third largest salary from all the employees. No joins allowed.

  2. Select salaries for all employees with their supervisor’s supervisor that has a salary NOT minimum out of all supervisor salaries. The query MUST use the "ANY" operator.

For the first one, how would I do it if I can't use any joins? I'm assuming I can't just simply use the MAX() function, so I would have to make one myself. I don't know how without using any joins.

For the second one, I'm guessing I would have to join a table to itself?

1
  • 1
    Define join please. Is a subselect a join (I would say it is, even if it doesn't use the word join)? What about exists/not exists? – Charlieface Apr 24 at 22:24
1

Just to tack on to this, depending on the question, you may want to use DENSE_RANK, not ROW_NUMBER

SELECT lastname, firstname, id, salary 
FROM (SELECT DENSE_RANK(Salary) OVER (ORDER BY Salary DESC)  as pos, lastname, firstname, id, salary
      FROM employee
     ) tmp
WHERE pos = 3;

...because if you have two people with the same salary at position 3, you're going to miss one of them and that's a no no, similarly, there may be multiple people with the same salaries at positions 1 and 2, so it's important to know what you are trying to obtain. Rank and Row number combined helps with disambiguation but you don't want to be arbitrary on matches.

1

Subselects. Both.

Select salary
from employee
where 2 = (select count(1)
           from employee as bigersalary
           where bigersalary.salary > employee.salary)

The second is subselect in select part. Hope this helps

0

For the first question, I can see two possible solutions:

  1. Use ORDER BY in a subquery with LIMIT:
    SELECT lastname, firstname, id, salary
      FROM (SELECT lastname, firstname, id, salary 
              FROM employee
             ORDER BY salary DESC LIMIT 3) tmp 
     ORDER BY salary LIMIT 1;
    
  2. Use ROW_NUMBER to determine the position of every salary so you can return one or many results:
    SELECT lastname, firstname, id, salary
      FROM (SELECT ROW_NUMBER() OVER (ORDER BY salary DESC) as pos, lastname, firstname, id, salary
              FROM employee
             ORDER BY pos) tmp
     WHERE pos = 3;
    
    If you need to return the 3rd, 4th, and 8th highest salaries, replace WHERE pos = 3 with WHERE pos IN (3, 4, 8). This would be suboptimal for very large tables, though.

For the second question, I'm not quite following the "has a salary NOT minimum out of all supervisor salaries" bit. Is it a single value that is being looked for? If so, an ANY with a subquery seems like an inefficient means to find the answer ... 🤔

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.