1.

How Can We Rewrite Sub-queries Into Simple Select Statements Or With Joins?

Answer»

YES we can write using COMMON Table Expression (CTE). A Common Table Expression (CTE) is an expression that can be thought of as a temporary result set which is defined within the EXECUTION of a single SQL 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.

E.g.

USE AdventureWorks
GO
WITH EmployeeDepartment_CTE AS (
SELECT EmployeeID,DepartmentID,ShiftID
FROM HumanResources.EmployeeDepartmentHistory
)
SELECT ecte.EmployeeId,ed.DepartmentID, ed.Name,ecte.ShiftID
FROM HumanResources.Department ed
INNER JOIN EmployeeDepartment_CTE ecte ON ecte.DepartmentID =ed.DepartmentID
GO

 

Yes we can write using Common Table Expression (CTE). A Common Table Expression (CTE) is an expression that can be thought of as a temporary result set which is defined within the execution of a single SQL 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.

E.g.

 


Discussion

No Comment Found

Related InterviewSolutions