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

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

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

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;

Hint

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

Practice

1.

What is the purpose of the ELSIF keyword in PostgreSQL's IF control flow?

easy

A. To test an additional condition if the previous IF condition is false

B. To end the IF statement

C. To execute code unconditionally

D. To start a loop inside the IF block

Solution

1. Step 1: Understand the role of IF and ELSIF

   The IF keyword tests the first condition. If it is false, ELSIF allows testing another condition.

2. Step 2: Differentiate ELSIF from other keywords

   ELSIF is not for ending or unconditional execution; it is for additional conditional checks.

3. Final Answer:

   To test an additional condition if the previous IF condition is false -> Option A

4. Quick Check:

   ELSIF = additional condition test [OK]

Hint: Remember: ELSIF adds more conditions after IF [OK]

Common Mistakes:

• Thinking ELSIF ends the IF block
• Confusing ELSIF with ELSE
• Using ELSIF without a preceding IF

2.

Which of the following is the correct syntax to close an IF statement in PostgreSQL?

easy

A. END;

B. ENDIF;

C. END IF;

D. FINISH IF;

Solution

1. Step 1: Recall PostgreSQL block ending syntax

   PostgreSQL requires END IF; to close an IF block explicitly.

2. Step 2: Compare options

   ENDIF; and FINISH IF; are invalid. END; alone closes other blocks but not IF.

3. Final Answer:

   END IF; -> Option C

4. Quick Check:

   Close IF with END IF; [OK]

Hint: Always end IF blocks with END IF; in PostgreSQL [OK]

Common Mistakes:

• Using END; alone to close IF
• Writing ENDIF; without space
• Forgetting to close IF blocks

3.

Consider this PostgreSQL code snippet inside a function:

IF score >= 90 THEN
  result := 'A';
ELSIF score >= 80 THEN
  result := 'B';
ELSIF score >= 70 THEN
  result := 'C';
ELSE
  result := 'F';
END IF;

If score is 85, what will be the value of result after execution?

medium

A. 'A'

B. 'F'

C. 'C'

D. 'B'

Solution

1. Step 1: Evaluate conditions in order for score = 85

   Check if 85 >= 90? No. Then check 85 >= 80? Yes.

2. Step 2: Assign result based on first true condition

   Since 85 >= 80 is true, result is set to 'B'. Remaining conditions are skipped.

3. Final Answer:

   'B' -> Option D

4. Quick Check:

   85 >= 80 = true, so result = 'B' [OK]

Hint: Check conditions top to bottom; first true sets the result [OK]

Common Mistakes:

• Choosing 'A' because 85 is close to 90
• Ignoring order of conditions
• Assigning 'C' or 'F' incorrectly

4.

Identify the error in this PostgreSQL IF block:

IF value > 10 THEN
  RAISE NOTICE 'Value is large';
ELSIF value < 5
  RAISE NOTICE 'Value is small';
ELSE
  RAISE NOTICE 'Value is medium';
END IF;

medium

A. Using ELSE without condition

B. Missing THEN after ELSIF value < 5

C. Incorrect use of RAISE NOTICE

D. Missing END IF;

Solution

1. Step 1: Check syntax of each condition

   The ELSIF line lacks the required THEN keyword after the condition.

2. Step 2: Confirm other parts are correct

   END IF; is present, RAISE NOTICE is valid, and ELSE does not take a condition.

3. Final Answer:

   Missing THEN after ELSIF value < 5 -> Option B

4. Quick Check:

   ELSIF must have THEN [OK]

Hint: Always write THEN after IF and ELSIF conditions [OK]

Common Mistakes:

• Omitting THEN after ELSIF
• Adding condition after ELSE
• Forgetting END IF;

5.

You want to write a PostgreSQL function that returns 'Positive', 'Negative', or 'Zero' based on an integer input num. Which IF-ELSIF-ELSE block correctly implements this logic?

-- Options:
A) IF num > 0 THEN RETURN 'Positive';
   ELSIF num < 0 THEN RETURN 'Negative';
   ELSE RETURN 'Zero';
   END IF;

B) IF num > 0 THEN RETURN 'Positive';
   ELSEIF num < 0 THEN RETURN 'Negative';
   ELSE RETURN 'Zero';
   END IF;

C) IF num > 0 THEN RETURN 'Positive';
   ELSIF num < 0 THEN RETURN 'Negative';
   ELSEIF num = 0 THEN RETURN 'Zero';
   END IF;

D) IF num > 0 THEN RETURN 'Positive';
   IF num < 0 THEN RETURN 'Negative';
   ELSE RETURN 'Zero';
   END IF;

hard

A. Correct use of IF, ELSIF, ELSE with proper syntax

B. Uses invalid keyword ELSEIF instead of ELSIF

C. Uses ELSEIF after ELSE which is invalid

D. Nested IF without closing first IF properly

Solution

1. Step 1: Check keywords and structure

   Correct use of IF, ELSIF, ELSE with proper syntax uses IF, ELSIF, and ELSE correctly with proper endings.

2. Step 2: Identify errors in other options

   Uses invalid keyword ELSEIF instead of ELSIF uses invalid ELSEIF. Uses ELSEIF after ELSE which is invalid uses ELSEIF after ELSE. Nested IF without closing first IF properly nests IF without closing properly.

3. Final Answer:

   Correct use of IF, ELSIF, ELSE with proper syntax -> Option A

4. Quick Check:

   Use IF, ELSIF, ELSE; no ELSEIF [OK]

Hint: Use ELSIF, not ELSEIF; close IF with END IF; [OK]

Common Mistakes:

• Using ELSEIF instead of ELSIF
• Placing ELSEIF after ELSE
• Improper nesting without END IF;