You are here

Common Table Expressions (CTEs)

Common Table Expressions(CTEs)

In this post we will learn about Common Table Expressions(CTEs) which is one of the table expression. It is similar to derived table in scope and naming requirements.

Unlike derived tables, CTE support multiple references and recursion. The body of the recursive CTE has two or more query separated by UNION ALL operator.

CTE involve three main parts:

i) inner query

ii) the name assign to the query and its column

iii) outer query

Syntax:

WITH <CTE_name>

AS

(

<inner_query>

)

<outer_query>;

Now we will see the example where CTEs are use:

Example:

Create table Product

Create table Product

(

ProId int NOT NULL IDENTITY(1,1) PRIMARY KEY,

CategoryId int NOT NULL,

ProName varchar(100),

UnitPrice money

);

 

Insert records

Insert into Product(CategoryId,ProName,UnitPrice)

Values(101,’Pen’,5),

(102,’Mobile covers’,100),

(103,’Chair’,500),

(104,’Laptop’,10000),

(105,’T-shirt’,200),

(102,’Laptop covers’,200),

(104,’Mobile’,20000);

 

Now we write a query to compute row number for products, partitioned by CategoryId. for Counting row number we use ROW_NUMBER() function. It compute unique incrementing integer from 1 and based on indicated ordering. You can use this function only in SELECT and ORDER BY clause. You are not allowed to use in the query’s WHERE clause.

Select

ROW_NUMBER() OVER(PARTITION BY CategoryId ORDER BY UnitPrice,ProId) as Rowno,

CategoryId,ProId,ProName,UnitPrice

from Product;

Output:

Rowno

CategoryId ProId ProName UnitPrice
1 101 1 Pen

5.00

1

102 2 Mobile covers 100.00
2 102 6 Laptop covers

200.00

1

103 3 Chair 500.00
1 104 4 Laptop

10000.00

2

104 7 Mobile 20000.00
1 105 5 T-shirt

200.00

Now we write a query to retrieve CategoryId that contains 2 products. You count product

Using ROW_NUMBER() function and you cannot use it in WHERE clause, so we use CTE.

WITH C AS

(

SELECT

ROW_NUMBER() OVER(PARTITION BY CategoryId ORDER BY UnitPrice,ProId) as Rowno,

CategoryId,ProId,ProName,UnitPrice

FROM Product

)

SELECT CategoryId,ProId,ProName,UnitPrice

FROM C

WHERE Rowno=2;

Output:

CategoryId

ProdId

ProName

UnitPrice

102

6 Laptop covers 200.00
104 7 Mobile

20000.00

So, using this way you can achieve your goal.

CTE

Derived Table

CTE can be referenced multiple times in the same query.

Derived table cannot be referenced multiple times.

You can use CTEs in recursive query. You cannot use derived tables in recursive queries.
CTE has better structure then derived tables. Derived table’s structure is not good as compare to CTE.

Now, you can use 70-761 practice test for clearing your exam. this practice test help you for your actual exam. it is created by me, and you can buy it from udemy. this course covers all topics of the syllabus. so don’t wary just try it.

if you buy it using below link you get only in 10 dollar. so harry up!!!!!!!!

70-761-querying-data-with-transact-sql-practice-test

70-761 pactice test

Leave a Reply

Top