Searched CASE syntax in SQL - Mini Project: Build & Apply

Using Searched CASE Syntax in SQL

📖 Scenario: You work at a small bookstore. You have a table of books with their prices. You want to add a new column that shows a price category for each book: 'Cheap', 'Moderate', or 'Expensive'. This helps customers quickly see which books fit their budget.

🎯 Goal: Create a SQL query that uses the searched CASE syntax to assign a price category to each book based on its price.

📋 What You'll Learn

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

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

Write a SQL query that selects id, title, price, and a new column price_category.

Use searched CASE syntax to assign 'Cheap' if price is less than 15, 'Moderate' if price is between 15 and 40 inclusive, and 'Expensive' if price is greater than 40.

💡 Why This Matters

🌍 Real World

Classifying products or items into categories based on numeric or text conditions is common in business reports and dashboards.

💼 Career

Understanding CASE syntax helps you write flexible SQL queries for data analysis, reporting, and decision-making tasks in many data-related jobs.

1

Create the books table and insert data

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

SQL

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

Need a hint?

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

2

Write the SELECT query skeleton

Write a SQL SELECT statement that selects id, title, and price from the books table. Do not add the CASE expression yet.

SQL

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 SELECT statement for id, title, price
-- Your code here

Need a hint?

Use SELECT to choose columns and FROM books to specify the table.

3

Add the searched CASE expression for price category

Extend the SELECT query to add a new column called price_category using the searched CASE syntax. Assign 'Cheap' if price < 15, 'Moderate' if price BETWEEN 15 AND 40, and 'Expensive' if price > 40.

SQL

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,
-- Add searched CASE expression for price_category here
-- Your code here

Need a hint?

Use CASE with multiple WHEN conditions and END AS price_category.

4

Complete the query with ordering

Add an ORDER BY clause to the query to sort the results by price in ascending order.

SQL

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  40 THEN 'Expensive'
  END AS price_category
FROM books
-- Add ORDER BY price ascending here
-- Your code here

Need a hint?

Use ORDER BY price ASC to sort by price from lowest to highest.