Creating stored functions in MySQL - Interactive Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to start creating a stored function named 'getSquare'.

MySQL

CREATE FUNCTION getSquare(num INT) RETURNS INT [1]

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

AAS

BBEGIN

CDECLARE

DEND

Attempts:

3 left

💡 Hint

Common Mistakes

Using AS instead of BEGIN to start the function body.

Forgetting to start the function body with BEGIN.

The BEGIN keyword starts the body of the stored function in MySQL.

2fill in blank

medium

Complete the code to return the square of the input number inside the function.

MySQL

RETURN [1] * num; END

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

Anum - 1

Bnum + 1

Cnum

Dnum / 2

Attempts:

3 left

💡 Hint

Common Mistakes

Returning num + 1 instead of num * num.

Using division or subtraction instead of multiplication.

The function should return num * num to calculate the square.

3fill in blank

hard

Fix the error in the function declaration by completing the missing keyword.

MySQL

CREATE FUNCTION getDouble(num INT) RETURNS INT [1] RETURN num * 2; END

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

AFUNCTION

BAS

CDECLARE

DBEGIN

Attempts:

3 left

💡 Hint

Common Mistakes

Using AS instead of BEGIN to start the function body.

Omitting the BEGIN keyword entirely.

The function body must start with BEGIN before statements like RETURN.

4fill in blank

hard

Fill both blanks to declare a variable and set it to the square of the input number.

MySQL

DECLARE result INT; SET result = num [1] num; RETURN [2]; END

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

A*

Bresult

C+

Dnum

Attempts:

3 left

💡 Hint

Common Mistakes

Using + instead of * for multiplication.

Returning num instead of the result variable.

Use * to multiply and return the variable result.

5fill in blank

hard

Fill all three blanks to create a function that returns the cube of a number.

MySQL

CREATE FUNCTION getCube(num INT) RETURNS INT [1] DECLARE result INT; SET result = num [2] num [3] num; RETURN result; END

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

ABEGIN

B*

DAS

Attempts:

3 left

💡 Hint

Common Mistakes

Using AS instead of BEGIN to start the function.

Using + or other operators instead of * for multiplication.

Start the function body with BEGIN and multiply num three times using *.

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