Creating stored procedures in MySQL - Interactive Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to start creating a stored procedure named 'GetAllUsers'.

MySQL

CREATE [1] PROCEDURE GetAllUsers() BEGIN SELECT * FROM users; END;

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

AEVENT

BPROCEDURE

CTRIGGER

DFUNCTION

Attempts:

3 left

💡 Hint

Common Mistakes

Using FUNCTION instead of PROCEDURE

Using TRIGGER or EVENT keywords incorrectly

The keyword PROCEDURE is used to create a stored procedure in MySQL.

2fill in blank

medium

Complete the code to declare an input parameter named 'userId' of type INT in the stored procedure.

MySQL

CREATE PROCEDURE GetUserById([1] userId INT) BEGIN SELECT * FROM users WHERE id = userId; END;

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

AIN

BOUT

CVAR

DPARAM

Attempts:

3 left

💡 Hint

Common Mistakes

Using OUT instead of IN for input parameters

Using VAR or PARAM which are not valid keywords here

The IN keyword declares an input parameter for a stored procedure.

3fill in blank

hard

Fix the error in the stored procedure declaration by completing the missing delimiter statement.

MySQL

DELIMITER [1]
CREATE PROCEDURE CountUsers() BEGIN SELECT COUNT(*) FROM users; END[1]
DELIMITER ;

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

A$$

B;

C//

D#

Attempts:

3 left

💡 Hint

Common Mistakes

Using semicolon as delimiter causes syntax errors

Using uncommon or invalid delimiter characters

Changing the delimiter to $$ allows the procedure body to contain semicolons without ending the statement early.

4fill in blank

hard

Fill both blanks to create a procedure that updates a user's email by id.

MySQL

CREATE PROCEDURE UpdateUserEmail(IN userId INT, IN newEmail VARCHAR(255)) BEGIN UPDATE users SET email = [1] WHERE id = [2]; END;

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

AnewEmail

BuserId

Cemail

Did

Attempts:

3 left

💡 Hint

Common Mistakes

Using column names instead of input parameters in SET or WHERE

Mixing up userId and newEmail in the query

The procedure sets the email column to the input parameter newEmail where the id matches the input parameter userId.

5fill in blank

hard

Fill all three blanks to create a procedure that inserts a new user with name and age, then returns the new user's id.

MySQL

CREATE PROCEDURE AddUser(IN userName VARCHAR(100), IN userAge INT, OUT newId INT) BEGIN INSERT INTO users (name, age) VALUES ([1], [2]); SET [3] = LAST_INSERT_ID(); END;

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

AuserName

BuserAge

CnewId

Did

Attempts:

3 left

💡 Hint

Common Mistakes

Using column names instead of parameters in VALUES

Not setting the output parameter correctly

The procedure inserts the input parameters userName and userAge into the users table, then sets the output parameter newId to the last inserted id.

Practice

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

easy

A. To create a new database

B. To save a set of SQL commands for reuse later

C. To delete tables automatically

D. To backup the entire database

Solution

1. Step 1: Understand stored procedure concept

   A stored procedure is a saved set of SQL commands that can be run multiple times without rewriting.

2. Step 2: Compare options with definition

   Only To save a set of SQL commands for reuse later correctly describes this purpose; others describe unrelated tasks.

3. Final Answer:

   To save a set of SQL commands for reuse later -> Option B

4. Quick Check:

   Stored procedure = saved SQL commands [OK]

Hint: Stored procedures store SQL commands to run later [OK]

Common Mistakes:

• Confusing stored procedures with database creation
• Thinking stored procedures delete tables
• Assuming stored procedures backup data

2. Which of the following is the correct syntax to create a stored procedure named GetUsers without parameters?

easy

A. CREATE PROCEDURE GetUsers() { SELECT * FROM users; }

B. CREATE PROCEDURE GetUsers BEGIN SELECT * FROM users; END;

C. CREATE PROCEDURE GetUsers() SELECT * FROM users;

D. CREATE PROCEDURE GetUsers() BEGIN SELECT * FROM users; END;

Solution

1. Step 1: Recall correct stored procedure syntax

   MySQL requires parentheses for parameters (empty if none), BEGIN and END to mark procedure body.

2. Step 2: Check each option

   CREATE PROCEDURE GetUsers() BEGIN SELECT * FROM users; END; uses parentheses and BEGIN...END correctly. Others miss parentheses, BEGIN/END, or use wrong braces.

3. Final Answer:

   CREATE PROCEDURE GetUsers() BEGIN SELECT * FROM users; END; -> Option D

4. Quick Check:

   Syntax needs () and BEGIN...END [OK]

Hint: Use () even if no parameters, and BEGIN...END block [OK]

Common Mistakes:

• Omitting parentheses after procedure name
• Missing BEGIN and END keywords
• Using curly braces instead of BEGIN...END

