Learn SQL Beginner to Advanced in Under 4 Hours

What's going on, everybody? Welcome back to another video. Today we're going to be learning MySQL from beginner all the way to advanced in under 4 [Music] hours.

SQL is one of the most important skills in my career, and so I'm really excited that you are going to start learning SQL as well because SQL is super important in the data world. In this really long lesson, I'm going to help you get your environment set up. We're going to be walking through all of the basics, so just selecting data, how to query basic data. But then, as we go through it, we'll be working towards more advanced things like CTEs, temp tables, and more. At the very end, we'll have two complete projects: one for data cleaning, where we take a messy data set and we clean that data, which is a very important skill to know how to do; and then lastly, we'll be doing exploratory data analysis on our real data to really dive in and see how you can use SQL to dig into the data and understand it better.

So, without further ado, let's jump on my screen and get started. All right, so let's get started by downloading MySQL. We're going to come right over here to dev.mysql.com/downloads/installer, and I will have that link in the description so you don't have to write all that out. But you should be seeing this page right here. Now we have to select an operating system. I'm using a Windows machine, and if you aren't—if you're using Linux or Mac or something else—it should populate it for you. But if it doesn't, just select this drop-down and select your operating system.

Next, we have two different downloads: we can install the MySQL Installer Community or MySQL Installer Web Community. This one is very small, but then you actually do have to download the installer; it just gets it from the web. This one—I'm going to download the actual installer; it's larger, but this is the one I'm going to do. So I'm going to go ahead and select download. It's going to ask me if I want to log in or create an account, and I don't. I'm going to say, "No thanks; just start my download." I'm going to save this in this desktop folder; it doesn't really matter where you save it. We're going to save that, and it's going to download. It should be done in just a few seconds. I'm going to go ahead and click on it, and it's going to open it up when it's finished, and we should get the interface or the UI for the actual installation for MySQL.

So here is the MySQL installer, and the first thing we need to do is choose a setup type. Now we're going to keep the developer default unless you really know what you're doing, and you can select the server only, the client only, full—which is literally everything MySQL has to offer—or custom. So we're going to keep this developer default, just installing the things that we kind of need. So let's go ahead and select next. For whatever reason, on my computer, it's saying this path already exists. You probably won't get that, but I'm just going to go ahead and select next, and then I'll select yes. It keeps doing that. I can't explain why, but it keeps doing that for me, even though I've deleted it from my computer completely. Um, but it just remembers it somewhere in its memory.

Now the next thing we need is to check requirements. Now I just have this one; it says I need to download this Visual Studio. I'm not going to do that, but on your screen you may have multiple, multiple requirements. Typically, you're looking at something like this: Microsoft Visual C++ Redistributable Package. What you need to do is download this. All you have to do is click download. Once you download and install that on your computer, and then we go back, all of those should be gone. That's the one that I see the most when I'm actually working with these requirements. I had to install it myself when I got this new laptop. So go ahead and install that if you need to, but if yours looks like mine, we don't need this Visual Studio for what we're going to do. So we're going to go ahead and select next. It's giving us a prompt that we haven't satisfied all the requirements, but that's okay. We're going to go ahead and select yes as well.

Now we're ready to install all of these things. These are all things that MySQL wants you to install. The most important are the server and the workbench, but it does not hurt to have all these other things as well. Some of these connectors are also important, so we're going to go ahead and execute this. This will take just a few minutes. I'll skip ahead when they're all done, but this should take just a few minutes, and then we'll continue on installing MySQL.

So everything just completed, and now we're going to select next. And now we need to actually configure our product. Now really the only one that we actually need to configure is the server. The router says we need to configure it, and the samples and examples say we need to configure it as well, but really it's just the server. Let's go ahead and select next. Now we're not going to change anything for this type and networking unless you know what you're doing with the port, the X protocol port. We're not going to change any of this; we'll go ahead and select next.

The next thing we need to do is select an authentication method. I'm going to be using a password; I'm not going to be using the legacy authentication method. So I'm just going to go ahead and create a password. Now for you—and I keep getting this error, and I can't explain why—right here for you, you should be creating a password at the bottom. It's remembering my password somehow, and I really can't explain it, but I'm going to create my password or check my password. This is one that I already created before I deleted it off my computer, but it's still there. Um, so it's saying my password is still good, but if you need to, you should be entering a password and then confirming your password and saving it, and then you should also be checking it as well. And then we're going to configure this as a MySQL server as a Windows service. I'm going to keep that checked, and we're going to start the MySQL server at system startup. I like that automatically being there; I don't want to mess with that, so I'm going to keep it as it has it. We're going to go ahead and select next. And the last thing we do is just need to execute this, and then everything we put in there is going to actually go. So let's run this and execute it, and that's just finished. So let's go ahead and select finish.

Now it says configuration complete for the server, but we also need to configure these other two. Let's take a look at these really quickly. We're not going to do anything on this; it even says we really don't need to do this. We just need to click finish, and configuration not needed. Next, we'll do samples and examples, and we can input our password, and all this is really going to do is put in some sample databases for us in our database, which if you want, you definitely can do that. I just connected that; it worked. I'm going to hit next and execute, and it's basically just going to put in a database or two, some sample ones for you to look at, and the configuration is complete. You don't have to do that one, but we'll see that in just a second. We're going to select next, and now the installation is completely done, and we can start MySQL Workbench after setup and start MySQL shell after setup. Now I'm not going to do the shell, so I'm going to actually uncheck that, and we're going to select finish.

Now MySQL just popped up for us, and this is exactly what you should be seeing. Now there's a lot of things in MySQL to learn and know how to do. We're not going to be taking a look at all of that stuff today, but in future lessons we'll walk through a lot of these different things that kind of correlate with different lessons or things that we're working on in MySQL. The first thing that we're going to click on is right over here; this is our local instance. This is local to just our machine; it's not a connection to, you know, some other database on the cloud or anything like that; it's just our local instance. We're going to go ahead and click on this.

So this is what you should be seeing right here. This is where we're going to actually write all our SQL code, and I'll show you all this in just a second, but this is where we can actually create our database, and our database is going to go right over here on this left-hand side. This silicon one is actually a sample database; it has a bunch of tables and views, stored procedures, functions; it has all these things in here. If you want to go ahead and mess around with that, what we're about to do is create our own database that we're going to be using throughout this entire series—both the beginner, intermediate, and advanced—we'll use a lot of this, and sometimes we'll import some other ones for different use cases, but this will serve for most of what we're trying to do throughout this entire series.

Now what I'm going to do is I'm going to go ahead and I'm going to say, "Open a SQL script file in a new query tab," and right here it opened up to a folder that I already created: this MySQL beginner series folder. Within it, we have this right here: the parks and rec create DB. Now, in order to get this, you just have to go to the GitHub and download this file; that's all you have to do. We're then going to open this file. So let's click on it; we're going to say open, and what you're now seeing is basically the query editor. This is where you can write your code. Now we're not importing a database; we're actually creating it by running code. Now because this is the first lesson in the beginner series, I'm going to assume that you don't know a ton about MySQL. Really all this is doing is creating the database name, and then we're inserting a few tables into that database, and then we're inserting data into those tables. So this is all of our data that will go into these tables that we create. We only have one, two, three different tables that we're going to be using. So all you have to do to run this is click this lightning button right up here. We're going to go ahead and execute this. If we come down to the bottom and we pull this up, this is our output. This says six rows affected, and we have a bunch of other things like create table, create table, insert, insert, create table, insert into; these things are all working perfectly. So now if we go ahead and click refresh in our schemas—with this refresh button right here—this Parks and Recreation table is populated. If we go under the tables, we see all of these things. So now that we've actually created our database and our tables, that's really all we were trying to do in this lesson, but I just want to open up a table really quickly, show you what it looks like, show you how we can run code, and then in the next lesson we'll start actually learning how to query this data.

So let's go up to employee demographics; we're going to right-click and select row limit 1000. This is going to open up a new window right up here, and it's going to say select everything from this database.this table employee demographics, and it ends with a semicolon. Now right down here we have this output window; this is the actual data that sits in our table. We have columns right here, so employee ID, first name, last name, age, gender, and birth date, and then here are all of our employees on each row. So these are all separate rows, so we have Leslie, nope, Tom Haverford, and it goes on and on. So this is all of our data. The most important things to know when we're actually working with this—and I'm going to zoom in—is if we hover over this query right here and we run it, we can select this execute, which is this lightning bolt with this I. We're going to execute this, and it's going to run this because we're highlighted over it. Now if we have two queries—let's say this one right here—but let's change it to employee_salary. We'll do underscore salary. Let's say we want to query this table. So now if we highlight over this and we go up and select the lightning bolt with the I, now we're looking at a different table. But if we select—even if we're hovering over this—if we select this button, we're going to execute everything in this editor window. So let's run this, and now you can see at the bottom we have two outputs: the employee demographics and the employee salary. So this button is going to run everything in this editor window, whereas if we select this lightning bolt with the I, we're doing everything that's just under where we have the cursor, where we have it highlighted.

The very last thing that I want to mention is that right over here you may have this up, and you probably don't want that. We're not going to do any SQL additions in this series. You can get rid of that by clicking this button right here.

Hello, everybody. In this lesson, we're going to be learning about the select statement in MySQL. The select statement is used to work with columns and specify what columns you want to see in your output. The first thing that we need to do is open up a tab or an editor window. So let's come right up here to the left-hand side, and we're going to create a new tab, and I'm going to zoom in just a little.

Now what we need to do is we need to select the actual table that we're going to be querying off of. If you remember from the very first lesson when we set everything up, we came over here and we right-clicked and did select rows limit 1000. We're not going to do that; we're going to actually write it out. So what we need to do to select that table, the employee demographics table, is we need to select everything; that's what the star means. The star means everything—all tables, all rows. Now we do have a limit on here; we have a limit to 1,000 rows. So if we had a table that had 50,000 rows, this limiter would be an issue; it would still limit it to 1,000 rows. We would have to change that to 2,000, 5,000, probably all the way up to 50,000 if we wanted to view everything. If we had, say, a million rows, we would need to come up here and say, "Don't limit," and it would give us a million rows. The reason they do this is mostly to keep the processing time low. If you have a million rows, it's going to take a long time for the output to actually appear. So let's come right back here.

The next thing that we need to do is we need to say select everything, and now we need to say where we're selecting it from. So we're going to come right down here, and we're going to say from, and now we need to specify what table, and we're going to say employee_demographics, and at the end we need a semicolon. Now why do we need a semicolon? This is going to tell MySQL that this is the end of this query. So if you write another one down here, which we will in just a second, it'll be able to distinguish between the two queries. We're going to go ahead and we're going to run this, and we'll just use this execute right here instead of this one, and there we have our entire table. So we were able to get our table.

Now there is one thing that is potentially wrong, depending on what you're using it for, but what we didn't do is we did not specify the actual database before it; we only specify the table, and this works perfectly fine because if you look over here on this left-hand side, we have Parks and Recreation; it's in black; it's bold; that means that we're hitting off of this database. What's going to happen though if we come down here to the CIS database and we double-click on it? Now this database is highlighted, so now when we're selecting this table, we're trying to select this table from the CIS database. Let's go ahead and try this. If you notice, we have no output. Let's come right down here and pull this up; it's going to say employees sis.employee_demographics doesn't exist. So it's assuming that we're highlighting this CIS database; that means we're trying to pull from that database. Now we can still have this highlighted and still select the correct database by saying parks_and_recreation. So now we're selecting everything from Parks and recreation.employee_demographics. If we run this, we do get the correct output. That's just something to consider, especially when you're working with a lot of databases and a lot of tables. It's usually best practice to actually put the database in front of the table name, although throughout this lesson we probably won't be doing that every time since we're only going to be using this Parks and Recreation database. Let's go ahead and double-click this so we have this highlighted again, and let's select all of this; let's copy all of this. We're going to come down just a little bit right here.

So far we've only selected everything, but we don't have to do that; we can actually just select one column if we would like to. For example, if we got rid of that star, we say first_name, we're selecting the first name column from this table. If we highlight this query and we hit the execute button with the I, now we are only going to return in our output all of the first names. And we can add a lot more. Let's actually look at all these; we can separate multiple columns with a comma. So we can do first_name, comma last_name, and then we could do birth_date. So now we have three separate columns. Let's go ahead and run this, and now we have first name, last name, and birth date in our output.

Now the way we just wrote it is all on one line, and that's perfectly acceptable because MySQL is going to read it the exact same as if we did it in a different format, as long as it's still in this order. But sometimes you'll see it like this, where it select first_name, comma last_name, comma birth_date, all on different rows. Now there's a lot of different use cases for this, or reasons for this, but it typically can be easier to read. Also, if you're doing any type of functions or calculations in the select statement, it's easier to separate those out on its individual row. Now again, we won't always be doing this, but it does help sometimes if you're doing that; it just makes it easier to visualize. For example, if we added the age—so let's add age in here—let's run this. Let's say we were doing a calculation where we wanted to add, you know, 10 years to their age. So we'll say age, and we'll actually create a new row for this, or new column; we'll do age + 10. So now we can easily see that we're doing plus 10 here, and this is another thing that you can do in the select statement: things like calculations. So if we go up here and we run this, we'll now have an age column, but we'll also have an age + 10 column where it just adds 10 to the age, and we can at least visualize and really easily see this when we're doing these calculations.

Now something really important to know about any type of calculations, any math within MySQL, is that it follows the rules of PEMDAS. Now PEMDAS is written like this: it's P-E-M-D-A-S. Now what I just did right here with this pound or this hashtag is actually create a comment, so this code isn't going to actually run, but it's just for note-taking or seeing things in your actual editor window. I'll come back to comments in just a second, but just wanted to explain what that was. Now what PEMDAS is is the order of operations for arithmetic or math within MySQL. This stands for parentheses, exponent, multiplication, division, addition, and subtraction. So this is the order that these calculations are going to run in the execution engine that MySQL has. So if I do age + 10, and we'll put that all in parentheses, and then we come over here and we add times 10, so we're doing plus 10 here and...

Then, a times 10 here. What's going to actually happen is it's going to say age + 10, so 44 + 10 = 54. Then we're multiplying times 10. The parenthesis executes first because it comes first in this order: parenthesis, multiplication comes next because it's this one, and then anything else after that. If we did, you know, plus 10, could run this, and you'll notice that it still follows the logic. 10 was just added at the very end to all of these outputs.

Now let's go right back up here. Let's select everything again from this table. Let's pull up this table so we can see it a little better, and let's go down because the last thing that I want to show you is something called distinct. Now this is really, really useful, and you use this a lot in my SQL. What distinct is going to do is it's going to select only the unique values within a column. Let's go ahead and copy this employee demographics, bring it right down here. Let's say select, and let's do first_name. So now we're just selecting the first name. Let's come right down here; there we go. So now we're selecting just the first name from this column. Now these are all unique values, so if we come right here and we say distinct, nothing should happen to this table because these are all unique values. Let's go ahead and run this. As you can see, the output looks exactly the same. But what if we were to do something like gender? So let's come here; let's do gender. Let's run this. It keeps going down; I don't know why it's doing that, um, but now we have male and female. Now these are not all unique; we have female, female, female, and female, and the rest are males. So there's only two unique values here. So if we come right here and we say distinct gender, now there should only be two in the output: male and female. Let's go ahead and run this. So now we get male and female in our output. Now this works perfectly in one column, but what happens if we have two columns? So let's do first_name, comma gender. Let's go and run this. Now the combination of first name and gender are no longer unique. Now Leslie and female are being grouped together, and it's taking the distinct between both of these columns. So when we're only working with gender, it's only looking at this one column for both male and female, so it reduces it down to the only two unique values. But because we added the first name, all of these values are unique, so therefore the name plus the gender combination is always going to be unique.

The very last thing that I want to show you in this lesson doesn't actually pertain to the select statement, but I want to save this code. Let's say we wanted to update this or upload this into our GitHub or save this and send it to somebody; we can do that. We can save it by clicking this save button right here. I'm going to go ahead and click this, and now we're in our MySQL beginner series folder. I'm just going to save this, and I can save this as anything I want, so I'm going to say two.select statement tutorial. So now when I save this, you'll notice that the name gets changed up here to two.select statement tutorial. Let's exit out of this. I'm going to open up, and now I'm going to come here to the select statement tutorial. I'm going to open it, and now I have our code again, exactly as we had it written before.

Hello everybody. In this lesson, we're going to be taking a look at the where Clause. The where Clause is used to help filter our records or our rows of data, whereas the select statement is used to help filter or select our actual columns. So when we're using the where Clause, we're only going to return the rows that fulfill a specific condition. Let's take a look at exactly how this works. Let's say we come right up here; we're going to say where, and let's go down with that one. Let's say where, and now we need to specify what column we're about to create this condition for. So we're going to say first_name. So we're saying where the first name, we'll say, is equal to, and let's do quotes and let's say Leslie. So we're saying the first name has to be equal to this value right here, which is Leslie. For Leslie, nope. If we run this, there's only going to be one row that's returned because Leslie is the only Leslie in this entire table. Now we just used an equal sign, and that's actually called a comparison operator, and there's a few other comparison operators that you can use. Let's take a look at some of these other ones. Let's pull this down right down here, and and let's actually highlight the select from, and we're going to run it with this one right here. It's going to only select everything from the whole table, so we didn't select that where Clause. Let's go right down here, and let's look at this salary field. So I'm going to say where the salary, and I'm going to do a different comparison operator called greater than. So when the salary is greater than 50,000. Now one thing I want to note before we actually run this is that right down here we have haford who makes exactly 50,000, and I think there's one more: Jerry gach, which also makes exactly 50,000. If we run this, you'll notice that both Tom and Jerry are not in this output, but in the salary field everything is greater than 50,000. The reason for that is that Tom and Jerry made exactly 50,000. What we're saying right here is where the salary is only greater than. If we want to include Tom and Jerry, we have to say greater than or equal to, and now we'll select 50,000 or above, whereas right here before, when you're doing just this, it was greater than 50; it didn't include the 50,000. Let's go ahead and include it and run this, and now you'll notice that Tom and Jerry were both included because they had exactly 50,000, and we said greater than or equal to. Now we could do the exact same thing but with less than. So we have less than 50,000, and now we only have two people who make less than 50,000: that's April and Andy. And if we say less than or equal to and we run that, now we include both Tom and Jerry who make exactly 50,000. So it's less than or equal to $50,000.

Now what we're going to do is head on over to a different table. We're going to do the demographics table; make sure I spell that right, and let add our semicolon. Let's run this, and what we want look at is the gender really quick. So we're going to say where the gender is equal to; we'll do in quotes female, and if we run this, we get all the genders that are equal to female. But we do have something called the not equal to, and it looks like this; it's an exclamation point and an equal sign. This is going to say where the gender is not equal to female. So if we run this, you'll notice that the the gender is all male now. Now so far we've worked with things like integers, which are numbers; we've worked with characters or strings like names, but there's a different type of data type as well. In here we have a date column for these birth dates. Now in the where Clause, we can also filter on birth dates. Let's come over here, and we'll say birth_dat. Let's say it's greater than, and within quotes we'll say 1985-01-01. This is kind of the standard default date format within my SQL, which is year, month, and day. If we go ahead and run this, we can also take all the people who are greater than or born greater than 1985, so all of these dates are greater than 1985.

Now the next thing that I want to take a look at is logical operators in the where Clause. So logical operators are things like and, or, and not. Now these are called and. Let's add this: logic operators. So logical operators allow us to have different logic, and let's take a look at how this works exactly. Let's copy this down because we already have this one written out. We're saying where the birth date is greater than 1985. We can also say where the gender is equal to male. So we can say and the gender is equal, and then we'll say male. So we're adding a different complexity or an additional conditional statement within our where clause. Let's go ahead and run this. So now we're only selecting birth dates that are greater than 1985 and where the gender is equal to male. Only the rows that fulfill both of those are returned. Now the and says both this and this have to be true, but we could change this; we could say or. What this means is is either this one has to be true or this one has to be true in order for it to be returned. So let's go ahead and run this. You'll notice that Jerry gurit was born much before 1985, but since he has a male gender, he is in our output. And we could also use the not Operator by saying or not gender equal to male. So now what this is saying is the birth date could be greater than 1985, or it could not be equal to male, which is female. So if we look at Leslie, nope, she was born before 1985, but because she is female, she is in the output. Now, like we talked about in the last lesson, there is something called pemdas, and that actually applies to these logical operators as well. So if we run this entire table, let's go ahead and run this. If we're looking at this entire table, let's say we want to get someone very, very specific. Let's say we're going to do uh where the first_name is equal to Leslie and their age has to be equal to to 44. That's extremely specific, and we can actually just do it like this; we don't need quotes um for integers; we could just do the number if we'd like to. This is very specific; this is only one person, but if we put this in parentheses, we can add an or over here. We could say or the age is greater than; let's just do 55. Let's go ahead and run this, and then we'll take a look at it. So within these parentheses, we have an and operator. What that means is both this condition has to be met and this condition has to be met, and that's only one person; that's Leslie, no. But then outside of these parentheses, we have another conditional statement: or the age is greater than 55. So what we're saying within these parentheses is that this is an isolated conditional statement within these parentheses. If this is true, then in our output it'll be returned, but then we have an or condition which says or someone with the age of greater than 55 can also be in the output. So these parentheses can be really helpful when you're actually using it in the where Clause with these ands, ors, and nots.

