SQL lecture - 16 | SQL CTE | NMD PVT LTD

[00:00:00]Online student voice comes Sir, what did we see in the last lecture, we started the windows function, right windows function, row number rank, bench rank is clear, difference between rank and dense rank, yes, so today we have to start lag and lead, okay lag and lead is not there, right, we will see lag and lead, suppose I have to extract the entire data from the salary table.

[00:01:06]How to do it?

[00:01:09]Select Star from Salary. Right?

[00:01:18]Ok. Now from this I only need the employee number and salary column. Then select employee number salary from salary.

[00:01:33]Ok? Now I need a third column in which the previous salary of the employees should be shown.

[00:01:39]Ok? Meaning, now the salary for Rs 10001 is Rs 617. What was his salary in the previous year? Or what is the salary for next year? What will we do with that type?

[00:01:51]Use the lag and lead function. When to use Lac? When you need to find the previous value.

[00:01:56]Then lead means finding the next value. If I want the previous salary of each employee.

[00:02:01]What was his salary last year and what is his salary now in the current year? Then what can we find? If we take the difference between the two, we will know by how much the salary has increased.

[00:02:10]So if you want to find out by how much the salary of each applicant is increasing. So first I took the employee number. Took the salary column.

[00:02:17]Now let me take the third column, I will apply lag and lag function over the salary column, then our same as it is over, we will partition by, what does partition by do, we will partition over employee, that means our employee number will change, as we were doing in row number, so what was happening was that the row number is again starting from one, the rank is also getting reset again, similarly what will we do in lag also, here I will name this column as Previous Challenge, okay? I have not placed an order here.

[00:02:48]If you do order by after partition by, this sequence will change.

[00:02:52]We do not have to change the salary sequence because this is the year wise salary growth.

[00:02:56]If you buy the order then the sequence will change. What do we need to find? By how much is the salary increasing every year?

[00:03:05]Execute this.

[00:03:25]Look, now look in the output, three columns will come, first is the employee number, second is our salary column, third is the previous salary column, now employee number 101, okay, I also add the from date and to date column here, I add a founder here, employee number 10000, what is his salary?

[00:04:15]617 When? From 1986 to 1987. Meaning when he joined his salary was Rs 617.

[00:04:23]Do we know his previous salary? Because this is his first salary.

[00:04:28]Previous salary is not known. So what is the previous value here? No.

[00:04:32]Then Next Year What was his salary from 1987 to 1988? 6212 But now do you know his salary last year? 617 this value came here previous value my then again next year his salary became 6674 but what was it in the previous year? 6214 What did you do here? Partition by employee number means as soon as the employee number gets changed, see your value gets reset again 10002 is the salary of the first year, do you know whose previous value is 1002, this is 10000, right? We will not take this value here, so 1002, we do not know the previous value of the first record, so what has come here is null again. Now what do you have to find, by how much is the salary of each employee increasing, how is the salary increasing year wise, how will you do that? Yes, what will have to be done if I have to find by how much is the salary increasing every year, how will I do that, say previous salary minus salary column, if you see, what was his salary in the first year, 617, how much did it become in the next year, 621.

[00:05:48]If I want to see here, by how much is the salary increasing, what will you do, you will subtract 60000 from 62, the salary will be calculated from the answer that comes. If it is increasing then I have to do the same here. First year salary and previous salary are not known. Second year salary was this much, current salary 6212 but what was the previous value? 617 If I subtract the previous salary column from the salary, what will the output tell us? How much is the salary increasing by? I remove these from date and to date.

[00:06:20]I have put this here just to explain it to you.

[00:06:22]You will know year wise.

[00:06:24]Ok? I copy it.

[00:06:33]Here I add another column after previous salary.

[00:06:35]Salary Minus Previous Salary Previous Salary Pay This column will be named Salary Increase By, by how much the salary has increased.

[00:07:27]First year salary was 60000, previous value is not known, difference between both came to zero, next year salary increased from 60000 to 62000, now what was the previous salary, salary 617, if we take the difference between both then how much is it coming, 1985, what is happening to the salary from this, it is increasing again, next year it increased from 62 to 66. So how much did the salary increase by? Look, 66 is here. This previous value appeared before him. If the difference between the two is taken, the salary will increase by Rs 3972. Every year is giving you salary difference.

