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.