Now I want to take a look at just one more thing, and let's bring this down here, and let's get rid of this entire thing. Now the last thing that we're going to take a look at is a like statement. Now the like statement is super unique because we can look for specific patterns; we're not necessarily looking for an exact match. Like here, if we said where first_name is equal to Jerry, if we're looking for Jerry, it has to be exactly Jerry. But if we take this out, say J, and then we run it, we get no output; it has to be an exact match. But here's where the like statement comes in because we can actually say like Jer, and we can add two special sequences or special characters within our like statement. So those special characters are the percent sign and the underscore. The percent sign means anything, and the underscore means a specific value. Let's see how that actually works. So what we're going to do is we're going to say like Jer percent sign; that's the first one in this like statement. What this says is the first name is like starting with Jer, but then has anything after it; doesn't matter what it is, as long as it has Jer at the very beginning, it will be returned. Let's go ahead and run this. Now the only person who starts with jr is Jerry. But what if I took the J out of here? Now it's saying it starts with E, and that's not anybody. What we can do is we can add another percent at the beginning. This is going to say anything comes before, anything comes after; all we're looking for is er somewhere in their name. Let's go ahead and run this. There still is only one person, and that's Jerry. Now let's come up here and let's get rid of this and let's say we're looking for everyone's name who starts with a. We can do that really easily by saying a percent sign. All that says is it starts with a; we don't have a percent sign before it, which would say this string just has to have an A somewhere in it. If we have it like this, this means an A has to come at the beginning. Let's go and run this. In our output, we have April and Andy. Now let's take a look at the underscore. If we get rid of this percent sign and we do two underscores, one, two, this is going to say it starts with an A and then it has two characters after it; no more, no less. So if we run this, an is going to be the only person who's returned because she has an A and then two characters after it. Now if we want Andy, we can specify that by doing another underscore; that's one, two, three, and now Andy is the only one in our output. Now there was also April in there, but she had more than three characters. But we can actually get her in our output by doing a percent sign. So we can combine both the underscore and the percent sign, and this is going to say it starts with an A, has one, two, three characters, and then it can have anything after that. So it just has to have at least an A and have one, two, three characters after it. So let's run it. Now you can see April comes into here because she does have a; the p, r, and i are the three next characters, but then we have a percent sign that allows that l to be in the output as well. Now we don't just have to do this with strings or text like April and Andy; we could also do this with birth dates, for example. Andy's birth date is 1989. We could say where the birth_date is like; let's say we want to look at everyone who who is 1989 or born in 1989. Let's go and run this, and Andy's the only person born in 1989. But again, we looked at the year at the very beginning, so that is how the like statement works; it looks for a specific sequence within that column that you can search for, so it doesn't have to be an exact match as long as it has that specified sequence that you've put in there anywhere within that cell or that column.

Hello everybody. In this lesson, we're going to be taking a look at group by and order by in my SQL. Now when you use the group by clause in my SQL, it's going to group together rows that have the same values in the specified column or columns that you're actually grouping on. Once you group those rows together, you can run something called an aggregate function on those rows. Let's see how this actually works. Let's go ahead and copy this right here; we'll bring that down, and let me go back up one. Let's go ahead and write gender right here. Now we want to group on this gender column, and we're going to say Group by gender. Let's go ahead and run this; we'll see what we get, and so we have male and female. Now we could get the exact same output by saying select distinct gender from this table. What is group by doing that the gender actually isn't doing well? It's actually rolling up all of these values into these rows. So later when we run aggregate functions like average, min, max, we'll do it based off of these rows, and all those rows are rolled up into these two rows, and we'll see that in a little bit. Now what if I was to come up here and in this demographics we have a first_name; what would happen if I'm selecting the first name but I'm grouping by the gender? Let's go ahead and run this. If we come right down here, we pull this up; you can see that the select list is not in group by clause and contains nonaggregated column. What this means is that when you are selecting a column, if it's not an aggregated column like say average of something, if we're not using the aggregate functions in the select statement, it has to be in the group by; these have to match. So this gender has to match this group by if we're not performing an aggregate function on it. Let's go ahead and run this, and now it works properly. Now let's go back up; let's run this query because I want to select everything again, but let's say we wanted to take a look at the average ages for gender. So what we're going to do is we're selecting gender; we're also grouping by gender, but what we're going to do is add a comma, and we'll say the average; that's AVG; that stands for average, and then we're going to put in here age. So now this right here is an aggregate function; this does not need to go in the group by; we're just grouping on the gender, and then we're performing this aggregate function or kind of a calculation based off of those grouped rows for gender. So let's go ahead and run this and take a look at the output. So what this is telling me is that for the males, all of the male rows that were grouped, the average age is 41.3, and for female the average age is 38.5. So super quickly you can tell that the average age of females is lower than the average age of males. Now we'll take a look at aggregate functions more in just a little bit. Let's actually go to a different table. Let's come right down here; we're going to go to the salary table and just select everything for now. Let's go ahead and run this. Now what we're going to actually be grouping on is this occupation right here. Now there's a lot of unique values; it's um not as distinct as the gender, which only had two values. You'll notice we do have a few that are the same; we have ones like office manager. So when we come up here, going to say occupation, and of course we need to group Group by the occupation as well. Now let's run this. You'll notice that office manager only has one row. Let's say we also want to group on the salary; let's say salary. Now we can group on multiple, so we're going to say salary like this. So we're grouping on the occupation as well as the salary. Now let's run this. You'll notice that we have two rows for office manager. Now this is because this salary and this salary for those two employees are different; we have 50,000 and 60,000. For this, I just wanted to demonstrate that if these had both been 50,000, there would only be one row: office manager 50,000. But because this is a unique value different than 50,000, they have their own individual rows, which we would then perform our aggregate calculations on. Let's go and get rid of that because we will not be using that anymore; I just wanted to demonstrate it really quickly. So before we were looking at gender and average age, and we were also grouping on the gender, we can perform other aggregate functions as well. Let's take a look at some of those. We could look at the max age as well. The max is going to show us the highest value within each of those groupings. So we have male and female; the max age for those for the male is 61, and the highest age for the female is 46. We do the exact same thing except we can say min, or the exact opposite thing; we can say the minimum age. So this is going to be the lowest for both the male and the female. Go and run this. Now we have female and male, and the minimum age is 29 and 34. And there is one last one that I want to show you, which is Count. We're going to do count. Now count is going to count the actual rows within this age column. So if we run this, you'll see that we have four females per count, and we have seven males; it's just telling us a count of how many values is in this.

Column when we're actually grouping on the gender; so that's how we can use groupy to actually roll up and group all of these similar values within a column or columns and perform our aggregate functions on them.

Now let's come down here, and what we're going to take a look at is order by. So we're going to say order by. Now let's actually pull in this demographics table right here; we're just going to say select everything and run this really quickly after we had a semicolon. So order by; order by is going to actually sort the result set in either ascending or descending order. Let's take a look at how this works. At the very end, we could say order by, and we could order by the first_name. So we're going to take this column; we're going to order all of our rows based off of this one column. Let's go ahead and run this. So it's going to do it based off ascending order, which means smallest to largest. Now this is a text column or a character column, so we do it A to Z; so Andy and April all the way down to Tom.

Now by default this is in ASC order, ascending order, and if we run this, it's going to be the exact same output, but we can change this to do it the opposite, highest to lowest or Z to A, by doing descending. So now if we run this, you'll see that goes Tom all the way down to Andy.

Now let's take a look at ordering on something like gender and age, because we can do both at the same time. So let's order by the gender first. Let's go ahead and run this, and you'll see that all the females are grouped together, and then all the males are grouped together, because that's just the order in which it is. But we can do an additional column; we could also do it based off of the age. Let's go ahead and run this. So now within the female, since that came first in our order by, we're ordering by the gender, and then we're also ordering by the age after we've ordered by the gender. So now it's 29 all the way up to 46, then 34 for males all the way up to 61. Now we can change this just for the age. Let's say we want to do age descending. So gender will stay the same in ascending order, but now age will be in descending order. Let's go ahead and run this. Now female and male stay the same, but now it starts at the highest down to the lowest.

Now this is something that I would absolutely do in real life, except sometimes you can make mistakes, and sometimes you do the wrong column first. Let's do age, and then we'll do gender. Now if we run this, the gender is not going to be used at all, and this is because there are no unique values that are going to be on the same row. So notice all these values are completely unique; so the gender never is actually used to order anything on because if there were things like 34, 34, 34, 34, these would be ordered based off of the gender, but since there's no unique fields, this is really pretty useless. That's why the order of the order by, or the columns that you place in the order by, are actually quite important.

Now the last thing that I want to show you, and I'll just go back to gender and age, is that you don't actually have to use the column names; we can use the column positions. Now I will preface this by saying I don't recommend doing this, but I sometimes do it in shorthand for just a quick query, um, if I know the column position and I don't want to write out the whole name. So sometimes I do it, although it's not best practice. But let's take a look at it. So gender is the one, two, three, four, fifth column. I'm going to replace this with five, and age is the one, two, three, fourth column. So these are the positions of the fields, but not the names of them. If we run it, we're going to get the exact same output, because these represent these columns appropriately. But again, I just don't recommend it; it's kind of a slippery slope that I've fallen down myself, uh, many times, and when you get to more advanced SQL and you're creating things like store procedures and triggers and all these things, this can actually cause a lot of issues. If you were to add any columns or remove any columns, then you'd be ordering by the wrong column. Because let's say this last name got removed; we didn't want it for some reason, then the gender is one, two, three, four; now we're ordering on the wrong column, and that would be a big mistake. So just by best practice, it is better to do gender, um, age. But I just wanted to show you that in case you want to be like me and kind of go down the wrong path.

Hello everybody; in this lesson we're going to take a look at the difference between having and where. Now in the last lesson we looked at group by and order by. The most obvious thing to do would be to come right here and say where, and we're going to say this column, which is actually named this; we'll say where the average age, let's say, is greater than 40, which would only be the males. So let's go ahead and run this, and as you can see we're not getting any output. Let's bring this up and take a look at the error; it says invalid use of the group by function. What's actually happening is something to do with this group by gender right here; when we're selecting gender and then we're performing an aggregate function, this occurs only after the group by actually groups those rows together. So when we're trying to filter based off of this column right here of average age, it really hasn't been created yet, because this group by hasn't happened. That's where the having clause comes into play. So let's go ahead, and what we're going to do is we're going to get rid of this; we're going to come right down here, and instead of where, we're going to say having. Now having was specifically created for this exact example; it comes right after group by, and after group by we can filter based off of these aggregate functions. So now if we run this, we're going to get an output that only has where the average age is greater than 40.

Now let's take a look at just one more example, and I'm going to show you how you can use both in one query. So instead of demographics, let's look at the salary table, and let's run it. Now in this salary table we have this occupation, and remember we have this office manager that happens twice, and this is going to be our main example. So we're going to say occupation, and then we'll say the average salary. Now we'll need to come down here and we'll say group by. Now we're going to say occupation. So this should look pretty similar, because right here we have our office manager, and one of the office managers made 50; one of the office managers made 60, so the average is 55,000. Now I can use the where by saying where, then I'll say occupation like, and let's see people who are managers; so I'll say percent manager percent and close that quote; so they're like a manager, and then I want to see where a manager makes more than, let's say, 75,000. So I won't actually say where; I'm going to say having an average salary, and I need to add a space there; having an average salary greater than, let's say, 75,000, and let's run this. So now I filtered at the row level right here in the where clause, but then down here I filtered at the aggregate function level. This having is only going to work for aggregated functions after the group by actually runs. So that is the difference between the having clause and the where clause. The where clause you're most likely going to use a lot more, but if you do want to filter on those aggregated function columns, you have to use the having clause.

Hello everybody; in this lesson we're going to be taking a look at limit and aliasing. Limit is just going to specify how many rows you want in your output. If we take this table for example, if we come right here and we say limit, let's do three; if we run this, it's only going to take the top three that we have. Let's go and run this. As you can see, we have employee one, three, and four; Leslie, Tom, and April. Now this seems super straightforward, really really easy, but it can be combined with order by to actually be really powerful. For example, let's say we wanted to take the three oldest employees; all we'd have to do is come right under here; we say order by, and we'll order by the age in descending order. So we're going to order on age descending, and then it's going to take the top three. So if we run this and very quickly we have the top three oldest people in this table.

Now there is one additional parameter that we can use in limit, and all we have to do to access it is have a comma here. Now what this is going to do, and I'll put a one here, what this is going to do is it's now going to say we're going to start at position three, and then we're going to go one row after it. Now I actually want to take one of these people, so let's start at position two and select the next one after it, which should be Leslie; nope. So we're going to start at position two, and we're going to select the one right after it; so we're going to start at position two, and then one means we're taking the next one row. Let's go ahead and run this, and as you can see we got Leslie, nope, in our output.

Now let's come right down here; we are going to now look at aliasing. Now aliasing is just a way to change the name of the column for the most part, and it can also be used in joins, but we're going to take a look at joins or aliasing joins in the intermediate series. In a previous lesson we looked at a group by that looked like this; we selected gender, and we said from, I believe it was Employee_demographics; then we say group by gender, and we also had the average, and I think it was age; there we go. Go and we'll add our semicolon. Let's go ahead and run this. In our output we have gender as our gender column, the same as the column name, but then average age is average age. And so if we want to actually do something like a having where we say having the average age, let's say, greater than 40, like we had it, we have to actually use this aggregate function in our having clause, and we don't want to always have to do that. We can actually change the name of this column and subsequently use it throughout our query with that alias name. So I'm going to say as, and that's the keyword to actually change it; we'll say as, and we'll do average_age. So now we've changed this name to average_age, and we can come down here to having and say having the average age greater than 40, and when we run this it works perfectly, and you'll notice that the name of the column was actually changed. Now this as isn't actually 100% needed; it's kind of implied; even if we get rid of it, it's implied there's like this as in there somewhere, um, but we don't have to have it. If we took it out and ran it like this, it would still work exactly the same.

Hello everybody; in this lesson we're going to be taking a look at joins. Joins allow you to combine two tables or more together if they have a common column. That doesn't mean the column name has to be the exact same, but at least the data within it are similar that you can use. There are several joins that we're going to look at today, like inner joins, outer joins, and self joins. These are the two tables that we'll be working with the most throughout this lesson; we have the employee demographics table as well as the employee salary table. Now within the employee demographics table we do have this employee ID column, and if we look at the employee salary, we also have the employee ID column. So in this instance the column name is actually the exact same, and of course the data inside of it is also very similar. So let's start by writing out an inner join. This is probably one of the most common joins, one of the most simple joins as well. An inner join is going to return rows that are the same in both columns from both tables. So let's see how we can actually write out this join. Let's come right down here, and let's copy this; this will be the first table that we start with, and then we'll join the salary table onto this demographics table. So what we need to do is we need to come right here, and we need to say join. Now by default join represents an inner join, although we can write inner join here to make it more explicit. Explicitly writing out inner join, then we're going to come up here and we're going to say employee salary. So we're selecting everything from the employee demographics; we're doing an inner join on the employee salary. Now we have to tell MySQL exactly what columns we're supposed to be joining on. I'm going to hit enter, and I'm going to hit tab. Now you don't have to hit tab; it just looks more code-like, and it's easier to read, and that's how I've done it for other programming languages as well; so that's how I'm going to show you how to do it. What we need to do is say on. Now this keyword is going to allow us to say we're joining the demographics table to the salary table based on these two columns. So from the demographics table we're doing the employeeid is equal to, and then in the salary table it's also the employee ID. Let's do employee, and you spell that right, employee ID. Now if we try to run this, and let's do this, we're going to get an error, and let's bring this up; it's going to say column employee ID on the in clause is ambiguous. Now what does it mean ambiguous? That means that it doesn't know what table this employee ID is from; is it from the employee demographics table? Is it from the employee salary table? We don't know, because it's ambiguous. Now what we can do is we can specify it by saying employee_demographics.employee_id and then employee_salary.employee_id. Now if we run this, we're going to get the output that we're looking for, and let's take a look at this real quick. Let me bring this up. So we're pulling everything from the employee demographics; that's right here all the way through the birth date; then we're pulling the employee salary table; that's the employee ID all the way, let's scroll over, through the department ID. So we're basically pulling in all of the rows or all the columns from both tables, but we're not pulling in all of the rows. Remember an inner join is only going to bring over the rows that have the same values in both columns that we're tying on. So in this employee ID we're missing number two; are we missing any other ones? No, we're only missing number two. Let's go back up, and I'm going to run both of these tables, and we're going to take a look. So let's run this. So you'll notice in the employee salary table we have a number two right here, and that's Ron Swanson, but in the employee demographics table we don't have that. I believe that Ron Swanson did this; that Leslie Knope would not know when his birth date was because he didn't want to bring that information. I think that makes the most sense, although Ron was not willing to give a comment on that. Now if we run this again, you'll notice that two is not in there; since two is not in the employee demographics table, the employee ID two is not going to be populated or brought over into this output from the employee salary table.

Now really quickly, this is honestly, uh, giving me some anxiety because this is so incredibly long. Something that I mentioned in the beginner series is that you can use something called aliasing when using joins, and it's really helpful. This is what I mean. So right here we have employee_demographics; we're going to call this DM. You can also do AS DM; you can do AS sal; these are just short names for demographics and short name for salary, and we can replace these and say dm.employee_id and s.employee_id. Oh, that looks so much better. Now we're going to run this, and it'll be the exact same output, but now we're using these aliases, which just makes it so much easier to read.

Now one last thing that I want to show you while we're just looking at the inner join is selecting the actual columns. Let's say we wanted to select the employee ID, and we wanted to select age, and then we wanted to select their occupation. If we try to run this, we're going to get an error, and it's going to be almost the exact same error that we got before, which is column employee ID in field list is ambiguous. So in our field list, which is right up here in the select statement, we have this employee ID; it does not know which employee ID to pull from, whether it's the demographics or the salary. So we have to tell it which one to pull from. So let's pull it from the demographics by saying dm.employee_id. Now when we run this, we're able to get information from both tables in our output without having all of the information, and if there are columns that are similar in both tables, we have to denote that by using this alias or the table name. All right, so that is inner joins. Now let's move down here; I'm going to copy this, and we're going to come right down here, and we're going to look at outer joins next, and let's put that right here.

Now for outer joins, we have a left join and we have a right join, or a left outer and a right outer join. A left join is going to take everything from the left table, even if there's no match in the join, and then it will only return the matches from the right table. The exact opposite is true for a right join. Let's see how this actually works. Let's start by changing this to a left join or a left outer join; they're both the same, and you can use them, uh, similarly. I'm just going to say left join, and we're joining it on the exact same things, and I'm going to take everything, so I think that'll be easier to visualize, and I'm going to run this. Now you may notice that this looks exactly the same, and that's for a very good reason; it's because in the left table, which is the employee demographics table, the from statement, that's our left table, and then the join where we're actually joining on, that's our right table. So this is our right table; so since we're doing a left, it's taking everything from the employee demographics. Now remember the employee demographics didn't have Ron Swanson; it had no information; so everything in the right table had a match. Let's change this to a right join, and what this is going to do, and I want to make it all caps, make it all the same, what this is going to do is it's going to take everything from the employee salary table, but if there is not a match in the employee demographics, it just will have nulls. Let's go ahead and run this. So now looks a little bit different. Now we're taking everything from the employee salary, so we're taking Ron Swanson, but if there is not a match, it will still populate that row, but it'll have all nulls in it; then any of the information that is overlapping, are the same, it will bring over; so employee ID is matched to employee ID one, then we'll bring all that information over from the left table, and that's essentially what a left and a right join is. With a left table, you're taking everything from the left table and then matches from the right table; if you do a right join, you're taking everything from the right table, but only matches on the left table, and again it populates it with nulls.

