Correlated subqueries for beginners (step by step walkthrough to find Nth highest salary in sql)

 

Correlated Subqueries

Let's explore correlated subqueries with the example of finding the 3rd highest salary among 5 employees. A correlated subquery is a subquery that references columns from the outer query. It executes once for each row processed by the outer query.

Example Table: Employees

ID Name Salary
1 Alice 9000
2 Bob 8000
3 Carol 8500
4 Dave 7000
5 Eve 9500

Query to Find the 3rd Highest Salary

    
    SELECT Salary
    FROM Employees AS E1
    WHERE 2 = (
        SELECT COUNT(DISTINCT Salary)
        FROM Employees AS E2
        WHERE E2.Salary > E1.Salary
    )

Step-by-Step Walkthrough with Diagram

  1. Outer Query (E1): 
                
            SELECT Salary
            FROM Employees AS E1

    This part selects the salary from the outer query's table (E1).

  2. Inner Query (E2): 
                
            SELECT COUNT(DISTINCT Salary)
            FROM Employees AS E2
            WHERE E2.Salary > E1.Salary

    The inner query counts distinct salaries in the inner table (E2) that are greater than the current salary in the outer table (E1).

  3. Condition: 
                
            WHERE 2 = (
                SELECT COUNT(DISTINCT Salary)
                FROM Employees AS E2
                WHERE E2.Salary > E1.Salary
            )

    The condition checks if the count of distinct salaries greater than the current salary (E1.Salary) is equal to 2. This means the current salary should be the 3rd highest.

