Creating stored functions in MySQL - Practice Exercises

Challenge - 5 Problems

❓ query_result

intermediate

Output of a simple stored function call

Consider the following MySQL stored function:

CREATE FUNCTION add_five(x INT) RETURNS INT
BEGIN
  RETURN x + 5;
END;

What is the output of the query SELECT add_five(10);?

MySQL

CREATE FUNCTION add_five(x INT) RETURNS INT
BEGIN
  RETURN x + 5;
END;

A 10

B NULL

C 5

D 15

💡 Hint

The function adds 5 to the input number.

The function takes an integer input and returns the input plus 5. So, 10 + 5 = 15.

📝 Syntax

intermediate

Identify the syntax error in stored function

Which option contains a syntax error when creating a stored function in MySQL?

A CREATE FUNCTION multiply_by_two(x INT) RETURNS INT BEGIN RETURN x * 2; END

B CREATE FUNCTION multiply_by_two(x INT) RETURNS INT BEGIN RETURN x * 2 END;

C CREATE FUNCTION multiply_by_two(x INT) RETURNS INT BEGIN RETURN x * 2; END;

D CREATE FUNCTION multiply_by_two(x INT) RETURNS INT BEGIN RETURN x * 2; END;;

💡 Hint

Check for missing semicolons inside the function body.

Option B is missing a semicolon after the RETURN statement, causing a syntax error.

❓ optimization

advanced

Optimizing a stored function for repeated calculations

You have a stored function that calculates the factorial of a number recursively. Which option is the best way to optimize it to avoid repeated calculations?

A Use a loop inside the function instead of recursion.

B Call the function recursively without any changes.

C Store intermediate results in a temporary table inside the function.

D Use a global variable to store the last factorial calculated.

💡 Hint

Recursion can be slow and cause repeated work.

Using a loop avoids the overhead of recursive calls and repeated calculations, making the function faster and more efficient.

🔧 Debug

advanced

Debugging a stored function that returns NULL unexpectedly

Given this stored function:

CREATE FUNCTION get_discount(price DECIMAL(10,2)) RETURNS DECIMAL(10,2)
BEGIN
  IF price > 100 THEN
    RETURN price * 0.1;
  END IF;
  RETURN 0;
END;

Why does SELECT get_discount(NULL); return NULL instead of 0?

MySQL

CREATE FUNCTION get_discount(price DECIMAL(10,2)) RETURNS DECIMAL(10,2)
BEGIN
  IF price > 100 THEN
    RETURN price * 0.1;
  END IF;
  RETURN 0;
END;

A Because any comparison with NULL returns NULL, the IF condition is not true or false, so no RETURN is executed.

B Because the function does not handle NULL inputs explicitly, it returns 0 by default.

C Because MySQL automatically converts NULL to 0 in arithmetic operations.

D Because the ELSE branch returns NULL explicitly.

💡 Hint

Think about how NULL behaves in comparisons.

In MySQL, comparing NULL with any value results in NULL, which is treated as false in IF conditions, so the function returns NULL by default.

🧠 Conceptual

expert

Understanding deterministic vs nondeterministic stored functions

Which statement correctly describes a deterministic stored function in MySQL?

A A deterministic function cannot use any SQL statements inside its body.

B A deterministic function can return different results for the same input depending on server state.

C A deterministic function always returns the same result for the same input values.

D A deterministic function must not contain any RETURN statements.

💡 Hint

Think about consistency of output for given inputs.

Deterministic functions always produce the same output for the same inputs, which helps optimization and caching.

Practice

1. What is the main purpose of creating a stored function in MySQL?

easy

A. To save reusable code that returns a value

B. To create a temporary table

C. To delete data from a table

D. To backup the database

Solution

1. Step 1: Understand stored function purpose

   Stored functions are designed to hold reusable code inside the database.

2. Step 2: Identify key feature

   They must return a value using RETURN, which distinguishes them from procedures.

3. Final Answer:

   To save reusable code that returns a value -> Option A

4. Quick Check:

   Stored function = reusable code + return value [OK]

Hint: Stored functions always return a value [OK]

Common Mistakes:

• Confusing stored functions with procedures
• Thinking stored functions do not return values
• Using stored functions to delete data
• Assuming stored functions create tables

2. Which of the following is the correct way to start defining a stored function in MySQL?

easy

A. CREATE FUNCTION funcName RETURNS INT BEGIN ... END

B. CREATE PROCEDURE funcName() RETURNS INT BEGIN ... END