Now let's go down and look at our last type of join that we're going to look at, and this is a self; let me spell that right, a self join. Now what is a self join? It is a join where you tie the table to itself. Now why would you want to do this? Let's take a look at a very serious use case. Let's do select everything; let's do this.

From uh, employee unor salary, and let's run this now. Let's say it's December 1st, and the employee and Rex Department decided to do a secret Santa. And they wanted to assign, based off of their employee ID, the person who they're going to have as a secret Santa. We can help orchestrate this very easily using MySQL. Well, "very easily" is subjective, I guess, but let's take a look at how we can do this.

So, just like any other join, the first thing we're going to do is Select everything from employee salary and then say join, and then we're going to say employee salary again. So we're tying it to itself. Now, when we come down here, and let me do this, when we come down here and we say on, we have to specify which table we're pulling from. Are we pulling from the left table, which is like the first table we're pulling from, or are we pulling from when we're joining on the right table? We need to be able to distinguish these two tables because they are the same. So I'm going to say emp1 and I'm going to say emp2, just to say this is employee table one and employee table two. So we're going to tie them based off the employee ID because we know those will be the exact same because we're pulling from the same table. So we'll do EMP p1.employee, and just so you know, if it populates like this, you can hit Tab and it'll Auto finish that for you, and EMP 2.employee ID.

Now, if we run this, let's do this. The output that we're going to get is literally just a one for one match; it's all the columns and all the rows because they all match exactly. But now what we're going to do is we're going to assign an employee ID to the next employee ID, and that'll be their Secret Santa. So just keep it really simple: the next highest person with an employee ID is their Secret Santa. So let's do an employee ID + 1 is equal to employee 2.employee ID. So we're adding one over here, and we're saying that's equal to this employee ID over here. Let's run this. So now you can see Lesly nope is now going to be assigned to Ron Swanson, who has an ID of two. Ron Swanson is going to be assigned to Tom haford, which I'm sure he's really happy about, and so on and so forth.

Now let's bring this down here, and what we're going to do is try to simplify this and simplify this output a little bit because this is a little bit chaotic down here. So we're going to specify what columns we want in our output. What we're going to want is the employee ID, first name, last name, and then employee ID, first name, last name of the person who they got for Secret Santa. So we're going to start with emp1 employee ID, and we can call this, we'll just say as empore Santa. Then we'll do a comma, and we'll come down. Now I need to spell m employee right. So we have our employee ID, and now we need our first name and last name. Now remember, Leslie nope is going to be the secret Santa for Ron Swanson. I don't know if I made that clear, but that's I guess how it works. So now we need to do uh emp1 Dot, and we'll do first uncore name, and we'll do as, we do as first unor namore Santa. We can do a comma; we'll do the exact same thing except for the last name, so lastore name as lastore name Santa. Now all we have to do is do a copy all this, bring it down here, and change this to two; we're pulling from the second table, and it'll look just like this, and get rid of this comma, and this is done. Let's run it, and let's bring this up. So we have employee Santa, first name Santa Leslie, last name Santa nope, then employee Santa, and we actually need to change these names; that is one thing we need to do. We'll just change it to employee name, first name employee, and last name employee. And now when we run this, we have our Santa, and then we just have the employee who this person is going to be the Santa for. Now this is kind of a silly way to look at it, but in essence, this is exactly how a self-join works.

Now, the very, very, very, very last thing, I promise you, the last thing that I want to show you is how we can join multiple tables together. So we're going to say joining multiple—you spell that right—multiple tables together. Now, not just one table together to another table; I'm talking about one table to another table to another table. So let's go all the way back up; we're going to take this right here and bring it all the way down, and what we're now going to do is we're going to tie in this table right here, the parks department. Let's actually look at this table, and let's select everything real quick, and let's run this. Now let's go down here, and we're going to say select everything; we'll do this from parore departments, and let's run this. Now this is something called a reference table; this is not a table that most likely you'll ever add a bunch of information to. It's there to reference that we have these Department names. Tables like the salary table or employee demographics table are going to change pretty often as people get raises or as they get older with their age; those are going to be updated fairly often, whereas this parks department table is just there for reference. Now, if we look down here in the columns, we have a department ID, then we have a department name. So we have the ID and the name of that ID. If we run our join and we scroll all the way to the right, you'll notice we have a d ID; this stands for Department ID that's in the salary table. So what we want to do is join this department ID to the department ID from the parks and wreck. So what we can do is we're going to say inner join, and now we're going to join—let's scroll down just a hair—there we go. Now we're going to take this and do it off this. So we're going to call this PD for short, and we're going to say we're joining it on. Now we cannot join this park Department to the employee demographics table. Why is that? Well, the employee demographics table only has employee ID all the way through birth date; there's no common column that we can tie to this parks department. The only table that has a common column is this department ID in this salary table. So what we need to do is actually take s, so we'll say Sal Dot, and then we're going to say d, and we'll say Department ID is equal to the PD Dot, and we need to take the department ID. Now notice these are not the exact same name; they are a little bit different, but they have the same values. One thing I forgot to mention is that in this parks department, there's no repeating; that's why it's a reference, whereas in the salary, the department ID repeats several times because multiple people are in the same department. So this reference table also usually does not have duplicates—uh, just one other thing to note—but we have now tied it successfully. Let's try to run this, and, and if we come down here, go all the way to the right, we now have the department ID 1111111 and the department ID and Department name Parks and Recreation, Healthcare, Public Works, Finance, Public Works, and Parks and Recreation. So this worked perfectly. So this is how you can tie multiple tables together if you have common columns between them. Even though employee demographics has no column that's related to the parks department table, we can still tie them together based through this employee salary because employee demographics can tie to employee salary, employee salary can tie to the parks department, and that really is the majority of what you need to know in order to use joins.

Well, hello everybody. In this lesson, we're going to be taking a look at unions in MySQL. A union allows you to combine rows together, not like columns like we were doing before with joins where one column is next to the other. A union allows you to combine the rows of data from Separate Tables or from the same table; it's up to you, but you do that by taking one select statement and using a union to combine it with another select statement. Let's see how this actually looks. So what we're going to do is right after this select statement, we're going to come here and say Union, then we're going to go right below the union, and we're going to do another select statement. So we're going to copy this, place it right here. Instead of the demographics table, just for example, we'll do the salary table. Now, if we look at the demographics table, let's say we want to take age and gender, and let's go and take a look really quickly at the salary table, and let's say we want to take first name and last name. So we'll do first uncore name and lastore name. Now let's go ahead and run this and see what it looks like, and let's pull this up. So as you can see, we have age and gender that's from the very first select statement, and that's also the column names, but then we have all of the data for the age and gender, and then below, if we move this over a little bit, we have the last name and first name from the employee salary table; it's just down here. Now what I just demonstrate is that this doesn't always work for everything; you can't just combine random data together because this is bad data; we shouldn't have age and gender mixed with first name and last name. Really, when you're using this, you need to keep the data the same. So for us, we should take the first—I'll actually just copy this—the first and last name from the employee demographics as well, and let's run this, and now we have all the names from all of the tables. Now you may be thinking, where did all the other data go? Before we had a lot of rows, but now we only have a unique Row for each one. Well, by default, this is actually a union distinct, and if you remember, distinct is only going to take unique values. So when we're doing this Union, it's going to remove all the duplicates, and the first name and last name from salary overlaps a lot with the employee demographics table. So when we ran this, the only one that's actually somewhat unique to one table is that in the employee salary table we have Ron Swanson, whereas we don't have that in the employee demographics. Now if we wanted to show all of them without the distinct, there is something called a union all. If we run this, now we're going to get all of the results without removing any of the duplicates. So if we scroll down, we're going to have duplicates in here, but we're just showing all of the results from this table and from this table.

Now that we know how to actually use a union, let's look at a use case. So let's go right down here, and let's copy this, why not, and let's put it right down here. Now let's say in the employee demographics we wanted to take the first name and last name where the age is greater than 50, and let's run this. There's only one person, but let's label them; let's add a label. We're going to say comma old, so this person is old, and if we run this, it says first name, last name, and old, and we can even call this as label, and if we run this, the label is old, so Jerry git is the only old person in this demographics table. Now why are we doing this? Well, the parks department is trying to cut their budget a little bit; they want to identify older employees that they can push out, and they also want to identify high-paid employees who they can reduce their pay or push them out to save money. So we just identified someone who's older who we're going to want to try to push out, but let's, in the same output, find people who are also highly paid. So now we can come down here; we can say Union, and let's do this like this: I need to spell this right. All right, Union, and let's take this; we're not going to be using this exact same query, but we actually need to pull from the salary table, so the employee salary. So we also want the first name and last name, but let's say where their salary is greater than let's say 70,000. That's a lot of money; if you're making more than 70, uh, for sure the parks department is going to try to get rid of you. But for the label, we're going to change it to a highly paid employee. Now let's go ahead and run this. So now we have Leslie nope and Chris Trager; they're both labeled as highly paid employees. Now 50, I think, is just a little too low, um, if I'm being completely honest. I think we need to change this, and we should do a union and then add another select statement. Let's bring this down. I think the 50 is too low; let's change it to 40, and let's add one more thing: let's say and the gender is equal to male, and then we'll go down here and say where the gender is equal to female because we want to separate this out. So we want to know who's the old man—oh, that's actually old lady—this is the female one, and for up here where it's male, we'll say old man. So we have three different select statements using two separate unions; we're selecting the first name and the last name in all of them, keeping the data consistent, and then in our third column, we're labeling it either old man, old lady, or highly paid employee. Let's go ahead and run this, and let's look at our output. Now you may notice something really quickly that Chris Trager and Leslie nope are an old man and an old lady, and Leslie nope and Chris Trager are both highly paid employees. So these people meet multiple criteria. So let's actually order by, and then we'll do first_underscore name, comma lastore name, because we want to order by these to see. So let's run, and now we can easily see that Chris Trager is both of these, Donna is just an old lady, J's just an old man, and Leslie is both an old lady and a highly paid employee. So now we can send this to whoever we need to send it to to make sure that these people get looked at first so that our job is still secure. The job market is tough these days; you got to do what you got to do. So that is how we use Union, and let's just take one more look at it; there we go, go. So this is how we can use unions; it's kind of a real use case. I've done something very similar to this in my real job, but you know, this is just an example of how you can have multiple select statements all combined or combining the rows using a union.

Hello everybody. In this lesson, we're going to be taking a look at string functions. Now, string functions are built-in functions within MySQL that will help us use strings and work with strings differently. Now we're going to look at a ton of different ones; they all have different use cases, but I'll try to walk through some of those as we go along, but we'll look at a lot of different string functions in this lesson. We'll start off with one that's really simple; this one is called length. So if we select, then we say length, and let's say we put in—and I don't know why it's popping up like that—let's say we put in something like sky, or Skyfall, or really anything. If we run this, it's going to give us the length of how long this string is. So if I come down here, we say select everything from employee unor demographics, and let's add a semicolon here. Let's run this one right down. Now what we can do is we can look at how long each person's name is. What we can do is just take the first name, but then we'll also do the length of the first uncore name. So if we run this, now we get Leslie, Tom, Jerry, Donna, and it gives us the length of their name. If we wanted to, we could even order by this, so we could do order by, and we could just do two for now, and we can order by the length from the shortest name all the way to the longest name. Now one use case that I've used length for in my actual job was when I was working with phone numbers; I wanted to make sure that they were exactly 10 characters long; otherwise, something went wrong somewhere in the daa cleaning process. So I would go and look at the length, and I would make sure they're all 10; if anywhere 10, I would go and specifically look at those and try to clean those and fix those up.

Now let's go on to the next one, and these next ones are pretty simple ones, at least I think they're fairly simple. We're going to look at upper first, and it's doing the same thing as the other one. We'll do upper, and let's say we're going to do Sky. If we select upper Sky, it's going to give us an all uppercase, or we can copy this and we can do lower. So now let me add semicolons, otherwise it's going to drive me crazy. Uh, let's try this lower. Now it's going to do all lower, even if I make it all capital. So if I say all capital Sky, it's going to make it all lower. So if we come back up, let's copy this, and instead of doing the length, now we'll do upper. Let's go ahead and select this. So we have Leslie, and then we have the upper first name, so upper all case Leslie. Now this is actually really good; this is really, really helpful, especially with standardization; is what I found a great use case for it because sometimes it'll be all capital Tom, and sometimes they'll put it in as T lowercase om, uh, and just making them all uppercase or all lowercase can help correct those really simple standardization issues within a single column. The next one that we're going to look at is trim. Now there's multiple trims; we have trim, left trim, and right trim. Trim is basically going to take the white space on the front or the end and get rid of it, which is really helpful. So what we're going to do is we're going to come right here, say select, and we'll start off with trim, and let me add a semicolon every time, so then we'll do trim, and for our actual string, we'll do something a little bit odd; we'll do some spaces, and then we'll do a sky, and then we'll add some spaces. Now let's run this and add our semicolon; that's going to be the end of me in this lesson, just adding semicolons. Now it fixes it completely. Now what if we don't add sky at; we'll just keep it like this. Well, you can see that there's spaces before and there's spaces after, but that's what trim does; trims gets rid of the leading and the trailing white spaces. Now if we come up here and we just do the left trim, it's only going to remove from the left-hand side, so we're only getting rid of the left-hand side white spaces; this right-hand side, as you can see, is really long; it's still there. And if we do our trim, we go ahead and, and run this one, it gets rid of the white space on this side, but it doesn't get rid of the left space on this side.

Now let's keep going; we have a lot to cover still. We're going to move on to what I think is probably my most favorite string function, if I'm allowed to have a favorite string function, and that's substring, but I'm going to kind of work us into substring a little bit by looking at two smaller functions, which is left and right. So let's select everything, and we'll do that from the employee demographics again; add our semicolon. Now I'm going to run this. Now I want to get the first name, and I'm going to do left of the first_underscore name just like this. Now when you're using this, is actually going to be an error. Let's see if I highlight over this if it'll tell me what the error is; it says the parenthesis is not a valid position; they're expecting something else, and basically what they're telling us is that this is not how it should be written. We're looking for a different value; that different value is actually a number. We're going to do comma, and let's do four; that's what it was looking for; it didn't want this at the end; it needed this comma four, and what we're actually specifying is how many characters from

The left-hand side, do we want to select? So we're selecting the first name, and we're going from the left four characters. Let's go ahead and run this. And so we have Leslie, Tom, April, all the way down. You can see that there's only four characters in each one. So someone like Chris, the S is no longer going to be there because we're only looking at the first four characters.

Now we can do the exact same thing, and let's actually copy this uh down here. So we'll come, we'll go like this, make this a little more professional, and we'll do right. So now we'll do right. If we do the right four, it's going to go from the right-hand side of the string and go left four. So we're looking at the far four most right characters. Now this can be useful in certain instances, but if I'm being honest, I don't use these that much. For the most part, I'm pretty addicted to using substrings. I love substrings. I think they're fantastic. And let's look at substrings like this. So a substring is going to allow us to do a few different things. Let's do first_name. The second thing that we put within this function is the position that we want to start at. So let's say we want to start at the third position, and then we specify how many characters we want to go. So with this, we specified four, but let's just do two. So now we're going to the third position, and we're going over to the right two characters. Let's go ahead and run this. So with Leslie, we get SL. So we go one, two, and three. We start at the third position, and then we take two characters, the S and the L. I have found this one to be extremely extremely useful. Let's take this for example. Let's do comma, let's do birth_date, and let's run this. I'm keeping everything in here, although it might be a bit much. Let's say we have this birthday, and this middle column is the month, and we'll running some, you know, query. We want to find the month that everyone is born. So we can do that very easily using substring, and we wouldn't have been able to do this very easily using left or right. So now we're going to take this birth date, we're going to use this substring, and we want to select these middle characters. So what we need to do, since it's all standardized, we do 1, 2, 3, 4, 5, 6. We start position six, and we want to select one and two. Let's go ahead and run this, and now we've pulled out all of the months. So we can say as birth_month, and now we could save that, put it into a temp table, add it as a new column in our table, whatever we want to do, uh, but now we have this information that we desperately desperately wanted to know. So that is left, right, and substring. Again, substring is, it's fantastic. Now let's keep going. The next thing that we're going to take a look at is replace. Now replace will replace specific characters with a different character that you want. So let's actually copy all this right here because I don't want to keep writing this out, and we'll say select everything. Now what we're going to do is we're going to take the first_name, and then we're going to say replace, and then we'll also do the first_name, but we can specify what we want to replace and then what we want to replace it with. So we have two more parameters that we need to put in this function. So let's say a, and let's replace it with a z. Let's just see what that does. Let's go ahead and run this, and so now when we see the letter a, and we are specifying a lowercase a, like Mark, that is replaced with a z. So that's really all replace does. Specifies what you want to replace and then what you're going to replace it with.

Now let's take a look at the next one, and we're going to take a look at a function called locate. So if I say select, and let's do locate, I'm going to give it a string. I'll say Alexander, that's my name, and I'm going to specify what I'm looking for. So let's close this parenthesis. The string that we're actually looking for comes first. So what we're going to do is I'm looking for the letter X in my name. So we'll do X and Alexander. Let's go ahead and run this, and it tells us that it is in position four. So we have 1, 2, 3, and 4. That's where our position is. That's where it locates that sequence that we're looking for. Now if we pull this down here, place this right here, and we'll change this locate. Now let's say we're still looking at the first name, but we want to locate people that have an an like this in their name. Let's go ahead and run this, and we get zeros for everybody except for Ann and Andy. So this might be something where we put it into a CTE or a temp table, then we can filter down based off of these results to where it only equals one.

Now the last one that we're going to take a look at, and let's go right here, we're going to do first_name, last_name. Now this one is super super useful because what we can do is have a concatenation of multiple columns. So let's go down right here. So we have first name and last name, but if we come down and we say concat, we can then combine these columns into one single column. So we'll do concat, and we'll do first_name, and then comma, last_name, and if we run this, it's going to be Leslie and Nope combined into LeslieNope. Now this doesn't look perfect, right? We don't want it to look like that. All we have to do is come in here, and we could do a little space. So we'll add a space in there, and if we run that, now we have Leslie and Nope, and we could call this as full_name, and this is something that I've done a million times in my real job where there's multiple columns. We want to create one column out of it, or take two columns and create one column. Happens all the time. So this concat is really really helpful to combine those columns really quickly.

Hello everybody. In this lesson, we're going to be taking a look at case statements in MySQL. A case statement allows you to add logic in your select statement, sort of like an if-else statement in almost all other programming languages or even things like Excel. Let's see how this actually works. So let's bring this down and let's take this employee_demographics table and let's take the first name and let's take the last name and let's add a case statement. How we need to do this is we have to say case, so that's going to signify that we're starting a case statement, and then I'm going to go over here and say tab. So this is where our logic comes into play. So I'm going to say when the age, let's say is less than or equal to 30, then. So I'm saying if the age is less than or equal to 30, then what's going to happen? We'll just keep it really simple for now. We'll just say that this person is Young. And then if we want to end the case statement, we'll come down here and say end. So this is a complete case statement. Let's go ahead and run it, and let's take a look at the output. So we have the first name, we have the last name, and then we have this case statement right here, and if their age is less than equal to 30, they're young. Let's actually add the age right here just so we can visually see that as well. So we have the age. So this person is the only person who's under or equal to the age of 30, that's April, so she has a label of young. The great thing about case statements is you can add multiple when statements. So we can come down here and say when, and then we could do something like when age, and maybe we'll say between. So I don't know if in previous lessons we've looked at between, but between just says between this number and this number. So we'll say between 31 and 50. If they're between 31 and 50, well, good night, uh, that person is old. So we're going to have it just like this. We're going to run it, and now we have a lot of people who are old. These are all people between the ages of 31 and 50, but we still have more people outside of the age of 50 or older than 50. So we could do when the age, and now we can say greater than or equal to 50, and we're going to say then, and then we're going to say on death's door, uh, because good night, if you're over 50. My parents are going to love me for this one. So let's go ahead and run this, and then if we look at this, we have on death's door right there. Now this is huge, this is massive. So let's actually name this, and we'll just say as at the end of end. So right after end, we'll say as age_bracket, and let's run this, and this looks a lot better. So now we have this age_bracket just signifying kind of where people are at, and most people are quite old, or Jerry, uh, you know, can't catch a break, that guy.

