CASE WHEN expression in MySQL - Mini Project: Build & Apply

Using CASE WHEN Expression in MySQL

📖 Scenario: You work in a retail store database. You have a table of products with their prices. You want to categorize products as 'Cheap', 'Moderate', or 'Expensive' based on their price.

🎯 Goal: Create a MySQL query that uses the CASE WHEN expression to add a new column called price_category which labels each product as 'Cheap' if the price is less than 50, 'Moderate' if the price is between 50 and 150, and 'Expensive' if the price is above 150.

📋 What You'll Learn

Create a table called products with columns id, name, and price.

Insert exactly three products with prices 30, 100, and 200.

Write a SELECT query that uses CASE WHEN to categorize prices.

The output must include id, name, price, and the new price_category column.

💡 Why This Matters

🌍 Real World

Categorizing products by price helps businesses analyze inventory and pricing strategies easily.

💼 Career

Using CASE WHEN expressions is a common skill for database querying and reporting in many data-related jobs.

1

Create the products table and insert data

Create a table called products with columns id (integer), name (varchar 50), and price (integer). Then insert these three rows exactly: (1, 'Pen', 30), (2, 'Notebook', 100), and (3, 'Backpack', 200).

MySQL

-- Create the products table and insert the three products
-- Your code here

Need a hint?

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

2

Write the basic SELECT query

Write a SELECT query to get id, name, and price from the products table.

MySQL

CREATE TABLE products (
  id INT,
  name VARCHAR(50),
  price INT
);

INSERT INTO products (id, name, price) VALUES
(1, 'Pen', 30),
(2, 'Notebook', 100),
(3, 'Backpack', 200);

-- Write SELECT query to get id, name, price
-- Your code here

Need a hint?

Use SELECT id, name, price FROM products; to get the data.

3

Add CASE WHEN expression for price_category

Modify the SELECT query to add a new column called price_category using CASE WHEN. Label products as 'Cheap' if price < 50, 'Moderate' if price BETWEEN 50 AND 150, and 'Expensive' if price > 150.

MySQL

CREATE TABLE products (
  id INT,
  name VARCHAR(50),
  price INT
);

INSERT INTO products (id, name, price) VALUES
(1, 'Pen', 30),
(2, 'Notebook', 100),
(3, 'Backpack', 200);

-- Add CASE WHEN expression to categorize price
SELECT id, name, price, 
-- Your code here
FROM products;

Need a hint?

Use CASE WHEN ... THEN ... ELSE ... END AS price_category inside the SELECT.

4

Complete the query with proper formatting

Ensure the final SELECT query includes id, name, price, and the price_category column using the CASE WHEN expression exactly as specified.

MySQL

CREATE TABLE products (
  id INT,
  name VARCHAR(50),
  price INT
);

INSERT INTO products (id, name, price) VALUES
(1, 'Pen', 30),
(2, 'Notebook', 100),
(3, 'Backpack', 200);

-- Finalize the SELECT query with all columns and CASE WHEN expression
SELECT id, name, price, 
  CASE
    WHEN price < 50 THEN 'Cheap'
    WHEN price BETWEEN 50 AND 150 THEN 'Moderate'
    ELSE 'Expensive'
  END AS price_category
FROM products;

Need a hint?

Make sure the SELECT query is complete and formatted as shown.