Common Table Expression in detail and how its used recursively

--LETS DISCUSS IN DETAIL REGARDING THE COMMON TABLE EXPRESSION

--1)WHAT IS COMMON TABLE EXPRESSION ?

--IT IS JUST LIKE TEMPORARY RESULT SET. IT IS DERIVED FROM A SIMPLE QUERY AND DEFINED WITHIN THE EXECUTION SCOPE OF A SINGLE SELECT,INSERT,UPDATE OR DELETE STATEMENT.

--A common table expression can include references to itself. This is referred to as a recursive common table expression. WE WILL DISCUSS THIS LATER IN DETAIL...

CREATE TABLE #TEMP1

(

ID INT IDENTITY PRIMARY KEY NOT NULL,

FILM VARCHAR(100),

RELAEASEDATE datetime

)

insert into #TEMP1

select 'kkhota hai','10/10/1998'

union all select 'sholay','10/10/1975'

union all select 'dosti','10/10/1964'

union all select 'baadshah','10/10/1999'

union all select 'soldier','10/10/1998'

union all select 'chennai express','10/10/2013'

union all select 'dhoom 2','10/10/2006'

union all select 'ghazni','10/10/2009'

union all select 'sehensah','10/10/1986'

union all select 'race','10/10/2005'

union all select 'once upon a time in mumbai dobara','10/10/2013'

select * from #TEMP1

--lets write a cte

with [old films] as

(

select film from #TEMP1

where RELAEASEDATE < '10/10/2000'

)

select * from [old films]

--o/p

kkhota hai

sholay

dosti

baadshah

soldier

sehensah

with [old films] as

(

select film from #TEMP1

where RELAEASEDATE < '10/10/2000'

)

print 'wow these are old movies'

select * from [old films]

--o/p error

Incorrect syntax near the keyword 'print'.

--you cannot do that... immediately after cte..it must be followed by select,insert update or delete

--union in cte

with [old films] as

(

select film from #TEMP1

where RELAEASEDATE < '10/10/2000'

),

[new films] as

(

select film from #TEMP1

where RELAEASEDATE >= '10/10/2000'

)

select * from [old films]

union

select * from [new films]

--look at the syntax closely.... we are using two cte...the second cte starts after comma....and with is not there in second cte..

AVERAGE

============lets explore some new things in cte============================

create table #temp3

(

id int,

countrecords int

)

insert into #temp3

select 1,5

union all select 1,10

union all select 1,15

union all select 2,10

union all select 2,20

union all select 2,10

union all select 2,20

union all select 3,10

union all select 3,10

--now to get average ..and that too by group by...we need to do like below... that looks little messy ..complex...this can be done using CTE.

SELECT AVG(NUMBERS) AS FINALAVERAGE FROM

(

  SELECT       

       SUM(countrecords) AS Numbers

  FROM

        #temp3

  GROUP BY

        id

) T

------------------AVERAGE USING CTE----------------------------

with averagecount(sumrecords) as

(

SELECT

        SUM(countrecords) AS Numbers

FROM

                #temp3

GROUP BY

                id

)

select avg(sumrecords) from averagecount

--we get the same output...now this is understandable.

--SUM(countrecords) AS Numbers --> is stored in sumrecords

--then using the cte[here averagecount].. we are calculating the average...hope its cleared

--the inner query gives me

30

60

20

--110 is sum.

--110/3 is 36.

RECURSIVE CTE

--now lets try to explore recursive cte.

create table #manageremployee

(

[emp id] int,

[emp name] varchar(100),

[man id] int

)

insert into #manageremployee

select '1000','anurag','2000'

union all select '2000','siba','3000'

union all select '3000','mukua','4000'

union all select '4000','sudhansu','5000'

union all select '5000','mike','6000'

union all select '6000','george',null

insert into #manageremployee

select '5500','steven','6000'

union all select '5800','smith','6000'

select * from #manageremployee order by [emp id] desc

--now lets use recursive cte to find out the hierarchy-------------THE BELWO CTE IS VERY INTERESTING...THE SECOND PART IS TOUGH TO UNDERSTAND...BUT DONT WORRRY

--I WILL EXPLAIN YOU

with FINALHIERARCHYREPORT(EMPLOYEE_ID,EMPLOYEE_NAME,MANAGER_ID,MANAGER_NAME,EMPLOYEELEVEL)

AS

(

SELECT

      [emp id],

          [emp name],

          [man id],

          cast('' as varchar(100)) as ManagerName,

          0 as ManagerLevel

FROM #manageremployee

WHERE [MAN ID] IS NULL

UNION ALL

SELECT

     ME.[emp id],

         ME.[emp name],

         ME.[man id],

         ME1.[emp name],

         EMPLOYEELEVEL + 1

FROM

#manageremployee ME

JOIN FINALHIERARCHYREPORT FH ON ME.[man id]=FH.EMPLOYEE_ID

JOIN #manageremployee ME1 ON  ME.[man id]=ME1.[emp id]

    

)

SELECT * FROM FINALHIERARCHYREPORT

--O/P

EMPLOYEE_ID     EMPLOYEE_NAME   MANAGER_ID                MANAGER_NAME            EMPLOYEELEVEL

6000            george            NULL                                                    0

5000            mike              6000                    george                          1

5500            steven            6000                    george                          1

5800            smith             6000                    george                          1

4000            sudhansu          5000                    mike                            2

3000            mukua             4000                    sudhansu                        3

2000            siba              3000                    mukua                           4

1000            anurag            2000                    siba                            5

1500            abhishek          2000                    siba                            5

1800            akash             2000                    siba                            5

--EXPLANATION OF ABOVE PART

--STEP 1

--SELECT

      [emp id],

          [emp name],

          [man id],

          cast('' as varchar(100)) as ManagerName,

          0 as ManagerLevel