3. Given the stored procedure:

CREATE PROCEDURE AddNumbers(IN a INT, IN b INT, OUT sum INT)
BEGIN
  SET sum = a + b;
END;

What will be the value of sum after calling CALL AddNumbers(3, 5, @result); SELECT @result;?

medium

A. 8

B. 35

C. NULL

D. Syntax error

Solution

1. Step 1: Understand procedure parameters and call

   The procedure takes two input integers a and b, and outputs their sum into the OUT parameter sum.

2. Step 2: Analyze the call and output

   Calling with 3 and 5 sets sum = 3 + 5 = 8. The OUT parameter is stored in @result, so SELECT @result returns 8.

3. Final Answer:

   8 -> Option A

4. Quick Check:

   3 + 5 = 8 [OK]

Hint: OUT parameters store results in user variables like @result [OK]

Common Mistakes:

• Confusing concatenation with addition
• Expecting sum to be NULL before assignment
• Forgetting to use user variable for OUT parameter

4. Identify the error in this stored procedure:

CREATE PROCEDURE GetCount()
BEGIN
  SELECT COUNT(*) FROM users
END;

medium

A. Missing semicolon after SELECT statement

B. Procedure name cannot have parentheses

C. BEGIN and END keywords are not allowed

D. COUNT(*) is invalid syntax

Solution

1. Step 1: Check syntax inside procedure body

   Each statement inside BEGIN...END must end with a semicolon in MySQL.

2. Step 2: Identify missing semicolon

   The SELECT COUNT(*) FROM users statement lacks a semicolon before END; causing syntax error.

3. Final Answer:

   Missing semicolon after SELECT statement -> Option A

4. Quick Check:

   Statements inside BEGIN...END need semicolons [OK]

Hint: End each statement with semicolon inside BEGIN...END [OK]

Common Mistakes:

• Omitting semicolon after SQL statements
• Thinking parentheses are not allowed in procedure name
• Believing COUNT(*) is invalid

5. You want to create a stored procedure UpdateSalary that increases an employee's salary by a given percentage. Which of the following is the correct way to write it?

hard

A. CREATE PROCEDURE UpdateSalary(IN empId INT, IN pct INT) UPDATE employees SET salary = salary * pct WHERE id = empId;

B. CREATE PROCEDURE UpdateSalary(empId INT, pct DECIMAL(5,2)) BEGIN UPDATE employees SET salary = salary + pct WHERE id = empId; END;

C. CREATE PROCEDURE UpdateSalary(IN empId INT, IN pct DECIMAL(5,2)) BEGIN UPDATE employees SET salary = salary * (1 + pct/100) WHERE id = empId; END;

D. CREATE PROCEDURE UpdateSalary(IN empId INT, IN pct DECIMAL(5,2)) BEGIN UPDATE employees SET salary = salary + pct WHERE id = empId END

Solution

1. Step 1: Check parameter declarations and body structure

   CREATE PROCEDURE UpdateSalary(IN empId INT, IN pct DECIMAL(5,2)) BEGIN UPDATE employees SET salary = salary * (1 + pct/100) WHERE id = empId; END; correctly declares input parameters with IN and uses BEGIN...END block.

2. Step 2: Verify salary update logic and syntax

   CREATE PROCEDURE UpdateSalary(IN empId INT, IN pct DECIMAL(5,2)) BEGIN UPDATE employees SET salary = salary * (1 + pct/100) WHERE id = empId; END; updates salary by multiplying with (1 + pct/100), which correctly increases salary by percentage. It also ends statements with semicolon.

3. Step 3: Identify errors in other options

   CREATE PROCEDURE UpdateSalary(empId INT, pct DECIMAL(5,2)) BEGIN UPDATE employees SET salary = salary + pct WHERE id = empId; END; lacks IN keywords and adds pct directly (not percentage). CREATE PROCEDURE UpdateSalary(IN empId INT, IN pct INT) UPDATE employees SET salary = salary * pct WHERE id = empId; misses BEGIN...END and uses wrong pct type. CREATE PROCEDURE UpdateSalary(IN empId INT, IN pct DECIMAL(5,2)) BEGIN UPDATE employees SET salary = salary + pct WHERE id = empId END misses semicolon before END.

4. Final Answer:

   CREATE PROCEDURE UpdateSalary(IN empId INT, IN pct DECIMAL(5,2)) BEGIN UPDATE employees SET salary = salary * (1 + pct/100) WHERE id = empId; END; -> Option C

5. Quick Check:

   Use IN params, BEGIN...END, and correct percentage formula [OK]

Hint: Use IN params and multiply salary by (1 + pct/100) [OK]

Common Mistakes:

• Forgetting IN keyword for parameters
• Adding percentage directly instead of multiplying
• Missing BEGIN...END or semicolons