Learning of SQL Day 26

 

Day 26: Correlated Subqueries

Introduction:

• A correlated subquery is a subquery that uses values from the outer query. It is executed repeatedly, once for each row processed by the outer query.

Key Concepts:

• Correlated Subquery: A subquery that references columns from the outer query.

• Dependency: The subquery depends on the outer query for its values.

SQL Commands and Examples:

1. Basic Correlated Subquery:

   • A subquery that references a column from the outer query.

sql

-- Select students whose age is greater than the average age of students in the same course
SELECT FirstName, LastName, Age
FROM Students S1
WHERE Age > (SELECT AVG(Age) FROM Students S2 WHERE S1.CourseID = S2.CourseID);

2. Using Correlated Subqueries with EXISTS:

   • Check the existence of rows in a subquery that meet a condition.

sql

-- Select courses that have students enrolled
SELECT CourseName
FROM Courses C
WHERE EXISTS (SELECT 1 FROM Students S WHERE S.CourseID = C.CourseID);

3. Filtering with Correlated Subqueries:

   • Use correlated subqueries to filter results based on complex conditions.

sql

-- Select employees whose salary is greater than the average salary of their department
SELECT FirstName, LastName, Salary
FROM Employees E1
WHERE Salary > (SELECT AVG(Salary) FROM Employees E2 WHERE E1.Department = E2.Department);

Practice Exercise:

1. Use the Students and Courses tables.

2. Write a query to select students whose age is greater than the average age of students in the same course.

3. Write a query to select courses that have students enrolled.

4. Use the Employees table and write a query to select employees whose salary is greater than the average salary of their department.

sql

-- Select students whose age is greater than the average age of students in the same course
SELECT FirstName, LastName, Age
FROM Students S1
WHERE Age > (SELECT AVG(Age) FROM Students S2 WHERE S1.CourseID = S2.CourseID);

-- Select courses that have students enrolled
SELECT CourseName
FROM Courses C
WHERE EXISTS (SELECT 1 FROM Students S WHERE S.CourseID = C.CourseID);

-- Select employees whose salary is greater than the average salary of their department
SELECT FirstName, LastName, Salary
FROM Employees E1
WHERE Salary > (SELECT AVG(Salary) FROM Employees E2 WHERE E1.Department = E2.Department);

Important Tips:

• Correlated subqueries can be less efficient than regular subqueries because they are executed once for each row processed by the outer query.

• Use correlated subqueries when you need to reference values from the outer query to perform the necessary calculations or conditions.

Understanding correlated subqueries allows you to create more dynamic and complex queries that depend on values from the outer query.