SQL Class 18: Mastering Common Table Expression (CTE), Lag, Lead Functions in SQL

hi my dear friends welcome back to another class of SQL and in this class we are going to see some additional things like uh Common Table Expressions lag function lead function all these things okay so uh first go back to our main database that we had created use Class 2 and activate this and press the execute button and uh we had previously in our videos discussed about these predefined functions okay then uh we had discussed about Windows functions like rank dense rank so if you have not uh gone through those videos you can check them out okay now in today's class we are going to see how what is Common Table expression and the short form is CTE okay the short form is CTE it is with the help of CTE that is Common Table expression we can create a temporary table okay and that temporary table will exist for that particular session only so whenever you log back into your SQL system you will have to again uh select and execute okay that is recreate that particular temporary table because it exists for only that particular session for which you're using it so you have to give the command with at line number 82 if you go with CTE as CTE stands for Common Table expression now this will give you a temporary table and in place of CTE you can give any other name also okay here the name doesn't matter but normally conventionally we use CTE so in line number2 if you follow with CTE as the this command you give then you have to give parenthesis that is first single bracket which starts here and it basically ends here okay and inside this parenthesis you have to write your SQL statements or SQL queries like here here I have written select ASX comma so it will dis all the fields from a table and all the rows and uh what I'm trying to display here mainly mainly row underscore number function I'm using if you remember from our previous video I had explained about the row underscore number function this will generate a column and in that column you can see the row numbers being allocated so the first row will definitely have row number one under that column second row will have the value of row number two under that column okay so this row underscore number function will generate a column for you and uh under that column you will be able to see the row numbers being allocated row by row okay and uh whenever you have to use this kind of function row underscore number you have to use this Over clause so over like over what over some column you have to mention so normally I'm mentioning here over order by score descending DC for descending so if you remember uh we previously had created a table train is one table there we had this column score so this row underscore number uh function I'm applying over what I'm applying over the score column where the score column will be arranged in descending order okay and whenever you have to arrange things in ascending or descending order then for that what we do we use the order by Clause order by Clause we use then give the column which you want to arrange in ascending or descending order and then after that if you want in ascending order then no need to mention DEC otherwise if you want to mention otherwise if you if you want to arrange in descending order then you have to mention DEC okay so row underscore number function I want to apply over the score column so for so row underscore number function I'm trying to apply over the score column and where the score column will be arranged in descending order okay and uh as I told you this row underscore number function will generate a column for you where you will have the row values row numbers so I'm giving the name of that column as rore num and from which table from traines one table okay and if you move up a little bit you see if you remember at line number 66 or 63 we had created this table traines one where we had uh given field names like ID number then training name scores score field for our various traines and then we inserted various values into this particular table okay there were seven records which we had inserted here so now I have written I've done nothing extra I've have simply here select select ASX comma and generate the row number over the score column okay and where the score column will be added in descending order and this row underscore number function will generate a column and I'm giving the name of that column as rore num from which table from train is one table and my parenthesis ends here and in the next line I'm writing select ASX from CTE where row underscore num is less than three so how the temporary table will be created and how you can manipulate it is that go to line number 82 and then from lines 82 to 84 you select everything okay that is with CTE as then the entire sqol statement inside the parenthesis and after that line numbers 83 and 84 also you have to select select ASX from CTE select asri from CTE this temporary table we created CTE now I'm trying to display my records from this particular temporary table CTE how now where row number is less than three okay that means the top two rows I want to be displayed the top two rows I want to be displayed okay now see only line number 82 if you select then what will happen is is go to execute and see incorrect syntax so in order to create a temporary table like this with CTE as and then the parenthesis inside the parenthesis you give your select and SQL statement and only select that one to execute that won't work to create a temporary table in the very next line you have to select and uh either display selected fields or display all the fields for displaying all the fields you can use ASX and you have to give select as from or select whatever Fields you want to display from this Common Table expression CTE which will which has already created internally a temporary table for you how now where where row number is less than three so uh the first top two rows will should be displayed here select lines 82 to 84 go to execute button and here you see the top two rows are displayed here okay uh if you if I go to line number 79 in our previous class we had seen how this particular uh line this particular statement walked okay that is 70 Line number 78 I have selected execute and you see this was our uh original output query and in this query all the uh traines names were displayed along with their ranks and dense ranks row numers scores and everything so this particular table I'm talking about now here here the top two rows like from lines 82 to 84 of what I'm trying to do uh in this output query how many rows we have got we have total seven rows and I don't want to see all the seven rows I want to see only the top two rows so that's why I've given here row underscore num this row underscore num I'm getting from where this row underscore num is the name of this column that I gave here where I use the row underscore number function to create this column and then I renamed that column as rowcor num and this particular column has only the row number values okay so lines 82 to 84 if you go and select and then execute you see the top two rows are only shown similarly similarly if you want to uh do something like this that is with c as with CTE as and then give something like this only select as tricks comma rore number over order by score descending format row underscore num I give the name of the column which I'm generating and uh from traines one if you give okay and here I just want to here I just want to display the ID number and rore number like in our previous uh in this previous output in the previous output query where we got our temporary table here we were displaying what idore number trainy underscore name score and row underscore number in our original table of traines one we had mainly these three Fields idore number trainy underscore name and score and this extra field we this extra field we created with the help of the row underscore number function okay now I do not want to display all these uh Fields I want to display s Ed Fields so that's why instead of star after select instead of giving star I'm mentioning the field names which are ID number and row num that is row number that is the name of the field which has been you know created here with the help of the rore number function okay and I'm selecting this from the CTE from my temporary table because temporary table also has these okay and how what is the condition I'm giving where the row underscore number is less than equals to 4 that means maximum the top four rows I want to display okay less than less than equals to four means uh less than equals to four means the top four rows here will be displayed from our temporary table so go to line number 86 then from lines 86 to 88 select and hit the execute button and here you go the top four rows the top four rows are displayed here and all the fields are not displayed here okay name is not displayed here score is not displayed here only _ number is displayed here and row underscore num displayed here which this row underscore number that isore num field we we we created in our Common Table expression and the top four rows are displayed here okay then uh go to line number 90 now in line number 90 I have created a employ sales table Suppose there is a sales company and uh we have certain employees and their database I want to check that is what is their ID what is what is their ID values what are their names okay uh how much how much sales those employees have made and all this kind of stuff so we know how to create table so create table and give name of the table as empore sales and these are the fields that I have selected employee empore ID means employee ID data type I've selected as int so employ ID field that will contain the employe IDS then empore name that is employee name okay empore name will be our second field data type is Vare 40 and under this field you will have the employee names and then the last field that is the last column is sales undor cost that is our sales cost how much uh sales each employee has made okay the data type is int and this will be our third field and this will contain all the sales values for our corresponding employees and go to line number 93 and and here I'm inserting some values into my table _ sales table values like in the first record you will have employee ID one employee name is Joy Bandy and uh suppose the sales value this person made the sales cost this person has uh made is 72,000 then similarly in the second row we have the second record that is employee id2 employee name is Johnny Smith and sales cost is uh 704 like this I have created Total six records okay in the third you have employ in the third R you have employ ID 3 and the name of the employee Arjun Kam and the sales cost Valu is 84700 similarly go to row number four here the employ ID is four and the employee name is sherna Joi and the sales cost made is 63,000 row number five you go here the employee ID I've given here as five and the employee name is Deon Smith and the sales cost correspondingly is 78995 and for the last record employ ID is six and uh employee name is mju goth and uh the sales cost this person has made for the company is 46180 okay so once you basically select this uh entire lines 93 to 99 then your MP empore sales table will be basically displayed okay uh if I want to display it go to line number 101 already I've created this table that's why I didn't hit the execute button now just uh display all the fields and records from this table and here we go okay empore ID field consisting of the employee IDs empore name field consisting of the employee names sales cost sales unor cost field consisting of the sales made by these employees okay so we have something known as lag function okay so go to line number 103 here what I'm trying to display is Select empore name so the employee name sales uncore cost and I'm the sales uncore cost I'm renaming here that is when the output query will be displayed when I run line number 103 output query will be displayed then the name of the field instead of sales uncore cost it will be rather present uncore sales what is the present sales okay so what are the fields mainly empore name employee names sales uncore cost okay how much cost the employees have made okay comma comma then something is known as lag function okay what this lag function will do that we will understand from our output query now this lag function this lag function on which column I'm trying to apply I'm trying to apply it over the sales uncore cost column okay so lag over lag on which column I'm trying to apply this lag function sales uncore cost column okay over over then again you have to mention order by sales Sal underscore cost so the here the sales uncore cost that is this uh column present uncore sales this will be arranged in ascending order okay employee names will be accordingly adjusted because my sales unor cost column which is now known as presentor sales for this particular query only when I like when I run line number 103 then instead of sales uncore cost field name the field name will be presentor sales that will be arranged in ascending order okay thanks to what thanks to this entire part thanks to this entire part where I'm applying where I'm applying lag function on the sales unor cost column and here you have to mention like lag if you have to use you have to use it with the Over clause over over again mainly we focus or mainly use this Over clause on our Target columns mainly integer columns where that is our numerical columns okay okay where sales uncore cost is a numerical column and integer column over what column over this sales uncore cost column and this sales underscore cost column has to be arranged in ascending order hence I've written here inside the Over clause that is inside the parenthesis of the Over clause that order by sales uncore cost okay and when this lag function overall this lag function will generate a column and I'm uh and when that column will be displayed then the name of that column will be previous transaction previous transaction from which table I'm trying to display it from this empore sales table okay so uh you have to select this line number 103 and go to the execute button and execute you see empore name is shown okay very good then uh this second field is present uncore sales which is what which is sales uncore cost column how is it arranged don't you think it is arranged in ascending order see 46180 then 63,000 then 7,400 then 72,000 then 78995 then last 84700 so from lowest to highest ascending order okay and what is the name of the column here that I chose as present uncore sales isn't it present uncore sales and then with respect to this lag function with respect to this lag function this also generated a column and I gave the name of that column as previous Transaction what was the previous transaction or previous sales cost made by that particular employee okay so here you have this column and how is it arranged you see uh for the first for the first row for the first the first row under this column is null okay then in the second row what happens is this 46180 comes here then in the third row the 63,000 of present cells comes here okay then in the fourth row this 7,400 comes here okay then in this particular row 70,000 is here in the fifth row and uh if you go to the present underscore sales column then 72,000 is present in the fourth row so whatever value is there whatever value is there in the second row that will go into the third row okay whatever value is present in the first row that will go into the second row and the first row of this type of a column will be null because prior to 46180 there is no other value okay 46180 if it belongs to the first row of present sales column then it will go to the second row 46180 when it belongs to the first row of present sales column then it will go to the second row of previous uncore transaction column before uh 46180 that is prior to this value there is no other value so the first row of previous underscore transaction column will go null okay now the second row value the second row value of pre present sales will go the second row value of present underscore sales column which is 63,000 it will go to the third row of the previous underscore transaction column okay similarly the third row value 7,400 of present underscore sales column it will go to the fourth row of previous uncore transaction column then 70 72,000 which belongs to the fourth row of the present sales column it will go to the fifth row of the previous uncore transaction column similarly 78,000 995 which belongs to the fifth row of the present uncore sales column this will go to the sixth row okay uh fifth row of present sales value is what that is 78995 fifth row of present uncore sales column consist of this value 78995 and this will go to the sixth row of the previous underscore trans action column now total how many entries we have in this uh Table Six rows there is six records okay so 84700 will go nowhere okay this is the job of the lag function in many uh High databases where we have to compare like this uh where we have to compare present and previous then we normally go for this lag function okay then some something uh then there is another function known as lead function if you come back to this line number 105 I'm doing the same thing C select empore name so empore name means employee names sales under comma sales uncore cost field and that I'm renaming as presentor transaction so this present the sales uncore cost field will be displayed as the second field but at that point of time the name of that field will be presentor transaction then comma lag function I'm giving you have to you have to mention the particular column on which you want to apply your lag function and lag function comes with this Over clause inside this also you have to mention that same particular column and better be an integer column and you also you have to mention the order in which you want to arrange that column like in our case it is sales uncore cost which we are arranging in ascending order thanks to this order by clause and this order by sales uncore cost is mentioned inside the Over clause because we if we have to use the lag function it has to be used alongside with the Over clause and for lag function the same column name we have to display or we have to select which is salor cost so how the lag function operates that we saw and uh whatever the lag function generated it generated a new column and uh I'm renaming that new column as previous uncore transaction so whatever in the output query I will get my third column the name of the third column will be previous _ transaction comma then instead of lag instead of lag I'm using this lead function we will see how the lead function operates instead of lag we have to use this lead function okay lag already we have used now the lead function then just like how we use lag function in same way we have to use the lead function also so you have to apply the lead function on a column but if it is a numeric column then normally we apply it on numeric columns only and our main numeric column in the table is sales uncore cost and and just like the lag function has to be used with the Over clause lead function also must be used with the Over clause and inside the Over clause we have to again select that same particular column that we selected for the lead function which is our sales uncore cost in inside the parenthesis of the Over clause and this sales uncore cost is it is a numeric column here we have to arrange it in an ascending order arranging you can arrange it in ascending or descending order whatever you like since we are going for the overall overall my present underscore sales we want it to be arranged in ascending order hence to maintain uniformity for the lag function also I have arranged the sales uncore cost in ascending order and in lead function also you have to maintain the uniformity that is uh ordered by sales unor cost only do not write DEC that is descending okay so has to maintain good amount of uniformity in order to mention it to the SQL that yes sales uncore cost known as present cells will be displayed in the form of ascending order and accordingly my lag function will operate and accordingly my lead function will also operate so for so for the lag function we had mentioned that sales cost will be arranged in ascending order and the same thing you have to mention with respect to the lead function also that my sales uncore cost column will be arranged in ascending order so that's why inside the Over clause that's why inside the Over clause you have to mention this order by clause or you have to use this order by clause on what our main numeric column which is sales uncore cost and since we have not mentioned any DEC here so this I mentioning it to the SQL that you in ascending order and uh definitely this lad this uh lead function this will generate a column this lead function will also generate a column okay and uh the column name I have chosen here as next uncore transaction okay from which table from empore sales table all right so select this entire line number 105 go to the execute button and you see empore name present uncore transaction which is displayed in ascending order previous underscore transaction we saw the first row becomes null because for present uncore transaction the first row value is 46180 and it is the first value only prior to it no other value and uh that's why the first draw of previous _ transaction goes now then this first row value of present uncore transaction goes to the second row of previous underscore transaction and accordingly 63,000 of the second row of present transaction goes to the third row of previous transaction like this it goes on and this like this fifth row value 78995 of present uncore transaction column goes to the sixth row value for previous uncore transaction column and nothing happens to this value okay because accordingly this is a lag function okay if a particular if uh if there would have been another row then 84700 would have gone to the uh last row of the previous underscore transaction column okay but that is not the case here now next underscore transaction is generated with the help of the lead function which I've applied over the sales unor cost column mentioning that yeah this column is to be arranged in ascending order how will this operate this will operate just opposite this will just OPP OPP this will operate just opposite to that of the lag function how now this this is the first if you now if you look from the if you look from the bottom previously previously we looked from the top of what column presentor transaction column we looked from the top and then we descended down to the bottom now with respect to the lead function follow this presentor transaction from the bottom from the bottom we go up okay so from the bottom if I see from the bottom if I see of present uncore transaction then below it there is no value below 84700 there is no value there is underneath it there is no value so 84,000 700 which belongs to the sixth row of presentor transaction column this will go to the fifth row of nextore transaction column and because uh because below the 84,7 100 there is no other value and I'm looking this table I'm looking at this particular column that is presentor transaction column from bottom to top so from bottom if I see then there is no prior value to 84700 that's why this particular row this bottom row of nextore transaction column goes null and this 84700 which if I'm looking from the bottom while I'm looking from the bottom of this column presentor transaction then this is the first value from the bottom not from the top from the bottom this is the first value belonging to the sixth row it goes to the fifth row okay similarly 78995 which belongs to the fifth row of present uncore transaction this will go to the fourth row of next underscore transaction 72,000 which belongs to the fourth row of present underscore transaction goes to the third row of nextcore transaction 70,000 7,400 belong belongs to the third row and it goes to the second row 70 7,400 which belongs to the third row of presentor transaction column goes to the second row of nextore transaction column 63,000 which belongs to the second row 63,000 which belongs to the second row of present underscore transaction column goes to the first row of nextore transaction column and uh here you have 46180 where will it go because we have already reached the top we have if you see if you look into the nextore transaction column we have already reached to the top of it the topmost row okay and with respect to the table above one above one is there anything there is nothing so this 46180 will go to nowhere else 46180 will go to nowhere else the second row value of present underscore transaction will become the first row value of next underscore transaction okay the second row value of present uncore transaction will become the first row value of nextore transaction column and uh since we have reached from bottom to top and uh above uh one there is nothing else so 46180 has no place to go this is how the lead function generates or operates okay now similarly uh select if you go to line number 109 you can also use substraction like select empore name sales unor cost as my presentor trans trans action then use the lag function over the sales uncore cost column where we are arranging the sales uncore cost column in ascending order and uh whatever the value it is generating whatever column this uh lag function is generating that I naming as previous underscore transaction okay and similarly uh just the same whatever code I give in line number one5 that same code I have written here only it will be slightly uh exaggerated at the end so after the lag function I've used here the lead function over what field sales _ cost field okay and how I arrange the sales uncore cost field in ascending order and whatever column it will generate I have named that column as nextore transaction okay and at the end what I'm doing here I'm trying to obtain some sort of difference where like that is what is the difference between the present and present transaction and the previous transaction these are the two col s present transaction is a column previous transaction is a column okay present present transaction is a column which is the sales uncore cost column okay and previous _ transaction column this we obtained with respect to the lag function okay previous underscore transaction column this we obtained with respect to the lag function so row by row what is the difference row by row what is the difference between these two Fields okay row by row like 46,8 minus null in SQL any value minus null will give you null only okay so 46180 minus null will be null 63,000 minus 46180 this will generate some value like say 16820 7,400 minus 63,000 okay that will have some value say like uh you know 7,400 suppose 72,000 minus 7,400 this will be, 1600 78,000 995 minus 72,000 this will yield a value 84,7 - 78995 this will also yield a value so how to go how to obtain this now sales undor cost minus minus you give and then that entire lag function that we wrote that you have to rewrite again okay the lag function that we wrote previously like in the previous line we wrote then in this line also we wrote that same lag function in the same way you will have to write again so lag then within parenthesis sales uncore cost lag function I'm applying with respect to the sales uncore cost column okay so over that not only here inside the parenthesis you have to mention not only inside the parenthesis here you have to mention of the lag function but also the lag function it comes with an Over clause so that Over clause has to be used and then inside the parenthesis of this Over clause you will have to mention the sales cost again because here you have to mention the order in which it will be displayed ascending or descending so over then inside the Over clause of inside the Over clause that is inside the parenthesis of the Over clause order by sales uncore cost order by sales uncore cost okay that you have to I'm going back there one again and again so sorry for that Interruption so here you have to mention order by sales uncore cost the same thing that we have done lag function applying over the sales _ cost column over because lag function has to be used in conjunction with the Over clause then inside the parenthesis of the Over clause you will have to mention the order of that uh column in which it we want to evaluate we want to display so here order by sales uncore cost we'll ensure that sales uncore cost is in ascending order already we have mentioned it mentioned this in our lag and Lead so to maintain the uniformity here also I'm mentioning that yes sales uncore cost column has to be arranged in ascending order and whatever so this sales uncore cost is what my present underscore transaction okay minus this uh lag function this lag function is doing what lag with respect to sales uncore cost over over because lag function has to be used in conjunction lag function has to be used in conjunction with the Over clause okay so here over Clause me mention and inside this you have to mention the order in which sales underscore cost column will be displayed which is ascending order thanks to the order by Clause order by sales uncore cost no DEC so presentor transaction which is my sales uncore cost column sales uncore cost field is what my presentor transaction column minus this uh this entire thing that we saw lag sales uncore cost over order by sales uncore cost this one this entire function this lag function lag sales uncore cost over order by sales uncore cost it is generating which column it is generating this particular column okay so that's what it is being uh mentioned here which will be convered to SQL and overall this difference when I do overall this difference when I do that will also generate a column and I'm giving a name to that column which is difference uncore amount from which table from empore sales table okay so select this entire line that is line number 109 you select and go to the execute button and you see everything is same you have employee name okay you have your present transaction you have your previous transaction you have the next transaction next transaction how we generated and thanks to the lead function with the help of the lead function we generated this column nextore transaction and this is our final column difference uncore amount difference uncore amount column we did what we are substracting the uh previous uncore transaction from the present uncore transaction where present uncore transaction is my sales uncore cost and this previous underscore transaction we are obtaining thanks to the lag function so see for the first row 46180 minus n is null then row by Row the differences happening between these two columns pre present transaction and previous transaction 63,000 minus 46180 is yielding this value then 7,400 minus 63,000 is yielding this value then 72,000 minus 7,400 is yielding this value and on and on if you come to the last row 84700 minus 78995 is yielding you this value 5,75 so these are some very important functions that we have uh covered so far in our SQL playlist and uh if you have liked this video then please hit the like share and subscribe button and press the Bell icon for notifications so that's it for this video thank you

2025-01-04 01:28

Other news