 Hello everybody. In this lesson, we're going to be taking a look at temporary tables. Now temporary tables are tables that are only visible to the session that they're created in. So if I create a temp table right now, and I exit out of my SQL and I come back in, it's not going to be there anymore. And we'll look at that in just a little bit. Now temporary tables can be used for a lot of things. But how I've mostly used them, especially as a data analyst, is restoring intermediate results for complex queries, somewhat like a CTE, but also for using it to manipulate data before I insert it into a more permanent table. So let's take a look at how we can create a temp table. There's two ways that you can do it. I'll show you the first way, which I don't think is as popular. And then I'll show you the second way, which is how I typically use it the most. Now the first way to create a temp table is to create a temporary table. I need to sound it out like that. That's the only way I can spell. So we're going to do temp underscore table. So this is our name. Now if we just took this out, and we created a table, this would create a table in our parks and recreation database. But we don't want that. We don't want to create a temporary table that just lives inside of our memory, or the memory within our computer. Now we're going to create this temporary table, much like we would a regular table. And we're going to need to name the columns as well as the data types. Let's do first underscore name, and our data type can be bar char, let's say 50. We'll do a comma, then we'll do last underscore name, we're going to keep this really simple. We'll do bar char 50 again. And then for our last one, we'll do favorite underscore movie. And for this one needs to be longer. So we'll do bar char, let's say 100. Now let's get rid of this. And let's actually run this after we do our semicolon. Let's actually run this. And nothing is going to happen. Let's click refresh. Nothing is going to happen. At least you can't see it happening. Let's pull this up. And you can see that create temporary table. So zero is affected, but it was created. Now in order to actually see it, we can do select everything. And we'll do this from our temp table. And we'll add a semicolon. And then we run this. And we have this empty table right here. Now what's really great about these temp tables is then you can insert data into it. And it basically is like a real table, except it just lives in memory. And they go away after a while. But you can reuse this temp table over and over and over again. Now let's insert some data into here. And then we'll take a look at this again. So let's come right down here. Let's insert data, we'll do insert into and we want to insert that into the temp table. And we're just going to say values. Now we just say values, I'll use myself for this one, we'll do Alex, freeberg. And what's my favorite movie, give me a comma, that'll be Lord of, I think it's like that Lord of the Rings, the two towers. That's probably my favorite movie of all time. Now, let's go ahead and insert this data. And let's pull this down here. And let's run it all the way down here after we add our semicolon. And when we run this, you'll notice that now we have data in here. So now we can use this table, much like any real table. So that's the first way to create a temp table, not my personal favorite way, although there have been some use cases where I've done it like that. I'm going to show you the way that I typically do it. And for this, let's select everything from the employee underscore salary table. Let's run this. Now let's say I just wanted a subsection of this data. Let's sit in this temp table where the salary is greater than let's say 50,000. Like it easily easily do this, I'm gonna say create temporary table. And let's do this one as salary over underscore 50k. Now one thing about naming either temp tables or CTs or subqueries or any of these things where you need to name something, I try to typically name it something that actually makes sense. So the salary over 50k is something I would actually name it in my real work. I wouldn't normally name it something like temp table. The reason for that is because when you're in a work environment and you have lots of temp tables, you're creating really advanced store procedures, really advanced queries, you have hundreds or even thousands of tables and different databases, it gets really complex. So naming conventions are actually pretty important or they become more important the more you get entrenched in this stuff. So just something to think about. Now we're creating this temp table. Now we don't have to really insert data into it more than we're just going to select data from an already existing table. I'm gonna say select everything from I'm gonna say employee salary. Then we're just gonna say where the salary is greater than 50,000. Now I want Tom and or Jerry, I want them to be included as well. So I'll actually say they're equal to. So now we're creating a temporary table based off of an already existing table and we're just selecting data into this temporary table. So when we run this, now we can select the salary over 50K and let's run this and it works perfectly. Now the great thing about temp tables is they last as long as you are within that session, meaning if I copy this query, let's go to a new window and let's paste this in here. Let's zoom in a little bit and let's run this. It still works even in a new window, but if I'm to exit out and come back in, then it is no longer going to be working. Now let's exit out of this. Let's come back in and we'll see if these temp tables still work. Let's go ahead and exit out. Oh geez, I'm embarrassed. All right, let's go to my SQL. Let's come over here to the local instance. So now it pulls right back up, zoom in once again on both these and let's try to pull up our salary over 50K temporary table. Let's run this and we're not getting an output. Let's go back. It's going to say error code, the table salary over 50K does not exist. So it only lasted as long as we were within this session. So that is how we create our temp tables. And that's how we use our temp tables. Now in the last lesson, we had looked at CTEs. CTEs and temp tables both have their own use cases within my SQL. For temp tables, this is usually for the more advanced thing. So I'm usually using these in store procedures when I'm really manipulating data and I'm doing a lot more complex queries overall. And oftentimes I'll use multiple temp tables and I'm joining them together and I'm just doing a lot of more advanced stuff with CTEs. It's typically more simple things because you can't make as advanced CTEs or as complex CTEs. So with those, I'm usually keeping it to just one level of transformation. I have my base CTE or my base sub query or query, however you want to call that. And I'm changing it or doing one level of advanced thing on top of that query. That's what a CTE is really great for. Temp tables, you can just get a lot more advanced with it. They also last within the session. And if I'm using it multiple times throughout something like a store procedure, then it makes so much sense to use a temporary table. So this has been our lesson on temporary tables. In the next lesson, we're going to be taking a look at string functions.