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.