Why CASE expressions are needed in SQL - See It in Action

Why CASE expressions are needed

📖 Scenario: You work in a retail store database. You have a table of sales with product names and quantities sold. You want to label each sale as 'Low', 'Medium', or 'High' quantity based on the number sold.

🎯 Goal: Build a SQL query that uses a CASE expression to categorize sales quantities into 'Low', 'Medium', or 'High'.

📋 What You'll Learn

Create a table called sales with columns product (text) and quantity (integer).

Insert these exact rows into sales: ('Apples', 5), ('Bananas', 15), ('Cherries', 25).

Write a SQL query selecting product, quantity, and a new column quantity_label using a CASE expression.

The CASE expression should label quantity as 'Low' if less than 10, 'Medium' if between 10 and 20 inclusive, and 'High' if greater than 20.

💡 Why This Matters

🌍 Real World

Retail and sales databases often need to classify data dynamically, like labeling sales volumes or customer ratings.

💼 Career

Understanding CASE expressions is essential for data analysts and database developers to write flexible and readable SQL queries.

1

Create the sales table and insert data

Create a table called sales with columns product (text) and quantity (integer). Then insert these rows exactly: ('Apples', 5), ('Bananas', 15), ('Cherries', 25).

SQL

-- Create table sales and insert rows
-- Your code here

Need a hint?

Use CREATE TABLE to define the table and INSERT INTO to add rows.

2

Write the SELECT query skeleton

Write a SQL SELECT query that selects product and quantity from the sales table.

SQL

SELECT product, quantity
FROM sales;
-- Your code here

Need a hint?

Use SELECT product, quantity FROM sales to get the basic data.

3

Add the CASE expression to label quantities

Modify the SELECT query to add a new column called quantity_label using a CASE expression. Label quantity as 'Low' if less than 10, 'Medium' if between 10 and 20 inclusive, and 'High' if greater than 20.

SQL

SELECT product, quantity,
  CASE
    WHEN quantity < 10 THEN 'Low'
    WHEN quantity BETWEEN 10 AND 20 THEN 'Medium'
    ELSE 'High'
  END AS quantity_label
FROM sales;
-- Your code here

Need a hint?

Use CASE WHEN ... THEN ... ELSE ... END AS quantity_label to create the labels.

4

Complete the query with proper formatting

Ensure the full SQL query selects product, quantity, and the quantity_label column with the CASE expression exactly as specified.

SQL

-- Verify the query is complete and formatted correctly
-- Your code here

Need a hint?

Check that the query includes all parts and is syntactically correct.