Solution: Categorize tracks by price - SQL Tutorial

(upbeat music) - [Instructor] In this challenge, you've been tasked by the management at Red30 Tech to produce a query which selects track names, composers, unit prices, and categorizes each track based on its price. Let's take a look at our response, starting with our comment block, which we've specified the name, create date, as well as a short description of what our query is doing. Now, going down to the from clause where we are specifying the table we're interested in getting our data from, which is the track table in this case. Now up to the SELECT clause, we are now going to specify the fields of the track table that we're interested in seeing in our result. And those fields are starting with name, which we've aliased as Track Name. The Track Name alias is surrounded with double quotes because of the space between the word Track and Name. Next we have a comma separating our next field, which is Composer. The third field we're interested in seeing is UnitPrice, and we're aliasing this field with the word Price. Now, we're not putting quotes around this because there's no space in this particular alias. Now onto our case statement, which is referred to as a calculated column. A way to dissect this case statement is start with the beginning, first, the keyword case, and go down to the end with the keyword end. And we are aliasing this calculated column as PriceCategory. With the start and the end done, let's now go one line up from the end and specify the else clause, which is the condition which holds true when all others have failed. And in our case we're saying else exclusive. Now starting with the first condition, which is when UnitPrice is less than or equal to 99 cent, then Budget, which is when this particular value within the UnitPrice field holds true, that is, it's less than or equal to 99 cent, then label these rows as Budget. Continuing with this logic in mind, go down to our next condition, which is when the UnitPrice is greater than 99 cents, and UnitPrice is less than or equal to $1, 49 cents, then label the records that hold true to this condition as Regular. Third condition is stating that when UnitPrice is greater than $1.49, and UnitPrice is less than or equal to $1.99, then label these records as Premium. And as we stated before, we said, when all those conditions don't hold true, label those records as Exclusive. Going down to the ORDER BY clause, we've now specified that we want to see our results ordered by UnitPrice in ascending order. Now let's take a look at what happens when we click on Test my code. We do see our output and we do see the rows that we are expecting to see, which is Track Name, the COMPOSER, the PRICE, and taking a look at the PRICE category, we can see that the various labels that we have specified in our case statement is being applied in Budget, Regular, Premium, and we do not seem to have any exclusives, but we certainly see that our labels are being applied. So we can go back to the management at Red30 Tech and present them with our report.