Scribd Logo
Upload

Welcome to Scribd!

  • 6 views

    SQL & Pandas Challenge 3 Solution

    Uploaded by

    Harish Reddy
    The document provides SQL and Pandas solutions to find the second highest salary from an Employee table. It includes examples of creating the Employee table with different number of rows and inserting sample data. The solutions demonstrate using a subquery, CTE, DENSE_RANK, and nlargest methods to return the second highest salary or null if there is no second highest.

    Copyright:

    © All Rights Reserved
    Download as PDF, TXT or read online from Scribd

    SQL & Pandas Challenge 3 Solution

    Uploaded by

    Harish Reddy
    6 views11 pages
    The document provides SQL and Pandas solutions to find the second highest salary from an Employee table. It includes examples of creating the Employee table with different number of rows and inserting sample data. The solutions demonstrate using a subquery, CTE, DENSE_RANK, and nlargest methods to return the second highest salary or null if there is no second highest.

    Original Description:

    This notes will be useful for who is preparing for data analytics

    Original Title

    SQL & Pandas challenge 3 solution

    Copyright

    © © All Rights Reserved

    Available Formats

    PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    The document provides SQL and Pandas solutions to find the second highest salary from an Employee table. It includes examples of creating the Employee table with different number of rows and inserting sample data. The solutions demonstrate using a subquery, CTE, DENSE_RANK, and nlargest methods to return the second highest salary or null if there is no second highest.

    Copyright:

    © All Rights Reserved
    Download as PDF, TXT or read online from Scribd
    Download as pdf or txt
    6 views11 pages

    SQL & Pandas Challenge 3 Solution

    Uploaded by

    Harish Reddy
    The document provides SQL and Pandas solutions to find the second highest salary from an Employee table. It includes examples of creating the Employee table with different number of rows and inserting sample data. The solutions demonstrate using a subquery, CTE, DENSE_RANK, and nlargest methods to return the second highest salary or null if there is no second highest.

    Copyright:

    © All Rights Reserved
    Download as PDF, TXT or read online from Scribd
    Download as pdf or txt
    of 11

    SQL & Pandas

    CHALLENGE-3

    PRAVEEN KUMAR MAURYA

    http://www.linkedin.com/in/pmpraveen802
    Question
    Table: Employee
    +--------------------+--------+
    | Column Name | Type |
    +--------------------+--------+
    | id | int |
    | salary | int |
    +--------------------+--------+
    id is the primary key (column with unique values) for this table.
    Each row of this table contains information about the salary of an employee.
    Continued…
    Write a solution to find the second highest salary from the Employee table. If there is no second highest salary, return null.
    The result format is in the following example.

    Example 1:
    Output 1:
    Input: Employee

    Example 2:
    Output 2:
    Input: Employee
    Query for the input table
    -- Example 1:
    CREATE TABLE Employee (
    id INT PRIMARY KEY,
    salary INT
    );

    INSERT INTO Employee (id, salary) VALUES


    (1, 100),
    (2, 200),
    (3, 300);

    SELECT * FROM employee;


    Continued…
    -- Example 2:

    CREATE TABLE Employee (


    id INT PRIMARY KEY,
    salary INT
    );

    INSERT INTO Employee (id, salary) VALUES


    (1, 100);

    SELECT * FROM employee;


    Solution using MySQL
    Solution using Subquery

    SELECT
    (SELECT DISTINCT salary
    FROM Employee
    ORDER BY salary DESC
    LIMIT 1 OFFSET 1) AS SecondHighestSalary;
    Solution using MySQL
    Solution using CTE and IFNULL

    WITH cte AS
    (
    SELECT *
    , DENSE_RANK() OVER(ORDER BY salary DESC) AS rn
    FROM employee
    )

    SELECT IFNULL(
    (SELECT salary
    FROM cte
    WHERE rn = 2
    LIMIT 1), NULL) AS SecondHighestSalary ;
    Solution using MySQL
    Solution using CTE and COALESCE
    /* Even COALESCE can also be used instead of IFNULL*/

    WITH cte AS
    (
    SELECT *
    , DENSE_RANK() OVER(ORDER BY salary DESC) AS rn
    FROM employee
    )

    SELECT COALESCE(
    (SELECT salary
    FROM cte
    WHERE rn = 2
    LIMIT 1), NULL) AS SecondHighestSalary;
    Solution using Python(Pandas)
    import pandas as pd

    # Creating the DataFrame for Example 1


    data1 = {'id': [1, 2, 3], 'salary': [100, 200, 300]}
    df1 = pd.DataFrame(data1)

    # Finding the second highest salary for Example 1


    if len(df1) >= 2:
    result1 = df1['salary'].nlargest(2).iloc[-1]
    else:
    result1 = "null"

    # Displaying the results


    print("Example 1:")
    print("Second Highest Salary:", result1)
    Solution using Python(Pandas)
    # Creating the DataFrame for Example 2
    data2 = {'id': [1], 'salary': [100]}
    df2 = pd.DataFrame(data2)

    # Finding the second highest salary for Example 2


    if len(df2) >= 2:
    result2 = df2['salary'].nlargest(2).iloc[-1]
    else:
    result2 = "null“

    print("\nExample 2:")
    print("Second Highest Salary:", result2)
    LET'S
    CONNECT
    PRAVEEN KUMAR MAURYA

    https://www.linkedin.com/in/pmpraveen802

    You might also like