CTE & RECURSIVE CTE(Common Table Expression)

Common Table Expression

Simple CTE

Print 1 to N numbers

;
WITH CTE AS
(
    SELECT NoM=1
    UNION ALL
    SELECT NoM+1 FROM CTE WHERE NOM+1<=10
) SELECT * FROM CTE

Ans:

Find the Employees under manager 

;WITH X AS
(
    SELECT * FROM EMP WHERE mgr=7788
    UNION ALL
    SELECT e.* FROM x  INNER JOIN emp e ON e.empno=x.mgr
)
SELECT * FROM X

Ans:

Find Cumulative Salary of employees 

select SAL,(select SUM(sal) from emp e where e.empno<=a.empno)as CUM_SAL from emp a