This should be a pretty fun guide and it's because we're going to implement something that if you're coming from another programming language, may seem pretty familiar but many people who work with SQL don't actually implement. I think it can be a fun way of exploring what the SQL language can do.

We're going to implement a case statement; this is going to allow us to have conditional logic and allow us to set up scenarios and then be able to build a result set based on those scenarios. I'm also going to walk you through a little bit different syntax for how you can run queries and structure the way that your code looks.

I'm going to say that I want to select, and I want to SELECT the guides_title, so we're going to be working with the guides_title here. Then I notice how I've indented this and it's because when working with Case statements it's important to be able to structure the code so that it's readable.

You can designate where the actual control flow logic is. Technically none of this is important because remember working with SQL you could technically put this on one line, but that would be a bad idea.

The reason being is because it'd be difficult to read and also to debug if things don't work right. With a case statement, the syntax is just to put "case".

In other words, we're setting up cases, or we're setting up scenarios. We're going to set up some ways that we can just check the values and then return different results based off of those.

So on the next line, I'm going to indent again and each one of our scenarios I'm going to place here indented a couple of spaces. I'm going to say WHEN guides_revenue is greater than 1000 THEN I'm going to give it a label of "Best Seller".

So essentially what I want to do here is I want to be able to generate a report and say these are all of the guides that have generated over a thousand dollars in revenue. So these are considered our best sellers.
Then I want to say WHEN guides_revenue is less than $600 THEN, I'm going to say not even displayed.

Now, this obviously is not going to change whether it displays it or not. This is simply assigning labels for a report. Then I'm going to give, kind of a catchall in this is going to say for everything else. So if it is below a thousand and if it is above 600. I just want to say "Average Sellers", and then right here I'm going to say END.

If you remember back to when the previous guides were, we talked about alias's I'm going to say END AS 'status'.

This is setting up a status here, and then we just have to define the table we're calling which is going to be guides.

Let's run this see if it works and it did.

What we have here is a result set and a report that shows each one of the titles, and notice here that we've generated a full set of statuses. Now, these statuses are not in the database.

This is actually inside of our queries, so we were able to go in based on some conditional criteria such as being over 1000, less than 600, or between those two. We can assign a status right here. If you're coming from a programming language or you work with frameworks such as rails or .NET.

Many times you would do this kind of work inside of the application logic itself, but sequel also can manage this. So say that you don't need to create a web application you just need to be able to create reports off of data in a database.

You can do that right here with SQL, and this is really a powerful tool for being able to implement conditional logic based off of criteria. You should now have a good idea on how to implement conditional logic in a case statement in SQL.

Code

USE devcamp_sql_course_schema;

SELECT
  guides_title,
  CASE
    WHEN guides_revenue > 1000 THEN 'Best Seller'
    WHEN guides_revenue < 600  THEN 'Not Displayed'
    ELSE 'Average Sellers'
  END AS 'status'
FROM guides;