[00:08:04]Is it clear? So if you use the lead function, what does lead do? The next value has to be found. Like if I make this element lead instead of lag.

[00:08:16]Next will come here.

[00:08:53]1001 This is the current salary 617 What was the salary for the next year 62000 What was happening here with the lag, the previous value was coming, now the next value is coming in front, so in the lag the first value was coming as null because we do not know the previous value, but now in the lead we know the next value, so what will happen in the lead, the value of the last record will be brought, 100001 This is the current salary, but do we know its next salary, is it 10000 because we do not know the next salary of 10000, here the record is starting from 10000 and here what have we done between the two, we have put partition by employee number, what is happening here, in the last record of each employee, the next value will be null because we do not know the next record, like what was happening in the lag, the previous value is coming as null in the first record, what is happening in the next lead? The last record in the next value is null. Now if we want to take the difference again, then how will we do it now? Now the next salary will have to be minus the salary. Do you understand?

[00:09:49]Is the lag and lead function clear to everyone? When to use it? Is it clear? When you will be asked if you want to calculate the year wise salary increase growth of each employee. What will you do then? We will use lag or lead function. Then remember to only partition by employee number. Will not do order by salary.

[00:10:08]What will happen to that? Your salary sequence will change. We need it in the same sequence in which year wise growth is taking place. Yes, is it clear to everyone? Lag Lead Rank Dense Rank Are these questions clear which we saw in the last lecture? Find the second highest salary third IS salary. Let us solve this question once again.

[00:10:36]We saw the question find second highest salary for each employee, tell me how to solve it, first select employee number then take salary column, after that second highest, second highest of each employee is required, which window function will be used, which window function will be used? We will use dense rank. Ok?

[00:11:14]Danes rank over. What to do in over?

[00:11:18]Partition by Employee Number then Order by Salary descending. Because what do we need? The second highest will come out on top. The one which will be at number two, second, every descending number from salary, if we execute this, see here what is the highest salary of 100001, 88958, that means where the D rank number one will come, what is that, the same, where is the highest salary of 1002, see, check number one, here we need the highest salary of 1002, second, that means what condition will have to be entered, balance rent number equal to 2 will have to be done. But which column is the condition on? Is the column present? First its presence will have to be created.

[00:12:10]Meaning what do we have to do with this output? Convert it into a table. So what were we doing? This entire query was being written inside from. Right? In from we write the table name, right? How were you doing this here? From this table. And we were naming this table.

[00:12:25]Like a okay? Select will appear above From.

[00:12:29]Select in select. Which columns are needed? Am number A salary from table A now condition where A dot pre rank number equals 2 this is second highest salary for each and right. Now what are we doing here? We are writing this query inside from.

[00:12:56]Everyone is using queries. But now we will use another method which we will call CD.

[00:13:08]Yes who?

[00:13:12]Oh no, it's offline, okay, what do we have to do here? Look, we have to find the second salary of every employee.

[00:14:01]You can also use this method. But there is another method which is CP. Now what to do in the city? See, whenever such a question comes which cannot be solved directly from any table.

[00:14:12]What was there? Second highest salary for each employee is directly solving the question? No. First we wrote a query. The output of that query has to be converted into a table.

[00:14:22]When there are scenario based questions of this type, then we will use C. Simple what to do?

[00:14:30]We wrote a query.

[00:14:33]We had to convert the output coming from this into a table.

[00:14:35]Right? Because we have to put a condition on this column and is this column present anywhere? No. So what were we doing? We are writing this entire output inside from this query. So a table is being created.

[00:14:46]Simply we will do one in its place.

[00:14:49]Here you will write with vi above, which means your city will start from here. And give a name to the table. Like we were giving A there.

[00:14:56]I pay here. I do it with city or I give a name here Employee Records.

[00:15:03]Ok? V Employee Records as this is simple, I wrote V in the beginning, decided the name of a table and wrote that query completely, now what will happen is the output of this query will be converted into this table, EMP records will be stored in this table, now this table is valid only for this query, here a table will not be created anywhere in the database, ok, if you want to see the data of this table, how will you see EMP records, select start from EMP records, see I have started the query from here and ended here, ok, complete, it is only one query, it is not like that you cannot write select query outside this, if I write select anywhere outside, select start from EMP ref, will this query be executed, now where will this query be run, on this database, now what error will come, table does not exist, does it exist, not here, this table is valid only for this query.

