CASE in SELECT for computed columns in SQL - Mini Project: Build & Apply

Using CASE in SELECT for Computed Columns

📖 Scenario: You work in a retail store database. You have a table of products with their prices. You want to add a new column that shows if the product is 'Expensive' or 'Affordable' based on its price.

🎯 Goal: Create a SQL query that uses CASE in the SELECT statement to add a computed column called PriceCategory. This column should show 'Expensive' if the price is more than 100, and 'Affordable' otherwise.

📋 What You'll Learn

Create a table called Products with columns ProductID (integer), ProductName (text), and Price (numeric).

Insert exactly three products with these values: (1, 'Laptop', 1200), (2, 'Mouse', 25), (3, 'Keyboard', 75).

Write a SELECT query that uses CASE in the SELECT clause to create a computed column PriceCategory.

The PriceCategory should be 'Expensive' if Price > 100, else 'Affordable'.

💡 Why This Matters

🌍 Real World

Retail stores often need to classify products by price ranges to help with marketing and inventory decisions.

💼 Career

Knowing how to use CASE in SELECT queries is essential for data analysts and database developers to create meaningful reports and computed columns.

1

Create the Products table and insert data

Create a table called Products with columns ProductID (integer), ProductName (text), and Price (numeric). Then insert these three rows exactly: (1, 'Laptop', 1200), (2, 'Mouse', 25), (3, 'Keyboard', 75).

SQL

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

Hint

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

2

Add a price threshold variable

Create a variable or a comment line that sets the price threshold to 100. This will help you remember the limit for expensive products.

SQL

CREATE TABLE Products (
    ProductID INTEGER,
    ProductName TEXT,
    Price NUMERIC
);

INSERT INTO Products (ProductID, ProductName, Price) VALUES
(1, 'Laptop', 1200),
(2, 'Mouse', 25),
(3, 'Keyboard', 75);

-- Set price threshold to 100
-- Your code here

Hint

You can use a comment to note the threshold since SQL does not support variables in all systems.

3

Write the SELECT query with CASE for PriceCategory

Write a SELECT query that selects ProductID, ProductName, Price, and a computed column called PriceCategory. Use CASE in the SELECT clause to set PriceCategory to 'Expensive' if Price > 100, else 'Affordable'.

SQL

CREATE TABLE Products (
    ProductID INTEGER,
    ProductName TEXT,
    Price NUMERIC
);

INSERT INTO Products (ProductID, ProductName, Price) VALUES
(1, 'Laptop', 1200),
(2, 'Mouse', 25),
(3, 'Keyboard', 75);

-- price_threshold = 100

SELECT ProductID, ProductName, Price,
-- Use CASE to create PriceCategory
-- Your code here

Hint

Use CASE WHEN Price > 100 THEN 'Expensive' ELSE 'Affordable' END AS PriceCategory inside the SELECT.

4

Complete the query with ordering

Add an ORDER BY clause to the query to sort the results by Price in descending order.

SQL

CREATE TABLE Products (
    ProductID INTEGER,
    ProductName TEXT,
    Price NUMERIC
);

INSERT INTO Products (ProductID, ProductName, Price) VALUES
(1, 'Laptop', 1200),
(2, 'Mouse', 25),
(3, 'Keyboard', 75);

-- price_threshold = 100

SELECT ProductID, ProductName, Price,
    CASE WHEN Price > 100 THEN 'Expensive' ELSE 'Affordable' END AS PriceCategory
FROM Products
-- Add ORDER BY Price DESC
-- Your code here

Hint

Use ORDER BY Price DESC at the end of the query to sort from highest to lowest price.

Practice

1. What does the CASE statement do in a SQL SELECT query?

easy

A. Creates a new column with values based on conditions

B. Deletes rows that do not meet a condition

C. Joins two tables together

D. Sorts the results in ascending order

Solution

1. Step 1: Understand the purpose of CASE in SELECT

   The CASE statement is used to create new columns by checking conditions and assigning values accordingly.

2. Step 2: Compare with other SQL operations

   Deleting rows is done with DELETE, joining tables with JOIN, and sorting with ORDER BY, not CASE.

3. Final Answer:

   Creates a new column with values based on conditions -> Option A

4. Quick Check:

   CASE = computed column based on conditions [OK]

Hint: CASE creates conditional columns in SELECT queries [OK]

Common Mistakes:

• Thinking CASE deletes rows
• Confusing CASE with JOIN
• Assuming CASE sorts data

2. Which of the following is the correct syntax to use CASE in a SELECT statement?

easy

A. SELECT CASE WHEN score > 50 THEN 'Pass' ELSE 'Fail' END result FROM students

B. SELECT CASE score > 50 THEN 'Pass' ELSE 'Fail' END AS result FROM students;

C. SELECT CASE WHEN score > 50 THEN 'Pass' ELSE 'Fail' AS result FROM students;

