Course contentsShow
← Data Science
Lesson 1032 of 2,14526. Advanced SQLFree lesson

CASE in SELECT: Creating Derived Columns

Use CASE statements to create new columns with conditional values based on existing data.

CASE in SELECT: Creating Derived Columns

What You'll Learn

You'll learn how to use CASE expressions in the SELECT clause to create brand-new columns with conditional logic based on existing data.

The Core Idea

Now that you understand CASE expressions (both simple and searched), you can put them to work creating derived columns—columns that don't exist in your original table but are calculated on-the-fly based on conditions you define.

Think of it like adding a custom label or category to each row. Imagine you have a list of students with test scores, but no column for pass/fail status. You can create one using CASE in your SELECT statement.

Creating Conditional Columns

The basic pattern looks like this:

SELECT 
    name,
    score,
    CASE 
        WHEN score >= 90 THEN 'A'
        WHEN score >= 80 THEN 'B'
        WHEN score >= 70 THEN 'C'
        WHEN score >= 60 THEN 'D'
        ELSE 'F'
    END AS letter_grade
FROM students;

The new letter_grade column doesn't exist in students—you're creating it based on the score column. Each row gets its grade calculated independently.

Multiple Derived Columns

You can create several derived columns in one query:

SELECT 
    product_name,
    price,
    CASE 
        WHEN price < 10 THEN 'Budget'
        WHEN price < 50 THEN 'Standard'
        ELSE 'Premium'
    END AS price_tier,
    CASE 
        WHEN stock_quantity = 0 THEN 'Out of Stock'
        WHEN stock_quantity < 10 THEN 'Low Stock'
        ELSE 'In Stock'
    END AS stock_status
FROM products;

Key Takeaway: CASE in SELECT lets you create new, conditional columns that categorize, label, or transform existing data without modifying the underlying table—perfect for reporting and analysis.