[00:16:04]Now when is it used? When you are creating a table here. So you can use this table in this query for multiple time references. Like I checked the data of EMP Records here.

[00:16:16]Right? Now we have to enter the condition. Simple select star from EMP records if I put it here.

[00:16:24]Ok? Meaning, now all these three columns are present in the EMP Records table. And what condition did we have to put? D Rank number = 2. Write here where D bank number equals executive employee number salary rank number second SI of each employee Okay let's see another example where CT will be used find how many let's read the question what is the question?

[00:17:43]Find out how many male employees earn a higher salary than female employees. Meaning what is the question?

[00:17:55]Sorry, I start from salaries here.

[00:18:05]Now understand the question properly. This is the salary table. What do you have to do in this?

[00:18:10]Records of such male employees are needed. Record means daily records of such male employees are required whose salary is paid by whom?

[00:18:17]Higher than the average salary of female employees.

[00:18:20]So what are your requirements to solve this question?

[00:18:24]What do you need to find first?

[00:18:29]Records of male employees will have to be found.

[00:18:32]Male employees records, then with what do you have to compare the salary of those male employees' records, average salary of female employees, average salary of female employees, these are the two requirements to solve this query because see this query will not be solved directly, till now all the queries that we have seen, for example, suppose I want such employees from the employees table whose name is Sania, on the simple employees table we put the condition of where where first name equals Sania, but now is this query getting solved directly? First, we need the record of a male employee. Then with whom do you want to compare them?

[00:19:08]With average salary of female employees. Solve these two first.

[00:19:10]First of all tell me how will you find the records of male employees?

[00:19:14]How will you record the salaries of male employees?

[00:19:21]I take the selected employee number.

[00:19:24]salary gender from where from employees what to do say join will have to be applied employees e join salary s on e number equal s number what will the condition come e gender = m male employees required here employee number s on salary these are all male employees and their salary records now with what to compare each salary record average salary of female employees second first requirement is clear now how to do the second requirement average salary of female employees select average of salary as average salary from salaries then this much will come. If you do this, it will find the average of all the records in the salary table.

[00:20:35]We only need female employees. Will have to join again. Join employees e on employee number equals s dot employee number again where e dot gender equals average salary of female employees here s dot salary execute average salary of female employees I copy this value and paste it here for a while now I execute this query.

[00:21:11]Where we found the salary records of male employees.

[00:21:15]Now look at this first record, with whom should it be compared? Here is the average salary of female employees. Check it. First record salary. Is it greater than the average salary of a female employee? Yes.

[00:21:25]Then this record will appear in the output. Will come.

[00:21:28]Then look at the second record. This is greater than the average salary of a female employee. There is also a third 4 6, look at this female male employee here. Record female employees have greater than average size.

[00:21:41]Look at the record here, it starts from 10000.

[00:21:44]1003 is coming directly after 10000. Why?

[00:21:48]Because there are 10,000 female employees. Ok?

[00:21:53]Is the question clear? There were two requirements.

[00:21:55]We found both of them. Now what do we have to do? See. Now we will use CTE. Let's start with a table.

[00:22:03]What was the first requirement? Records of Male Employees.

[00:22:04]So here I add the name of the Mail Emp Res table, this is done, after adding this, copy paste this query inside the parenthesis, do not copy the semi colon, that means now where is the output of this query getting stored, the Mail Employee record will be stored in this table, see check select star from Mail Employee record, right this has become the first table. I'll remove this in a second. Seconds became one city at a time.

[00:22:43]What will you name the second table after comma? What is? I will write the average salary of female employees here as F average salary.

[00:22:51]Edge will copy this query into it.

[00:22:59]You now have two tables. The first table is the Male Employees record. The second table is F average salary. How many records are there in the F Average Salary output? There is a single value inside this table. There is a male employees record. This table contains multiple records. What should I do now? The record of this table has to be joined with every record of this table. Which join will you use? Yes. We will do cross join not right join. We will not look at the conditions here. The first row of table one has to be joined with every record of table two.

