Variables and control flow in MySQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What is a variable in MySQL stored procedures?

A variable in MySQL stored procedures is a named memory location used to store data temporarily during the execution of the procedure. It helps hold values that can be changed and used in control flow statements.

beginner

How do you declare a variable inside a MySQL stored procedure?

You declare a variable using the DECLARE statement followed by the variable name and data type, for example: DECLARE myVar INT; This must be done at the beginning of the procedure block.

beginner

What is the purpose of the IF statement in MySQL?

The IF statement allows you to execute different blocks of code based on a condition. It helps control the flow by choosing which code runs depending on whether the condition is true or false.

intermediate

Explain the WHILE loop in MySQL stored procedures.

The WHILE loop repeats a block of code as long as a specified condition remains true. It is useful for running repetitive tasks until a condition changes.

intermediate

What is the difference between a local variable and a user-defined variable in MySQL?

A local variable is declared inside a stored procedure and only exists during its execution. A user-defined variable starts with @ and can be used outside procedures and persist for the session.

How do you assign a value to a variable inside a MySQL stored procedure?

A DECLARE variable_name = value;

B SET variable_name = value;

C ASSIGN variable_name TO value;

D DEFINE variable_name = value;

You assign values to variables using the SET statement, for example: SET myVar = 10;

Which keyword starts a conditional block in MySQL stored procedures?

A FOR

B CASE

C IF

D LOOP

The IF keyword starts a conditional block to execute code based on a condition.

What happens if the WHILE loop condition is false at the start?

A An error occurs.

B The loop runs infinitely.

C The loop runs once anyway.

D The loop does not run at all.

If the WHILE condition is false initially, the loop body does not execute.

Where must DECLARE statements appear in a MySQL stored procedure?

A At the beginning of the procedure block

B At the end of the procedure

C Anywhere in the procedure

D Outside the procedure

DECLARE statements must be at the start of the procedure block before any other statements.

Which symbol is used to start a user-defined variable in MySQL?

A @

B $

C #

D &

User-defined variables start with the @ symbol, like @myVar.

Describe how variables and control flow statements work together in a MySQL stored procedure.

Think about how you store values and decide what code runs next.

Explain the difference between DECLARE and SET statements in MySQL stored procedures.

One is for making space, the other is for putting things in that space.

Practice

1. What is the purpose of a variable in MySQL stored procedures?

easy

A. To create new tables dynamically

B. To permanently save data in the database

C. To store temporary data during procedure execution

D. To define user permissions

Solution

1. Step 1: Understand variable role in procedures

   Variables hold temporary data only while the procedure runs.

2. Step 2: Differentiate from permanent storage

   Permanent data is stored in tables, not variables.

3. Final Answer:

   To store temporary data during procedure execution -> Option C

4. Quick Check:

   Variables = Temporary data [OK]

Hint: Variables hold temporary data inside procedures [OK]

Common Mistakes:

• Confusing variables with table data storage
• Thinking variables create database objects
• Assuming variables control user access

2. Which of the following is the correct way to declare and set a variable in MySQL stored procedures?

easy

A. SET x INT; DECLARE x = 10;

B. DECLARE x INT; SET x = 10;

C. DECLARE x = 10 INT;

D. SET x = 10; DECLARE x INT;

Solution

1. Step 1: Recall syntax for variable declaration

   Use DECLARE variable_name data_type; to declare.

2. Step 2: Assign value using SET

   Use SET variable_name = value; after declaration.

3. Final Answer:

   DECLARE x INT; SET x = 10; -> Option B

4. Quick Check:

   DECLARE then SET = correct syntax [OK]

Hint: Declare first, then set value with SET [OK]

Common Mistakes:

• Using SET before DECLARE
• Combining declaration and assignment incorrectly
• Wrong order of keywords

3. Consider the following MySQL procedure snippet:

DECLARE counter INT DEFAULT 1;
WHILE counter <= 3 DO
  SET counter = counter + 1;
END WHILE;
SELECT counter;

What will be the output of the SELECT counter; statement?

medium

A. 3

B. Error due to missing BEGIN...END

