 What is going on everybody? My name is Alex Friedberg and today we're going to be walking through case statements in SQL a case statement allows you to specify a condition and then it also allows you to specify what you want returned when that condition is met So we're going to be using this employee demographics table that we're looking at right here We're going to walk through the syntax of how to create a case statement And then we're actually going to some use cases at the end So let's start off by specifying what columns we want. Let's say we want the first name We want the last name and We want the age Now let's just get that information Now for our case statement, we're going to be using this age column. So we actually want the age to be in there So let's specify where age is not known and Run that so now we have a pretty good look at it and let's just order by age Just to clean it up a little bit So now let's start building our case statement. So we're gonna say case and Then we want to say when Now we need to specify what condition we want to look for. So let's do when age is greater than 30 Then then what do we want to be returned? So we want to return that they are old Else so that means anything that is not over the age of 30 We want to return young and then you need to specify that you were done with the case statement And so you'll write and at the very bottom. So this is our first case statement. Let's run it and see what we get So as you can see a new column was created and if the person is over the age of 30 So 31 and up they're given old and if they're not over the age of 30, they are given young Now we can do as many when and then statements as we want So if we want to we can also do when the age is between 27 and 30 Then we want to return young and Anyone else we're gonna call a baby So now we have Ryan Howard as the baby anyone between 27 and 30 they're considered young and Anyone over the age of 30 is old Now something to note is that the very first condition that is met is going to be returned So if there are multiple conditions that meet the criteria only the very first one is going to be returned and let's Demonstrate that real quick. So if the age equals 38 then return Stanley because that is Stanley And let's execute this real quick So right here, I'm specifying that if it's 38 it should return Stanley But he is right here and it still says old and that's because this condition was already met now if we were to put this right here It should work correctly And let's try it out So now because this condition is met first it is going to return Stanley down here So now let's get into our first use case. Let's start off by copying this and Then commenting it out. I only did that because I don't want to rewrite it because I'm lazy Let's get rid of that and let's look at this real quick. We are going to join on another table that we have really fast That's going to be sequel tutorial if you've watched my other videos than you Know this table and we're gonna do that on employee demographics employee ID is equal to employee Salary that employee ID Okay, so let's just look at everything in these tables really quick Now we are gonna be focusing on the job title in the salary column, but we want their first name and last name as well So let's start building that out. Let's do first name last name Job title and salary and Let's look at this really quick. So now we have our employees and here is the situation We had a fantastic year this year selling paper and corporate has allowed Michael Scott to give out a yearly raise to every single employee But not every employee is going to get the same raise because our salesmen are Genuinely the people who made us our money and they're gonna get the biggest raises Well, other people really are gonna get that big of a raise So now let's go through and create a case statement to calculate what their salary will be after they get their raise So let's start off by saying case and when and We want it to say when job title is equal to salesmen So when they are a salesman, what do we want to happen? So this is where the calculation occurs So we're gonna take their salary and Then we're gonna add their salary Times how much their raise is gonna be so the salesman did really really well and we want to give them a 10% raise this year now when their job title is equal to Accountant Then and we'll take their salary We will give them Let's give them a 5% raise still very generous there we go and When the job title is equal to HR then It's gonna be the salary plus the salary Times and then we're gonna do point zero zero one Alright, and else we are just gonna do salary plus salary oops Let's do a parentheses Times and let's just give everyone else a 3% raise and then we'll write end now. Let's take a look at our results So here's what we have so far. We have our first name our last name our job title and our salary That is our current salary and then we're gonna have our salary after we get our raise So I'm gonna actually write that up here. So let's do as salary after raise And let's execute that So let's look at these raises really quick So we have 45,000 and since he is a salesman he gets a 10% raise, which is a raise of $4,500 so 45,000 plus 4,500 is $49,500 and as you can see down here, we have HR who is making $50,000 is now he's making $50,000 and five cents. So everybody got a raise So that is our case statement. I hope that was helpful I find myself using the case statement a lot when I'm wanting to categorize things or label things and that's kind of what we did in the First example and you can even do calculations like we did in this use case. So I hope that was helpful Thank you guys so much for watching. I really appreciate it If you learned anything from this video, be sure to like and subscribe below and I'll see you in the next video