[00:23:31]This is the output of your mail employees record. Ok?

[00:23:36]I execute this query.

[00:23:38]This is the record of male employees. Now if you create a column in front of this, this value should appear in every row. This is the average salary of female employees. The same value will appear in each row. Then it will be easy for comparison. You just have to enter the condition. Salary greater than average salary.

[00:23:54]What will we do then? We will apply cross join.

[00:23:56]In which two tables? Male Employee Record Average Salary Okay these are two tables Harry one I have intentionally given space in between here you will understand from here now all that needs to be done is to write the select query select start from [sound of clearing throat] from which table will the Mail Emp records be called this M join with which will we do F average salary now here pay on condition is given what not means which join is it execute cross join right now we have cross joined only both the tables.

[00:24:30]Now actually our table is ready.

[00:24:33]Now solve our question on this.

[00:24:35]What was the question? There are records of male employees. With whom should he be compared? With average salary of female employees.

[00:24:42]So look, the salary of the first record is 617. These are male employees. Compare her salary with the average salary of an average female employee.

[00:24:50]Meaning is it greater than this value? If the first record is greater than if it is not there then this record will come in the output, second record will not come, third record will come, yes the third record will come, so what condition do I have to enter now salary is greater than average salary, but which table does this salary column belong to, m is of the record of male employees, right, male MP record and f is the same column in average salary, what will be the condition by doing average salary, where m dot salary should be greater than f dot f means this is table f dot average salary, if you execute this then now these are the records of such male employees whose salary is greater than the average salary of female employees, it is clear but see what was asked in the question, find how many male employees records, how many has been asked, right, what does one have to do, you have to count the records, you do not need them. I just need the number of records. So here pay two count of star s number of mail records execute how many records? There are 255363 salary records of male employees whose salary is greater than the average salary of female employees. Did you understand how to solve the question? Using CD.

[00:26:20]First we understood the requirements. Look, from here till here there is only one query. A complete is a query.

[00:26:27]Because look, there's a blue dot here. The query is starting from here. Semi colon right here. Ok? Should I remove the space? Am I clear from the middle? What did we do first?

[00:26:40]Read the question properly. After reading the requirements, I understood what all I had to find.

[00:26:43]Then wrote a separate query for each requirement. Then we converted those queries into tables using CD.

[00:26:49]And we put a join between those tables.

[00:26:52]Now look at this table, it is valid for this query.

[00:26:54]Now if you write the query outside select star from male mp records the query is not executed. Because what are you doing? You are using city here.

[00:27:02]Common Table Expression means that when you create a table, you can use that table for reference within this query.

[00:27:09]Like here I created a table with a male employee record.

[00:27:13]Used it here. Look at the join.

[00:27:16]Then used a second table here. I joined these two. That means I used both those tables in this query.

[00:27:22]Is it clear? Did you understand the question?

[00:27:27]You take one question to try.

[00:27:29]Simple find here and do it like this. How many female employees record whose salary is higher than the average salary of all employees?

[00:27:48]Try this. What is the plan? How many such female employees are there on record whose salary is greater than the average salary of all employees. First of all, what are the requirements?

[00:28:06]What is the first requirement?

[00:28:09]You need records of female employees.

[00:28:11]What is the second requirement?

[00:28:14]Employees Average Salary is one of the two requirements of all the employees.

[00:28:20]Two queries will come separately for both.

[00:28:22]Convert each query into a table using CT, meaning there are two table names. A join has to be made between those two tables.

[00:28:28]Come on, take two minutes. Try this question.

[00:28:48]Four days for practice SQL is on right ok Om how will you find the records of first female employees?

[00:31:28]Select Employee Number Now if you want the salary record of female employee then take Employee Number Salary Gender column. Ok? Then from which table? Employees salary as on equal as dot where equal to whose records are required Employee number of female employee Execute salary here Records of female employee are found First requirement is clear Second requirement is average salary of employees Now it is told here whether you want male employee or not female Simple you want average salary of employee Simple someone will come Select average salary as salary from salary No need to do group by because what we want is average salary of all employees One value will come These are two requirements What to do with both Simple we will convert them into one table First what is f records as open clearis this entire query as it is ok Now the output of this query will be converted into a table By putting comma in fm records Second table am average will copy it ok Again by doing else Now two tables have been created.

