Top SQL Questions of my recent interviews Part 1

A question of current interview tests your ability to work with the classification and sorting in SQL. The challenge is to recover the second highest salary without using LIMIT Or TOP directly.

Relevant columns in the employee table:

• EmployeeID (Unique identifier for each employee)
• Name (Employee name)
• Salary (Employee salary)

Approach:
To find the second highest salary, you can use the DISTINCT keyword with ORDER BY And OFFSET or use a sub-recovery.

Solution using LIMIT (MySQL, PostgreSql, etc.):

SELECT DISTINCT Salary 
FROM Employee 
ORDER BY Salary DESC 
LIMIT 1 OFFSET 1;

Fondamentalement, nous trouvons tous les salaires uniques de la table des employés, les commandons en descendant pour obtenir le plus élevé d’abord, puis pour obtenir un seul résultat, nous utilisons la limite 1 et pour obtenir le deuxième résultat que nous utilisons le décalage by 1.

Solution using a sub-recovery (works in standard SQL):

SELECT MAX(Salary) 
FROM Employee 
WHERE Salary < (SELECT MAX(Salary) FROM Employee);

Another approach is to create two requests: one that selects the maximum salary of the employee table and another which selects the maximum salary of the first request, which is directly less than the highest salary.

This request first finds the highest salary using MAX(Salary). The external request then finds the maximum salary lower than the highest, effectively giving the second highest salary.