IF-ELSE in procedures in SQL - Mini Project: Build & Apply

Using IF-ELSE in SQL Procedures

📖 Scenario: You work at a bookstore that wants to categorize books by their price range automatically.They want a procedure that takes a book's price and returns a category: 'Cheap', 'Moderate', or 'Expensive'.

🎯 Goal: Create a SQL procedure named categorize_book_price that uses IF-ELSE statements to assign a category based on the price input.

📋 What You'll Learn

Create a procedure named categorize_book_price with an input parameter book_price of type DECIMAL.

Declare a variable price_category to hold the category as VARCHAR(20).

Use IF-ELSE statements to set price_category to 'Cheap' if book_price is less than 10.

Set price_category to 'Moderate' if book_price is between 10 and 20 inclusive.

Set price_category to 'Expensive' if book_price is greater than 20.

Return the price_category as an output parameter.

💡 Why This Matters

🌍 Real World

Bookstores and retail systems often need to categorize products automatically based on price or other attributes to help with pricing strategies and customer information.

💼 Career

Knowing how to write conditional logic in SQL procedures is essential for database developers and analysts to automate data processing and business rules.

1

Create the procedure header and input parameter

Write the first line to create a procedure named categorize_book_price with one input parameter called book_price of type DECIMAL(5,2).

SQL

-- Create procedure header with input parameter book_price
-- Your code here

Hint

Start with CREATE PROCEDURE and define book_price as an input parameter.

2

Declare a variable to hold the price category

Inside the procedure, declare a variable named price_category of type VARCHAR(20) to store the category.

SQL

CREATE PROCEDURE categorize_book_price(IN book_price DECIMAL(5,2))
BEGIN
    -- Declare price_category variable
    -- Your code here
END;

Hint

Use DECLARE inside the procedure body to create the variable.

3

Add IF-ELSE logic to set the price category

Write IF-ELSE statements inside the procedure to set price_category to 'Cheap' if book_price < 10, 'Moderate' if between 10 and 20 inclusive, and 'Expensive' if > 20.

SQL

CREATE PROCEDURE categorize_book_price(IN book_price DECIMAL(5,2))
BEGIN
    DECLARE price_category VARCHAR(20);
    -- Add IF-ELSE logic here
    -- Your code here
END;

Hint

Use IF ... THEN ... ELSEIF ... ELSE ... END IF; structure and SET to assign the variable.

4

Add an output parameter to return the category

Modify the procedure to add an output parameter named category of type VARCHAR(20), and set it to the value of price_category before ending the procedure.

SQL

CREATE PROCEDURE categorize_book_price(IN book_price DECIMAL(5,2), OUT category VARCHAR(20))
BEGIN
    DECLARE price_category VARCHAR(20);
    IF book_price < 10 THEN
        SET price_category = 'Cheap';
    ELSEIF book_price <= 20 THEN
        SET price_category = 'Moderate';
    ELSE
        SET price_category = 'Expensive';
    END IF;
    -- Set output parameter category
    -- Your code here
END;

Hint

Add OUT category VARCHAR(20) to the procedure parameters and assign it inside the procedure.

Practice

1. What is the main purpose of using IF-ELSE statements inside SQL procedures?

easy

A. To delete all data from a table

B. To execute different code blocks based on conditions

C. To create new tables automatically

D. To change the database schema

Solution

1. Step 1: Understand the role of IF-ELSE

   IF-ELSE statements allow conditional execution of code depending on whether a condition is true or false.

2. Step 2: Identify the correct purpose

   Creating tables or deleting data are not related to IF-ELSE logic but to other SQL commands.

3. Final Answer:

   To execute different code blocks based on conditions -> Option B

4. Quick Check:

   IF-ELSE controls flow [OK]

Hint: IF-ELSE runs code only when conditions match [OK]

Common Mistakes:

• Thinking IF-ELSE creates or deletes tables
• Confusing IF-ELSE with data manipulation commands
• Assuming IF-ELSE changes database structure

2. Which of the following is the correct syntax to start an IF statement inside a SQL procedure?

easy

A. IF condition THEN

