Working with Table Expressions

Introduction

The Working with Table Expressions module provides you with the instructions and devices to develop your hands-on skills in the following topics.

• Working with Common Table Expression (CTE)
• Using CTE with AVG function
• Working with recursive CTE

Lab time: It will take approximately 45 minutes to complete this lab.

Exam Objectives

The following exam objectives are covered in this lab:

• Create a simple common table expression and reference the CTE in a SELECT query
• Create a common table expression to report averages
• Create a recursive common table expression to display multiple levels of recursion (a hierarchical list)

Exercise 1 - Working with Common Table Expression (CTE)

Common Table Expression (CTE) is a temporary table that stores the result derived from the use of SELECT, UPDATE, INSERT, DELETE, or CREATE VIEW statements in a query. The CTE is not stored as an object in the database and exists only till the execution of the query. CTE can be self-referencing. In other words, it can refer to itself in a query statement. In addition, it can also be referenced multiple times in a single query statement.

Some of the advantages of using CTE are as follows:

• It improves the readability and maintenance of a complex query.
• It helps divide the query into simple logical blocks.

Exercise 2 - Using CTE with AVG Function

CTE can be used with mathematical functions such as AVG(), SUM(), ROUND(), and so on.

In this exercise, you will learn to use CTE with the AVG() function.

Exercise 3 - Working with Recursive CTE

One of the advantages of using a CTE is that it can be referenced to itself. Such self-referencing CTE is known as a recursive CTE. The recursive CTE definition includes at least two CTE query definitions. The first CTE definition or the primary CTE is known as the anchor member. The second CTE definition or the self-referenced CTE is known as the recursive member. The number of columns in the anchor and recursive members should be same. The data type of fields in the recursive member must exactly match the data type of the corresponding fields in the anchor member.