Now let's go down and let's take a look at a different table. So let's select everything. We'll do from employee_salary. Now that we have our employee_salary table, here is the scenario that we are given. The Pawnee Council sent out a memo of their bonus and pay increase for end of year, and we need to follow it and determine people's end-of-year salary or the salary going into the new year, and if they got a bonus, how much was it. So the first thing we need to do is we need to get the pay increase and bonus, and their pay increases look like this. So if they made less than 50,000, then that equals a 5% raise, very generous, and if they made greater than 50,000, that equals a 7% raise, very very generous. Lastly, if they work in the finance department, that equals a 10% bonus, just cash that goes into their bank account, very very generous, but only the finance department gets it. So these are the guidelines that the Pawnee Council sent out, and it is our job to determine and figure out those pay increases as well as the bonuses. So let's come right down here. We're going to have our salary, employee. I actually want to be able to see these. Let me pull this up just a touch. There we go. So we want to be able to write this out. So first thing we should do is just select the columns that we need: first name, last name, probably salary as well. And now what we can do is determine this first one, which is if they make less than 50,000, they get a 5% raise. So let's say case, and I'll also add end in here, and we're going to say when their salary is less than 50,000, what's going to happen? Then we say then salary. So we're taking their initial salary, and we're saying plus, and we're going to do salary times 0.05. And if we run this, it should work. Let's pull this up really quickly. So April Ludgate, she made under 50,000, so she got a raise, and her new salary is 26,250. We can actually call that, we'll say uh as, actually let's do new_salary. That's their new salary, and let's run this. So the new salary is 26,250. Andy Dwyer is now making 21,000. Now this calculation, you can do it different ways. We can do it exactly like this, or we can just do times 1.05. Should be the exact same thing, uh, just however you'd like to write it out. It's just, you know, adding it or multiplying it by this. So let's take this, and now we're going to say when it is greater than 50,000. So let's say greater than 50,000, they get a 1.07. So this is the 7% increase. This is a 5% increase. This is a 7% increase. And let's run this, and let's put this up here. So now if they made greater, so 50,000, that's 75,000, they got a 7% increase. Now unfortunately, we did not make the rules, the Pawnee Council did, and the people who made exactly 50,000 unfortunately were not part of those brackets, uh, and that just wasn't up to us. We couldn't control that. So unfortunately, Tom Haverford and Jerry Gergich just didn't get raises this year, and that's not our fault. Okay, that's not our fault. Now the next thing that we need to do is determine the bonuses. Now let's come right back up here really quickly, and let's just copy this because what we need to determine is how we know that somebody is in the finance department because if they're in the finance department, that means they get a 10% bonus. That's really important. Now it's not in the employee_demographics. Don't have anything about the department, but if we look in the salary and we run this, we do have the department ID. Now let's open up and let's pull this up right here. We'll look at the Parks department, and in the Parks department, here we go, the finance is Department ID of six. So if we're looking at the salary, there's only one person who's in uh Department ID equal to six. So what we can do is another case statement. We can say comma, we'll do case and n, and we'll do another one. We're going to say when dID, so when the department ID is equal to six, then we're going to give them a bonus. So we're going to say salary times 1.10, and we'll call this as bonus. Let's go ahead and run this, and let's pull it up. So he gets a $7,000 bonus this year. That's Ben Wyatt, uh, because he was part of the finance department that just did uh an exceptional job this year, apparently according to the Pawnee Council. So that is how case statements work. They're really powerful, really useful. I honestly use them quite often, and they're just a way to really add some logic and some, you know, labeling or even do calculations like we did right here with the salary.

Hello everybody. In this lesson, we're going to be taking a look at subqueries in MySQL. Now a subquery is basically just a query within another query. We can do this in a few different ways, and I'm going to try to show you a lot of the different variations within this lesson. The first way that we're going to use a subquery is in the WHERE clause. Then we'll take a look at the SELECT and the FROM clause also. Let's take this demographics table that we have down here. What if we only wanted to select the employees who worked in the actual Parks and Rec Department? Well, we could do that if we had a few joins. We have this salary table, and one actually represents that they work for the parks and rec. If we come over here and we open this up, we can see that Parks and Rec is the department ID of one. So we do have that option. We could just join these two tables together, but sometimes we don't want to do that, and we'll use a subquery. Let's see how it works in the WHERE clause. So let's go ahead and get rid of this. So what we're going to do is we're going to say select everything from employee_demographics WHERE, and now we want to pull, because this is the salary table, we want to pull employee IDs where the department ID is equal to one. But remember, we're querying off of this table. So let's actually pull this up. This is what we're working with. So we want to say WHERE the employee ID, that's referencing this column in the demographics table, is in. What we're going to do is we're going to do a parenthesis here, and we can even come down and put a parenthesis down here. So what we're going to do now is write our query, which is our subquery, and this is our outer query. So now we're going to write an entirely other query within this. So we'll say select, and now we're going to say employee ID, and let's just bring this over. I usually have it something like this, and I'm going to try to bring this down a little bit. So select everything, and then we'll do FROM. Then instead of employee_demographics, we'll do employee_salary, and let's just format this a little better. So select the employee ID from employee_salary, and remember we wanted to do WHERE the department ID is equal to one. Now let's bring this back up, and this is what the query is going to look like. Now just by itself, let's run this subquery or this inner query. When we run this, it's going to create this list of just employee IDs where the department ID is equal to one. So when we say WHERE the employee ID from the employee_demographics table is in, it's going to try to match those employee IDs to this list of employee IDs. So just remember 1, 2, 3, 4, 5, 6, and 12. Let's go ahead and run this entire query. Now we have 1, 3, 4, 5, 6, 12. If you remember from previous lessons, the two is Ron Swanson, and he's only in the salary table. So since we're doing just the employee_demographics table, he's not in here. So what we're doing is we're selecting everything from the employee_demographics WHERE the employee ID in this table matches or is in the select employee ID from the salary table WHERE the department ID is equal to one. In essence, this is what a subquery is. It's a query within a query. Now what would happen if we have the employee ID, but we also wanted to say the department ID because we just wanted to view this? Let's go ahead and try to run this. We are going to get no output, and we're going to get an error that says operand should contain one column. The operand referring to this entire thing right here because this is an operator. So this is our operand, and we're returning two columns in here, which is saying we cannot do. We have to only have one. So now if we run this, it works perfectly well. And let's bring that down. Now we can also use the subquery in a select statement. So let's take a look at that next. Let's go down here and let's say we want to do select everything from employee_salary. Let me spell that right. Let's say we want to look at all the salaries just like how we have it now, but in a column next to it, we also want to compare it to the average salary for everyone. So we'll be able to see, you know, whether somebody's salary is above average or below average. So what we would try to do potentially is do something like uh first name, salary, and average salary, and we tried to run this, and of course we're going to get an error. It's going to basically tell us that we need to group by if we're doing this. So let's go back down and let's actually add that group by, and we'll say GROUP BY first name and salary, and we'll look at this output, and this is not looking good at all. It's just looking at the average salary for each unique row, which is Leslie, 75,000. So the average is 75,000. This is not what we're looking for. This is not what we want. Here's what we really do want. We want to just take the average salary of this entire column regardless of GROUP BY or anything else. So let's get rid of this, and let's see how we can do that. So let's come right down here. We're going to say SELECT, select the average salary, and then we're going to say FROM at our parenthesis because this is our subquery, FROM the employee_salary table just like that. Now if we run this, we should get the exact output we're looking for. So the average salary is 57,200, and we have our salary right here. So we can compare really quickly just like that. And we can also use a subquery in the FROM statement. So let's go down here and let's say SELECT everything FROM employee_demographics. Let's have it autocomplete for me. So we have the employee_demographics table. Now let's create a group by based off the gender column. It'd add some aggregated functions, and I'm going to show you how you can use this as a subquery. So let's go up here, say gender, and then we'll go ahead and add our group by. So we'll say GROUP BY gender as well. Now let's add a few things. We'll do average, we'll do average age, and then we can do, let's just do all of them based off the age. We'll just do AVG age, MIN of age, and COUNT of age. When I try to write fast, it doesn't always go...

Right, so we have this. Let's run this, and this is what our output is going to look like. Now, what if we wanted to get the average of the oldest age, or the average of the smallest ages, or, you know, see what the average count is for males and females? Well, we can't do that given this table, but let's do something right here. Select everything, and we're going to say "from," and in our "from" statement, we're going to have a parenthesis. We're going to paste our select statement and then close the parenthesis. So we're going to select everything from this output that is right down here.

So if we run just this, we're going to get an error, and I forgot this was going to happen, but every derived table must have its own alias, so you have to name a table. I forgot it does that. All we have to do to fix this is just name it. So we'll say "as," and we'll say "aggregated table." We'll just call it "aggregated table." So let's run this, and we get the exact same output. But here's the neat thing: we can now select; we can do "gender," and these are actually the column names now. So I can do the average of this column right here, but I can't do it just like this because it's going to give us an error, and I'll show you why in just a second. It says "unknown column 'age' in field list." So what it's saying is that we're trying to perform an aggregated function on the aggregation of an "age" column, but we don't have an "age" column in our table right here. Let me run this again. We have a column named this exact thing. So we actually need to do is do this backtick and backtick. This is the actual name of the column; it's not an aggregation anymore. The backtick on my laptop is right above the tab on the far left-hand side, um, right under the Escape; that's where mine is. Uh, so these backticks, it's not a quote like this, it's a backtick, so you just need to find that on your keyboard. But now if we run this, it looks like we encountered another error. It says "in aggregated query without GROUP BY." That's right; now we need a "group by," so now we need to group by "gender." Sometimes you got to figure this out on the fly, and it should work. There we go. So now we can perform aggregations on this table. Now, this doesn't actually work perfect because we're still grouping by the female/male, but let's get rid of this for a second, and we'll get rid of this "group by" entirely. And if we run this, we're now looking at the averages of this column right here, "max age."

Now, when you're doing something like this, it's actually really smart to rename these. We'll say "as average age," we'll say "as maxor age," and it makes it so much easier; you won't have to do these backticks anymore. Um, and "as Minore age," and so on and so forth. And I would probably format this better and stuff like that. We don't have to go through everything; right, I'm just kind of giving you an example. But then when we're using this table, these columns are actually named this, so I don't have to do these backticks anymore. I can just take this whole thing—oops, get rid of that backtick—now I can just take this column because this is the column name. So let's go ahead and run this, and it's still going to work perfectly. So this one's pretty cool because you're basically creating this kind of like a temp table; um, you're just creating your own little output, then you can query off of it, and you can do, you know, more advanced calculations this way. It's actually really useful, but there are better ways to do something like this, uh, like a CTE or a temp table that we'll look at in the advanced series, but this is at least how you can do it, and you can actually try it out using subqueries.

So hello everybody. In this lesson, we're going to be taking a look at window functions. Now, window functions are really powerful and are somewhat like a "group by," except they don't roll everything up into one row when grouping. Window functions allow us to look at a partition or a group, but they each keep their own unique rows in the output. We're also going to look at things like row numbers, Rank, and dense rank at the end of this lesson. So before, before we jump into writing a window function and seeing how the syntax works, let's actually write out a "group by," and then we'll compare the two when we actually do write the window function. Let's say we want to take this demographics table and we want to take this "gender" and compare it to the actual salaries. So what we actually need to do: we need to say "join," and we're going to join on the "employee salary." Let's go like this; get rid of all of this, and we'll do "salary," and we're going to say "on," and and let's do "demm" and "Sal" for the aliases. We'll see "dm.employeeid = sal.employeeid." Now we're going to come up here and we're going to say "gender," comma, and we want to look at the average salary, and we need to get rid of this right here, and we need to come down to the bottom and say "Group by gender." Now let's go ahead and run this query; see if it works, and it did. So we have our gender and we have our average salary from our salary table, and we can rename this as "average," and we'll do "average uncore salary," just like that. So this is how "group by" works; it rolls everything up into one row. Now let's try doing something pretty similar, except we're going to use a window function. Let's come right down here and let's paste this, and let's start writing out our window function. Now we don't have to use use the "group by"; we're going to go ahead and get rid of that, and right here for "gender," we can keep that the exact same. All we're really going to change is this part right here. We're going to say "average salary," and that is part of creating a window function. Typically with a straightforward window function, all we have to put is "over" with a closed parenthesis. This is going to say we're looking at the average salary over, and normally in here you'll specify something, and we'll get to that in a little bit, but we're just going to look at an average salary over everything. So let's go ahead and run this output. So this is going to look a little bit different, right? So the male and female all have their own individual rows, which is not the same as "group by," and this average salary is looking at the average salary of everybody. We're not breaking it out by the gender like we did up here. Here we rolled it up; now we're looking at the average salary for the entire column. Now what we can do is actually "Partition by." Now "Partition by" is going to separate it out, kind of like grouping it. So let's say "partition Partition by," and we'll say "gender." So just like when we did the "group by," the "group by" rolled everything up into one row; this is not going to roll everything up, but it is going to perform this calculation based off of the different genders, the unique values in this column. Let's go ahead and run this, and if you'll notice, the female is 53,750; the male 57,428. Name, so we'll do "dem.or name," we can do "last or dem.lastore name," so we can add other information, and it doesn't affect this column at all because we're using a window function. If we try to add these exact things—I'm going to go up here and do it—if we try to add these exact things to this, let's see if—um, yeah, that works, and then we also have to "group by" this. If we run this query now, it's going to be completely different because we're using a "group by." We're grouping by the first name, the last name, and the gender. We're breaking everything out based off of the unique values in these columns, whereas down here it's completely independent of what's going on in these other columns. All we're doing is we're doing a window function just based off of that column. So I think that's pretty amazing, and there's a a lot of additional functionality that we can do with these window functions, and we're going to take a look at a lot of those things in just a little bit. Let's try another example really quickly. Let's literally just copy this, paste it down here, and all we're going to do is we're going to change this to "sum." So now, instead of the average salary, we're looking at the sum of salaries, and we're still partitioning by the gender. Let's go ahead and run this, and let's pull this up. So all the men together make $42,000; all the females make $215,000. Now what we're about to do is something called a rolling total. If you've never heard of a rolling total, a rolling total is super cool and can be done within MySQL. A rolling total is going to start at a specific value and add on values from subsequent rows based off of your partition. So all we have to do is add an "order by," and we're going to order by, let's say, the employee ID. Let's go ahead and take a look at this, and it looks like the employee ID is ambiguous. I had a feeling. So I just need to say uh "dem. employee ID." Let's try this one. So now we have something called a rolling total. I'm I'm going to actually name it as "rolling unor total" because this is super cool that window functions can do this, and this is something that a lot of people in like Finance do. I did it myself when I worked in healthcare, and it partitions based off the female, and you can't see the employee ID, but there's an employee ID that we're kind of ordering on in the background. Now what it's doing is it's starting with Leslie nope, and she made 75,000, then the next person, April, she made 25,000, which equals 100, and just to actually see this better, I'm going to add "salary," and so Leslie no had 75,000, then we're adding this 25,000 to the 75, and we get 100, then we're adding the 60,000 to 160,000, then we're adding 55,000 to 25,000, so we're adding every single time; we're adding this salary to the already existing total all the way up to our grand total, which was 215,000. The exact same thing happens with the males, so we start with 50,000, then we add 50, then we add 90, then we add 70, all the way up to 402,000. Now you you can do this in a lot of different configurations on a lot of different columns, but in essence, this is exactly what a rolling total is; that's how it works, and we were able to partition based off of this column. We don't have to use "Partition by"; we could do this completely regardless of the partition, but I thought it was interesting to at least break it out by female versus male.

So now that we know how to use a window function, let's look at some special things that you can really only do with window functions or window-like functions. So we're going to bring this down, and what we're going to do is get rid of this entire thing, and we're going to look at something called "row number," then we're going to look at Rank, and then we'll look at dense rank. So let's look at "row_number," and this is just like an aggregate function, like we're doing the average age or average salary or something like that. This is what we're doing; we're doing a row number. Now we're going to do this "over," and we'll just do everything for right now. So let's go ahead and run this and just see what it looks like. Let's bring it up, and what we're doing is we're saying, okay, we have first name, last name, gender, salary; that's all great, but then we get to "row number," and we're doing a row number based off of everything; it doesn't matter what it is. So we're starting at one, which is the very first row, and we go all the way down to the bottom, just like an employee ID. So let's actually add that; let's do "dm. employee ID," just like this. So we have this 1, 2, 3, 4, 5, 6, 7, 8, 9, 101. Now, if you remember on this table, we are missing Ron Swanson, so it kind of skips that, but it's basically like an employee ID; we're kind of giving it its own unique value, and these row numbers are not going to repeat itself if you do it like this. Now they can repeat themselves if we do a partition, and let's do a partition on the gender again because we know how to do that one. We'll do "Partition by"—I spell that right—"Partition by" the gender. Now we're going to add a row number based off the gender, but again, it's broken out or partitioned by gender. Let's look at this. Now it goes for the females 1, 2, 3, 4, then for the males it restarts 1, 2, 3, 4, 5, 6, 7. Now this is just in a random order based off how the, you know, data was stored in the table itself. Now what if we wanted to kind of rank these based off of the highest salary first down to the lowest salary? You nailed it; we just add an "order by." We'll "order by salary," and if you want to do it from highest to lowest, giving the highest salary the number one and the lowest salary, you know, later down, we'll do "descending," and let's run this, and you'll see that for female, we're still partitioning by gender. For female, the highest salary is one, next is two, three, and then four; then for males, the highest salary is one all the way down to seven. So that's what "row number" does; it just gives a row number based off of whatever your partition by or ordering by in your window function. Now let's go over here and add a comma, and we're going to add, and let's go down just a hair; let's add "rank." So we want to do "Rank," and we'll do our parenthesis. Now "rank" is going to give it more of an official Rank, and let's see how this works. So we'll do "Rank," and we'll do "over Partition by salary descended," the exact same thing, and while we're here, I'm going to rename name these; I'm going to say "as as row num," and we'll call this one uh "rankor num." So let's go ahead and run this, and it looks very, very, very similar except for one small thing: this right here. So when we're using the "row number," whatever we are partitioning by, it's not going to have duplicate rows within that partition; it just won't. So even if there's 50,000 right here, it's just gonna automatically assign it based off of something that is running in the background, whether it's the order of how the data is stored in the table or some other "order by" that you are using on the table. Now "rank" is a little bit different because "rank" is going to take it just like it did the "ronom," except when it encounters a duplicate based off of the "order bu," which is the salary, it's going to assign it the same number. So this is five and five. What's unique about "rank" is that the next number is not going to be the next number numerically; it's going to be the next number positionally. So this is 1, 2, 3, 4, 5; this is kind of like a six, and then it goes to seven, so it skips number six. Now there's another one. Let's copy this "rank." There's another type of rank called "dense Rank," and we'll do "denore Rank." So we'll do "denore Rank," and let's run this, and let's pull this up. There we go. Now, now "dense rank" is ever so slightly different than "rank" in the fact that when it gets down to duplicates, it's still going to duplicate them, so it's going to have a five and a five, but it's going to give the next number numerically, not positionally. That is the only real difference between "Rank" and "dense Rank," and again, "row number" is just not going to have duplicates; it's going to give it its own unique within that partition. So I know I just threw a lot at you, but that's "row number," "rank," and "dense rank" in a nutshell, and you can review this, mess around with it; all of these things because, you know, these are actually really, really useful.