D. SELECT CASE WHEN score > 50 THEN 'Pass' ELSE 'Fail' END AS result FROM students;

Solution

1. Step 1: Review correct CASE syntax

   The correct syntax requires WHEN conditions, THEN results, ELSE for default, and END to close, with AS to name the column.

2. Step 2: Identify errors in other options

   A omits AS before result (END result); B misses WHEN after CASE; C misses END before AS result.

3. Final Answer:

   SELECT CASE WHEN score > 50 THEN 'Pass' ELSE 'Fail' END AS result FROM students; -> Option D

4. Quick Check:

   CASE syntax = WHEN...THEN...ELSE...END AS alias [OK]

Hint: Always use WHEN, THEN, ELSE, END, and AS alias [OK]

Common Mistakes:

• Omitting WHEN keyword
• Forgetting END keyword
• Not using AS for alias

3. Given the table employees with column salary, what will this query return?

SELECT name, CASE WHEN salary >= 5000 THEN 'High' ELSE 'Low' END AS salary_level FROM employees;

medium

A. An error because CASE cannot be used in SELECT

B. Only employees with salary ≥ 5000

C. A list of employee names with salary_level showing 'High' if salary ≥ 5000, else 'Low'

D. A list of employee names with salary values doubled

Solution

1. Step 1: Understand the CASE condition

   The CASE checks if salary is 5000 or more, then assigns 'High', otherwise 'Low'.

2. Step 2: Analyze the SELECT output

   The query returns all employees with their name and a new column salary_level showing 'High' or 'Low' accordingly.

3. Final Answer:

   A list of employee names with salary_level showing 'High' if salary ≥ 5000, else 'Low' -> Option C

4. Quick Check:

   CASE creates conditional labels per row [OK]

Hint: CASE labels rows based on condition, does not filter [OK]

Common Mistakes:

• Thinking CASE filters rows
• Expecting numeric salary changes
• Assuming syntax error

4. Identify the error in this SQL query:

SELECT name, CASE salary > 10000 THEN 'Rich' ELSE 'Average' END AS status FROM users;

medium

A. Missing WHEN keyword after CASE

B. Missing END keyword

C. Missing ELSE keyword

D. Missing AS keyword for alias

Solution

1. Step 1: Check CASE syntax

   The CASE statement must have WHEN before the condition. Here, WHEN is missing.

2. Step 2: Verify other parts

   END and ELSE are present, AS is used correctly, so no errors there.

3. Final Answer:

   Missing WHEN keyword after CASE -> Option A

4. Quick Check:

   CASE requires WHEN before condition [OK]

Hint: Always write WHEN after CASE before condition [OK]

Common Mistakes:

• Omitting WHEN keyword
• Confusing ELSE with END
• Forgetting AS alias

5. You want to classify products by price into 'Cheap', 'Moderate', and 'Expensive' using CASE in SELECT. Which query correctly implements this?

hard

A. SELECT product_name, CASE price < 50 THEN 'Cheap' WHEN price BETWEEN 50 AND 100 THEN 'Moderate' ELSE 'Expensive' END price_category FROM products;

B. SELECT product_name, CASE WHEN price < 50 THEN 'Cheap' WHEN price BETWEEN 50 AND 100 THEN 'Moderate' ELSE 'Expensive' END AS price_category FROM products;

C. SELECT product_name, CASE WHEN price < 50 THEN 'Cheap' ELSE WHEN price BETWEEN 50 AND 100 THEN 'Moderate' ELSE 'Expensive' END AS price_category FROM products;

D. SELECT product_name, CASE WHEN price < 50 THEN 'Cheap' WHEN price > 100 THEN 'Expensive' ELSE 'Expensive' END AS price_category FROM products;

Solution

1. Step 1: Check correct CASE syntax with multiple WHEN

   SELECT product_name, CASE WHEN price < 50 THEN 'Cheap' WHEN price BETWEEN 50 AND 100 THEN 'Moderate' ELSE 'Expensive' END AS price_category FROM products; correctly uses multiple WHEN conditions with THEN, ELSE, and END, and aliases the column.

2. Step 2: Analyze logic correctness

   SELECT product_name, CASE WHEN price < 50 THEN 'Cheap' WHEN price BETWEEN 50 AND 100 THEN 'Moderate' ELSE 'Expensive' END AS price_category FROM products; classifies prices below 50 as 'Cheap', between 50 and 100 as 'Moderate', else 'Expensive', which matches the requirement.

3. Final Answer:

   SELECT product_name, CASE WHEN price < 50 THEN 'Cheap' WHEN price BETWEEN 50 AND 100 THEN 'Moderate' ELSE 'Expensive' END AS price_category FROM products; -> Option B

4. Quick Check:

   Multiple WHEN conditions with ELSE classify ranges [OK]

Hint: Use multiple WHEN for ranges, ELSE for default [OK]

Common Mistakes:

• Omitting WHEN keywords
• Misplacing ELSE before WHEN
• Not using AS for alias