In this SQL tutorial, we’ll tackle a common interview problem: finding the second highest salary among employees in a company. This task helps you practice working with aggregate functions, subqueries, and sorting in SQL. By the end of this tutorial, you’ll be equipped to handle similar ranking and filtering challenges in SQL.

Learning Objectives

• Use SQL sorting and filtering to find specific values in a dataset.
• Learn how to handle duplicates when determining unique values.
• Apply these skills to solve real-world problems in HR analytics and data analysis.

Problem Statement

Imagine you’re an HR analyst at a tech company tasked with analyzing employee salaries. Your manager wants to understand the pay distribution and asks you to determine the second highest salary among all employees. If multiple employees have the same second highest salary, display that salary only once.

Schema for employee Table

Example Input Data

Expected Output

Step-by-Step Solution

Let’s break down the problem into a series of interactive steps to solve it effectively.

Step 1: Finding Unique Salaries

The first task is to list all unique salaries in descending order. This will allow us to identify the highest and second highest salaries.

Interactive Exercise 1: Select Unique Salaries

Task: Write a query to select unique salaries in descending order.

SELECT DISTINCT salary
FROM employee
ORDER BY salary DESC;

• Try It: Run this query and observe how it lists all unique salaries in descending order.

Explanation

• DISTINCT: This keyword ensures that only unique salary values are returned.
• ORDER BY salary DESC: Sorts the salaries in descending order, with the highest salary first.

Step 2: Finding the Second Highest Salary

Now that you have the unique salaries sorted, the next step is to find the second highest salary. There are several ways to do this, but we’ll use a subquery to make the solution clear and concise.

Interactive Exercise 2: Select the Second Highest Salary

Task: Modify the query to find the second highest salary using a subquery.

SELECT MAX(salary) AS second_highest_salary
FROM employee
WHERE salary < (SELECT MAX(salary) FROM employee);

• Try It: Execute this query to find the second highest salary.

Explanation

• Subquery (SELECT MAX(salary) FROM employee): Finds the highest salary in the dataset.
• Main Query: Selects the maximum salary that is less than the highest salary, effectively giving you the second highest salary.

Step 3: Edge Cases and Duplicates

What if multiple employees have the same second highest salary? The current solution already handles this scenario because it uses the MAX() function to find the second highest salary. By using MAX(), the result will display the second highest salary only once, even if there are duplicates.

Summary

In this tutorial, you learned how to:

• Use DISTINCT to retrieve unique salary values.
• Use ORDER BY to sort values in descending order.
• Use subqueries to find specific values within a dataset, such as the second highest salary.

Final Query Recap:

SELECT MAX(salary) AS second_highest_salary
FROM employee
WHERE salary < (SELECT MAX(salary) FROM employee);

Additional Exercises

Exercise 1: Finding the Third Highest Salary

• Task: Modify the query to find the third highest salary.
• Hint: You may need to use a subquery similar to the one used here but adjusted to filter out the top two salaries.

Conclusion

This tutorial provided a step-by-step guide to finding the second highest salary in an employee dataset. Understanding how to work with aggregate functions, subqueries, and filtering unique values is crucial for solving many SQL interview questions, especially those involving ranking and salary analysis.