Hello everybody, and welcome to the first lesson in the advanced MySQL tutorial series. Today we are going to be looking at CTE. Now, CTE stands for Common Table Expression. They're going to allow you to define a subquery block that you can then reference within the main query. Now that may not make perfect sense, but we've looked at subqueries in the past or in previous lessons in the intermediate series, so you kind of understand that it's kind of like a query within a query, except we're going to name this subquery block, and it'll be a little bit more standardized, a little bit better formatted than actually using a subquery. Let's take a look at the basics of writing a CTE. Let's pull this down really quickly, and all we're going to do is we want to create this as a CTE, so we'll say "with," and that is our keyword to define our CTE. So we're going to say "with," and then we're going to name our CTE, and we'll just call it "CTE," and we'll do "_example," and then we're going to say "say as." So this is how we define it, and now we need to actually put it in parentheses. Now you can do this in several different ways; I'm going to do it kind of like this just to really emphasize that this is within the CTE. Now CTEs are unique because you can only use the CTE immediately after you create it. So if we come right down here and we come right below it, we'll say "select everything," and we're going to say "from CTE example." So we'll say "from CTE example," and let's bring this back up. Now if we run this, we're going to get the exact same output. Now this should seem pretty familiar, almost like we're using a subquery, and within our subquery we have this right here; we're kind of building our own little table, and then we can query off of it down below. So we can come down here and let's actually change the names in here. We're going to say "average uncore cell," and we'll change all of these real quick just because—don't—I don't like having to actually put the, you know, uh, the tick marks; I don't like doing that. So here we're going to say "Max," then we'll say "Min," and then we'll say "count," and let's go ahead and run this again, and so now we have these different names, and when we come right here, we can say "select," and then we'll just do something really simple; we'll just do the average of "aage average uncore cell," so the average salary, and let's run this, and so this is the average between both the males and the females. Kind of the purpose of these CTEs is to be able to perform more advanced calculations, something that you can't easily do or can't do at all within just one query. Another reason to use a CTE is just the readability. You can absolutely write this using a subquery, and let's do that really quickly, and it's just going to be a little bit tougher to read and look at. So let's come right up here; we're going to say "from," and we'll do right here; we'll say "select everything," we'll do "select average s from" here, and we're going to need to name this, so we'll say "do example unor subquery." We'll get rid of this, and then we just need to get rid of this, and we can run this query, and we get the exact same output. Now, if I formatted this exactly the same, just like this, and the names down there, if we look at this, the syntax is just a little bit more difficult to read. We're selecting the average of "average s from," and then we have our subquery right here, and then we're naming it at the bottom. If we scroll up and compare this, this one just looks a lot better. Now, when you're writing in MySQL, sometimes it doesn't matter if it looks pretty or not as long as it gets the job done; that is true, especially if you're just going to be using it yourself. But in a more professional environment, when you're using this in your actual job, there are going to be people who have been using this for 10, 20 years, and they're going to expect you to write it well. They don't want it to be really messy; they aren't most likely going to want it to be written like this. I've been using it for quite a long time, and I much prefer CTE over subqueries, just visually, and it makes it a

Lot quicker to actually read through, so that is just one of the reasons. Although you get the exact same output, now there is some additional functionality within CTE as well. Now, one thing that I mentioned just a second ago is that when you build a CTE, you can only use it immediately after; you can't use it right below it. So let's go ahead and let's copy this query, and we're going to bring it right here. If we try to run this, and let's do this, we're going to get an error. And let's pull this up; it says table Parks_andre. CTE example doesn't exist. So we're looking for a table called CT example in our database, but it's not there.

Now, the reason this happens is because you're creating a CTE; you're not creating a permanent object like a temp table, which we'll look at in the next lesson, and you're not creating a real table, and you're not creating a view; you're really not creating anything. It's just a Common Table expression to create this table right here. This basically almost like a temporary table, almost, but then you're just using it to query off of it; you're not saving it, you're not storing it in memory, you're not really doing anything with it. It's it's just like writing a regular query. So this is why you can only write it immediately after creating the CTE; you can't write it down below and reuse it because it's just like calling a query that you wrote before; it just isn't going to work.

Now, the next thing that I want to take a look at, and let's copy this down here, next thing I want to take a look at is that you can actually create multiple CTE within just one. So if you wanted to do a more complex query or joining more complex queries together, we can do that all within one CTE. So let's come right here, let's get rid of all of this, and we're going to say, uh, from the demographics table, we're going to say where birth_date and let's just do is larger than 1985. D1, D1. So we have one query, and we'll take just a few columns from this table, so we'll take let's say the employer or employee ID, we'll take the gender, and the birth_date. So this is one query, and we're filtering just based off of this birth date. Now, when we create this, this is the CTE example, but we can have a comma here; we come down below, and then we can say CTE example 2, and I need to combine that, so two, and then we can say as, and then we have another query. So then right here, here we could say select everything; we'll change that in a second, from employee, say salary, and in the salary we'll just do a simple one; we'll do where salary is greater than 50,000, and we'll actually just take the employee ID and the salary.

Now, if I come right down here, I can say select everything from CTE example, which is, and let me scroll up so we can see everything that's our original, our CTE example is this first query right here, then we're creating our second one right here, and we can join basically on these two common table expressions. So now we can say join, then we'll do CTE example 2, and need change that X, and then we'll say on, then we're just going to do CTE example.employeeid is equal to CTE example 2.employeeid, and not an equal sign, but a dot, there we go. Now, if we run this, it should work, and we can pull this down and look at our output. Now, this is just an example; this isn't a real use case because, of course, we just join these two tables together normally, but you can imagine you have a much more complex query, or you're doing a lot of functionality within this table, and you just want a certain subsection of this table, and you're wanting to combine those; this is how you can do that with a CTE. So now we have all of our information right here, and that can be extremely, extremely helpful.

Now, one last thing that I want to show you; we're going to go all the way back up really quickly right here. Let's run this one one more time, and let's actually take everything, and let's run this. So here we have our avg salary, max salary, min salary, and count salary. The last thing that I want to show you, and this is more of something that's just somewhat helpful; you don't have to actually do it in your main query, is before we went in here and we changed all the column names by doing an alias by saying as and then saying the average salary, and the as is just implied here, but we're changing this via an alias; we don't have to do this. In fact, we could come right here, and we could do a parenthesis; we could call it gender; we could call it avg_salary, max_salary, min_salary, and let's do count_salary. So now, if we were to run this, let's change this up; we'll do capital on this one. If we wanted to run it like this, when we run this, it'll change all of those names to what we have it right here. So this will be the default; this will overwrite the column names that you have in your actual CTE expression or the query that you have within your CTE. So that is all we're going to take a look at in this lesson on CTE. These are very, very helpful; definitely help with more complex queries, and they're just really easy to read and understand, which is why I personally use them a lot.

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 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 for storing 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 at 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; it's the only way I can spell uh. So we're going to do temporary 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. So let's do first_name, and our data type can be varchar, let's say 50, and we'll do a comma, then we'll do last_name; we're going to keep this really simple; we'll do varchar 50 again, and then for our last one, we'll do favorite_movie, and for this one it needs to be longer, so we'll do varchar, 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's going to happen; let's click refresh; nothing's going to happen, at least you can't see it happening. Let's pull this up, and you can see that create temporary table says Z 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 uh, I think it's like that, Lord of the Rings: The Two Towers; uh, it'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_salary table; let's run this. Now, let's say I just wanted a subsection of this data to sit in this temp table where the salary is greater than let's say 50,000; I could easily, easily do this. I'm going to say create temporary table, and let's do this one as salary_over_50k. Now, one thing about naming either temp tables or CTEs 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 temptable. The reason for that is because when you're in a work environment, you have lots of temp tables; you're creating really advanced stored 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, so they become more important uh 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. So I'm going to say select everything from, I'm going to say employee_salary, then we're just going to say where the salary is greater than 50,000. Now I want uh Tom and or Jerry; I want them to be included as well, so I'll actually say greater than or 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 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 jeez, I'm embarrassed. All right, let's go to MySQL; 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 CTE. CTE and temp tables both have their own use cases within MySQL. For temp tables, this is usually for the more advanced things, so I'm usually using these in stored procedures when I'm really manipulating data and I'm doing a lot more complex queries overall, and often times I'll use multiple temp tables, and I'm joining them together, and I'm just doing a lot of more advanced stuff with CTE. It's typically more simple things because you can't make as advanced CTE or as complex CTE. So with those, I'm usually keeping it to just one level of transformation; I have my base CTE or my base subquery 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 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 stored procedure, then it makes so much sense to use a temporary table.

Hello everybody. In this lesson, we're going to be taking a look at stored procedures. Stored procedures are a way to save your SQL code that you can reuse over and over again. When you save it, you can call that stored procedure, and it's going to execute all the code that you wrote within your stored procedure. It's really helpful for storing complex queries, simplifying repetitive code, and just enhancing performance overall. So let's take a look at how we can create a stored procedure. Now, we're going to start by just creating a really simple query; we'll make it a little bit more advanced as we go along and take a look at the different things within stored procedures that you can do. Now, let's change this query; let's say where the salary is greater than let's do 50,000; let's actually do greater than or equal to 50,000; we want to include Tom uh and Jerry as well. So let's go ahead and run this. Now, what we want to do is save this really complex code within a stored procedure. Let's come right down here, and we can create a super, super, super simple stored procedure by just saying create procedure and pasting that. Now, we just have to name it, so we have create procedure, and we'll call this large_salaries, and then we do a closed parenthesis. Now, this is as simple as it can possibly be; it does not get any simpler than this. So let's go ahead and run this, and if we go down, we pull this up, you can see that it says create procedure zero row affected; it looks like it worked, and if we come over here to this refresh button, you should see now that under stored procedures it drops down, and we have our large_salaries; that's exactly what should have happened; we wanted to save that into our Parks and Recreation. Now, if you wanted to be careful, you could say use parks_and_recreation; this is not a bad idea, but you don't have to, but you can specify what database within your actual editor window; sometimes that is helpful, but now we've created it. Now, let's see how we can call it; all we have to do is say call; we're going to copy this entire thing, including the parenthesis, and let's end it with that's right, a semicolon. Let's go ahead and run this, and as you can see, it worked because we got the exact output. So we actually called this stored procedure, and this code ran, so it's just a select statement, so it worked perfectly. Now, you can also come over here to large_salaries, and there's this little tiny, little button here that looks like a lightning bolt, and if you click it, it's going to open up a different window, and we'll say call parks_and_recreation.large_salaries. So you can do it that way as well, but uh we're not going to be doing it that way.

Now, what we've written right here is not best practice by any means, and I'm going to copy this down here because there's a lot of different things that you need to take into account when you're creating a stored procedure. For example, this right here is most likely not what you're going to be putting into a stored procedure; this is super, super simple; typically you'll be having multiple queries. And let's see what happens if I try to put another query in here, and let's get rid of this. So we're going to select everything where the salary is greater than 50,000, then we'll select everything where it's greater than 10,000, which is everybody. Let's call this large_salaries_2. So we have two different statements in here, and we want them all to be under this large_salaries_2. Let's select everything, and let's run this, and we're getting an output, which is already not a good sign, but we created the stored procedure, and then we selected everything. So what's actually happening here? Pull this back down; what's happening is is this is creating the stored procedure, and this is just some other, you know, random query, but that's not what we want; what we want is everything or both of these queries within one stored procedure. The best practice is to use something called a delimiter. Now, this right here is a delimiter, the semicolon. So this semicolon separates our queries from one another; it tells MySQL, hey, you know, this is a different query; don't be mixing these and cause errors; you know, that's essentially what a delimiter does. Now, we can change the delimiter by coming up here and saying delimiter, and we can can change it to almost anything we want. Now, in my actual job, I've seen it done many different ways; I've seen these forward slashes, also seen dollar signs; this is probably the one that I've seen the most when I worked with data engineers, data scientists, database developers; you this one I see a lot, and then you'll come into the code, and you'll say begin, and let's go over here, and let's tab all of this, and then we'll say end. Now, when we end, we're going to end it with this dollar sign. So here's what's happening: we're changing the delimiter right here to dollar sign; we're creating our stored procedure, and within it we are keeping all of this, so all of this code is going to go into this one stored procedure; then at the end we are saying this is the end right here of this stored procedure; these semicolons no longer are the delimiter; that's telling us when it is the end of the stored procedure; that's what the delimiter does. Now, it is best practice at the end to change it back, right uh, let me spell it right, because if you don't, then you're going to have to start using uh these dollar signs for everything, and how do you spell delimiter? Oh man, there we go. Now we've changed it back to a semicolon afterwards, so then we can go and write other queries, and it'll act appropriately uh let's go down. So this is getting closer to best practice; let's go ahead and run this entire thing, and if we pull this up, we're not getting an output; that's a good sign. If we pull this up, it's saying we already created number two; change that to three; my apologies. Let's go down here. Now we've created the stored procedure three. Now let's go over here; we're going to right click on this; we're going to say alter stored procedure. And now you can see that we have both of these queries within the stored procedure. Let's get rid of this, and we're going to go and call this. So let's copy this large_salaries_3; bring this all the way down, and let's say call that stored procedure. If we run it, you'll notice we get two outputs; we have six and seven; this result six is where it's greater than 50,000 or 50,000 uh or greater; this one is where it's greater than 10,000, which is essentially the entire table. Now, so far we've done everything just by writing it all out, and that's fantastic, fantastic, but you can also come over here to stored procedures; you can right click and say create stored procedure. Now, let's actually copy this; we're just going to create the exact same thing; we'll create stored procedure, and we can just paste this in here, and let's go ahead and do that; there we go, and sure, we'll call it new_procedure, why not? And if we say apply, you'll notice that it generates this script right here, and we can apply it, and we can create it; we will in just a second, but let's take a look at it. So we're going to use parks_and_recreation; that's what I was mentioning before; we're then going to say drop procedure if exists. Now, this is something that I was going to show you later, but I'll just show it.

To you now, sometimes it is really beneficial to write something like this before you create it. In case you've already created a story procedure with that name that you're wanting to replace, so it's checking if it's there. And if that new procedure is already there, it's just going to drop it. Then it comes down and let me see if I can zoom in on this, and then it's going to create our delimiter, which it uses dollar signs. So my SQL is even, you know, validating what I was saying earlier; we're going to use Parks and Recreation again. And now again, we have to use, instead of a semicolon, we're using dollar signs. Then we're creating the procedure, which is new procedure; we're saying begin end, and then it's even changing the delimiter back. So basically, everything that I said, this is kind of doing it for you automatically.

Now, when I click apply, it went ahead and executed that SQL statement, and our new one is ready. So we can go ahead and alter that store procedure, and it looks exactly the same as this one out here, which was uh, large salaries number three. So it looks exactly the same. Now let's go ahead and get rid of this, get rid of this, and let's go down below. The next thing I want to take a look at is something called a parameter.

Now, before I actually get into this, I'm going to copy all this down here because I don't want to rewrite all of it; I'm being honest. So let's paste this in here. Now parameters are variables that are passed as an input into a store procedure. And they allow the store procedure to accept an input value and place it into your code. Let's take a look at what that actually means. Now, before I do anything, I'm just going to change this to uh, number four, so I don't forget. So let's get rid of all of this; we're going to keep it somewhat simple because we're looking at something new.

Now, when I say we're passing through a parameter, I'm talking about when we're calling it. So let's say we've already created this one; I'm not going to, you know, run this yet, but let's say we've created it. Let's say I want to pass in an employee ID; I want to pass in a specific person, and I want to retrieve their salary. I know their employee IDs; I just want it to pull up their salary for us. So what we're going to do is we'll get rid of this, and when we're calling it, put this down; when we're calling it, I'm going to pass through value like one, that's Leslie nope, and then I want the salary to be the output. So, so I'm going to select the salary. So we're selecting salary from the employees salary, but how do we know that this one is the person we're looking for? Well, when we're actually creating this parameter, we create it right in here; that's what tells the store procedure to accept an input value. When we're calling it down below, we're going to call this employeeid.

Now, after we call it, after we name our parameter, we need to then give it a data type. So I'll call this an integer. So we're telling the store procedure, when somebody calls the store procedure, they have to pass through an integer; it can't be a string, or it can't be a date; it has to be an integer. Now what we're going to go do is right down here, we'll say where the employee uncore ID, that's from this column in the actual table, we'll say is equal to the employee uncore ID, which is our parameter right here. Now you may be thinking that's really confusing; they name the exact same thing; can I change it? The answer is yes; I actually encourage it. So there are some naming conventions that are out there that I think are helpful ones that I personally use, um, but remember this is just kind of a variable parameter name; you can kind of call it whatever you want. So if I wanted to say Huggy muffin, I could, uh, and this could be Huggy muffin. So let's try it with Huggy muffin; I just came up with that off the top of my head, so don't judge me, um, but we're going to create the story procedure, and then when we call it later, we want it to return the salary where the employee ID right here is equal to whatever was passed through that parameter, that input parameter, and we, we're going to keep it as one, so it should return 75,000. Let's go ahead; we're going to create this, and now let's go right down here, and we're going to run it, and we can see that that is the salary, and it worked perfectly.

Now, like I was saying, that is not what I would actually name it; uh, there are some naming conventions likeor Pam at the end, so you kind kind don't want to keep it; at least I recommend you try to keep it similar to what you're actually looking for, and you can either end it inore pram, or there's another way you can do it, which is come right over here and do pcore. And these are just ways that you can tell the code, or you can just be able to visually see the difference in the code. So this is just what I recommend; then you put it right down here; you say where the employee ID is equal to pcore employee ID, saying this is the parameter that's being passed through and put into our actual query.

Hello everybody; in this lesson, we're going to be taking a look at triggers and events. A trigger is a block of code that executes automatically when an event takes place on a specific table. For example, let's take a look at these two tables. Now, when a new employee is hired, they're put into this table with their salary information and everything, but sometimes people forget or don't add their information, like uh, you know, who right here; uh, they're not put into this demographics table, and we wanted change that because we want to have everybody in here. So when somebody is put into this salary table, we want it to automatically update with the employee ID, first name, and last name into this table right here for the employee ID, first name, and last name. So we're going to write a trigger; when data is updated into the salary, it's going to also update the employee demographics for us.

Now let's go right down here, and we're going to take a look at how we can do that. Now, if you watched the last lesson on store procedures, we'll do a lot, lot of the same writing style or same formatting for triggers and events. So we're going to start with is the delimiter; we're just going to do that right off the bat before we get into anything, and we're going to change that to the double dollar sign. Now the delimiter, again, in case we have multiple lines of code, which we're going to have; if we have multiple lines of code when we're creating this trigger, this delimer is going to help us have multiple queries within our create trigger statement. So this is really important; we'll just start out by doing that. Now let's create our trigger, and we do need to name this, so we'll say employee insert, and we'll just call it like that; did I spell that right? Yeah, employee insert. So we have our create trigger; we've named it. Now we need to specify what event needs to take place in order for this to be triggered. So we're going to say after an insert, and I need to spell insert right; after an insert on, and we'll do the employee_salary table. So after we insert onto the employees salary table, down below we're going to write what's actually going to happen. Now we're writing after because we're doing it where when new information is put on the salary table, it's automatically updated into the demographics table, but you could also do before, which means if data is deleted from the employee salary table, something could happen, but we're not doing any deleting or any updating; we're doing insertion. So we're going to say after an insert on. Now the next part that we need to write is for each row. Now this for each row means that the trigger is going to get activated for each row that is inserted. So if we had an insert statement that inserted four different people who were just hired, that means this trigger is going to be activated four times. Now some SQL databases like Microsoft SQL Server have things like batch triggers or table level triggers that'll only trigger once for all four of them, and in my opinion, those are really, really nice; uh, I've used those; I like them. The way that my SQL has it right here is not the most optimal way to do it, unfortunately, but we don't have access to the batch level or the table level triggers at this time. So this is really just the setup for what we're about to write. So after it's inserted on the employee salary table for each row, what is going to happen? We're going to go down here, and we're going to say begin and we'll have end. Now the code that we're going to write here is what's going to happen after this event takes place. So what we're going to do is we want to take from this table; let's bring this back up real quick; when we insert a new person, we want to take the employee ID, the first name, and the last name and automatically put it into the demographics table. So we want to say insert, and let me do tab; insert into; we're going to insert into the employee unor demograph table, and we're not taking everything, so let's actually specify what columns we're doing; we're doing employee ID, first_name, and then the last_name. Now we need to specify what the values are. Now from the employee salary table, we're taking employee ID, first name, and last name, but we don't want to take all of them, right; we don't want to take every single employee ID, every single first name, every single last name; we only want to take the new Val, vales that were just inserted. Well, lucky for us, there is something that we have for this. So let's do values new parentheses; we have something called new. Now new is going to say we're only taking the new rows that were inserted; there's also an old like this where it takes rows that were deleted or updated, but of course, for us, we're going to be using new. So we'll say new.employeeid, new.first_name, and then new.last_name, and we'll close that, then we'll come down here, and we'll do our delimiter, and we'll change this as well; we'll say delimiter back to a semicolon. Now we're getting this error because we need this right here. So let's recap what we've created, then we'll actually create it and try it out. So we're creating our trigger called employee insert; after a row is inserted into the employee salary table, for each row, here's what's going to happen; we are going to insert into the employee demographics table the employee ID, the first name, and the last name; those are the columns that we're going to insert into; then we're taking the values new.employee ID, new.first name, and new.last name. Now MySQL understands that when we say new, we're talking about the event that takes place; this is the data that's being inserted; it just knows that. So let's go ahead and create it; we're going to run this, and it should work. Let's pull this up; says create trigger, so that worked. Now the thing about triggers, uh, that's unfortunate; it doesn't have its own little section under here, right, but it does have under the employee salary; let's go right here, and then under the triggers, so we can find it, which is great. So we have this employee insert; if we right-click, we can't really do anything with it; that's the unfortunate thing; we can't alter it; we can't change it; we can't drop it; we can't do anything, um, that's the unfortunate part, but let's actually test it. So now we're going to say insert into, and we're going to insert into this employee salary; that's how we're going to trigger it. So insert into the employee salary, and then we'll do employee ID; these are all the columns; the first_name, last_name, occupation, uh, bear with me for a second; then we have salary, and then Department ID. So this is what we're inserting into. Now we have to do our values. Now this should be, should be shorter, hopefully; we'll do 13; we'll call him uh, Jean ralphio; there we go; last name is sapperstein, just like that, and not actually just like that; that's not spelled right. So we have saprin; his occupation is entertainment; 720 CEO; how much is he making? Uh, let's say a million; is that a million? A million; he's making a million dollars, and he's really not part of any uh, department, so we're just going to have null. So what we're about to do is we're only inserting on the employee salary table, but we're putting all the values that we need into the appropriate places. Let's add a semicolon; let's go ahead and run this; make sure it worked; it says insert into, and then one row affected. Now let's come back up; let's look at our salary table first and get rid of this; if we pull this up, you can see John ralphio sapin; he was added. Let's go over to the demographics; is the moment of truth; let's see if it worked, and as you can see, it worked perfectly; we have Jean ralphio sapine. Now they do need to come back and fill in this information, but it's already in here, kind of queuing them up, saying hey, we need this person's age, gender, birth date, all that other information. So that is how we can create a trigger based off of a specific table, and then when it happens, it just automatically does it for us; we don't have to really think about it; we just know that we've created a trigger, and we can actually go and insert data on that table; that trigger is going to work; it's going to do what it's supposed to do, and that's really, really helpful in the real world when you're working with a ton of tables; a ton of things need to be automatically done, and you don't want to have to manually do this. So having these triggers can save you a ton of time.

