Intermediate SQL Tutorial | Case Statement | Use Cases

Key Takeaways

what is going on everybody my name is Alex Freiburg and today we're gonna be walking through K statements and sequel a case statement allows you to specify condition and then it also allows you to specify what you want returned when that condition is met so we're gonna be using this employee demographics table that we're looking at right here we're gonna walk through the syntax of how to create a cait 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 gonna be using this age column so we actually want the age to be in there so let's specify where age is not know 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 I'll 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 will 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 win 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 where they call a so now we have Ryan Howard as the baby anyone between 27 and 30 they're considered a 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 cuz I'm lazy let's get rid of that and let's look at this real quick we are gonna join on another table that we have really fast and that's gonna be sequel tutorial if you watch my other videos then you know this table and we're gonna do that on employee demographics employee ID is equal to an employee salaried 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 sing employee but not every employee is gonna 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 aren't gonna get into 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 titles 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 it's still very generous there we go and when the job title is equal to H R then it's gonna be the salary plus the salary times and then we're gonna do one all right and else we're just gonna do salary plus salary oops let's do 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 or last name our job title in our salary that is our current salary and then we're gonna have our salary after we get our raise so I'm going to actually write that up here 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 thousand five hundred dollars and as you can see down here we have HR who is making fifty thousand dollars is now he is making fifty thousand dollars 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