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

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

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

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

Need a hint?

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