FROM #manageremployee

WHERE [MAN ID] IS NULL

--THIS PART IS VERY SIMPLE. ITS SELECTING THE EMPLOYEE ID WHOSE MANAGER ID IS NULL. EMPLOYEELEVEL IS ZERO.

--STEP 2----------------

SELECT

     ME.[emp id],

         ME.[emp name],

         ME.[man id],

         ME1.[emp name],

         EMPLOYEELEVEL + 1

FROM

#manageremployee ME

JOIN FINALHIERARCHYREPORT FH ON ME.[man id]=FH.EMPLOYEE_ID

JOIN #manageremployee ME1 ON  ME.[man id]=ME1.[emp id]

    

--THIS PART IS LITTLE TOUGH.,,LET ME EXPLAIN YOU

select * from #manageremployee order by [emp id] desc

--THIS IS ME

emp id  emp name        man id

6000    george          NULL

5800    smith           6000

5500    steven          6000

5000    mike            6000

4000    sudhansu        5000

3000    mukua           4000

2000    siba            3000

1800    akash           2000

1500    abhishek        2000

1000    anurag          2000

--THIS IS ME1

emp id  emp name        man id

6000    george          NULL

5800    smith           6000

5500    steven          6000

5000    mike            6000

4000    sudhansu        5000

3000    mukua           4000

2000    siba            3000

1800    akash           2000

1500    abhishek        2000

1000    anurag          2000

--HOW RECURSIVE WORKS

--FOR 1ST RECORD.

--GEORGE IS THE EMPLOYEE ID..WHOSE MANAGER ID IS NULL...

--BELOW RECORD FOR GEORGE IS OUR

 FH

emp id  emp name        man id  EMPLOYEELEVEL

6000    george          NULL     0

--------NOW AFTER THIS STEP IS COMPLETED.....

SELECT

     ME.[emp id],

         ME.[emp name],

         ME.[man id],

         ME1.[emp name],

         EMPLOYEELEVEL + 1

FROM

#manageremployee ME

JOIN FINALHIERARCHYREPORT FH ON ME.[man id]=FH.EMPLOYEE_ID

JOIN #manageremployee ME1 ON  ME.[man id]=ME1.[emp id]

--LETS UNDERSTAND THIS FIRST JOIN....

JOIN FINALHIERARCHYREPORT FH ON ME.[man id]=FH.EMPLOYEE_ID

--THE ABOVE JOIN WILL GIVE THOSE RECORDS IN ME WHOSE MANAGERID IS 6000... SO WE HAVE NOW

5800    smith   6000

5500    steven  6000

5000    mike    6000

--LETS UNDERSTAND THIS SECOND JOIN....

JOIN #manageremployee ME1 ON  ME.[man id]=ME1.[emp id]

--THE ABOVE JOIN WILL GIVE LIKE

ME

emp id  emp name        man id

5800    smith           6000

5500    steven          6000

5000    mike            6000

ME1

6000    george  NULL

--WE ARE JOINING ME WITH ME1..AND FOR MANAGER NAME WE NEED TO SELECT ME1.[emp name].... THE ABOVE JOIN I HAVE SHOWN FOR RECORDS 5800 5500 AND 5000. BUT AS THE JOIN IS FOR ENTIRE TABLE..SO IT WILL BE FOR ALL THE RECORDS

--BUT BOTH THE JOINS WHEN COMBINED WE ARE RESTRICTED TO 5800, 5500 AND 5000.. I HOPE YOU GOT IT.... USE PEN AND PAPER IF YOU R CONFUSED...

DELTE DUPLICATE RECORDS USING CTE AND ROW_NUMBER

>>DELETE DUPLICATE RECORDS USING  CTE[ONE OF THE COMMON INTERVIEW QUESTION]<<

CREATE TABLE #DUPLICATE

(

NAME VARCHAR(100),

AGE INT,

SEX VARCHAR(2)

)

INSERT INTO #DUPLICATE

SELECT 'ANURAG',24,'M'

UNION ALL SELECT 'ANURAG',24,'M'

UNION ALL SELECT 'SIBA',24,'M'

UNION ALL SELECT 'SIBA',24,'M'

UNION ALL SELECT 'SWAMY',22,'M'

UNION ALL SELECT 'ABHI',24,'M'

UNION ALL SELECT 'ABHI',24,'M'

UNION ALL SELECT 'MUKUA',26,'M'

UNION ALL SELECT 'MUKUA',26,'M'

UNION ALL SELECT 'MUKUA',26,'M'

select * from #duplicate--duplicate records are there

WITH DELETEDUPLICATE(ROW_NUMB,FIRSTNAME,AGE) AS

(

SELECT ROW_NUMBER() OVER (PARTITION BY NAME ORDER BY NAME)AS ROW_NU,

       NAME,

           SEX

FROM #DUPLICATE

)

SELECT * FROM DELETEDUPLICATE WHERE ROW_NUMB >1

--O/P

ROW_NUMBFIRSTNAME       AGE             SEX

2                       ABHI            M

2                       ANURAG          M

2                       MUKUA           M

3                       MUKUA           M

2                       SIBA            M

--LETS DELETE THIS

WITH DELETEDUPLICATE(ROW_NUMB,FIRSTNAME,AGE) AS

(

SELECT ROW_NUMBER() OVER (PARTITION BY NAME ORDER BY NAME)AS ROW_NU,

       NAME,

           SEX

FROM #DUPLICATE

)

DELETE FROM DELETEDUPLICATE WHERE ROW_NUMB >1

SELECT * FROM #DUPLICATE--O/P

ANURAG  24      M

SIBA    24      M

SWAMY   22      M

ABHI    24      M

MUKUA   26      M

--WOW IT WORKED... J J J