How do you find the second highest salary, and what is the subtlety in the question?
You sort the salaries and take the second one, and there is a subtlety that catches people.
CREATE TABLE employees (
id INTEGER PRIMARY KEY,
name VARCHAR(120) NOT NULL,
salary_cents INTEGER NOT NULL
);
So what is the subtlety? It is the word "second".
If the top two people are both paid 500000, is the second highest salary 500000 or is it the next value down? Both readings are reasonable, and they give different answers.
the salaries are 900000, 500000, 500000, 400000
the second highest DISTINCT value 400000
the second highest ROW 500000
So the question has to be asked carefully, and the answer has to say which reading it took. In an interview it is worth saying the reading out loud before you write the query.
And there is a second subtlety, which is what happens when there is only one salary. Then there is no second highest, and the query has to give back nothing rather than the same value twice.
The next question in this topic asks for the query.