SolveWithSQL

SQL Beginner Challenge 11: Conditional Logic with CASE

Difficulty: Beginner
Estimated time: 15–20 minutes
SQL concepts: CASE, conditional logic, derived columns
Goal: Add human-readable logic to query results.

The Scenario

A product manager looks at your product list and says:

“Can we label products as Cheap, Standard, or Expensive based on price?”

Instead of doing this logic in a spreadsheet or app code, you’ll do it directly in SQL using a CASE expression.

Database Table

`products`

column_name	type	description
product_id	INTEGER	Unique product ID
name	TEXT	Product name
category	TEXT	Product category
price	DECIMAL	Product price

Sample Data

product_id	name	category	price
101	Wireless Mouse	Accessories	24.99
102	Mechanical Keyboard	Accessories	89.00
103	27-inch Monitor	Displays	229.99
104	USB-C Hub	Accessories	34.50
105	Laptop Stand	Workspace	39.99

Your Task

Write a SQL query that returns:

Product name
price
A new column called price_category

Pricing rules

Price below 50 → Cheap
Price 50 to 150 → Standard
Price above 150 → Expensive

Expected Output

name	price	price_category
Wireless Mouse	24.99	Cheap
Mechanical Keyboard	89.00	Standard
27-inch Monitor	229.99	Expensive
USB-C Hub	34.50	Cheap
Laptop Stand	39.99	Cheap

Constraints

Use a CASE expression
Name the derived column price_category
Do not use SELECT *

Hint (Optional)

A CASE expression works like an IF / ELSE statement:

CASE WHEN condition THEN result WHEN condition THEN result ELSE result END

SELECT
name,
price,
CASE
WHEN price < 50 THEN ‘Cheap’
WHEN price BETWEEN 50 AND 150 THEN ‘Standard’
ELSE ‘Expensive’
END AS price_category
FROM products;

Explanation

CASE evaluates conditions top to bottom.
The first matching condition is used.
ELSE acts as a fallback when no condition matches.
This logic runs inside the database, not in application code.

Common Mistakes

Forgetting the END keyword
Overlapping conditions
Missing an ELSE
- This can result in NULL values.

Optional Extension (Mini Bonus)

Try:

Add a label like Very Cheap for prices below 25.
Combine CASE with GROUP BY to count products per price category.
Use CASE to flag products as In Budget / Over Budget.

Next Challenge

Beginner Challenge #12: Handling NULL Values with COALESCE

🔗 View reference solution on GitHub
(After you’ve tried the challenge)

Want more practical SQL challenges?
Subscribe to the Solve With SQL newsletter and get new problems delivered to your inbox.