Lecture_5_CTE_SQL_Server

1. Lecture 5: Common Table Expressions (CTE)

CTE • Recursive Queries
Analytical SQL
SQL Server
LECTURE 5:
COMMON TABLE
EXPRESSIONS (CTE)

2. Learning Objectives

• Understand what a
CTE is
LEARNING
OBJECTIVES
• Use CTEs to simplify
complex queries
• Compare CTEs with
subqueries
• Write readable
analytical SQL

3. Why CTEs Are Needed

WHY CTES
ARE NEEDED
Complex SQL becomes
hard to read
CTEs improve clarity
and structure

4. What Is a CTE

WHAT IS A
CTE
A temporary named
result set
Exists only during
query execution

5. Basic CTE Syntax

WITH CTE_Name AS (
SELECT ...
)
SELECT * FROM CTE_Name;
BASIC CTE SYNTAX

6. Simple CTE Example

WITH StudentAvg AS (
SELECT StudentID, AVG(CAST(Score AS FLOAT)) AS
AvgScore
FROM Submissions
GROUP BY StudentID
)
SELECT * FROM StudentAvg;
SIMPLE CTE EXAMPLE

7. CTE vs Subquery

CTE VS
SUBQUERY
CTEs are more readable
Subqueries are nested
and harder to debug

8. CTE with JOIN

WITH CourseAvg AS (
SELECT CourseID, AVG(CAST(Score AS
FLOAT)) AS AvgScore
FROM Submissions
GROUP BY CourseID
)
SELECT c.CourseName, ca.AvgScore
FROM CourseAvg ca
JOIN Courses c ON c.CourseID = ca.CourseID;
CTE WITH JOIN

9. Filtering CTE Results

WITH CourseAvg AS (
SELECT CourseID, AVG(CAST(Score AS
FLOAT)) AS AvgScore
FROM Submissions
GROUP BY CourseID
)
SELECT * FROM CourseAvg
WHERE AvgScore >= 80;
FILTERING CTE RESULTS

10. Multiple CTEs

WITH StudentAvg AS (
SELECT StudentID, AVG(CAST(Score AS FLOAT))
AS AvgScore
FROM Submissions GROUP BY StudentID
),
TopStudents AS (
SELECT * FROM StudentAvg WHERE AvgScore >= 85
)
SELECT * FROM TopStudents;
MULTIPLE CTES

11. Recursive CTE

WITH Numbers AS (
SELECT 1 AS Num
UNION ALL
SELECT Num + 1 FROM Numbers WHERE Num
< 5
)
SELECT * FROM Numbers;
RECURSIVE CTE

12. Execution Order

EXECUTION ORDER

13. Typical Student Mistakes

• Forgetting WITH keyword
• Missing SELECT after CTE
• Overusing subqueries
TYPICAL STUDENT MISTAKES

14. Lecture Summary

CTEs simplify SQL
Prepare for window functions
LECTURE SUMMARY