[00:33:14]What will the records do in both? I will join.

[00:33:17]Select Star Star Take First From FMP Records Join Average Salary.

[00:33:28]Ok?

[00:33:30]Now you can put a simple condition on the output that comes, in which if the salary should be greater than the average salary. Just enter the condition of the wear. So you can enter the where condition because all four columns are present in the table. Which table contains the first three columns? Average salary column in FPM Reds in this table. So what are you supposed to do?

[00:33:51]Salary greater than average salary. But which table does this salary column belong to? F is of EMT Records? Here I will change it to e and a.

[00:34:00]Ok? So f dot salary should be greater than a dot average salary.

[00:34:08]Right, there are so many records, you have to count them.

[00:34:10]If you are asked which records are there, then you will keep this output, but what have you been asked, how many, then you will count them. It is clear, you have understood how to solve the question.

[00:34:30]After reading the question, first you have to understand the requirement, what all will have to be found to solve this question.

[00:34:37]Then as per the requirement, you are holding that output inside a table using CT. Then you are using the same table here for reference in that query. Do you understand that the tables where we have joined are clear? Let's clear the basics, actually, you understood all the lectures today, okay, online online students, are you able to understand, okay, you understood all the questions that I had given on window function, they come in the interview, one of these questions will come in the interview, okay, is it okay? Has anyone read normalization and acid properties? About normalization and acid properties. I told you to read it on Wednesday.

[00:35:43]Normalization and Acid Property. The simple theoretical concept is acid property.

[00:35:50]A for Atomicity C for Consistency I for Isolation D for Durability ok normalization means the technique of how to design a database in which you have to remember OneNF to NF3NF, there are only four definitions here, each one means you have to remember the definition and one example, ok, read these two concepts, ok, if you do n't read, but you, ok, I will explain, you will only listen, you will not study, but you should read it yourself, how to do it, how to read, from where will you read, give a simple chart to GBT, explain the concept of normalization from basic to advanced with proper examples. Same thing has to be done there. Explain acid properties from basic to advanced with examples. Ok? So the proper person will give you examples and also explain. Everyone has to read these two concepts. Still, in our lecture tomorrow, I will give you an overview of what normalization is.

[00:37:06]What is acid property? Then we also add temporary table index and view in it, okay.

[00:37:16]Tell me if anyone has any doubt in the syllabus covered till now, yes study it, no doubt, no doubt, which window function, who do not understand, starting of the lecture, understand window function of ours, row number rank, doubt actually, lecture starting, think through the lecture, think through it, do not understand the concept, mind as today's lecture understand window function, do not understand two f up vegi window function, one is partition by over m condition, then partition by and order by, two like up sum number rank rank partition partition by and group by, yes down [nasal sound] yes, can anyone tell me what is the difference?

[00:38:51]Partition by group by simple question will come in interview.

[00:38:56]What is the difference between partition by and group by?

[00:39:04]Say.

[00:39:07]GROUP BY. What is the function of group by?

[00:39:12]What is the work of group partition brother, what does it do, okay share it and send an email like what is your question? Partition By and Group By. What is the difference?

[00:40:25]Ok. Group Simple. If I am showing the salary table here now, sorry salary date. Okay, and when we did group buy here.

[00:40:41]What does GROUP BY do? We will group by on the employee number. So what is the group by doing? Creating a group on the same record.

[00:40:47]Right? I mean, what is he doing? Different groups are being created on the basis of employees.

[00:40:53]But partition by group does not create. What does he do? Divides numbers. Like 100001, a line will come here from which the software will know that the next group is starting from here.

[00:41:06]Like a new employee is starting. Now what is happening by doing group by? Look, we did group by on the employee number.

[00:41:12]But when do we do group by? When we have used some aggregation. Aggregation like sum find has to be done. So what is the group by doing? This gives us a single value output for the entire group. But what does partition by do? Look what we are doing with Partition By Partition By is just getting a single output for Partition By Employee Number Group? No, what is he doing? What will simple partition by do? 10000 As long as the employee number is same, the row number will keep increasing one by one.

[00:41:37]As the employee number is changing, a partition will be inserted here between the two.

