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

Simple CASE Syntax in SQL

📖 Scenario: You work at a small bookstore. You have a table of books with their genre codes. You want to create a report that shows the genre name instead of the code.

🎯 Goal: Build an SQL query using the CASE statement to convert genre codes into readable genre names.

📋 What You'll Learn

Create a table called books with columns id, title, and genre_code.

Insert 5 rows with specific genre codes.

Write a query using CASE to display the genre name based on genre_code.

Include the book id and title in the output.

💡 Why This Matters

🌍 Real World

Booksellers and many businesses use CASE statements to convert codes into readable labels in reports.

💼 Career

Understanding CASE syntax is essential for writing clear, readable SQL queries in data analysis and reporting jobs.

1

Create the books table and insert data

Create a table called books with columns id (integer), title (text), and genre_code (text). Then insert these rows exactly: (1, 'The Hobbit', 'FAN'), (2, '1984', 'SCI'), (3, 'Pride and Prejudice', 'ROM'), (4, 'The Da Vinci Code', 'THR'), (5, 'The Cat in the Hat', 'CHI').

SQL

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

Hint

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

2

Set up the genre code to name mapping

Create a query that selects id, title, and a new column called genre_name which will use a CASE statement to convert genre_code to genre names. Start by writing the SELECT statement and the CASE keyword.

SQL

CREATE TABLE books (
  id INTEGER,
  title TEXT,
  genre_code TEXT
);

INSERT INTO books (id, title, genre_code) VALUES
  (1, 'The Hobbit', 'FAN'),
  (2, '1984', 'SCI'),
  (3, 'Pride and Prejudice', 'ROM'),
  (4, 'The Da Vinci Code', 'THR'),
  (5, 'The Cat in the Hat', 'CHI');

SELECT id, title, CASE
-- Your code here

Hint

Start your query with SELECT id, title, CASE to begin the CASE statement.

3

Complete the CASE statement with genre mappings

Complete the CASE statement by checking genre_code and returning the genre names: 'FAN' = 'Fantasy', 'SCI' = 'Science Fiction', 'ROM' = 'Romance', 'THR' = 'Thriller', 'CHI' = 'Children'. End the CASE with END AS genre_name and select from books.

SQL

CREATE TABLE books (
  id INTEGER,
  title TEXT,
  genre_code TEXT
);

INSERT INTO books (id, title, genre_code) VALUES
  (1, 'The Hobbit', 'FAN'),
  (2, '1984', 'SCI'),
  (3, 'Pride and Prejudice', 'ROM'),
  (4, 'The Da Vinci Code', 'THR'),
  (5, 'The Cat in the Hat', 'CHI');

SELECT id, title, CASE
  -- Add WHEN clauses here
  -- Your code here

Hint

Use WHEN 'code' THEN 'name' for each genre code and finish with END AS genre_name.

4

Add ordering to the query

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

SQL

CREATE TABLE books (
  id INTEGER,
  title TEXT,
  genre_code TEXT
);

INSERT INTO books (id, title, genre_code) VALUES
  (1, 'The Hobbit', 'FAN'),
  (2, '1984', 'SCI'),
  (3, 'Pride and Prejudice', 'ROM'),
  (4, 'The Da Vinci Code', 'THR'),
  (5, 'The Cat in the Hat', 'CHI');

SELECT id, title, CASE genre_code
  WHEN 'FAN' THEN 'Fantasy'
  WHEN 'SCI' THEN 'Science Fiction'
  WHEN 'ROM' THEN 'Romance'
  WHEN 'THR' THEN 'Thriller'
  WHEN 'CHI' THEN 'Children'
END AS genre_name
FROM books
-- Your code here

Hint

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

Practice

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

easy

A. It loops through rows and updates values conditionally.

B. It compares one expression to multiple values and returns a result for the first match.

C. It creates a new table based on conditions.

D. It deletes rows that match a condition.

Solution

1. Step 1: Understand the purpose of Simple CASE

   Simple CASE compares one expression against multiple possible values.

2. Step 2: Identify the result behavior

   It returns the result for the first matching value it finds.

3. Final Answer:

   It compares one expression to multiple values and returns a result for the first match. -> Option B

4. Quick Check:

   Simple CASE = compare expression to values [OK]

Hint: Simple CASE matches one value to many options [OK]

Common Mistakes:

• Thinking it loops through rows
• Confusing with IF statements
• Assuming it creates or deletes tables

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