Now let's scroll down, and we're going to take a look at events. Now event is kind of similar to a trigger; a trigger happens when an event takes place, whereas an event takes place when it's scheduled. So this is more of a scheduled automator rather than a trigger that happens when an event takes place; these can be fantastic for a lot of things, like when you're importing data; you can pull data from a specific file path on a schedule; you can build reports that are exported to a file on a schedule; you can do it daily, weekly, monthly, yearly, really whatever you'd like; it's just super helpful for Automation in General. Now let's say the pwy council comes up with some new legislation; they need to save some money, especially in the Parks and wreck department; we're just spending too much, or they're spending too much, and what they want to do is retire people who are over the age of 60 immediately and give them lifetime pay. So what we want to do is create an event that checks it, let's say every month or every day, and then if they're over a specific age, we are then going to delete them from the table, and they will be retired; this is a fake example, so you know, go with it. So what we're going to do is come right down here; we'll select everything from employee demographics, and let's run this, and let's pull this up. So let's say if they are over the age of 60, which unfortunately is Jerry gach; like I don't make the rules, but if they're over the age of 60, they're going to be automatically retired. So let's come right over here; we're going to say create event, and we'll call this the delete, delete_retirees. Now before, when we were creating the trigger, we were saying based off of a specific event, but here we're going to schedule it; we're going to say on schedule, and then we're going to say every, and we could do one month; maybe we'll look every single month, but here we'll do, let's do every 30 seconds; every 30 second. Now we're going to go down; we'll say do, and this is going to say, here's what needs to happen every 30 seconds. So we'll say begin and end. Now what's going to happen every 30 seconds is we're just going to start with a select statement, and I'll just copy this; actually, we'll start with a select statement, then we'll update it to a delete statement, but we'll come right here; we'll say where the age is greater than or equal to 60. So if we just run this query right here, that's only one person, as Jerry gach. Now if we want to write this correctly, we'll do the delimiter; we'll have the dollar signs; we'll have the dollar sign right down here as well, and we'll say the limiter back to a semicolon. Now every 30 seconds, we don't want to select people who are that age; we want to delete. So let's go right here; we're going to change this CU. Now we know it should be deleting the right person, and we're going to go ahead and create this event. Let's go ahead and run this, and let's make sure it was created properly; it looks like create event; zero is affected; this should be working. Let's go back up to the demographics table and let's run this, and let's pull this down and pull this up, and as you can see, unfortunately, Jerry gach is no more, um, you know, he's just too old, and the pony Council, they recognize that, and so it wasn't my rule; that was unfortunately Pony council's Rule. Now really quick, if that did not work, let's say you couldn't create your event at all; let's go down here; we're going to show variables, and we'll run it just like this, um, I'm going to show you how you may need to fix this. So we can say where variables is like, and then we'll say uh, event; do it just like this. So I have a vent scheduler where the value is on; if yours is off, which sometimes that can happen, your, you're just going to update this to on. Now another issue could have happened, and I just want to explore this for just one second; you may not have permissions to delete things; if you do not come right up here, let's try to figure this out together; it's actually edit references, and I want to say it's right here into the SQL editor at the very bottom; yeah, so save updates, rejects updates and deletes with no restrictions; this needs to be unchecked. So go to preferences, go to the SQL editor, down at the bottom, unclick this; if that didn't work. Now if everything worked perfectly, you don't need to change a thing, but if it didn't, I just wanted to work through, you know, some uh, troubleshooting that you may just have to Google or chat gbt or something to try to figure out. So that is how we can create an event in my squel to run on a schedule. Now typically you wouldn't do it on every 30 seconds; you would do something like every one month or every one year or, you know, a longer time frame, but you get the picture of what we're trying to do.

Hello everybody, and welcome to the very first project in the MySQL Series; today we're going to be focusing on data cleaning. Now, if you don't know what data cleaning is, it's basically where you get it in a more usable format. So you fix a lot of the issues in the Raw data that when you start creating visualizations or start using it in your products, that the data is actually useful, and there aren't a lot of issues with it. So that's really what data cleaning is. Now what we're about to do is create a database; we're going to import a data set; this is a real data set, and what we're going to do is we're going to clean the data. So I'm going to show you and walk you through all the steps in order to clean the data; the data set that we're going to be working with will be in the GitHub, so you can just go and download that; I'll have a link somewhere in the description, but let's get started. First thing we're going to do is create a new database. So we'll go right over here to create a new schema, and we're just going to call this one; we'll do this is world_layoffs. So if you can't tell already, uh, we're going to do World layoffs; that's the data set they're going to be doing; we'll just click apply, and that creates our world layoffs right here. Now we're going to go into here; there are no tables; we're going to right-click on tables and go to table data import wizard. Now we haven't done this yet, uh, in this

series we have an import any data, but that's what we're doing here. We're going to show you how to import data, so we'll go ahead and click browse. And as you can see right here, we have this layoffs data set. Let's open this up, and we're going to click next. And we're going to create a new table; there's no existing table in this database. You can drop it if it exists, uh, if you'd like to; it doesn't matter; this is new. We're going to go ahead and select next.

Now, right here is where you configure import settings. Now, MySQL is going to automatically assign a data type based off of the data in these columns, so we'll take a look at the data later. Now, there is one thing that you can take a look at real quick: we have this date column. Now, in here, it assigned it as a text; that's because of the format. We are going to import this as the raw data; we're not going to try to change anything in the import settings. We're just going to assume this is how the data was in the table, so I'm not going to change anything, although this may be something that you would want to change to something like a date time and go and fix that, but we're going to import this as the raw data. Let's go ahead and select next. We're going to import it; we just select next. Now, this could take a little bit, uh, so while this is importing, I'm just going to skip ahead. This should take just a few minutes to import. All right, this just finished. Let's select next, and we imported 2361 records. Let's go ahead and select finish. We can get rid of this, and let's refresh this. Perfect. We have our layoffs table, so we'll select everything, and I'm going to go and double click on the world layoffs because I don't want to write out the whole thing every time, so we're going to say from layoffs, and let's see what we get.

So let's take a look at the data that we're going to be working with in this data cleaning project. So this data set is layoffs from around the world, starting I think 2021, and we'll take a look at that in this date column later, but it has the company, so it has the company that did the layoffs; it has the location of where they are; what industry they are part of; how many they laid off; the percentage that they laid off, so the percentage of their company; the date; the stage, which refers to the stage that the company is in, whether it's a series B, post IPO, uh, they don't know; then there's the country; and then we have funds raised million. So we have a lot of information here, and in the next project we're going to be doing explor at data analysis, so we're cleaning all of this data, and then in the next lesson we're going to actually dive into it and try to find trends and patterns and all these other things.

So what we are going to do is we're going to go through multiple steps. Step number one is we are going to try to remove duplicates if there are any; that is the first thing I typically do, especially if I know this data shouldn't have any duplicates or it'd be, you know, repetitive or unnecessary to have duplicates. The second thing is going to be to standardize the data; that just means that if there are issues with the data, with spellings or things like that, we just want to standardize it to where it's all the same as it should be. Number three is we'll look at the null values or blank values, and there's a lot of null values in here; there's even a blank value right here, and we're going to see if we can populate that if we can, and there are times where you should; there are times where you shouldn't. I'll kind of walk through that as well. And lastly, we want to remove any columns and rows that aren't necessary, and there's a few different ways to do that. Uh, this one is a little bit, you know, um, let me write this actually real quick: remove any columns. So I'm just going to say there are instances where you can do this; there are instances where you shouldn't do this when you're working with massive data sets and you have a column that's, you know, completely irrelevant, completely blank, you don't have any ETL process that is required for it, um, you can get rid of it, and it can save you time when you're querying your data.

Now, with that being said, uh, and we'll talk about this later, in the real workplace, often times you have processes that automatically import data from different data sources. If you remove a column from the raw data set, that's a big, big problem. So what we're going to do is something I would actually do in my real work, which is I would create some type of staging or raw data set. Let's say this one's our raw one, and we could have even called this layoffs_raw. We're going to create another one; we're going to create a table, so we'll say create table, and let's call this one layoffs_unor_staging, and we literally just want to copy all of the data from the raw table into the staging table, so we can do that really quickly by just saying like layoffs, and if we run this and we refresh, you'll see we have the staging database, and let's copy this here, we go, and we'll do layoffs_staging, and so now we have all of the columns, and all we have to do is insert the data, so we're just going to say insert, then we're going to say layoffs_staging, go right here, and we'll select everything from layoffs, and let's run this, and if we select the table, we now have all the data over, so super, super easy, and now we have these two different tables.

Now, again, why do we do this? Is because we're about to change the staging database a lot. If we make some type of mistake, we want to have the raw data available. This does happen; this is something that you do in the real workplace because you're not going to work on the raw data; it's just you shouldn't do it; it's not best practice. So I'm going to show you what I would actually do in my, you know, like a real job, so that's what we're going to do now. We're only going to be working off the staging database, and we can copy this and make different databases for different things as long as we have our raw data; we can really do anything we want going forward, and that's what we're going to do. So the number one thing we're going to look at is to make sure that we are removing duplicates; we want to make sure we don't have any duplicate data in here, and if so, we're going to get rid of it.

Now, really quickly, if you did my Microsoft SQL Server project, we did something very similar, but we had an extra column over here that gave the unique row ID, which made it really easy to remove the duplicates. Here, there is no identifying factor that's going to be easy for that, so I'm just going to tell you up front: removing these duplicates is not going to be easy, but we'll walk through it every step of the way. So what we can do is try and do something like a row number, and we'll match it against all of these columns, and then we'll see if there are any duplicates. Now, I'm just—we're starting off strong; okay, we're jumping into kind of some of the more advanced things; it does get actually easier as we go, but this is the actual order that I follow, so, uh, I'm going to keep it so. Let's try to identify duplicates. So let's copy this; let's pull this down; do underscore staging; there we go. Now what we can do is we can do row_number, and we'll do that Partition by; basically, we could do every single one of these columns; that's kind of what we're doing. So what we can do is we can say everything, then we can do a comma, and we'll say row_number, and would be just like this, and we're going to do this over, and we want to Partition by all of these columns; essentially, we could just do a few for now to see if we get any hits, and then we can look at that, but they're going to be multiple companies that have layoffs in the same location and industry, although their total laid off would probably be different; the date would probably be different. So if we do something like uh company, let's do industry, we will do total_laid_off, comma percentage_laid_off, and then let's do date. Now I'm doing date with the back ticks because date is a keyword in MySQL, so if we do it like this, it just really makes it easy. So we're going to Partition by all of these things, so let's do Partition by, and let's bring this down real quick, so I'm just going to say over Partition by, and we're going to call this as row_num. Now let's try running this; let's see if it works really quickly; that's important. And over here, you can see that we have our row number. Now these mostly are unique, and these all look unique; I'm not going to scroll through all of them, but we want to be able to filter on this, so we can filter where the row number is greater than two; if it has two or above, that means there's duplicates; that means there's an issue. So let's go ahead, and we're going to take this; we'll put it into either a uh subquery or a CTE; I'll create a CTE for this, uh, cuz it's really easy, so we'll say with, then we'll do uh duplicate_CTE as, then we'll just do our parenthesis; we'll paste this in here; get rid of that right there; and now we're going to say select everything from this duplicate_CTE, then we'll say where row_num is greater than one. Let's run this, and let's add a semicolon; let's run this, and you, you can see that these ones have duplicates, so these are our duplicates actually, and we want to get rid of these exact rows.

Now, just to confirm that these are uh the duplicates, let's look at this one; I never heard of this company, um, but we'll take it really quick, and let's select; we'll say where company is equal to, and we'll call this Oda. So let's run this, and it looks like these no, no, no, no, these aren't duplicates; that's a good thing we checked; okay, because it looks like um these aren't the exact same, although they're very, very close; these technically are not duplicates, so I'm glad we checked this; we need to do this partition bu over every single column; that's what I'm realizing, so we'll do company, comma location; I'm glad I'm genuinely glad we, you know, it's good to make mistakes, um, and figure things out as you go; it really is important. So company, location, industry, total_laid_off, percentage_laid_off, date, then we'll do stage, and then we'll do country, and then funds_raised_Millions. So we're changing the CTE to partition over everything, so now let's run this. Okay, Oda is not in there; that's the only one we checked, um, but let's look at Casper; I know this—these are the—aren't these the mattress people? Didn't know they had layoffs; poor guys. Um, all right, let's take a look; it looks like this row and this row are duplicates; these are our duplicates, so we are going to want to remove only one of those; we don't want to remove all of those; we don't want to remove both; that would be bad.

Now, in MySQL, it's a a little bit trickier to remove things than it is in something like Microsoft SQL Server, PostGrace SQL; um, they have different ways that they can delete rows. For example, in Microsoft SQL Server, we could literally identify these row numbers in the CTE and delete them from it, and it would delete it from the actual table; we can't do that in MySQL, and I'll show you—let's actually copy this; we'll go like this, and we'll say uh let's say we want to delete these; we'll say delete from; We're deleting this from where the row number is uh greater than one; what am I writing right here? delete; there we go; so delete from this duplicate_CTE where the row_num is greater than one; that's all these duplicates; we want to remove them; let's try to do this; let's run it; let's go down; if we look at the bottom, it says the target table duplicate_CTE of the delete is not updatable, so you cannot update a CTE; a delete statement is like an update statement, um, essentially. So what we are going to do is we're going to do something a little bit different because this is how I would love to do it; that makes it super, super easy to remove duplicates, but that is not always the way that things happen in the real world. I think what we should do is take this right here, and let's run this; we should take this right here and put this into let's say a staging two database, and then we can delete it because we can filter on these R_nums, and we can delete those which are equal to two, so it's essentially like, you know, creating some type of table and then uh just deleting the actual column, so we're—that's exactly what we're going to do, so it's essentially just creating another table that has this extra row, then deleting it where that row is equal to two, so, you know, somewhat fairly straightforward, but um let's try it and let's see what happens. So we're going to come down here, and do is create our table uh let's try doing that with here; let's uh let's copy to clipboard a create statement; let's see if this works; perfect; that's exactly what I wanted. Now all we're going to do is say we're creating the table layoffs_staging_two; now this is a create table statement, and we're naming the columns, and then we're also assigning the data type, so we have all these things, but we want one more; do a comma, and we want to add row_num, and I need underscore num, and that should be an integer data type, so we're just keep it just like this; let's go ahead and copy this, and let's run it; see if it worked; bring this up; looks like it worked properly, uh, and let's say let's go back up; I want to rewrite things that I don't have to; let's run this. So now we have this empty table, so we want to insert this information right here, so we're going to insert into, so we'll insert into, and then we'll do this right here, so insert into staging_two; now let's try to run this; see if it works; and let's run it; and let's select that table; and now we have it. So let's pull this back up, and I'll walk through what we just did, cuz I know I'm going quick, but we have so much to cover um in this lesson. So we just inserted basically a copy of all these columns, but in this new table, we added one more, the row_num, so now we can filter; we can say where I need to spell that right; where row_num is equal to two, or we should should say greater than one because some might have multiple duplicates, and there you go; here are our duplicates. Now we're going to delete these, so all we have to do is come right back down; where did I go? copy this; come right back down here, and we're just going to say delete from; we just did a select statement; I always recommend doing that to identify what you're deleting, then you change it to delete, and now if we run this, go, and I'm actually going to keep this, um, let me see; there we go; and let's run it again, and now they're gone, and if we say um just the whole table, this looks wonderful. Now this row_num is going to going to be a column at the end that we probably don't need anymore, right? It's a redundant column; it adds up extra space in memory and storage and all these other things and processing times; we're just going to get rid of it; uh, that'll be at the very end; I'm sure. So it looks like we are good to go; that's how we remove duplicates. Now, um, there are different ways to do it when you have different columns; like if you have a unique column over here, makes it so much easier, so so so much easier, but we didn't have that, so we had to kind of do a workaround; Welcome to the Real World. Now now let's look at standardizing data. So standardizing data is finding issues in your data and then fixing it, so I'm already noticing right here; looks like we have a space at the beginning; uh, we could easily just do a trim on this column, um, and let's I I don't even think I was um I did this when I wrote out all the the scripts for this; let's just do from this table; why am I writing it all out again? We actually want to select the company and then the or actually we'll just do distinct company; distinct company; let's run this; and if we do a trim around this, let's run this again, and that looks better. So if we do uh company, company, comma, and then we'll just do the trim; I don't want to; we don't need to do distinct right now; we'll do the company; this just looks better. So we're going to update that; it's super easy. Now, if you ran into just a second ago, uh, I may need to help you change that. So if you couldn't update or delete those things earlier, I should have told you this earlier; I apologize. All you need to go is to edit; you just need to go to edit; go to preferences at the very bottom; go to SQL editor; go all the way down to the bottom; and right here, we have safe updates on; if you have this selected, that means you can't update anything; that's a problem. So what you need to do is Select this uh or unselect it like I have it and save it; you may have to even restart your MySQL potentially in order for those changes to take effect, but then you should be able to update that. Now all we're going to do is update this table, and we're going to set, and now we need to come back here, and we'll say we're going to set the company equal to trim. Now, if you don't know what trim is or you haven't taken that lesson, trim just just takes off the white space off the end, so it took the white space out of here or off the right hand side as well. So we're going to update this, and let's do a semicolon; a semicolon; let's run this; let's select this again, and it was updated properly, so we're already off to a great start.

Now the next thing that I want to take a look at is the actual industry, so let's go back; copy this; and let's take a look at the industry, so we'll do industry, and we'll run it. Now, if you look in here, there's a ton of different industries, um, and there's marketing and marketing; oh, because I haven't done distinct; please ignore me; let's do distinct; and there's a ton of different industries in here; Transportation, Healthcare, consumer; uh, there's a blank one, which we'll take a look at later, uh, but this is an issue; crypto, cryptocurrency, and cryptocurrency; these are all the same thing; these should all be uh on or labeled the exact same thing; the reason we need to change this is because when we start doing uh the exploratory data analysis, visualizing it, these would all be their own rows, their own unique thing, which we don't want; we want them all to be grouped together so we can accurately look at the data. Let's take a look at any other ones; fintech and finance; that could be the same thing; I'm not 100% sure; I'm not a fintech person; um, I think for now the only one that I'm confident in changing is this one right here, which is cryptocurrency, so let's go ahead and update that. So all we have to do, and we need to actually—let's select really quickly where it's like

