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

Need a 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

Need a 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

Need a 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

Need a hint?

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