CASE expression in PostgreSQL - Mini Project: Build & Apply

Using CASE Expression in PostgreSQL

📖 Scenario: You work at a bookstore that wants to categorize books by their price range to help customers quickly find affordable or premium books.

🎯 Goal: Create a PostgreSQL query that uses the CASE expression to classify books into price categories: 'Cheap', 'Moderate', and 'Expensive'.

📋 What You'll Learn

Create a table called books with columns id (integer), title (text), and price (numeric).

Insert exactly these three books with their prices: 'Book A' priced 10, 'Book B' priced 25, and 'Book C' priced 50.

Write a SELECT query that uses a CASE expression to add a column price_category with values 'Cheap' if price < 15, 'Moderate' if price between 15 and 30, and 'Expensive' if price > 30.

Order the results by id ascending.

💡 Why This Matters

🌍 Real World

Classifying products or items into categories based on numeric values is common in sales, inventory, and reporting systems.

💼 Career

Understanding CASE expressions helps in writing flexible SQL queries for data analysis and reporting tasks in many database-related jobs.

1

Create the books table and insert data

Create a table called books with columns id as integer, title as text, and price as numeric. Then insert these three rows exactly: (1, 'Book A', 10), (2, 'Book B', 25), and (3, 'Book C', 50).

PostgreSQL

-- Create the books table and insert the three rows
-- Your code here

Need a hint?

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

2

Set up the SELECT query base

Write a SELECT query to get id, title, and price from the books table. Do not add the CASE expression yet.

PostgreSQL

CREATE TABLE books (
    id INTEGER,
    title TEXT,
    price NUMERIC
);

INSERT INTO books (id, title, price) VALUES
(1, 'Book A', 10),
(2, 'Book B', 25),
(3, 'Book C', 50);

-- Write a SELECT query to get id, title, and price from books
-- Your code here

Need a hint?

Use SELECT id, title, price FROM books; to get the basic data.

3

Add CASE expression for price categories

Modify the SELECT query to add a new column called price_category using a CASE expression. It should return 'Cheap' if price < 15, 'Moderate' if price is between 15 and 30 inclusive, and 'Expensive' if price > 30.

PostgreSQL

CREATE TABLE books (
    id INTEGER,
    title TEXT,
    price NUMERIC
);

INSERT INTO books (id, title, price) VALUES
(1, 'Book A', 10),
(2, 'Book B', 25),
(3, 'Book C', 50);

-- Add a CASE expression to create price_category column
SELECT id, title, price,
-- Your code here
FROM books;

Need a hint?

Use CASE WHEN ... THEN ... ELSE ... END AS price_category to classify prices.

4

Order the results by id ascending

Add an ORDER BY id ASC clause to the SELECT query to sort the results by id in ascending order.

PostgreSQL

CREATE TABLE books (
    id INTEGER,
    title TEXT,
    price NUMERIC
);

INSERT INTO books (id, title, price) VALUES
(1, 'Book A', 10),
(2, 'Book B', 25),
(3, 'Book C', 50);

SELECT id, title, price,
    CASE
        WHEN price < 15 THEN 'Cheap'
        WHEN price BETWEEN 15 AND 30 THEN 'Moderate'
        ELSE 'Expensive'
    END AS price_category
FROM books
-- Your code here

Need a hint?

Add ORDER BY id ASC at the end of the query to sort results by id.