Correlated Subqueries:

In a correlated subquery, the inner query depends on the outer query. It references columns from the outer query, and for each row processed by the outer query, the inner query is executed.

Example:

SELECT employee_id, first_name, salary
FROM employees e1
WHERE salary > (SELECT AVG(salary) FROM employees e2 WHERE e1.department_id = e2.department_id);

In this case, the inner query needs the department ID from the outer query to calculate the average salary for that specific department. Therefore, it is executed for each row of the outer query
.
.
.

Example of Correlated subquery

Identify employees who earn more than the average salary of their respective departments.
.
.
.

Step 1: Create the Employees Table

First, we create a table named employees with relevant fields.

CREATE TABLE employees (
employee_id NUMBER PRIMARY KEY,
first_name VARCHAR2(50),
last_name VARCHAR2(50),
department_id NUMBER,
salary NUMBER
);

Step 2: Insert Sample Data

Now, let's insert some sample data into the employees table. This data includes employee details along with their respective department IDs and salaries.

INSERT INTO employees (employee_id, first_name, last_name, department_id, salary) VALUES (1, 'John', 'Doe', 10, 60000);
INSERT INTO employees (employee_id, first_name, last_name, department_id, salary) VALUES (2, 'Jane', 'Smith', 20, 75000);
INSERT INTO employees (employee_id, first_name, last_name, department_id, salary) VALUES (3, 'Alice', 'Johnson', 10, 50000);
INSERT INTO employees (employee_id, first_name, last_name, department_id, salary) VALUES (4, 'Bob', 'Brown', 20, 80000);
INSERT INTO employees (employee_id, first_name, last_name, department_id, salary) VALUES (5, 'Charlie', 'Davis', 30, 45000);
INSERT INTO employees (employee_id, first_name, last_name, department_id, salary) VALUES (6, 'Eve', 'Wilson', 30, 70000);

Explanation of Sample Data

Employees with IDs 1 and 3 belong to Department 10 with salaries of 60,000 and 50,000, respectively.

Employees with IDs 2 and 4 belong to Department 20 with salaries of 75,000 and 80,000.

Employees with IDs 5 and 6 belong to Department 30 with salaries of 45,000 and 70,000.

Step 3: Write a Correlated Subquery

Now, we will write a correlated subquery to find employees who earn more than the average salary of their respective departments.

SELECT employee_id, first_name, salary
FROM employees e1
WHERE salary > (SELECT AVG(salary)
FROM employees e2
WHERE e1.department_id = e2.department_id);

Explanation of the Correlated Subquery

1. Outer Query:

The outer query selects employee_id, first_name, and salary from the employees table, aliased as e1.

1. Inner Query (Correlated Subquery):

The inner query calculates the average salary of employees (AVG(salary)) from the same employees table, aliased as e2.

It uses a condition (WHERE e1.department_id = e2.department_id) to correlate with the current row of the outer query, meaning it finds the average salary for the same department as the employee being considered in the outer query.

1. Execution:

For each employee in the outer query, the inner query computes the average salary of employees in the same department.

The outer query then checks if that employee's salary is greater than this average salary.

Step 4: Execute the Query

When you execute the above query, it will return the employees whose salaries are higher than the average salaries of their respective departments.
Detailed Execution of the Correlated Subquery

For each employee in the outer query:

For Employee 1 (John Doe):

Department 10: Average salary is (60000 + 50000) / 2 = 55000. John’s salary (60000) > 55000.

For Employee 2 (Jane Smith):

Department 20: Average salary is (75000 + 80000) / 2 = 77500. Jane’s salary (75000) < 77500.

For Employee 3 (Alice Johnson):

Department 10: Average salary is still 55000. Alice’s salary (50000) < 55000.

For Employee 4 (Bob Brown):

Department 20: Average salary is still 77500. Bob’s salary (80000) > 77500.

For Employee 5 (Charlie Davis):

Department 30: Average salary is (45000 + 70000) / 2 = 57500. Charlie’s salary (45000) < 57500.

For Employee 6 (Eve Wilson):

Department 30: Average salary is still 57500. Eve’s salary (70000) > 57500

Conclusion

The correlated subquery has successfully filtered the employees, returning only those who earn more than the average salary of their respective departments. This step-by-step execution illustrates how to set up a table, insert data, and apply a correlated subquery in an SQL environment.

Conclusion

This example illustrates the creation of a correlated subquery in an employee context. It demonstrates how to create a table, insert data, and use a correlated subquery to derive results based on dynamic calculations relevant to each employee's department.