[00:41:41]Group means he will keep the number of roses as it is. But what is the GROUP BY doing? If there are 10 records in a group, it combines them and gives a single value in the output.

[00:41:51]But partition by will keep the group as it is if 10 records are coming.

[00:41:52]Just what do we have to do? What will each group do?

[00:41:57]Assigns a number to Rose. And as soon as 10000 one becomes 10000 two here, your row number or dense rank number which you might have used will be reset again and start from one.

[00:42:07]Ok?

[00:42:10]And starting from the basics, if you have any doubt in any topic, which one? Which one?

[00:42:22]Look, simple, if you are ever asked about window functions, you just have to tell five window functions.

[00:42:28]First is yours, whatever we saw, we will tell you the row number and second rank.

[00:42:37]Third then fourth is your lag and just have to tell this window function. There is nothing extra to tell. Now apart from this, you can use all the aggregate functions you have.

[00:43:03]Window function like in window function we have dense here. You can also use sum here.

[00:43:12]Over there is the same condition that Partition by Employee Number Order by Salary Descent.

[00:43:19]You can use all the aggregate functions as a window function also.

[00:43:22]Which ones are even your mean max average okay. Now apart from this, there are many such window functions like L tree R tree, you do not use every single function. These are the main five window functions. Row number, rank, rank, lag, and that's all there is to it. There is nothing much to do.

[00:43:46]Extra, if you keep doing every function in each window, then how many such functions are there in the entire Excel? Then the string function in SQL is different. The date time function is different.

[00:43:57]You will learn power base later. In that too, every function is different. If you learn Dex, it has many date and time functions. Date Difference OK Total NTD Total VDI Total QTD So do only as many important functions as you need because you do not use every single function. For example, in aggregate function it should be five and in window function it should be five.

[00:44:18]Just remember this much that is important.

[00:44:24]Now, as we started from the basics, what is data? Database relational database then DBMS RDBMS from there, if you have any doubt in the syllabus that has been covered till now, then you can ask sir, I have complete doubt from day one till now, yes do I have to revise it, do I have to complete it again, yes I have to take all the lectures again, sir I have started, that general value comes, simple example is ma count kar or how many students are there in the class, answer is one, ma count kar or class add students, student ID count, answer is same, ok.

[00:45:33]Ok. Look, tomorrow will be our last lecture.

[00:45:35]Tomorrow we will look at views and indexes.

[00:45:39]Temporary Table Acid Property and Normalization Concept will be revised once and there will be no lecture on Wednesday.

[00:45:46]But on Thursday there will be a revision lecture in which you can ask any doubts you have.

[00:45:50]But we will just discuss it. No topic will be repeated. If you still have genuine doubts then the entire topic will be explained.

[00:45:58]But for that you have to study first, if you do not study then even if I take all the lectures again then you still have doubts okay Day one per day there would be two to three topics daily there would be doubts on two to three topics okay Notes for all notebooks today yes you carry plastic notes not how many notebooks you have doubts on two to three lectures pending notes you were not in notebook no you yes you were pending I think join but which topic of notes I did not understand What is join join commit roll back prop delete try update case statement how to use one query homework find salary growth of each employee right how much salary is increased in query output year wise salary is increased for each employee right how many columns are there in output employee number salary previous salary difference add one column fifth number what salary increase by if salary is 5000 pay increase actual than high increment 3000 salary increase medium increment 2000 salary increase low increment add condition 2000 salary decrease increase than very low increment use case statement which column to use salary increase by if salary increase by greater than 5000 than high increase if salary increase by greater than 3000 medium increase if salary increase by greater than 2000 low increment else very low increase start in case statement and we will write this entire column inside select so as a column we will give it a name what will you name it salary increase range what is its range of increase then from me give salary take a question this is good for homework salary increase by greater than if it is 5000 then high increase is okay Again it is greater than 3000, then increase it, it is greater than 2000, sorry here it is medium, okay and what will you do, take it, okay. Take a question for homework.

[00:50:00]Ok? Did you understand the question? What should I do?

[00:50:03]Solve these questions tomorrow and bring them. Ok? Try it. If it is not happening then tomorrow in the lecture we will see how to solve it.

[00:50:14]Ok?

[00:50:18]So we will end today's lecture here.