easy

A. CASE expression WHEN value THEN result ELSE default END

B. IF expression THEN result ELSE default END

C. CASE WHEN expression THEN result END

D. SWITCH expression WHEN value THEN result END

Solution

1. Step 1: Recall Simple CASE syntax structure

   The correct syntax is: CASE expression WHEN value THEN result ELSE default END.

2. Step 2: Compare options

   CASE expression WHEN value THEN result ELSE default END matches this syntax exactly, others use incorrect keywords or structure.

3. Final Answer:

   CASE expression WHEN value THEN result ELSE default END -> Option A

4. Quick Check:

   Simple CASE syntax = CASE expr WHEN val THEN res ELSE def END [OK]

Hint: Simple CASE uses 'CASE expr WHEN val THEN res' format [OK]

Common Mistakes:

• Using WHEN without expression after CASE
• Confusing with IF or SWITCH syntax
• Omitting END keyword

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

SELECT EmployeeName,
 CASE DepartmentID
   WHEN 1 THEN 'HR'
   WHEN 2 THEN 'Sales'
   ELSE 'Other'
 END AS DeptName
FROM Employees;

medium

A. Employee names with department names 'HR', 'Sales', or 'Other' based on DepartmentID.

B. Only employees from HR and Sales departments.

C. An error because ELSE is missing.

D. Employee names with numeric DepartmentID values.

Solution

1. Step 1: Understand the CASE expression in the query

   The CASE checks DepartmentID: 1 returns 'HR', 2 returns 'Sales', others return 'Other'.

2. Step 2: Analyze the SELECT output

   The query returns EmployeeName and DeptName with the mapped department names or 'Other'.

3. Final Answer:

   Employee names with department names 'HR', 'Sales', or 'Other' based on DepartmentID. -> Option A

4. Quick Check:

   CASE maps DepartmentID to names [OK]

Hint: CASE returns first matching value or ELSE default [OK]

Common Mistakes:

• Thinking ELSE is required (it's optional)
• Assuming only HR and Sales appear
• Expecting numeric DepartmentID instead of names

4. Identify the error in this Simple CASE query:

SELECT ProductName,
 CASE ProductCategory
   WHEN 'A' THEN 'Category A'
   WHEN 'B' 'Category B'
   ELSE 'Other'
 END AS CategoryName
FROM Products;

medium

A. CASE expression should be inside parentheses.

B. Missing END keyword at the end.

C. Incorrect use of ELSE keyword.

D. Missing THEN keyword after WHEN 'B'.

Solution

1. Step 1: Review each WHEN clause syntax

   WHEN clauses must have THEN keyword before the result. The second WHEN is missing THEN.

2. Step 2: Check other parts of the query

   END keyword is present, ELSE is correctly used, parentheses are not required around CASE expression.

3. Final Answer:

   Missing THEN keyword after WHEN 'B'. -> Option D

4. Quick Check:

   WHEN must be followed by THEN [OK]

Hint: Each WHEN must be followed by THEN [OK]

Common Mistakes:

• Forgetting THEN after WHEN
• Thinking END is optional
• Adding unnecessary parentheses

5. You want to categorize sales amounts into 'Low', 'Medium', and 'High' using Simple CASE. Which query correctly assigns:
- 'Low' for 0
- 'Medium' for 1
- 'High' for 2 or more (default)?

SELECT SaleID,
 CASE SaleAmount
   WHEN 0 THEN 'Low'
   WHEN 1 THEN 'Medium'
   ELSE 'High'
 END AS SaleCategory
FROM Sales;

hard

A. Fails because ELSE cannot be used for multiple values.

B. Incorrect because Simple CASE cannot handle ranges like 2 or more.

C. Correctly categorizes sales as described.

D. Syntax error due to missing WHEN for 2.

Solution

1. Step 1: Understand Simple CASE limitations

   Simple CASE matches exact values; ELSE handles all other cases.

2. Step 2: Analyze the query logic

   WHEN 0 returns 'Low', WHEN 1 returns 'Medium', ELSE covers 2 or more as 'High'.

3. Final Answer:

   Correctly categorizes sales as described. -> Option C

4. Quick Check:

   ELSE covers all unmatched values [OK]

Hint: Use ELSE for all other values in Simple CASE [OK]

Common Mistakes:

• Thinking ELSE can't cover multiple values
• Trying to use ranges in Simple CASE
• Expecting WHEN for every possible value