C. CREATE FUNCTION funcName() RETURNS INT BEGIN ... END

D. CREATE FUNCTION funcName() RETURNS INT { ... }

Solution

1. Step 1: Check syntax for stored function

   MySQL stored functions start with CREATE FUNCTION, include parentheses for parameters, and specify RETURNS type.

2. Step 2: Identify correct syntax

   CREATE FUNCTION funcName() RETURNS INT BEGIN ... END uses correct syntax with parentheses and RETURNS INT, followed by BEGIN ... END block.

3. Final Answer:

   CREATE FUNCTION funcName() RETURNS INT BEGIN ... END -> Option C

4. Quick Check:

   CREATE FUNCTION + RETURNS + BEGIN/END = correct syntax [OK]

Hint: Use parentheses and RETURNS keyword in function definition [OK]

Common Mistakes:

• Using PROCEDURE instead of FUNCTION
• Omitting parentheses after function name
• Using curly braces instead of BEGIN/END
• Missing RETURNS keyword

3. Given the following stored function, what will be the output of SELECT addFive(10);?

DELIMITER $$
CREATE FUNCTION addFive(num INT) RETURNS INT
BEGIN
  RETURN num + 5;
END$$
DELIMITER ;

medium

A. 15

B. 5

C. 10

D. Syntax Error

Solution

1. Step 1: Understand function logic

   The function addFive takes an integer input and returns the input plus 5.

2. Step 2: Calculate output for input 10

   Input 10 plus 5 equals 15, so the function returns 15.

3. Final Answer:

   15 -> Option A

4. Quick Check:

   10 + 5 = 15 [OK]

Hint: Add input value to 5 as per function logic [OK]

Common Mistakes:

• Confusing input and output values
• Ignoring RETURN statement
• Assuming syntax error due to DELIMITER
• Forgetting to add 5

4. Identify the error in the following stored function definition:

DELIMITER $$
CREATE FUNCTION multiplyByTwo(num INT) RETURNS INT
BEGIN
  SET result = num * 2;
  RETURN result;
END$$
DELIMITER ;

medium

A. Function name is invalid

B. Incorrect use of DELIMITER

C. RETURN statement is missing

D. Missing variable declaration for 'result'

Solution

1. Step 1: Check variable usage inside function

   The variable 'result' is used but not declared with DECLARE inside the function.

2. Step 2: Understand MySQL variable rules

   Local variables must be declared before use; missing DECLARE causes error.

3. Final Answer:

   Missing variable declaration for 'result' -> Option D

4. Quick Check:

   Variables must be declared before use [OK]

Hint: Declare variables before using SET inside functions [OK]

Common Mistakes:

• Not declaring variables before SET
• Misusing DELIMITER keyword
• Forgetting RETURN statement
• Using invalid function names

5. You want to create a stored function isEven that returns 1 if a number is even and 0 if odd. Which of the following is the correct complete function definition?

hard

A. DELIMITER $$
CREATE FUNCTION isEven(num INT) RETURNS INT
BEGIN
RETURN num / 2;
END$$
DELIMITER ;

B. DELIMITER $$
CREATE FUNCTION isEven(num INT) RETURNS INT
BEGIN
IF num % 2 = 0 THEN
RETURN 1;
ELSE
RETURN 0;
END IF;
END$$
DELIMITER ;

C. DELIMITER $$
CREATE FUNCTION isEven(num INT) RETURNS INT
BEGIN
RETURN num % 2;
END$$
DELIMITER ;

D. DELIMITER $$
CREATE FUNCTION isEven(num INT) RETURNS INT
BEGIN
IF num / 2 = 0 THEN
RETURN 1;
ELSE
RETURN 0;
END IF;
END$$
DELIMITER ;

Solution

1. Step 1: Check logic for even number

   Even numbers have zero remainder when divided by 2, so use num % 2 = 0.

2. Step 2: Verify correct syntax and return values

   Uses IF with correct condition num % 2 = 0 and returns 1 or 0 accordingly, with proper BEGIN/END block, END IF, and DELIMITER.

3. Final Answer:

   DELIMITER $$ CREATE FUNCTION isEven(num INT) RETURNS INT BEGIN IF num % 2 = 0 THEN RETURN 1; ELSE RETURN 0; END IF; END$$ DELIMITER ; -> Option B

4. Quick Check:

   Use modulo operator and IF for even check [OK]

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

Common Mistakes:

• Using division instead of modulo for condition
• Returning wrong data types
• Omitting END IF statement
• Returning num % 2 without comparing to 0