Nested CASE expressions in SQL - Mini Project: Build & Apply

Nested CASE Expressions in SQL

📖 Scenario: You work in a retail store database. You want to classify sales transactions based on the amount and the payment method. This helps the store understand customer behavior better.

🎯 Goal: Build an SQL query that uses nested CASE expressions to categorize sales into different groups based on amount and payment_method.

📋 What You'll Learn

Create a table called sales with columns id (integer), amount (decimal), and payment_method (text).

Insert exactly three rows into sales with these values: (1, 50, 'cash'), (2, 200, 'card'), (3, 120, 'cash').

Write a SELECT query that uses a nested CASE expression to classify each sale into categories:

'Small Cash' if amount < 100 and payment_method is 'cash',

'Large Cash' if amount >= 100 and payment_method is 'cash',

'Card Payment' if payment_method is 'card',

Otherwise, classify as 'Other'.

💡 Why This Matters

🌍 Real World

Retail stores often classify sales transactions to analyze customer payment preferences and spending habits.

💼 Career

Understanding nested CASE expressions helps database analysts and developers write complex conditional queries for reporting and data transformation.

1

Create the sales table and insert data

Write SQL statements to create a table called sales with columns id (integer), amount (decimal), and payment_method (text). Then insert these exact rows: (1, 50, 'cash'), (2, 200, 'card'), and (3, 120, 'cash').

SQL

-- Create the sales table and insert data
-- Your code here

Need a hint?

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

2

Add a SELECT statement to retrieve all sales

Write a SQL SELECT statement to retrieve all columns from the sales table.

SQL

CREATE TABLE sales (
  id INTEGER,
  amount DECIMAL,
  payment_method TEXT
);

INSERT INTO sales (id, amount, payment_method) VALUES
  (1, 50, 'cash'),
  (2, 200, 'card'),
  (3, 120, 'cash');

-- Select all columns from sales
-- Your code here

Need a hint?

Use SELECT * FROM sales; to get all rows and columns.

3

Write a nested CASE expression to classify sales

Write a SQL SELECT statement that selects id, amount, payment_method, and a new column called category. Use a nested CASE expression inside the SELECT to classify each sale as follows: if payment_method is 'cash', then check if amount is less than 100 to label it 'Small Cash', else label it 'Large Cash'. If payment_method is 'card', label it 'Card Payment'. Otherwise, label it 'Other'.

SQL

CREATE TABLE sales (
  id INTEGER,
  amount DECIMAL,
  payment_method TEXT
);

INSERT INTO sales (id, amount, payment_method) VALUES
  (1, 50, 'cash'),
  (2, 200, 'card'),
  (3, 120, 'cash');

-- Select id, amount, payment_method, and category using nested CASE
-- Your code here

Need a hint?

Use a CASE inside another CASE to check payment_method first, then amount.

4

Complete the query with proper formatting

Ensure the final SQL query includes the nested CASE expression with correct indentation and aliases the classification column as category. The query should select id, amount, payment_method, and category from the sales table.

SQL

CREATE TABLE sales (
  id INTEGER,
  amount DECIMAL,
  payment_method TEXT
);

INSERT INTO sales (id, amount, payment_method) VALUES
  (1, 50, 'cash'),
  (2, 200, 'card'),
  (3, 120, 'cash');

-- Finalize the query with proper formatting and alias
-- Your code here

Need a hint?

Check your query for correct CASE nesting and alias category.