Solution For Second Highest Salary

Problem Statement:

Write a SQL query to get the second highest salary from the Employee table.

Table schema for Employee table:

+—-+——–+——–+
| Id | Salary | Name |
+—-+——–+——–+
| 1 | 100 | John |
| 2 | 200 | Bob |
| 3 | 300 | Jerry |
| 4 | 400 | Alice |
| 5 | 500 | Donald |
+—-+——–+——–+

Solution:

To find the second highest salary from the Employee table, we can follow the below steps:

1. First, we need to find the maximum (or highest) salary from the Employee table. For this, we can use the MAX() function as:

SELECT MAX(Salary) FROM Employee;

This will give us the highest salary from the Employee table, which in this case is 500.

1. Next, we need to find the salary which is just less than the highest salary. For this, we can use the subquery and the WHERE clause as:

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

This will give us the salaries which are less than the highest salary, which in this case are 400, 300, 200, and 100.

1. Finally, we need to get the maximum salary from the salaries we obtained in the previous step, which will be the second-highest salary. For this, we can again use the MAX() function as:

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

This will give us the second highest salary from the Employee table, which in this case is 400.

Final SQL query to get the second highest salary from the Employee table:

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

Output:
+————-+
| MAX(Salary) |
+————-+
| 400 |
+————-+

Step by Step Implementation For Second Highest Salary

/**
 *
 * @author chanakya
 */
import java.util.*; 
public class secondHighest { 
  
    public static int getSecondHighest(int[] arr, int n) 
    { 
        int first = Integer.MIN_VALUE, second = Integer.MIN_VALUE; 
        for (int i = 0; i < n; i++) 
        { 
              
            // If current element is greater than first 
            if (arr[i] > first) 
            { 
                second = first; 
                first = arr[i]; 
            } 
              
            // If arr[i] is in between first and second 
            else if (arr[i] > second && arr[i] != first) 
                second = arr[i]; 
        } 
        if (second == Integer.MIN_VALUE) 
            return -1; 
        return second; 
    } 
  
    public static void main(String args[]) 
    { 
        int arr[] = {12, 35, 1, 10, 34, 1}; 
        int n = arr.length; 
        System.out.println(getSecondHighest(arr, n)); 
    } 
}

# Second Highest Salary
# Given a table salary, such as the one below, that has m=male and f=female values. Swap all f and m values (i.e., change all f values to m and vice versa) with a single update query and no intermediate temp table.
# For example:
# | id | name | sex | salary |
# |----|------|-----|--------|
# | 1  | A    | m   | 10000  |
# | 2  | B    | f   | 20000  |
# | 3  | C    | m   | 15000  |
# | 4  | D    | f   | 25000  |
# After running your query, the above salary table should have the following rows:
# | id | name | sex | salary |
# |----|------|-----|--------|
# | 1  | A    | f   | 10000  |
# | 2  | B    | m   | 20000  |
# | 3  | C    | f   | 15000  |
# | 4  | D    | m   | 25000  |

# Solution:

# Update salary table to swap all f and m values
UPDATE salary
SET sex = CASE sex
           WHEN 'f' THEN 'm'
           WHEN 'm' THEN 'f'
         END;

// Given a table salary, such as the one below, that has m=male and f=female values. Swap all f and m values (i.e., change all f values to m and vice versa) with a single update query and no intermediate temp table.
// For example:
// | id | name | sex | salary |
// |----|------|-----|--------|
// | 1  | A    | m   | 2500   |
// | 2  | B    | f   | 1500   |
// | 3  | C    | m   | 5500   |
// | 4  | D    | f   | 500    |
// After running your query, the above salary table should have the following rows:
// | id | name | sex | salary |
// |----|------|-----|--------|
// | 1  | A    | f   | 2500   |
// | 2  | B    | m   | 1500   |
// | 3  | C    | f   | 5500   |
// | 4  | D    | m   | 500    |

UPDATE salary
SET sex = CASE sex
           WHEN 'm' THEN 'f'
           WHEN 'f' THEN 'm'
         END;

#include  
#include  
#include  
#include  

using namespace std; 

class Solution { 
public: 
    int secondHighestSalary(vector& salaries) { 
        // check for empty input 
        if (salaries.empty()) { 
            return -1; 
        } 
        
        // sort the salaries in descending order 
        sort(salaries.begin(), salaries.end(), greater()); 
        
        // return the second highest salary 
        return salaries[1]; 
    } 
};

public int SecondHighestSalary(List employees) { // sort employees by salary in descending order var sortedEmployees = employees.OrderByDescending(e => e.Salary).ToList(); // return the salary of the second highest paid employee return sortedEmployees[1].Salary; }