IF-ELSIF-ELSE control flow in PostgreSQL - Mini Project: Build & Apply

Using IF-ELSIF-ELSE Control Flow in PostgreSQL

📖 Scenario: You are managing a small bookstore database. You want to classify books based on their price into categories: 'Cheap', 'Moderate', and 'Expensive'. This will help the store staff quickly identify the price range of each book.

🎯 Goal: Create a PostgreSQL function that uses IF-ELSIF-ELSE control flow to categorize book prices into 'Cheap' (less than 10), 'Moderate' (between 10 and 20), and 'Expensive' (above 20).

📋 What You'll Learn

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

Insert exactly three books with prices 5, 15, and 25.

Create a function called price_category that takes a numeric price and returns a text category using IF-ELSIF-ELSE.

Use the function in a SELECT query to show each book's title and its price category.

💡 Why This Matters

🌍 Real World

Classifying data based on conditions is common in business databases, such as categorizing products by price or customers by age group.

💼 Career

Understanding IF-ELSIF-ELSE in SQL functions helps database developers write dynamic and conditional logic inside the database, improving data processing efficiency.

1

Create the books table and insert data

Create a table called books with columns id (integer), title (text), and price (numeric). Then insert these three books exactly: (1, 'Book A', 5), (2, 'Book B', 15), and (3, 'Book C', 25).

PostgreSQL

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

Need a hint?

Use CREATE TABLE to define the table and INSERT INTO to add the three books with exact values.

2

Create a function price_category with IF-ELSIF-ELSE

Create a PostgreSQL function called price_category that takes a numeric parameter p_price and returns a text. Use IF-ELSIF-ELSE control flow to return 'Cheap' if p_price < 10, 'Moderate' if p_price <= 20, and 'Expensive' otherwise.

PostgreSQL

CREATE TABLE books (
  id INTEGER,
  title TEXT,
  price NUMERIC
);

INSERT INTO books (id, title, price) VALUES
  (1, 'Book A', 5),
  (2, 'Book B', 15),
  (3, 'Book C', 25);

-- Create the price_category function using IF-ELSIF-ELSE
-- Your code here

Need a hint?

Use CREATE OR REPLACE FUNCTION with plpgsql language. Use IF, ELSIF, and ELSE to check the price ranges and return the correct category.

3

Use the function in a SELECT query

Write a SELECT query that shows each book's title and its price category by calling the price_category function on the price column.

PostgreSQL

CREATE TABLE books (
  id INTEGER,
  title TEXT,
  price NUMERIC
);

INSERT INTO books (id, title, price) VALUES
  (1, 'Book A', 5),
  (2, 'Book B', 15),
  (3, 'Book C', 25);

CREATE OR REPLACE FUNCTION price_category(p_price NUMERIC) RETURNS TEXT AS $$
BEGIN
  IF p_price < 10 THEN
    RETURN 'Cheap';
  ELSIF p_price <= 20 THEN
    RETURN 'Moderate';
  ELSE
    RETURN 'Expensive';
  END IF;
END;
$$ LANGUAGE plpgsql;

-- Write a SELECT query to show title and price category
-- Your code here

Need a hint?

Use SELECT with the price_category(price) function to get the category for each book.

4

Add a comment explaining the function

Add a SQL comment above the price_category function explaining that it classifies book prices into categories using IF-ELSIF-ELSE control flow.

PostgreSQL

CREATE TABLE books (
  id INTEGER,
  title TEXT,
  price NUMERIC
);

INSERT INTO books (id, title, price) VALUES
  (1, 'Book A', 5),
  (2, 'Book B', 15),
  (3, 'Book C', 25);

-- Add a comment explaining the price_category function
CREATE OR REPLACE FUNCTION price_category(p_price NUMERIC) RETURNS TEXT AS $$
BEGIN
  IF p_price < 10 THEN
    RETURN 'Cheap';
  ELSIF p_price <= 20 THEN
    RETURN 'Moderate';
  ELSE
    RETURN 'Expensive';
  END IF;
END;
$$ LANGUAGE plpgsql;

SELECT title, price_category(price) AS category FROM books;

Need a hint?

Use -- to add a comment above the function definition.