Variables and control flow in MySQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Output of variable assignment and IF statement

What is the output of the following MySQL code snippet?

MySQL

SET @x = 5;
IF @x > 3 THEN
  SELECT 'Greater';
ELSE
  SELECT 'Smaller';
END IF;

A Smaller

B Syntax error

C Greater

D NULL

💡 Hint

Control flow statements like IF require stored routine context.

IF statements can only be used within stored routines (procedures, functions, etc.). This standalone code causes a syntax error.

❓ query_result

intermediate

Output of WHILE loop with variable increment

What will be the output of this MySQL code?

MySQL

SET @count = 1;
SET @result = 0;
WHILE @count <= 3 DO
  SET @result = @result + @count;
  SET @count = @count + 1;
END WHILE;
SELECT @result;

A Syntax error

B 3

C 0

D 6

💡 Hint

Control flow statements like WHILE require stored routine context.

WHILE loops can only be used within stored programs; this standalone code results in a syntax error.

📝 Syntax

advanced

Identify the syntax error in variable declaration

Which option contains the correct syntax to declare and set a variable in MySQL stored procedure?

A DECLARE x INT = 10;

B SET x = 10;

C DECLARE x INT DEFAULT 10;

D DECLARE x = INT 10;

💡 Hint

Look at the correct order of keywords in DECLARE.

In MySQL stored procedures, variables are declared with 'DECLARE var_name type DEFAULT value;'. Options C and D have wrong syntax. Option C sets a variable but does not declare it.

❓ optimization

advanced

Optimize variable usage in a loop

Which option optimizes the following code to avoid unnecessary variable assignments? Original code: SET @i = 1; WHILE @i <= 5 DO SET @sum = @sum + @i; SET @i = @i + 1; END WHILE; SELECT @sum;

A Initialize @sum to 0 before the loop and keep the rest same.

B Remove the loop and directly set @sum = 15;

C Initialize @sum inside the loop to 0 each time.

D Use a FOR loop instead of WHILE.

💡 Hint

Uninitialized variables can cause unexpected results.

Without initializing @sum to 0, the addition may produce NULL or wrong results. Initializing @sum before the loop is necessary.

🧠 Conceptual

expert

Understanding variable scope in MySQL stored procedures

Consider a stored procedure where a variable is declared inside a BEGIN...END block. Which statement is true about the variable's scope?

A The variable is accessible throughout the entire stored procedure regardless of where declared.

B The variable is accessible only after the block ends.

C The variable is global and accessible outside the stored procedure.

D The variable is accessible only within the BEGIN...END block where it is declared.

💡 Hint

Think about local vs global variable scope.

Variables declared inside a BEGIN...END block are local to that block and cannot be accessed outside it.