Comparisons for Each Row with Diagram

  • Row with Alice (Salary = 9000): 
                
            +----+-------+--------+
            | ID | Name  | Salary |
            +----+-------+--------+
            | 1  | Alice | 9000   |
            +----+-------+--------+

    Inner Query: 

                
            SELECT COUNT(DISTINCT Salary)
            FROM Employees AS E2
            WHERE E2.Salary > 9000

    Inner Query Result: 1 (only Eve's salary 9500 is greater)
    Condition: 2 != 1 (not satisfied)

                
            +-----------------------+
            | E2.Salary > E1.Salary |
            +-----------------------+
            | Eve's Salary (9500)   |
            +-----------------------+
            | Result: 1             |
            +-----------------------+
  • Row with Bob (Salary = 8000): 
                
            +----+-------+--------+
            | ID | Name  | Salary |
            +----+-------+--------+
            | 2  | Bob   | 8000   |
            +----+-------+--------+

    Inner Query: 

                
            SELECT COUNT(DISTINCT Salary)
            FROM Employees AS E2
            WHERE E2.Salary > 8000

    Inner Query Result: 3 (Alice's, Carol's, and Eve's salaries are greater)
    Condition: 2 != 3 (not satisfied)

                
            +-----------------------+
            | E2.Salary > E1.Salary |
            +-----------------------+
            | Alice's Salary (9000) |
            | Carol's Salary (8500) |
            | Eve's Salary (9500)   |
            +-----------------------+
            | Result: 3             |
            +-----------------------+
  • Row with Carol (Salary = 8500): 
                
            +----+-------+--------+
            | ID | Name  | Salary |
            +----+-------+--------+
            | 3  | Carol | 8500   |
            +----+-------+--------+

    Inner Query: 

                
            SELECT COUNT(DISTINCT Salary)
            FROM Employees AS E2
            WHERE E2.Salary > 8500

    Inner Query Result: 2 (Alice's and Eve's salaries are greater)
    Condition: 2 = 2 (satisfied)

                
            +-----------------------+
            | E2.Salary > E1.Salary |
            +-----------------------+
            | Alice's Salary (9000) |
            | Eve's Salary (9500)   |
            +-----------------------+
            | Result: 2             |
            +-----------------------+
  • Row with Dave (Salary = 7000): 
                
            +----+-------+--------+
            | ID | Name  | Salary |
            +----+-------+--------+
            | 4  | Dave  | 7000   |
            +----+-------+--------+

    Inner Query: 

                
            SELECT COUNT(DISTINCT Salary)
            FROM Employees AS E2
            WHERE E2.Salary > 7000

    Inner Query Result: 4 (Alice's, Bob's, Carol's, and Eve's salaries are greater)
    Condition: 2 != 4 (not satisfied)

                
            +-----------------------+
            | E2.Salary > E1.Salary |
            +-----------------------+
            | Alice's Salary (9000) |
            | Bob's Salary (8000)   |
            | Carol's Salary (8500) |
            | Eve's Salary (9500)   |
            +-----------------------+
            | Result: 4             |
            +-----------------------+
  • Row with Eve (Salary = 9500): 
                
            +----+-------+--------+
            | ID | Name  | Salary |
            +----+-------+--------+
            | 5  | Eve   | 9500   |
            +----+-------+--------+

    Inner Query: 

                
            SELECT COUNT(DISTINCT Salary)
            FROM Employees AS E2
            WHERE E2.Salary > 9500

    Inner Query Result: 0 (no salaries are greater)
    Condition: 2 != 0 (not satisfied)

                
            +-----------------------+
            | E2.Salary > E1.Salary |
            +-----------------------+
            | None                  |
            +-----------------------+
            | Result: 0             |
            +-----------------------+

Final Result

The 3rd highest salary is 8500 (from Carol).

Support Our Efforts and Earn Together ðŸš€

Visit https://parucodes.github.io/ today and start your journey to becoming a fast, accurate, and confident touch typist.

If you find our website useful and want to support us, consider joining the exciting world of Bitcoin mining on your mobile phone. Follow this link: Mine PI Bitcoin and use my username prarthanadp as your invitation code. With the referral code prarthanadp, you'll receive a special referral bonus.

Thank you for your support! Let's grow and earn together! 🌟

 

Comments

Post a Comment

Popular posts from this blog

Recursion examples for Beginners (step by step code execution walkthrough with python code)

  Factorial Explanation and Example Factorial Definition: The factorial of a non-negative integer n is the product of all positive integers less than or equal to n. It can be defined recursively as: factorial(0) = 1 (Base Case) factorial(n) = n * factorial(n-1) (Recursive Case) +----------------------------------+ | factorial(n) | | +---------------------------+ | | Base Case: | | | factorial(0) = 1 | | +---------------------------+ | | Recursive Case: | | | factorial(n) = n * | | | factorial(n-1)| | +---------------------------+ +----------------------------------+ Python Code for Factorial def factorial(n): if n == 0: return 1 else: return n * factorial(n - 1) # Example Usage print(factorial(5...
Read more

Handling hierarchical Data using Dictionaries for beginners (with python code)

  Handling Hierarchical Data with Python Dictionaries 1. Introduction to Hierarchical Data Hierarchical data is organized into a tree-like structure with different levels of depth. Each level contains unique objects that are connected to objects in the level above. This type of data is common in various domains, such as organizational structures, file systems, and biological classifications. 2. Example Data We'll create a 5-level hierarchical data structure. The data represents an organizational hierarchy with departments, teams, managers, employees, and tasks. data = { "Department1": { "TeamA": { "Manager1": { "Employee1": { "Task1": "Complete report", "Task2": "Attend meeting" }, "Employee2": { "Task1": "Write...
Read more

Word Search in Maze using Depth First Search (with python code and step by step code execution walkthrough)

  1. Introduction In this blog, we will explain how to solve the word search problem in a maze using Depth First Search (DFS) algorithm. We will use a 3x3 maze and search for the word 'the' in the maze. The word 'the' is located in the second row of the maze. 2. Algorithm Explanation The DFS algorithm is used to explore all possible paths in the maze to find the target word. We start from each cell in the maze and recursively explore its neighbors to find the word. 2.1 Steps of the Algorithm Start from each cell in the maze. Check if the current cell matches the current character of the word. Mark the current cell as visited. Recursively explore the neighboring cells (up, down, left, right). Unmark the current cell and backtrack if the word is not found. Return true if the word is found, otherwise return false. 3. Python Code def dfs(maze, word, i, j, index): if index == len(word): return True if i < 0 or i >= len(maze) or j < 0 or j >= len(...
Read more