Learning of SQL Day 27

 

Day 27: Common Table Expressions (CTE)

Introduction:

• A Common Table Expression (CTE) is a temporary result set that you can reference within a SELECT, INSERT, UPDATE, or DELETE statement. CTEs improve readability and make it easier to manage complex queries.

Key Concepts:

• CTE: A temporary result set defined within an SQL statement.

• WITH Clause: Used to define a CTE.

SQL Commands and Examples:

1. Basic CTE:

   • Define a simple CTE and use it in a SELECT statement.

sql

-- Define a CTE named CourseDetails and use it to select course names and credits
WITH CourseDetails AS (
    SELECT CourseID, CourseName, Credits
    FROM Courses
)
SELECT CourseName, Credits FROM CourseDetails;

2. CTE with Multiple References:

   • Use a CTE multiple times within the same query.

sql

-- Define a CTE named StudentAges and use it to select and filter students
WITH StudentAges AS (
    SELECT StudentID, FirstName, LastName, Age
    FROM Students
)
SELECT * FROM StudentAges WHERE Age > 21;

3. Recursive CTE:

   • Define a recursive CTE to perform operations like hierarchical data queries.

sql

-- Example of a recursive CTE to generate a sequence of numbers
WITH RECURSIVE Numbers AS (
    SELECT 1 AS Number
    UNION ALL
    SELECT Number + 1
    FROM Numbers
    WHERE Number < 10
)
SELECT * FROM Numbers;

Practice Exercise:

1. Use the Students and Courses tables.

2. Write a query to define a CTE named EnrolledStudents that selects StudentID, FirstName, and CourseID from the Students table.

3. Use the EnrolledStudents CTE to select student names and course names.

4. Write a recursive CTE to generate a sequence of numbers from 1 to 20.

sql

-- Define a CTE named EnrolledStudents
WITH EnrolledStudents AS (
    SELECT StudentID, FirstName, CourseID
    FROM Students
)
-- Use the EnrolledStudents CTE to select student names and course names
SELECT EnrolledStudents.FirstName, Courses.CourseName
FROM EnrolledStudents
INNER JOIN Courses ON EnrolledStudents.CourseID = Courses.CourseID;

-- Define a recursive CTE to generate a sequence of numbers from 1 to 20
WITH RECURSIVE Sequence AS (
    SELECT 1 AS Number
    UNION ALL
    SELECT Number + 1
    FROM Sequence
    WHERE Number < 20
)
SELECT * FROM Sequence;

Important Tips:

• Use CTEs to simplify complex queries and improve readability.

• Recursive CTEs are powerful for working with hierarchical or sequential data but can be more resource-intensive. Always use them carefully.

Mastering Common Table Expressions will greatly enhance your ability to write clear and efficient SQL queries.