How To Use Common Table Expressions

Question

Anonymous User · Answer

A common table expression (CTE) can be thought of as a temporary result set that is defined within the execution scope of a single SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement. A CTE is similar to a derived table in that it is not stored as an object and lasts only for the duration of the query. Unlike a derived table, a CTE can be self-referencing and can be referenced multiple times in the same query. CREATE TABLE [CUSTOMER](	[CUST_ID] [bigint] IDENTITY(1,1)PRIMARY KEY,	[CUST_NAME] [varchar](200) NULL,	[CUST_EMAILID] [varchar](100) NULL,	[CUST_PHONE] [varchar](20) NULL,	[CUST_ADDRESS] [varchar](800) NULL) SELECT * FROM [CUSTOMER]and 
CREATE TABLE [ORDER](
	[ORD_ID] [bigint] IDENTITY(1,1) PRIMARY KEY,	[CUST_ID] [bigint] NULL,	[PRODUCT_ID] [bigint] NULL,	[ORD_DATE] [date] NULL )
 SELECT * FROM [ORDER]and 
CREATE TABLE [PRODUCT](
	[PRODUCT_ID] [bigint] IDENTITY(1,1) PRIMARY KEY,
	[PRODUCT_NAME] [varchar](200) NULL,
	[PRICE] [decimal](10, 2) NULL
)
SELECT * FROM [PRODUCT]
and 
　;With CTE(ORD_ID, ORD_DATE, CUST_NAME,CUST_EMAILID,CUST_PHONE,PRODUCT_NAME,PRICE) AS	 (		 SELECT O.ORD_ID,O.ORD_DATE,C.CUST_NAME,C.CUST_EMAILID,C.CUST_PHONE,P.PRODUCT_NAME,P.PRICE FROM 
				[ORDER] AS [O] INNER JOIN CUSTOMER AS [C] ON O.CUST_ID=C.CUST_ID
				INNER JOIN PRODUCT AS [P] ON P.PRODUCT_ID=O.PRODUCT_ID 
	 )	 SELECT * FROM CTE WHERE PRODUCT_NAME='COMPUTER' --Using CTEand and

forum

How To Use Common Table Expressions

Anonymous User

1 Answers

Liked By