 Hello, everybody. In this lesson, we're gonna be taking a look at case statements in my SQL. 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 or 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 can 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. 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 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 will 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, 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 be from employee underscore 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 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 then we're going to do salary times 0.05. And if we run this, 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 could actually call that we'll say as a underscore, actually, let's do new underscore 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 you just do times 1.05 should be the exact same thing. 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, 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. 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. 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 department ID equal to six. So what we can do is another case statement. We can say comma, we'll do case and end. And we'll do another one. We're gonna say when DEP TID sort of when the department ID is equal to six, then we're gonna give them a bonus. So we're gonna say salary times 0.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. Because he was part of the finance department that just did an exceptional job this year, apparently, according to the Plenty 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. In the next lesson, we're gonna be taking a look at some queries in my SQL.