IF-ELSE in procedures in SQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to start an IF block in a SQL procedure.

SQL

IF [1] THEN
  -- some statements
END IF;

Drag options to blanks, or click blank then click option'

Ax > 10

Bx = 10

Cx := 10

Dx == 10

Attempts:

3 left

💡 Hint

Common Mistakes

Using assignment operator ':=' instead of comparison.

Using '==' which is not valid in SQL.

In SQL procedures, the IF condition uses a boolean expression like 'x > 10'.

2fill in blank

medium

Complete the code to add an ELSE block in a SQL procedure.

SQL

IF x > 0 THEN
  SET result = 'Positive';
[1]
  SET result = 'Non-positive';
END IF;

Drag options to blanks, or click blank then click option'

AELSE IF

BELSEIF

CELSIF

DELSE

Attempts:

3 left

💡 Hint

Common Mistakes

Using ELSEIF or ELSIF which are for else-if conditions, not else alone.

Writing ELSE IF as two words which is invalid syntax.

The ELSE keyword starts the else block in SQL procedures.

3fill in blank

hard

Fix the error in the IF statement condition in this SQL procedure code.

SQL

IF [1] THEN
  SET status = 'Valid';
END IF;

Drag options to blanks, or click blank then click option'

Ascore > 100

Bscore = 100

Cscore := 100

Dscore == 100

Attempts:

3 left

💡 Hint

Common Mistakes

Using assignment ':=' instead of comparison.

Using '==' which is not valid in SQL.

The IF condition must be a boolean expression like 'score > 100'.

4fill in blank

hard

Fill both blanks to complete the IF-ELSEIF structure in a SQL procedure.

SQL

IF score [1] 90 THEN
  SET grade = 'A';
[2] score > 80 THEN
  SET grade = 'B';
END IF;

Drag options to blanks, or click blank then click option'

A>

BELSEIF

CIF

D<

Attempts:

3 left

💡 Hint

Common Mistakes

Using '<' instead of '>' in the first blank.

Using 'IF' instead of 'ELSEIF' for the second blank.

The first blank is a comparison operator '>', the second blank is the keyword 'ELSEIF'.

5fill in blank

hard

Fill all three blanks to complete the IF-ELSEIF-ELSE structure in a SQL procedure.

SQL

IF age [1] 18 THEN
  SET status = [2];
[3]
  SET status = 'Minor';
END IF;

Drag options to blanks, or click blank then click option'

A>=

B'Adult'

CELSE

D<

Attempts:

3 left

💡 Hint

Common Mistakes

Using '<' instead of '>=' in the first blank.

Missing quotes around 'Adult' in the second blank.

Using ELSEIF instead of ELSE in the third blank.

The first blank is '>=' to check age 18 or older, second blank is the string 'Adult', third blank is 'ELSE' for the else block.

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