Scribd
Upload
29 views4 pages

L46 SQL-Developer-Lecture-25

The document explains the use of Common Table Expressions (CTEs) in SQL, demonstrating how they can simplify complex queries by allowing the definition of subqueries with aliases. It provides examples of single and multiple CTEs, illustrating how to select and filter data from temporary tables. Additionally, it includes an example of a recursive CTE to generate a series of statements.

Uploaded by

ambeydeveloper
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
29 views4 pages

L46 SQL-Developer-Lecture-25

The document explains the use of Common Table Expressions (CTEs) in SQL, demonstrating how they can simplify complex queries by allowing the definition of subqueries with aliases. It provides examples of single and multiple CTEs, illustrating how to select and filter data from temporary tables. Additionally, it includes an example of a recursive CTE to generate a series of statements.

Uploaded by

ambeydeveloper
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd

CTE

Intellipaat Software Solutions Pvt. Ltd.


SELECT * FROM ( SELECT A.Address, E.Name, E.Age From Address A
Inner join Employee E on E.EID = A.EID) T
WHERE T.Age > 50 ORDER BY T.NAME

Which can be replace by CTE (Comman table Expression)

CTE allows you to define the subquery at once, name it using an alias
and later call the same data using the alias just like what you do with a
normal table.

With T(Address, Name, Age) --Column names for Temporary table


AS ( SELECT A.Address, E.Name, E.Age from Address A INNER JOIN
EMP E ON E.EID = A.EID )
SELECT * FROM T --SELECT or USE CTE temporary Table
WHERE T.Age > 50 ORDER BY T.NAME
Multiple CTE

Intellipaat Software Solutions Pvt. Ltd.


With T1(Address, Name, Age) --Column names for Temporary table
AS ( SELECT A.Address, E.Name, E.Age from Address A INNER JOIN
EMP E ON E.EID = A.EID )
,
T2(Name, Desig)
AS
( SELECT NAME, DESIG FROM Designation)

SELECT T1.*, T2.Desig FROM T1 --SELECT or USE CTE temporary Table


WHERE T1.Age > 50 AND T1.Name = T2.Name
ORDER BY T1.NAME
Awful Series

Intellipaat Software Solutions Pvt. Ltd.


WITH ShowMessage(STATEMENT, LENGTH)
AS
( SELECT STATEMENT = CAST('I Like ' AS VARCHAR(300)),
LEN('I Like ')

UNION ALL

SELECT CAST(STATEMENT + 'CodeProject! ' AS VARCHAR(300))


, LEN(STATEMENT) FROM ShowMessage WHERE LENGTH < 300 )

SELECT STATEMENT, LENGTH FROM ShowMessage


Intellipaat Software Solutions Pvt. Ltd.
Open SAS

You might also like