B. IF (condition) {

C. IF condition DO

D. IF condition BEGIN

Solution

1. Step 1: Recall SQL procedure IF syntax

   In SQL procedures, the IF statement starts with IF condition THEN.

2. Step 2: Compare options

   Options B, C, and D use syntax styles from other languages or incorrect keywords.

3. Final Answer:

   IF condition THEN -> Option A

4. Quick Check:

   SQL IF starts with IF condition THEN [OK]

Hint: Use THEN after condition in SQL IF [OK]

Common Mistakes:

• Using curly braces {} like in programming languages
• Using DO or BEGIN instead of THEN
• Missing THEN keyword after condition

3. Consider this SQL procedure snippet:

DECLARE v_score INT DEFAULT 75;
IF v_score >= 90 THEN
  SELECT 'Grade A';
ELSEIF v_score >= 80 THEN
  SELECT 'Grade B';
ELSEIF v_score >= 70 THEN
  SELECT 'Grade C';
ELSE
  SELECT 'Grade F';
END IF;

What will be the output?

medium

A. 'Grade A'

B. 'Grade B'

C. 'Grade C'

D. 'Grade F'

Solution

1. Step 1: Check the value of v_score

   v_score is 75.

2. Step 2: Evaluate conditions in order

   75 is not >= 90, not >= 80, but it is >= 70, so 'Grade C' is selected.

3. Final Answer:

   'Grade C' -> Option C

4. Quick Check:

   75 >= 70 = 'Grade C' [OK]

Hint: Check conditions top to bottom, first true wins [OK]

Common Mistakes:

• Choosing 'Grade B' or 'Grade A' without checking all conditions
• Ignoring ELSEIF order
• Assuming ELSE runs when a previous condition matches

4. Identify the error in this SQL procedure snippet:

IF score > 50
  SELECT 'Pass';
ELSE
  SELECT 'Fail';
END IF;

medium

A. Missing semicolon after ELSE

B. Missing BEGIN keyword

C. Incorrect END IF syntax

D. Missing THEN after IF condition

Solution

1. Step 1: Check IF statement syntax

   In SQL procedures, IF must be followed by THEN after the condition.

2. Step 2: Verify given code

   The code has IF score > 50 but no THEN keyword.

3. Final Answer:

   Missing THEN after IF condition -> Option D

4. Quick Check:

   IF condition THEN is required [OK]

Hint: Always put THEN after IF condition [OK]

Common Mistakes:

• Omitting THEN after IF
• Adding semicolon after ELSE incorrectly
• Misusing END IF syntax

5. You want to write a procedure that checks a user's age and returns 'Adult' if age is 18 or more, 'Minor' if less, and 'Invalid' if age is negative. Which IF-ELSE structure correctly implements this?

hard

A. IF age < 0 THEN SELECT 'Invalid'; ELSEIF age >= 18 THEN SELECT 'Adult'; ELSE SELECT 'Minor'; END IF;

B. IF age >= 18 THEN SELECT 'Adult'; ELSEIF age < 0 THEN SELECT 'Invalid'; ELSE SELECT 'Minor'; END IF;

C. IF age < 0 THEN SELECT 'Invalid'; ELSE SELECT 'Minor'; ELSEIF age >= 18 THEN SELECT 'Adult'; END IF;

D. IF age >= 18 THEN SELECT 'Adult'; ELSE SELECT 'Minor'; END IF; IF age < 0 THEN SELECT 'Invalid'; END IF;

Solution

1. Step 1: Check condition order for correctness

   Negative age must be checked first to return 'Invalid'. Then check if age is 18 or more for 'Adult'. Otherwise, 'Minor'.

2. Step 2: Analyze options

   IF age < 0 THEN SELECT 'Invalid'; ELSEIF age >= 18 THEN SELECT 'Adult'; ELSE SELECT 'Minor'; END IF; checks negative first, then adult, then minor. IF age >= 18 THEN SELECT 'Adult'; ELSEIF age < 0 THEN SELECT 'Invalid'; ELSE SELECT 'Minor'; END IF; checks adult before negative, which is wrong. IF age < 0 THEN SELECT 'Invalid'; ELSE SELECT 'Minor'; ELSEIF age >= 18 THEN SELECT 'Adult'; END IF; has ELSE before ELSEIF, invalid syntax. IF age >= 18 THEN SELECT 'Adult'; ELSE SELECT 'Minor'; END IF; IF age < 0 THEN SELECT 'Invalid'; END IF; splits checks into two IFs, which can cause multiple outputs.

3. Final Answer:

   IF age < 0 THEN SELECT 'Invalid'; ELSEIF age >= 18 THEN SELECT 'Adult'; ELSE SELECT 'Minor'; END IF; -> Option A

4. Quick Check:

   Check invalid first, then adult, else minor [OK]

Hint: Check invalid cases before valid ranges [OK]

Common Mistakes:

• Checking valid age before invalid
• Using ELSE before ELSEIF
• Splitting conditions into separate IFs causing multiple outputs