crypto. So we'll say, uh, where industry and we want to select everything where the industry is like, and we'll just do crypto. They all start with crypto, right? Yeah, we'll do crypto just like this. And let's run this, and let's just take a look. Lot of layoffs in the crypto industry. Good night. All right, let's find where it's cryptocurrency. Okay, so even this one, it's crypto, and I know Gemini crypto, crypto, and then it says cryptocurrency. So these should be all crypto. You see how 95% of them are crypto? So we're going to update these other ones. Oh, this one is CR YP. Is that how you spell crypto? Jez, I don't know anything. All right, so we want to update all of them to be crypto. So what we're going to do is we're going to say update layoffs industry 2. We want to set the industry equal to crypto, just like this, where, and we can do it a few different ways. We can say industry, we, I think we can do like, let's try this real quick. I, I, some of this stuff I don't have planned out. I'm just kind of going with it as we go, um, which I like better, you know, we kind of we work together on this, we figure these things out together. That's what I like. Um, then we'll do like crypto, just like this, exactly like we had it up here. So if it's like crypto, it should be crypto. Let's try this, let to see if it ran. CU, it may not have. I can't remember. Yeah, it worked. Okay, so it updated uh, three rows, and that looks correct. Now let's go back up and let's run this. As we scroll down, they are all the exact same. Beautiful, beautiful, beautiful. So if we do uh, distinct industry again, let's get rid of this. If we run this query and we scroll down, crypto is its own thing. Beautiful, and it looks great. We can look at those later on how we can update those, um, but let's keep going. Let's look at our whole table again. And these blanks and these NULLs are actually an issue. We do need to deal with them, but I, I, my instinct is telling me, go fix it, um, but my, you know, tutorial side is saying, okay, stick with uh, the tutorial, the order that we agreed on. Um, so let's go take a look. We've looked at company, we've looked at industry. Um, let's just real quick look at uh, distinct uh, location. Now it's good to look at most of these things, right? There could be small tiny issues that you just never saw. Um, and we're just going to order by, order by one, just do a real quick, just a scan to see if we find any issues. Um, that could be an issue, but that could just be another language, if I'm being honest. I don't know. I'm as I'm just scrolling through here because I want to make sure because this is not something I had in my uh, pre-written script. This looks pretty good to me. Um, let's do everything. We'll run this, and now let's look at Country. So we'll do distinct country, and let's run this, and let's scroll down again. This is sometimes just what I actually do. All right, we got an issue right here. Super common. Somebody put a period at the end. Some dingus, uh, and we're not going to judge that person. I don't know who it was or who ruined this data set, but um, yeah, that's a problem. So we're going to need to just update that. It looks pretty simple, um, but I'll just say where country is equal to, or let's say like, and then I'll say like United States. There we go. And oops, want to say select everything. I just want to see um, where it's at. Oh, jeez, there's too many. Let me see if I can spot it. I can't spot it. It looks like they're supposed to be United States, not United States. That's the issue. Um, we can easily, easily fix this, and we can probably, let's do um, really quickly, let's do select, oops, select distinct, and then we'll do country, comma, and then we'll do a trim, cuz we want to get rid of that um, that one. We'll do country. Now just doing the trim won't fix it. Let's go to the bottom. So that doing the trim doesn't fix it, but here's what you can do. It's a little trick of the trade here. We're going to do something called trailing, which means coming at the end. So what's trailing the period from country? Let's try running this. Scroll to the bottom, and it fixed it. So this is a little um, a little advanced, little tidbit for the trim here. We can do trailing from the country, and we're looking for something that's not a white space. We're specifying we're looking for a period. So now we can do is we can say update, we can set the country. Do update um, this table, and we'll oops, and we'll set what am I doing? What's going on here? We'll set the country equal to, and we'll do it just like this. We're only going to do it for Country, right? Uh, so we'll say is equal to trim, and we'll say where country is equal to, or actually let's say like, and let me see if I have this. Oh, I don't. Let's let's just say like United States, like we had it before, just like this. So let's go ahead and update this after I put my semicolon in. Let's run this, and let's run this again. Again, it shouldn't need to fix it anymore. It's just one row. That's perfect. That's exactly what we wanted. Now one thing that's really important, uh, and this is, you know, this is a longitudinal, it's not the right word at all. Give me a second. I can't I can't speak and write at the same time. So sometimes I just say uh, dumb things. Um, uh, if we want to do not longitudinal, but um, time series, that's the word I'm looking for. If we're trying to do time series um, exploratory data analysis, time series visualizations later on, this needs to be changed. Right now it's text, and we can look at that by going, right, actually let's refresh this. We're not looking at staging, we looking at staging two. If we look at the columns and we come down here to date, it is a text column. That's not good. If we're trying to do uh, time series stuff, we want to change this to a date column. Now how can we do that? Let's take a look. So let's do date backslash. So we're just going to look at the date. Now let's change this cuz we want to format it how we want to format it with it, which is month day year. So how can we do this? Well, there's something that's very, very helpful, works perfectly in the situation and is exactly what we're going to do. It's called string to date. So we're going to do string underscore, there it is right there, underscore 2core date. It literally helps us go from a string, which is a text, that's the data type, to a date. So it's perfect. Now all we need to do is pass through two parameters. We have to pass through the column, which is the date column, and then what format we wanted in. Now if you haven't done date formats before, I'm going to kind of walk you through it while we're looking at it. Um, in order to format this properly, you use a percent sign. So it's going to be a formatting for a month, a lowercase m. A capital M is something completely different. I believe it's spelled out. I need to, I, we can look at that in a second if we want to, actually. And then we can do this right here, and then we'll do another one. So we're formatting it in the way that we want it, but also converting it to an actual uh, date column. So now we want month, and then we want day, lowercase day. We'll do a forward slash, and then another percent sign, and then a capital Y, which stands for I believe the four um, four number long year. Uh, have a let's just um, let's look at this real quick. So it worked perfect. So we're it's taking in this format that it's in right over here and converting it into the date format. So this is the standard date format that you're going to find in MySQL. Now let's see what happens really quickly, just for fun. Uh, let's see if we do capital M, uh, it looks like that's not going to work at all. Uh, let's do lowercase Y, and um, just formatted it to 2020. I think it took the first two numbers. It looks like I don't know why it's doing that, if I'm being honest, um, but if we keep it with the capital Y as we should, this looks perfect. This looks exactly like what we're trying to do. So you can mess around with it. It depends on the how the data is formatted in your original column when it converts it to the string to date, and there's a lot of different stuff you should just look up um, date formatting in MySQL. Really interesting stuff. So we're going to update this date column to this, which is our new date column. Let's go ahead and do that. We're going to say update. You guys should be getting used to this by now. That's the whole point is getting used to doing these things. So we're going to set date equal to, and then we're going to put in this right here, the string to date. Go ahead and do this, and let's run it. Make sure it worked. 2355 rows. It looked like it did every single one, uh, but let's go ahead and get rid of this, and let's run it, and it looks like it worked perfectly. Now there were some NULLs, it looks like, and that'll be something we have to look at later when we talk about NULLs, but um, overall, I believe this looks proper. Now if we refresh this, let's refresh, let's come down to the date, you'll notice it is still a text, it's date, it's called text, but now it's in the date format. Now that's really important, and maybe I should have done that earlier, if I'm being honest. Um, tried to convert it to a date column, it wouldn't work. It would give us an error. Um, you'll just have to trust me on that one, but now we can do it where we can change it to a date column. So let's do alter table. Now only do this, never ever do this on your raw table. Only do this on things like a staging table because we're about to completely change the data type of the actual table. So we want to change the layoff staging two, and then we're going to come down here, and we're going to say modify column, and what column are we modifying? It's this date column. There we go, and we want to change it to what data type? A date. And am I spelling this right? Yeah, I just need a semicolon here. Whenever I see an error, I always yet a just look for the semicolons. So let's go and run this, and let's refresh, see if it worked, and the date was changed to a date, which is perfect. It's all we wanted to do. Uh, just to make sure we were doing what uh, or we'll set ourselves up later in the future really well. Let's look at our table. All right, this is very good. So we fixed a few uh, just issues with the company. I believe something with the industry. Oh, the cryptocurrency. We changed the country. Um, I'm just going to go ahead and tell you right now this one, uh, we're not going to look at until we look at the um, NULLs and whatnot in just a second. So we're not looking at that one yet, and then uh, we have this extra column that we've done. So we've done a lot so far, but the next thing in the process, step one was remove duplicates, step two was standardization, step three is working with null and blank values. Now this is going to happen. You're going to have NULLs, and you're going to have uh, blank values in here. I, it's somewhere, um, it's just going to happen, and so we need to think about what we're going to do with that information, whether we want to make them all NULLs, make them all blanks, try to populate that data. Let's see what we're going to do. So let's start off with the total laid off. We'll just do uh, where total_laid_off is null. So in order to look at the null, we say is null. Let's try equal to null. It's not going to give it to us. We have to say where it is null. So we have these values. These are completely null. Uh, there's quite a few of them, but remember this is also useful information, but if they have two NULLs, uh, that probably is pretty useless to us. Um, that's something I think we'll take a look at in a little bit. Actually, we'll say we, and we may save this query. Percentage_laid_off is null. So if they're both null, like these, these are all I believe fairly useless to us. These might be ones that we remove. So let's actually look at this. Um, in step four, we look at removing rows and columns, but one thing we should take a look at, I remember this industry. Let's do uh, industry. Do distinct. This industry had some missing values, and let's take a look at that. Okay, so we have a missing value, and we have a NULL here. So let's look at this query, and let's say where industry is null or do industry is equal to a blank like this. We'll select everything to run this. All right, so it looks like there are a few that are blank. Now what we can try to do is see if any of these have one that's populated. Let's take Airbnb for example. Let's search for this really quickly, and this is 100% um, you know, it's just helpful. It's really, really helpful to be able to populate data that isable. Is that a word? Um, let's try it. So we'll say uh, select everything. I just wanted to do where, spell that right, where company is equal to, and let's do Airbnb. There we go. Let's run this, and it looks like we have this one right here. So for example, um, these, whether they have them or not, we're going to try to populate these. If this Bal or carvana or Jewel had multiple layoffs, these ones should, if these ones aren't blank, if they have one that's not blank, we should be able to populate it. For example, um, not the one I was trying to do. If we look at Airbnb, this one has travel. So we know this is the travel industry. So we can populate this with travel. Again, we want this data to be uh, the same. So if we're trying to look at, you know, what industries were impacted the most, this row isn't going to be affected, or this row won't be in our output because it's blank. We want that to be traveled to represent the data properly. So we want to update it. So if this one has travel, we should be able to update this row with this travel right here. So let's see how we can write this, and let me give myself some rows right here. All right. Now what we're going to need to do is try to do a join here. So let's try writing out in a select statement, then we'll just change it to an update if it works. So we're going to select everything, and we're going to do this from staging two, from staging two, and we'll call this uh, st2, and then we'll join on itself, cuz what we're going to do is we're going to check in this table, does it have one that is blank and not blank? If so, update it with the nonblank one. That's essentially uh, in layman terms what we're trying to write, but writing it out could be a little bit more difficult. Um, so we're going to join on itself, and we'll call this uh, let's actually call this table one T1 and T2 because they're the exact same table, uh, and we'll we'll do this on, and we're going to say t1.company is equal to t2.company. So the company has to be the same. That's important, and we probably should do the location is the same as well. Now we'll do and t1.location is equal to t2.location. I'm imagining, you know, there's another Airbnb in like South America somewhere that's called Airbnb, but you know, I'm just imagining a scenario right where we have to think about different use cases rather than just large companies. So those other ones, they may have ones that are in different locations. We don't want those. Um, we don't want to change them if they're not the same. So these are the same. Now what we want to find is we're going to say oops, we want to say where, then we'll do t1.industry is null, and then we want to check that t2.industry is not null. We'll say and t2.industry is not null, and let's just run this. Let's see if we get anything. So let's think this through because we got nothing in our output. We're selecting everything, we're joining on the company and the company um, and the location where T1 industry is null and T2 industry is not null. Let's just get rid of this for a second. I just want to see if this changes anything. It doesn't. And it's possible actually that instead of doing is null, we could do or, and this I'm glad we're walking through this. We can do or is equal to blank, and let's try running this. There we go. Okay, so it looks like there's Jewel, carvana, and Airbnb. These ones all have Industries um, where it's null or blank, and an industry is not null. So that's really good. Now if we scroll over, see the industry here, this is our T1, this is our first table. If we scroll over, I bet we'll see the t2 industry where it's not null. Let's scroll over, and here's our industry. We have travel, transportation, and consumer. So this worked exactly as we had hoped. I can even um, pull this up here just to show, kind of show you a little bit easier what that's doing, and we'll do t2.industry. This is kind of like what we're trying to do. So if it's blank, this one is going to be populated into here, here if there is one that is not blank. So that's essentially what we're going to do. Let's write the update statement, and we're going to see if it works. This we have to translate this to an update statement. So we'll do update, and we're going to update uh, this right here. So we'll say update T1, and then we'll do the join right there, and now we have to do a set statement. So we'll set the t1.industry equal to, and I'll just copy this t2.industry. I just don't like I don't like writing things out. Um, then we say where. So we do this, it's like that, and let add a semicolon. Okay, let's confirm. So we're updating this table T1, we're joining on T2 where the company is the exact same. We're setting T1 industry equal to T2 industry. So the T1 should be the blank one. So where the T1 industry is null or blank, and T2 industry is not null. Let's go ahead and run this semicolon. See if there were about three updated. Yeah, rematch zero R affected though. Let's go take a look. Um, we have to let's run this query. Looks like those are still null. Let's run this, uh, that one is still blank. Now let me think here. I'm I'm trying to think of why this didn't work, and I want to walk you through my thought process. It is possible that because these are blanks and not nulls that it's not working, I, and I will say that is something I typically do where I set these blanks to nulls first. So let's actually try that and see if that changes anything. I'm just going to update uh, this. I'm going to say set the industry equal to null. We'll say where industry is equal to blanks. So we're just changing it to null where it's blank. Let's try this. Let's go back down here to our select statement. So these are all nulls. Okay, I think I think this is now going to work because now you can see on this side, it's going to there's only one option for it to populate it. Before there were those um, blanks, which I think was causing the issue. Um, let's get rid of of this part cuz now we have no nulls, and now let's try running this. We're workshopping this on the fly, guys. Uh, let's see. Three rows affected. Heyo. All right, let's go see if it worked. Um, let's run this query, and we have none. That's perfect. Let's look at Airbnb. All right. All right. R into into some issues, but we work through it. We figured out the issue, and now it's working properly, and we can even come

Back up here to select everything, and it looks like Bailey's is the only one that still has a null. Let's look up Bailey's real quick, and we'll say we're company is like, uh, Bailey. Let's run this, yeah, and there's only one, so there wasn't another row. All these other ones like Carvana and um, I can't remember the other, Jewel and Airbnb, those ones had an extra row; they did multiple layoffs. This one only did one layoff, so we don't have another populated row where it's not null. Actually populate the null row; that's really all that happened. Uh, that's why that worked that way, so I'm really happy that worked. Awesome job, guys. Uh, I was starting to question myself: Do I even know how to use my SQL? I mean, I was really starting to question my abilities here. Um, take a look. Uh, I think that is all we're going to do for populating null values.

Now, here's why things like total laid off, percentage laid off, um, funds raised—how are we going to populate that with the data that we have here? I don't believe we can. Now, we might be able to populate—oops—we might be able to populate some of this if we had the um company total, like if we had the original total before laid off, because then we could do calculations like um, oh, these companies went completely out of business; that's not good. 1%; that means 100% was laid off. Um, but if we had, you know, the total—if they had 50 employees and 100% were laid off—we could populate the total laid off—whoops, did it again—we could populate the total laid off by saying if this is 50, 100% was laid off, that's 50 people were laid off. We don't have that data, so we can't go and populate it. I don't believe funds raised. We might be able to scrape some data from the web and populate this, but that's a totally different thing, um, not part of this project. So I think the data cleaning for the null values and blank values, I think, I think that's going to be done. Um, it's possible that the stage could be the same, and if you want to go check, you can, but we're going to keep chugging along because we want to remove columns and rows that we need to. Now, if you remember, we were looking at this before—did I save that uh query? Let's go look. Here we go. Bring this down to the bottom. All right, these rows—let's, let's really take a look at these um and think about if there's going to be health dust.

Um, what we are trying to do with this data in the near future is we're not just trying to identify a company or a location that had layoffs, and maybe we are, maybe that, maybe we are trying to do that, but these have no layoffs and no percentage laid off. So in my opinion, I don't know if these laid off any at all. Um, I believe that we can get rid of these. Now, deleting data is a very interesting thing to do; you have to be confident. Am I 100% confident? No, not really, but I'm confident enough to know that what we're about to look at in the next one, we're going to be using these total laid off a lot, percentage laid off a lot when we're looking at, um, you know, actually querying the data and doing some exploratory data analysis, so we're going to use these a lot. I don't think uh these—I'm not even sure if these are accurate; I'm not even sure if they actually did have a layoff. It's saying they did; it doesn't show if they laid off any, so um, can we delete this? Yes. Should we delete this? It's iffy. Uh, I'm not 100% if I'm being completely honest, and there's a lot of rows like that. This is, this could be like 100 or so. I'm really not—I mean, I could run a query and run it, but I don't want to; I don't—it's not a big deal. The point being, I don't think we need this information, so we're going to get rid of it, if nothing else just to show that you can do it. So now we'll say, uh, Delete, and then we'll do from here. There we go. So now we're going to delete these rows. Let's try to select them again, again, and they are gone. So we deleted the ones where the total laid off was blank and the percentage laid off was blank. We just—I can't trust that data; I really can't. Um, and let's go back down from right here, a semicolon. So sometimes I have to walk myself through these things. Um, all right, this R num—I mean, come on, we don't need that anymore; let's get rid of it. Um, so what we can do now—it's a little bit different syntax—we want to drop a column from this table, so we have to do the alter table again. We're going to alter table layoff staging two, and then we're going to say drop column and rowcor num. If we run this, then we run the table again, it should be gone, and it is. So this is it; this is our finalized clean data.

Now, in the next project, we're going to be doing exploratory data analysis on this cleaned data; we're going to be finding trends and patterns and writing complex queries. It's going to be phenomenal; I'm super excited about it, and I love this data cleaning one. Um, I made some mistakes; I'll be the first one to admit, but cleaning data is not always a straightforward thing. Um, you know, you have to, you kind of mess around with it, figure it out, and, and you know, that's what we did. Uh, whoa, took a while. So just a recap: we removed duplicates, we standardized the data, we looked at the null values or blank values, then we removed any columns or rows, so we did a lot. Um, if you go back and you actually scroll through here and look at some of this code that we wrote, uh, it's not super beginner stuff, so if you're following along with these things and you are getting this project, this is a fantastic project to put on your portfolio. I myself would put this project on my portfolio because it's a very, very relevant thing.

Hello everybody. In this project, we're going to be focusing on exploratory data analysis. Now, in the first project, we worked with this exact data set, and we cleaned up the entire thing, and that was a really good project, and it set us up to explore the data. And with all that clean data, we'll be able to look at our data much better and find better insights while we are using it. Now, normally when you start the EDA process, or the exploratory data analysis process, you have some idea of what you're looking for—sometimes, not always—and sometimes when you're exploring the data, you also find issues with the data that you then have to clean. So even though I did a data cleaning video and then an exploratory data analysis video, and they're kind of separate projects, sometimes those coincide together where you're exploring it and cleaning it at the same time. Now, what we're going to be doing here with this data set, we're just going to be kind of exploring it. I don't have any agenda; I don't have any, you know, one thing that I want to look at; I just kind of want to look at everything, and we'll kind of discover and go uh about things as we are learning and looking at this data set. We will, however, start off really simple with kind of the basics, work a little bit more towards the tougher stuff, and then at the end, we'll have some more advanced things that I think will be really fun. So with that being said, let's start off with kind of more easier things; we'll kind of just ease our way into exploring this data set. Let's pull this down, and let's copy this right down here.

