 What's going on everybody welcome back to another sequel tutorial today we're going to be talking about CTEs. A CTE is a common table expression and it's a named temporary result set which is used to manipulate the complex subqueries data. Now this only exists within the scope of the statement that we were about to write. Once we cancel out of this query it's like it never existed. A CTE is also only created in memory rather than a tempdb file like a temp table would be but in general a CTE acts very much like a subquery and so if you know how to do subqueries you should be able to pick up on CTEs fairly easily. So let's get started writing our very first CTE and we're going to come down here I'm going to say with and we're going to write CTE underscore employee and we're going to say as and this is where everything's going to start. Now CTEs are sometimes called with queries I've never personally used that but I've seen it called that online but that's because it uses this with statement right at the very beginning. So now we have with CTE employee as then we have an open parentheses and now we have to construct our select statement and this is kind of where we build out our quote-unquote subquery and so I'm going to take in a select statement that I actually used in a previous video where we're using the partition by and so I'm going to put that in there and kind of walk us through what that does and how we're going to use this. So I'm going to paste this down right here and I'm actually going to go like this just to make it look a little nicer and then I'm going to close the parentheses at the end. So now we have our CTE in place and as you can see it is basically just a select statement within the with CTE employee as and what this is going to do is going to take the first name, last name, gender and salary and then it's going to take this aggregate function with the partition by aggregate function with the partition by and it's going to place it's where we can now query off of this data. So it's putting it basically in a temporary place where we can then go and grab that data. So all we're going to do at the very bottom is we're going to say select everything and we can do that from CTE employee. So let's run this entire thing and see what we get. So as you can see this select everything from CTE employee we are selecting everything from this select statement and so this feels a lot like a temp table we were actually querying off of a temp table but it actually acts a lot more like a sub query. Now we don't have to do these select everything we can just do first name and let's do average salary and when we run this we'll just get those two columns and we don't have to go through and actually write this out each time it's just in this CTE for us so it does all the heavy lifting within the CTE and then we can just query off of what we want. Now something to note is that the CTE is not stored anywhere and so it's not stored in some temp database somewhere if I try to run just this by itself it is not going to work. So let's try that out really quick and we should get an error and that's because each time we run this query is actually creating the CTE again and so it's not being saved anywhere and so each time we run it we have to run it with the entire CTE. Another thing to note is you actually have to put the select statement right after the CTE if I try to go down here and say select everything from let's do CTE underscore employees it doesn't actually work it's not going to come up at all and that's because it only is going to work with the select statement directly after the actual CTE that you've created. I hope this was helpful and I hope that you understand how to use a CTE a little bit better again you don't have to go super complicated with the select statement within your CTE it can be very very simple. I just wanted to demonstrate that you can use aggregate functions within your CTE and then just query off of those without having to do the aggregate function again which I find is very very useful. Again thank you for watching if you like this video be sure to like and subscribe below and I'll see you in the next video.