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

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

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

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

Hint

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

Practice

1. What does the searched CASE expression in SQL do?

easy

A. Deletes rows from a table

B. Joins two tables together

C. Creates a new table

D. Returns different results based on multiple conditions

Solution

1. Step 1: Understand the purpose of searched CASE

   The searched CASE expression evaluates multiple conditions and returns a result for the first true condition.

2. Step 2: Compare with other SQL operations

   Unlike joins, deletes, or table creation, CASE is used to return values conditionally within queries.

3. Final Answer:

   Returns different results based on multiple conditions -> Option D

4. Quick Check:

   Searched CASE = conditional results [OK]

Hint: Think of CASE as SQL's if-else for conditions [OK]

Common Mistakes:

• Confusing CASE with JOIN operations
• Thinking CASE modifies tables
• Assuming CASE deletes data

2. Which of the following is the correct syntax for a searched CASE expression in SQL?

easy

A. CASE WHEN condition THEN result ELSE default END

B. CASE condition WHEN THEN result ELSE END

C. CASE THEN condition WHEN result ELSE END

D. CASE WHEN THEN condition ELSE result END

Solution

1. Step 1: Recall the searched CASE syntax

   The correct syntax starts with CASE, followed by WHEN condition THEN result, optional ELSE, and ends with END.

2. Step 2: Check each option

   Only CASE WHEN condition THEN result ELSE default END follows the correct order: CASE WHEN condition THEN result ELSE default END.

3. Final Answer:

   CASE WHEN condition THEN result ELSE default END -> Option A

4. Quick Check:

   Syntax = CASE WHEN ... THEN ... ELSE ... END [OK]

Hint: Remember: WHEN condition THEN result, ELSE optional, END last [OK]

Common Mistakes:

• Mixing order of WHEN and THEN
• Omitting END keyword
• Placing ELSE before THEN

3. Given the table Employees with column Salary, what will this query return?

SELECT Salary, CASE WHEN Salary > 5000 THEN 'High' WHEN Salary > 3000 THEN 'Medium' ELSE 'Low' END AS SalaryLevel FROM Employees;

medium

A. Returns only salaries greater than 5000

B. Shows an error because ELSE is missing

C. Labels each salary as 'High', 'Medium', or 'Low' based on value

D. Labels all salaries as 'Low'

Solution

1. Step 1: Analyze the CASE conditions

   The CASE checks if Salary > 5000, then returns 'High'; else if Salary > 3000, returns 'Medium'; otherwise 'Low'.

2. Step 2: Understand the query output

   Each row shows Salary and its label based on these conditions, so salaries are categorized correctly.

3. Final Answer:

   Labels each salary as 'High', 'Medium', or 'Low' based on value -> Option C

4. Quick Check:

   CASE categorizes salaries correctly [OK]

Hint: Read CASE conditions top to bottom, first true wins [OK]

Common Mistakes:

• Thinking ELSE is required and missing causes error
• Assuming query filters rows instead of labeling
• Ignoring order of WHEN conditions

4. Identify the error in this searched CASE expression:

SELECT Name, CASE WHEN Age > 18 THEN 'Adult' WHEN Age < 18 THEN 'Minor' END AS AgeGroup FROM People;

medium

A. Incorrect use of THEN keyword

B. No error, query is correct

C. No END keyword to close CASE

D. Missing ELSE clause for other cases

Solution

1. Step 1: Check CASE syntax completeness

   The CASE has WHEN conditions, THEN results, and ends with END keyword properly.

2. Step 2: Consider ELSE clause necessity

   ELSE is optional; if no ELSE, NULL is returned for unmatched cases, which is valid.

3. Final Answer:

   No error, query is correct -> Option B

4. Quick Check:

   ELSE optional, END required, syntax correct [OK]

Hint: ELSE is optional; END is mandatory [OK]

Common Mistakes:

• Assuming ELSE is always required
• Forgetting END keyword
• Misplacing THEN keyword

5. You want to classify products by price range using searched CASE. Which query correctly assigns 'Cheap' for prices below 50, 'Affordable' for prices between 50 and 150, and 'Expensive' for prices above 150?

hard

A. SELECT ProductName, CASE WHEN Price < 50 THEN 'Cheap' WHEN Price BETWEEN 50 AND 150 THEN 'Affordable' ELSE 'Expensive' END AS PriceRange FROM Products;

B. SELECT ProductName, CASE WHEN Price > 150 THEN 'Cheap' WHEN Price BETWEEN 50 AND 150 THEN 'Affordable' ELSE 'Expensive' END AS PriceRange FROM Products;

C. SELECT ProductName, CASE WHEN Price < 50 THEN 'Cheap' WHEN Price > 150 THEN 'Affordable' ELSE 'Expensive' END AS PriceRange FROM Products;

D. SELECT ProductName, CASE WHEN Price BETWEEN 50 AND 150 THEN 'Cheap' WHEN Price < 50 THEN 'Affordable' ELSE 'Expensive' END AS PriceRange FROM Products;

Solution

1. Step 1: Match conditions to price ranges

   Price < 50 should be 'Cheap', 50 to 150 'Affordable', above 150 'Expensive'.

2. Step 2: Verify each option's logic

   SELECT ProductName, CASE WHEN Price < 50 THEN 'Cheap' WHEN Price BETWEEN 50 AND 150 THEN 'Affordable' ELSE 'Expensive' END AS PriceRange FROM Products; correctly orders conditions and labels prices as required. Others mislabel or misorder conditions.

3. Final Answer:

   SELECT ProductName, CASE WHEN Price < 50 THEN 'Cheap' WHEN Price BETWEEN 50 AND 150 THEN 'Affordable' ELSE 'Expensive' END AS PriceRange FROM Products; -> Option A

4. Quick Check:

   Conditions match price ranges correctly [OK]

Hint: Order CASE conditions from smallest to largest range [OK]

Common Mistakes:

• Mixing condition order causing wrong labels
• Using wrong comparison operators
• Misplacing BETWEEN ranges