C. 1

D. 4

Solution

1. Step 1: Trace the WHILE loop increments

   Counter starts at 1, increments by 1 each loop until counter > 3.

2. Step 2: Count loop iterations and final value

   Loop runs while counter=1,2,3; increments to 4 and stops.

3. Final Answer:

   4 -> Option D

4. Quick Check:

   Counter ends at 4 after loop [OK]

Hint: WHILE increments counter until condition false [OK]

Common Mistakes:

• Assuming counter stops at 3
• Forgetting counter increments before condition check
• Thinking syntax error without BEGIN...END in snippet

4. Identify the error in this MySQL stored procedure snippet:

DECLARE total INT;
SET total = 0
WHILE total < 5 DO
  SET total = total + 1;
END WHILE;

medium

A. Missing semicolon after SET total = 0

B. WHILE loop condition syntax is wrong

C. DECLARE must be inside BEGIN...END block

D. Variable total not declared

Solution

1. Step 1: Check statement endings

   SET total = 0 is missing a semicolon at the end.

2. Step 2: Validate syntax for DECLARE and WHILE

   DECLARE is valid before BEGIN, and WHILE syntax is correct.

3. Final Answer:

   Missing semicolon after SET total = 0 -> Option A

4. Quick Check:

   Every statement needs semicolon [OK]

Hint: Check semicolons after each statement [OK]

Common Mistakes:

• Ignoring missing semicolon errors
• Thinking DECLARE must be inside BEGIN...END always
• Misreading WHILE syntax

5. You want to create a MySQL stored procedure that counts how many numbers between 1 and 10 are even, using variables and control flow. Which of the following code snippets correctly implements this?

hard

A. DECLARE count_even INT DEFAULT 0; DECLARE i INT DEFAULT 1; WHILE i <= 10 DO IF i % 2 = 0 THEN SET count_even = count_even + 1; END IF; SET i = i + 1; END WHILE; SELECT count_even;

B. DECLARE count_even INT; SET count_even = 0; FOR i IN 1..10 DO IF i % 2 = 0 THEN SET count_even = count_even + 1; END IF; END FOR; SELECT count_even;

C. DECLARE count_even INT DEFAULT 0; SET i = 1; WHILE i < 10 DO IF i % 2 = 1 THEN SET count_even = count_even + 1; END IF; SET i = i + 1; END WHILE; SELECT count_even;

D. DECLARE count_even INT DEFAULT 0; DECLARE i INT DEFAULT 1; WHILE i <= 10 DO IF i / 2 = 0 THEN SET count_even = count_even + 1; END IF; SET i = i + 1; END WHILE; SELECT count_even;

Solution

1. Step 1: Check loop and variable initialization

   DECLARE count_even INT DEFAULT 0; DECLARE i INT DEFAULT 1; WHILE i <= 10 DO IF i % 2 = 0 THEN SET count_even = count_even + 1; END IF; SET i = i + 1; END WHILE; SELECT count_even; correctly declares variables and initializes them.

2. Step 2: Verify condition and increment logic

   DECLARE count_even INT DEFAULT 0; DECLARE i INT DEFAULT 1; WHILE i <= 10 DO IF i % 2 = 0 THEN SET count_even = count_even + 1; END IF; SET i = i + 1; END WHILE; SELECT count_even; uses WHILE loop from 1 to 10, checks even with modulo, increments count and i properly.

3. Step 3: Identify errors in other options

   DECLARE count_even INT; SET count_even = 0; FOR i IN 1..10 DO IF i % 2 = 0 THEN SET count_even = count_even + 1; END IF; END FOR; SELECT count_even; uses invalid FOR syntax; C counts odd numbers; D uses division instead of modulo.

4. Final Answer:

   Option A code correctly counts even numbers between 1 and 10 -> Option A

5. Quick Check:

   Use WHILE + modulo for even check [OK]

Hint: Use WHILE loop and modulo (%) to check even numbers [OK]

Common Mistakes:

• Using invalid FOR loop syntax in MySQL
• Confusing modulo (%) with division (/)
• Counting odd numbers instead of even