[SQL Basic] Let’s designate a value based on the rental duration. — SQL CASE
#12. Marketing/Data Analysis for Beginners
Last time we used GROUP BY and HAVING to learn how to group and condition data in a particular column. Now let’s use a query called CASE to figure out how to assign values to data that corresponds to a specific condition.
#Glossary:
📌Lesson 3: SELECT / FROM / WHERE
📌Lesson 4: ORDER BY
📌Lesson 5: AND / OR / IN
📌Lesson 6: LIKE
📌Lesson 7: AS /DISTINCT / IS NULL
📌Lesson 9: COUNT / SUM
📌Lesson 10: AVG / MAX / MIN
📌Lesson 11: GROUP BY / HAVING
🆕 🌟
📌CASE: set a condition and return values depending on the condition
#Table of Contents
Query 1. In the customer table, let’s specify which customers are active and inactive.
SELECT customer_id, active, CASE WHEN active=1 THEN ‘ACTIVE’ ELSE ‘INACTIVE’ END as ‘Status’ FROM customer;Query 2. In the film table, let’s designate a value based on the rental duration.
SELECT title, rental_duration, CASE WHEN rental_duration <=3 THEN ‘SHORT’ WHEN rental_duration >=4 THEN ‘MEDIUM’ WHEN rental_duration >=6 THEN ‘LONG’ ELSE NULL END as ‘Duration’ FROM film;#CASE
Q1. In the customer table, let’s specify which customers are active and inactive.
CASE WHEN (condition1) THEN (specified value1)
WHEN (condition2…) THEN (specified value2…)
ELSE (specified value)
END AS (new column name)
Let’s review what information we have in the customer table. You can do this by using your Object Panel (double click on tables and then double click on customer) or by running this query:
SELECT * FROM customer;
We’ve discussed a method on how to pinpoint exactly how many customers are active in Lesson 11. Now let’s learn an additional way to filter this information with the CASE query.
The information in the active column has two results, 0 and 1. If the customers are active, it’s marked with a 1. If they’re not active, they have a 0. The first step is to designate what we want to see, so let’s specify that by writing customer_id, first_name, last_name, and active after the SELECT query. Like this:
SELECT customer_id, first_name, last_name, active FROM customer;
This query will give you a list of all the customers with their active information. Figuring out the 1s and 0s can get a little bothersome, so let’s change that information into text. We’re going to create a new column and rename the values inside so that instead of numbers, we can see Active and Inactive. No, we won’t use AS. We’re going to use CASE.
First, we’re going to set some precedents. We will tell SQL that if the information in the active column is a 1 (WHEN active=1), we want to see the word Active (THEN ‘ACTIVE’). If the information is not a 1 (ELSE), we want to see INACTIVE(‘INACTIVE’) . Here’s how:
SELECT customer_id, first_name, last_name, active, CASE WHEN active=1 THEN ‘ACTIVE’ ELSE ‘INACTIVE’ END FROM customer;
⚠️Remember to put a comma (,) before CASE WHEN here! And we need to let SQL know the conditions for the CASE query are finished by writing END.
We have a cleaner table now, and it’s easier to read this information. But look at that title for the new column… that doesn’t look very good. We can clean it up in the same CASE query by adding that we want to finish up the new column with the name Status (END as ‘Status’). Try this:
SELECT customer_id, first_name, last_name, active, CASE WHEN active=1 THEN ‘ACTIVE’ ELSE ‘INACTIVE’ END as ‘Status’ FROM customer;
💡 Using the CASE query, we learned how to tell SQL that if condition 1 applies (customer has 1 in the active column), then please return the information as specified value 2 (tell us ACTIVE).
Q2. In the film table, let’s designate a value based on the rental duration.
Going back to the film table, let’s use the same structure we just learned to specify the rental period. Let’s start by checking out the rental duration of the movies we have in the film table.
SELECT title, rental_duration FROM film;
As you can see, the duration is displayed numerically (6 days, 3 days, 7 days, etc). We can change this so that the values are displayed as text. Let’s set some parameters first. Let’s say break down the rental periods into short, medium, and long.
Short: Rental period is less than(<) or equal to (=) 3 days. (<=3)
Medium: Rental period is more than 3 days but less than 6 (> 3) and (< 6)
Long: Rental period is more than (>) or equal to (=) 6 days. (>=6)
If they don’t fit any of these categories, they will be considered Null (ELSE NULL). And we’re going to name our new column Duration (END as ‘Duration’)
Here’s the full code:
SELECT title, rental_duration, CASE WHEN rental_duration <=3 THEN ‘SHORT’ WHEN rental_duration > 3 AND rental_duration < 6 THEN ‘MEDIUM’ WHEN rental_duration >=6 THEN ‘LONG’ ELSE NULL END as ‘Duration’ FROM film;
💡 You can take this one more step and plug in ORDER BY rental_duration !
# Practice Time
📰 Using the payment table, organize the data so customers that owe $9.99 and above are considered ‘High’, $4.99 and above is considered ‘Medium’ and $3.99 and below is considered ‘Low’.
🔑 A good place to start is to check out the payment table with SELECT * FROM payment; and get a general idea of all the outstanding debts to our imaginary video rental store.
SELECT * FROM `sakila`.`payment`;
🔑 Show Customer ID and amount to filter the information. You don’t need the date or other IDs. And make sure to put a comma (,) before writing your CASE query.
🔑 Remember our criteria:
High: Equal to or more than 9.99 (>=9.99 )
Medium: The amount between 9.99 and 3.99 (<9.99 and >3.99)
Low: Equal to or less than 3.99 (<=3.99 )
🔑 It might be a good idea to organize it by price~ ORDER BY amount
How did you do? Check out our answer below!
SELECT customer_id, amount, CASE WHEN amount >= 9.99 THEN 'High' WHEN amount <9.99 AND amount >3.99 THEN 'Medium' WHEN amount <= 3.99 THEN 'Low' ELSE NULL END as 'Payment Amount' FROM payment;
💡 You can take this one more step and plug in ORDER BY amount!
# Tutorial Video
# [SQL Basic Series] — Marketing/Data Analysis for Beginners
1. What is SQL? — Database Introduction
2. Downloading SQLGate and Connecting to a Database
3. What kind of data is in the ‘film’ table? — SELECT/FROM/WHERE
4. How to sort film lists by price (rental rate)? — ORDER BY
5. Can I rent a 3 hour movie for $0.99? — AND/OR/IN
6. How can I find a movie that’s thrilling? — LIKE
7. How to rename columns and see unique values? — AS/DISTINCT/IS NULL
8. Let’s see Sample Database tables!
9. What is the total length of all the films added together? — COUNT/SUM
10. What is the average film length? — AVG/MIN/MAX
11. How many movies have the same rating? — GROUP BY/HAVING
12. Let’s designate a value based on the rental duration. — CASE
13. Let’s find out the movie genres by joining tables! — INNER JOIN
14. Let’s check movie rentals by the inventory list! — LEFT/RIGHT JOIN
15. Let’s find customers that have rented movies priced $9.99! — Subquery
16. How to work with Date Functions in SQL — DATE_FORMAT, DATEDIFF
17. How to work with String Functions in SQL — CONCAT, LENGTH, SUBSTR
18. Let’s use some Numeric SQL functions — TRUNCATE, ROUND, MOD
19. Let’s learn some advanced CASE queries!
20. Let’s learn some advanced JOIN queries!