Learning of SQL Day 25

 

Day 25: Subqueries

Introduction:

• A subquery is a query nested inside another query. Subqueries can be used in various parts of an SQL statement, such as the SELECT, FROM, WHERE, and HAVING clauses.

Key Concepts:

• Subquery: A query nested inside another query.

• Inner Query: The query inside the main query.

• Outer Query: The main query that contains the subquery.

SQL Commands and Examples:

1. Subquery in SELECT Clause:

   • Use a subquery to calculate a value for each row in the main query.

sql

-- Select students and their total credits from courses they are enrolled in
SELECT FirstName, LastName, (SELECT SUM(Credits) FROM Courses WHERE Students.CourseID = Courses.CourseID) AS TotalCredits
FROM Students;

2. Subquery in WHERE Clause:

   • Use a subquery to filter rows in the main query.

sql

-- Select students enrolled in courses with more than 3 credits
SELECT FirstName, LastName
FROM Students
WHERE CourseID IN (SELECT CourseID FROM Courses WHERE Credits > 3);

3. Subquery in FROM Clause:

   • Use a subquery as a table within the FROM clause.

sql

-- Select the average age of students grouped by the courses they are enrolled in
SELECT CourseName, AVG(Age) AS AverageAge
FROM (SELECT Students.CourseID, Courses.CourseName, Students.Age
      FROM Students
      INNER JOIN Courses ON Students.CourseID = Courses.CourseID) AS Subquery
GROUP BY CourseName;

Practice Exercise:

1. Use the Students and Courses tables.

2. Write a query to select students and their total credits from courses they are enrolled in.

3. Write a query to select students enrolled in courses with more than 4 credits.

4. Write a query to select the average age of students grouped by the courses they are enrolled in.

sql

-- Select students and their total credits from courses they are enrolled in
SELECT FirstName, LastName, (SELECT SUM(Credits) FROM Courses WHERE Students.CourseID = Courses.CourseID) AS TotalCredits
FROM Students;

-- Select students enrolled in courses with more than 4 credits
SELECT FirstName, LastName
FROM Students
WHERE CourseID IN (SELECT CourseID FROM Courses WHERE Credits > 4);

-- Select the average age of students grouped by the courses they are enrolled in
SELECT CourseName, AVG(Age) AS AverageAge
FROM (SELECT Students.CourseID, Courses.CourseName, Students.Age
      FROM Students
      INNER JOIN Courses ON Students.CourseID = Courses.CourseID) AS Subquery
GROUP BY CourseName;

Subqueries are powerful tools that enable complex and flexible queries. By mastering subqueries, you'll enhance your ability to perform advanced data manipulations and analysis.