IF-ELSE in procedures in SQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Output of IF-ELSE in a stored procedure

Consider the following stored procedure in SQL. What will be the output when the procedure is called with input value 15?

SQL

CREATE PROCEDURE CheckValue(IN input_val INT)
BEGIN
  IF input_val < 10 THEN
    SELECT 'Less than 10' AS result;
  ELSEIF input_val BETWEEN 10 AND 20 THEN
    SELECT 'Between 10 and 20' AS result;
  ELSE
    SELECT 'Greater than 20' AS result;
  END IF;
END;

A Between 10 and 20

B NULL

C Less than 10

D Greater than 20

💡 Hint

Check the condition ranges in the IF-ELSE statement carefully.

The input value 15 falls between 10 and 20, so the ELSEIF branch executes, returning 'Between 10 and 20'.

📝 Syntax

intermediate

Identify the syntax error in IF-ELSE block

Which option contains a syntax error in the IF-ELSE block of a SQL procedure?

SQL

CREATE PROCEDURE SampleProc(IN val INT)
BEGIN
  IF val > 0 THEN
    SELECT 'Positive';
  ELSE
    SELECT 'Non-positive';
  END IF;
END;

A Missing END keyword for procedure

B Missing THEN keyword after IF condition

C Missing semicolon after END IF

D Missing BEGIN keyword after ELSE

💡 Hint

Check punctuation after control flow statements.

In SQL procedures, the END IF statement must be followed by a semicolon to mark the end of the IF block.

❓ optimization

advanced

Optimizing nested IF-ELSE in procedures

Given a procedure with nested IF-ELSE statements checking multiple ranges, which option optimizes the logic to reduce unnecessary checks?

SQL

CREATE PROCEDURE RangeCheck(IN num INT)
BEGIN
  IF num < 0 THEN
    SELECT 'Negative';
  ELSE
    IF num = 0 THEN
      SELECT 'Zero';
    ELSE
      IF num > 0 AND num < 10 THEN
        SELECT 'Small positive';
      ELSE
        SELECT 'Large positive';
      END IF;
    END IF;
  END IF;
END;

A Use ELSEIF to combine conditions instead of nested IFs

B Replace IF with CASE statement for clarity

C Remove ELSE blocks to simplify code

D Use multiple separate procedures for each condition

💡 Hint

Think about how ELSEIF can reduce nesting.

Using ELSEIF allows checking multiple conditions sequentially without deep nesting, improving readability and performance.

🔧 Debug

advanced

Debugging unexpected output in IF-ELSE procedure

A procedure is expected to return 'Adult' for age >= 18 and 'Minor' otherwise. However, it always returns 'Minor'. Which option explains the bug?

SQL

CREATE PROCEDURE CheckAge(IN age INT)
BEGIN
  IF age >= 18 THEN
    SELECT 'Adult';
  ELSE
    SELECT 'Minor';
  END IF;
END;

A The ELSE block is missing a SELECT statement

B The condition should be age >= 18, not age > 18

C The procedure lacks a RETURN statement

D The input parameter is not declared correctly

💡 Hint

Check the boundary condition in the IF statement.

Age 18 does not satisfy age > 18, so it falls to ELSE returning 'Minor'. Changing to age >= 18 fixes this.

🧠 Conceptual

expert

Understanding IF-ELSE execution flow in procedures

In a SQL procedure, what happens if multiple IF conditions are true but only IF and ELSEIF branches are used without ELSE? Which option best describes the behavior?

A The ELSE block executes regardless of IF conditions

B All true conditions' blocks execute sequentially

C The procedure throws a runtime error due to multiple true conditions

D Only the first true condition's block executes, others are skipped

💡 Hint

Think about how IF-ELSEIF chains work in SQL procedures.

In IF-ELSEIF chains, once a true condition is found, its block runs and the rest are skipped.

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