Now we're going to be working with this total laid off and percentage laid off, or most likely this total laid off quite a bit. The percentage laid off isn't super helpful because we don't know how large the company is; we don't have another column here that says, here's how many total employees they had, and then okay, they had a percentage laid off. You know, we won't work as much with this one, but we'll work quite a bit with this total laid off. Let's look real quick; we can look at something like the Max uh total, and I need to use a parenthesis, Max total laid off, and let's look at this. So on one day, there was somebody out there who had the Max total laid off of 12,000 people. That's a lot of people to lay off in one, you know, one go; that's a lot. Uh, let's also take a look at the max, and I think it was percentage laid off. Let's run this, and it looks like one. Now, one represents 100; that means 100% of the company was laid off, um, and that's, you know, that's not great; it just means an entire company went under essentially. We can actually take a look at that because I'm interested to see, you know, if there's any companies I recognize or can see um where, and you come right down here where the percentage laid off is equal to one. Let's go ahead and look at this, and let's take a look. So we have this ahead; I'm just going to go through here and see if I recognize any of these. Summon the crypto space, BlockFi; I feel like I recognize that one. I don't know. Uh, let's keep going. Deliver—it's not good; they let go of 120 people. I'm just curious; I mean, I'm, I'm just kind of scrolling through here trying to see if I recognize any. These are companies that like completely went under or or lost all their employees. Vault Bank—interesting—just interesting to me. We're going to be taking a look at a lot of stuff, um, but these are companies that completely went under. Um, that's, you know, fortunate. We can also order by uh total uncore laid off—and that's not how you spell it—in descending. We'll see which company went under had the largest. So this one had 2,000; construction company had 2,400 people; they went um under; doesn't say what stage they were at, but that's in the United States. We can also take a look at—and there's another column over here called funds raised in millions; let's look at that one. So I want to see—um, these are companies that had a lot of funding or potentially a ton of funding. Uh, let's go over—this is like $2.4 billion, I believe—like, I think it's like a ton of money—Quibi; I believe I know this company—and BlockFi; I, I thought I had heard of them. I'm pretty sure I know that is. So Quibi is one that I'm definitely familiar with; it was like a short-form uh media company. Yeah, yeah, yeah. Then there's British Volt, which looks like an electric company that went under. So, you know, some big companies that went under um in 2023, 2020, 2022. So that's interesting. So we have a lot of companies here, and we're just looking at um that had total laid off, but let's take a look; let's, let's use Group By real quick. I want to look at the company, and I also want to look at the sum of the total laid off, and for that we need to use a group by the company, and let's just start with this, and I'm sure we'll use an order by in a second. Yeah, let's order, order by—let just do two for now—in descending, and two stands for one, two. This is the total laid off, so uh for the total for this table, and we don't know how far go back; I goes—we haven't checked that yet; we'll check that in a second—but for this table, you should recognize a lot of these companies. So I think it starts in like 2020 until like sometime in 2023, but this is Amazon, laid off 1,800 people; Google, 12,000; I'm guessing that's at one time because that was the max that we looked at earlier; uh, this is Facebook or Meta, Salesforce, Microsoft, Phillips, Uber, Dell, Cisco, Peloton—I mean, these are a ton of big companies; Carvana, they let go of thousands and thousands and thousands of people; Twitter—that's not surprising—uh, given what's the change of things; Groupon—um, ton of, ton of people or ton of companies, and that's a lot of people that have been let go.

Now, let's really quickly—uh, before we keep going, I want to look at our date ranges real quick. So let's select everything, um, whoops, we'll do from there, and how do we want to do this? Let's do minimum of date, and let me do it like this, date, and then we'll do uh the max as well, because I want to look at the date range that we have here. Let's run this; it looks like it starts in 2020-03-11, so right when like I believe the pandemic started, the uh COVID started; I want to say that's like right when it hit, at least the US in the United States. Then this is almost exactly 3 years later, so early 2023. So just in those three years, you know, here's some of what we're looking at; these companies have let go of quite a few people or had layoffs. We could also take this exact thing—oops, what did I do here?—copy this again; we can also take this exact thing and look at quite a few other things; there was um the industry. So we look at industry, like what industry got hit the most during this time or had the most layoffs. Um, all we're looking at right now is total laid off; we can also look at um percentage in a little bit, but it looks like Consumer got hit really hard, Retail really hard; that makes a lot of sense with shops closing down because people couldn't come in for the coronavirus. Now, we're just making assumptions, right, um, but you know, during that time it was mostly COVID that impacted a lot of stuff. Then we have Transportation, Finance, Healthcare, Food, Real Estate, um, yeah, there's a lot, a lot of people. Let's look at the lowest ones: Manufacturing, Fintech, Aerospace, Energy, Legal—so low numbers on those; high numbers on these. So really, really interesting. Uh, let's go back up; just want to look at our whole table really quickly, see what we got while we're looking at this stuff, and let's run this. Now we're looked at the company, looked at the industry; I would really be interested to look at the country as well, which countries—at least from this data set—and we can copy or we can go right here, country, because I believe that United States had the most—holy mackerel—they had by far the most; uh, then India—this is 256,000 people—um, lost their jobs. We'll look—I think we'll look at the dates in a little while, like a kind of like time series, like how many per year, per month, per day, or whatever you want to look at, but goodness gracious, uh, that's a lot of people within just three years in the United States, India, Netherlands, Sweden, Brazil, Germany, United Kingdom, then it goes down and down and down, but these are just reported um from this data set that I, I had gotten. So really, really interesting. Good night; the United States had much more than than most, for sure. Um, let's actually look at that date real quick, or we can look at it by year. Um, so we have this date, and if we do it like this, and we can by date real quick. So this is going to do it by individual date, and let's order by—let's do one—so this is the most recent date, so it's literally by date that's reported, and we don't want that. Let's do it by the year, so 2020, 2021, 2022, 2023. We can do that fairly easily; we'll use this year function, and we'll group, group by the year as well. Let's try running this. There we go; it looks like in 2020, 880,000 people; 2021, uh, 16,000, 160,000; in 2022, this looks like the worst year, and then it's only—we only have three months of data in 2023; there's 125,000—holy smokes. So in 2023, it looks like we're ramping up because I'm recording this in 2023, about a month after this data set um that we got this data set; there's 125,000 people um around the world, you know, but just in those first three months. So this is going to be a lot higher than even 2022; that's pretty wild. Um, very, very interesting. One other one, one while we're looking at Group By, um, there's, there was a column, and you can go back and look at it if you'd like, but it's called stage, and this shows the stage of the company, and if we run this—we're all just looking at total laid off—but if you look at the um stage of the company, this is like the different series that they're in, A, B, C, D; A, I believe, is like a series A funding; that's like a super, super starting—oh, this is like a seed phase; then there's Series A, and then it goes up, up, up, up until usually they go um like they do IPO or they get acquired or something. Now, if we go up here and we do two descending, I want to see which one had the most. So this is post IPO; this is the Amazons, the Googles of the world, the large, large company compies that are post IPO or initial public offering; then there's unknown; we don't know which that is; um, a lot of, you know, layoffs from acquisitions, C, D, B, all the way down. So it looks like um most of it's coming from, you know, these ones right here. Really, really interesting. Let's go look at percentages; I'm just going to—I'm trying to say literally—I'm going to literally copy these um and with percentages; I don't think uh—let me look at percentage—I don't think the sum is going to be a good indicator; I don't know if this is a good one to even look at because, and then we're looking at company right now because percentages refer to a percent of the company, right? So we don't have hard numbers because we don't know how large these companies are. So now that we're actually looking at this, this percentage laid off isn't super relevant; really the one that's kind of more, you know, has better—this is a better use for what we're looking at; this total laid off, because again, we don't know these sums; we could, we could look at like the average, right, um, but again, that just doesn't help us that much, I don't think. Think we're going to really dive into that too much is my uh is my feeling.

Now, one thing that I would be really interested in is to kind of look at the progression of layoff, right? Uh, you could call this a rolling sum, so start at the very early est of layoffs and do a rolling sum until the very end of these layoffs. Um, and let's go to the bottom; this is where it's going to start getting a little tougher, um, and there's, you know, we're just doing a little bit of exploratory data analysis, you know, do digging into this a little bit; you can go and dig into this as much as you'd like; you don't have to just do what I'm doing, but I'm just trying to show you some stuff. Now, let's try to do rolling total of layoffs. Um, we could do that on the day, although I feel like that's going to be way too many rows; let's do it based off the month, so right here in this month. Now, let's see if we do just the month; let's do something; I'll show you the month, and that's going to be an issue, and I'll—in my head, I already know—but let's look at it. We could do something like select um from, and let's get this; there we go. So if we do um we'll do substring; let me add a sem—let's do substring, and we want to pull out this month right here, so we'll go 1, 2, 3, 4, 5, 6, so start at position six, um, and this is of course in the date column; we'll start position six, and then we'll take two. Let's just run this really quickly, and there's our month. So this we could do this as month, right, um, or like this. Is that correct? Yeah, so as month. So this is our month that we're doing it. Now, if we group on this and we do like something like a sum of total uh laid off, I think that's the column, and then we do a group By on this month, so it be like this right here; we'll do group by this. Let's try running this; we should be able to do month as well. Let's try this real quick as well because I don't want to have this if I don't have to. Run it perfect. So the months right here don't show us the year, so if we're trying to get a rolling total of just the month…

It's actually could work fine when we actually implement the rolling total using, um, you know, a window function. But the issue with this is it's just going to show us the month. So this is 2020, this is January of 2020, 2021, 2022, 2023, any other years we have. This is not a great rolling total. What if we did one all the way to—I want to say it's 7, 67—let's try this now. This is going to give us a much better—let's order this, order by a one sending. This is just our first column. So now, uh, well, we should do it where it's—not give me a second; I'm I'm I'm figuring this out as we go. We'll do where, uh, the month—write that—where the month is not null. I'm just going to get rid of that one. And, of course, uh, that doesn't work because we're looking at the substring. So let's try doing this. Let's—there we go. Um, it just wasn't reading in that month that I was trying to use. Let's go down. Now here's what we're going to do: We want to take it from the very first month, and we're grouping everything. So these are all the layoffs from 2020 of 03, so that's March of 2020, and we have April, May, and these are the layoffs. So this is really good; this is exactly what I was imagining in my head. So we want this—this is just, you know, 12 months in a year—and we go all the way to the bottom, and I want to do a rolling sum of this. So let's see how we can do that, and we'll use this logic in a little bit. Let's copy this, and let's do select everything. We'll do right here. Now what we actually want to do now that I'm thinking about is we—you want to take this data, and we want to do the rolling sum based off this exact thing. So we actually need to take, uh, this—let's get rid of this—and we'll do it with a CTE. So we'll say with, and we'll do rolling_undor_total that we'll say as, and then we'll put this in here just like that. So with rolling_total as, now we're going to say select, and we'll just do from here. Now what we need to do is we need to select the month. So let's go ahead and select that month, and we'll take it just like this. So we'll select the month, and we need to do a rolling total. All we have to do for that is the sum of which column we're doing. Let's actually change this real quick. Um, we're going to call this as, um, total_off. I'm just going to keep it simple. So the sum of total_off. So now we're doing that, but we want to do it over, and all we need to add into here is an order by. We're not going to Partition by anything because in here we already did a group by, so it's, you know, kind of like partitioning it. We just need to say order by—we just need to order by the month, I believe. So let's try that, and let's run it. Let's do that, and we actually need to, since we're doing, um, this, we need this at the end, and we can rename this if we'd like. So we can do this as rolling_uncore_total lowercase. Let's try running this, and let's see what we get. Okay, and this looks correct. So starting in 2020 of 03, we had 9,000 layoffs. Then the next total we added onto here—now this visually isn't the best. I would like the month right here as well. So let me actually add—let me create its own row, put a comma here, then right here. I want to keep is total_off, so we can visually see better—much better. Okay, so we have the month, and as it goes down, we're having more laid off. Now this is our rolling total. Here's essentially how this works: It starts with 9,000, 628, then it adds on the next month, which is 26,000, which equals 36,000. Then it adds on the next month, and we get 62; adds on the next month, 69; right, it keeps going all the way down. This just shows each month how many were laid off, and this shows a month-by-month progression all the way down to the bottom. So let's keep—let's just, you know, take a look. In 2020 of 03, we had 9,000. By the end of 2020, we had about 81,000 or so. Then at the beginning right here, all the way down to 2021, by the end of 2021, we only had 96,000. So 2021 was a good year, it looks like, um, comparatively. We had 90, 80—well, let me see—91,000 people let go, and here we only have 96,000 let go. So that's what our 81—it's only like 15,000 people; that's like nothing, um, comparatively. Then in 2022, uh, things start ramping up dramatically. It looks like we have, um, 12,000 people, 17,000, 16,000, and they're adding up. It's going from 97 all the way up to—good night—um, right before the holidays in 2022 of this past year, I mean, we had, uh, 247,000 people. So that's like 30 some thousand—my math is really bad—it's like 150,000, and then we only have—oh, we have even more here actually—and then we only have the first three months of 2023. So these months right here were really devastating, just around the world. Now we can also break this out potentially by country, so we can see how many per country, but this—just around the world—that's a lot of people losing their jobs, all the way up to 383,000. So in this range, 383,000 from March of 2023 all the way back to March of 2020 lost their jobs, and this is just reported. I'm sure there was, uh, you know, much more than that. This is that—like companies, larger companies that have like series A funding, IPOs, etc.—um, but a lot of small businesses went out of business, um, so we don't we don't have that information in this data set. So I think that's what we're going to do next: Let's kind of look at the company maybe because I'm always interested in the company. And actually earlier—let's not do that one; earlier we're looking at the company, the sum of total_aof. Let's, um, let's bring this down. Let's run that CTE. That's what rolling totals are, by the way. Rolling totals are great; I'm really good for visualizations as well. Um, let's see. Yeah, so I want to take a look at these companies, but I want to see how much they were laying off per year. So instead of just looking at it as a total, we'll break it out by the year. Now I'm just going to warn you: This is probably going to—this most likely be our last one in the in the lesson. This is going to be probably our hardest one yet, um, potentially. We'll see; maybe the other one was earlier, uh, was harder earlier. Now let's use this kind of as, um, a starting point. But what we're going to need to do is we want to take the company, but I also want the date. So I need to do a comma, then date. So we need our date here, here, and I'm going to do that. I need to group by the date as well, so we'll do date, and let's run this. All right. Now this is just doing the, you know, company and the exact date. We don't want to do that. Let's actually do the year. Let's just look at the year; I think that'll be plenty. You could also do the exact same thing as we did above with the substring, um, although I think that's going to get a little messier, um, Stu—you know, just a thought. Let's run this. Okay, so now we're looking at just the—we're grouping by year. Let's order by, uh, let's say the company, and we'll do that in sending. There we go, and let's run this. So now we have it open. Let's see who—you know, you can see people who made multiple layoffs. This is in 2020; they let go of 200, and then in 2023 they let go of 155. This is a company I've never heard of, so this is already looking really good. Now let's say we wanted to use this, and what we want to do is we want to rank which years they laid off the most employees. Now this is just a small, uh, sample; we'll look at more just a little bit. We can actually look at, um, let's just do three, uh, three descending, just like this. Should be large companies, so you know, some of these companies like Microsoft, even Amazon right here, and Amazon right there, they let go of multiple or thousands of people in different years. So I want to rank those; I want to say, you know, the highest one, uh, based off of the laid off should be ranked number one; that's the year that they laid off the most people. So let's go ahead and try to do that. The thing we need to do is, uh, do a CTE; we'll start with that. Let me, um, add some more things down here so we're good to go. So let's do—we'll do with, let's do, uh, companies—this is going to be the company_year_underscore_year—we'll do it as, and that's what this is going to be. This is—is our company year, and we can do select everything from company_year. It's going to be the exact—that we're looking at. Let's go ahead and run this. Okay, so this is good. Now I do want to change these columns, and I can do that right here. We'll do company, um, let's call this years, and then we'll do—I'm going to do total_laid_off again. So total_uncore_laidor_off—this the sum—right, total_laid_off per year. So let's go—go ahead and run this. Now there we go. We have company, years, and total_laid_off. So this looks much better. And what we're going to do is select everything, but we want to partition it, uh, probably based off this years right here, and then we want to rank it based off how many they laid off in that year. So we'll get to see who laid off the most people per year, cuz some companies like Amazon, they let—they let off multiple people per year. What was it, the highest per year? That's kind of what we're going to look at, um, so we'll do DENSE_RANK, and we're going to do that over—now we're going to Partition by—oops, that's not how you spell partition—PARTITION BY—we want to Partition by the years, so all of the 2021 layoffs will be in the same partition, all the 2022 will be in the same partition, and we'll do years, and we want to also order by the total_laid_off. Now we want to do that in descending, so we'll do total_laid_off descending, and then we want to—to, um, add this DENSE_RANK to it. So let's try it. Let's run this. Good night; that's a a big one. So let's take a look. So in 2021—it looks like—or 2020, it looks like Uber had the highest, and we want to take out these NEs. So let's do, um, where years, let's say, is not null, and let's run that. Here we go. So in 2020, and that's what we're partitioning on first, it looks like this is one, two, three; these are the top ones, um, and let's order by, and let's do the—let's order by the rank, um, first. Let's call this as—bring it down—what do we want to call this? We'll call this as ranking. There we go. Order by ranking—in there—we go. Now we have our ranking. So in 2020, this is the biggest one of layoffs. 2021, this is the biggest layoff. I guess we'll have to take a look in Meta. In 2022, they had the biggest layoff, and Google had the biggest layoff total, uh, for 2023. So this looks correct, but I kind of want to filter on this ranking to be able to only filter maybe the top, like, five, um, companies per year, and I think we can do that. Let's actually get rid of this. I think what we should do is we should add this as another CTE and query off of that. So now we'll call this company_uncore_yearor_rank. So now we have the year_rank as—we'll have our query—oops—have our query. So now this is our company_year_rank. So now if we do select everything—thing from company_your_rank—this—we run it. So now we have our rankings. Let's come down. Now we have our rankings, but I just want to filter it based off of that ranking. We'll say, uh, where ranking is greater than or equal to—let's say five—we'll look at the top five rankings. Let's run this. And I said greater than; I wanted, uh, less than. Run that. That's look good. Okay, so really quickly, we have in 2020; we had—these are the top five people who laid people off: Uber, Booking.com, Groupon, Swiggy, Airbnb. In 2021, the largest layoff was ByteDance, which you think is TikTok, right? Cera, Zillow, uh, yeah, these are top five. So 2021 or 2022 and 2023 were definitely the largest as well. We have Meta, 11,000 people; Amazon, Cisco, P&G, Carvana, as well as Phillips; they tied; that's why we have the DENSE_RANK, because some of these will be ties. Then we have Google, uh, in 2023, all the way down to Dell. These are all ones I know: Microsoft, Ericsson, Amazon, Salesforce, and Dell. So this is really, really interesting, just looking at a year-by-year snapshot, right? These are the total laid-off for each company, and we could even go back and change this for like industry or, you know, really whatever we want to change this to. This is just an interesting query in general to look at, you know, per year, and we could go back and change per month or lots of stuff we can change in here, but this is really interesting to me. Um, it just looks like a lot of the large tech companies had some—took some big L's, took some big hits. Um, let's recap this query really quickly in case, you know, it's tough to follow. But we created this query up here, and we were looking at the company by the year and how many people they let off. Then right over here, we said with the company_year—we changed these columns—this is our CTE. So we created our first CTE, then we went and we gave it a rank, and we wanted to, you know, filter on that rank. So we did this rank as another CTE; we just did a comma, had a second CTE, and we hit off the first CTE, the company_year, which is right here. So we hit off our first CTE to make this second CTE, and then finally we, um, queried off of the final CTE. Definitely not an easy query to kind of think through and walk through, but I hope, you know, you're able to follow, um, because, you know, that's a a a really good query. This is something I've definitely done in a real job when I was working with a lot of healthcare data. This is—a lot of stuff that I would do, and so this is a, you know, pretty good, um, pretty good query to know how to do. But with that being said, uh, we are done with this lesson. I hope this wasn't too short; I don't know how long I ran, but, um, you know, we looked at a lot of different stuff. Let's go back to the top again. We were just exploring the data; we looked at layoffs a lot, um, looked a lot at the company, uh, when these dates actually started for these layoffs in this data set; we looked at the country, the actual year of laid off, uh, then we went to a little bit more difficult things; we looked at it per month, so per month how many layoffs they had, and then we did a rolling total. This one was a pretty good one using that substring. Um, I love substrings, man; they're awesome. Or lady, they're awesome, uh, and then we came down here, and we did the one we just did with multiple CTEs in the company. I think it was a a really, really good, solid project, um, combine that with that data cleaning project, and man, you got just a really good start with some MySQL projects. And this one can be expanded upon. Don't stop where I stopped, right? Let me go back up the top. Don't stop where I stopped, right? This data set has so much data in it; you can do a lot of different things. And even if you want to, you could go and find these companies right over here, and you could try to, uh, get their total, uh, total company that they had, and you could use this column a lot more; that'd be really interesting with some calculations there. So with that being said, that is the end of our exploratory data analysis project. I hope you enjoyed it; I hope you learned something both in the data cleaning project and in this exploratory data analysis project. That's what this is all about—about—and getting the confidence and gaining the experience to create these projects and add those to your portfolios. Speaking of which, if you haven't already, check out my video on how to create a free portfolio website, uh, using GitHub. Awesome; I highly recommend it. You can add these to your portfolio. So with that being said, thank you so much for watching. I really appreciate it. If you like this video, if you learned anything at all, be sure to like and subscribe below. Check out my channel for tons of other videos just like this one and more. I will see you in the next video. [Music]