Why stored procedures centralize logic in MySQL - Test Your Understanding

Practice - 5 Tasks

1fill in blank

easy

Complete the code to create a stored procedure that selects all rows from the employees table.

MySQL

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

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

Asalaries

Bdepartments

Cemployees

Dprojects

Attempts:

3 left

💡 Hint

Common Mistakes

Choosing a table unrelated to employees like departments or projects.

Forgetting to specify the table name.

The stored procedure should select from the employees table to retrieve all employee records.

2fill in blank

medium

Complete the code to call the stored procedure named GetAllEmployees.

MySQL

CALL [1]();

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

AGetAllEmployees

BSelectEmployees

CFetchEmployees

DListEmployees

Attempts:

3 left

💡 Hint

Common Mistakes

Using a different procedure name than defined.

Omitting the parentheses after the procedure name.

To execute the stored procedure, use the exact procedure name GetAllEmployees with CALL.

3fill in blank

hard

Fix the error in the stored procedure that tries to update salary but misses the WHERE clause.

MySQL

CREATE PROCEDURE UpdateSalary(IN emp_id INT, IN new_salary DECIMAL(10,2)) BEGIN UPDATE employees SET salary = [1]; END;

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

Anew_salary

Bnew_salary WHERE id = emp_id

Csalary = new_salary WHERE id = emp_id

Dsalary WHERE id = emp_id = new_salary

Attempts:

3 left

💡 Hint

Common Mistakes

Forgetting the WHERE clause, which updates all employees.

Incorrect syntax in the SET clause.

The UPDATE statement must set salary to new_salary and include a WHERE clause to target the correct employee by emp_id.

4fill in blank

hard

Fill both blanks to create a stored procedure that inserts a new employee with name and age.

MySQL

CREATE PROCEDURE AddEmployee(IN emp_name VARCHAR(50), IN emp_age INT) BEGIN INSERT INTO employees ([1], [2]) VALUES (emp_name, emp_age); END;

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

Aname

Bage

Csalary

Ddepartment

Attempts:

3 left

💡 Hint

Common Mistakes

Using columns unrelated to the input parameters like salary or department.

Swapping the order of columns and values.

The columns to insert into are name and age matching the input parameters.

5fill in blank

hard

Fill all three blanks to create a stored procedure that deletes an employee by id and returns the number of affected rows.

MySQL

CREATE PROCEDURE DeleteEmployee(IN emp_id INT) BEGIN DELETE FROM employees WHERE id = [1]; SELECT ROW_COUNT() AS [2]; END;

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

Aemp_id

Baffected_rows

Cdeleted_count

Did

Attempts:

3 left

💡 Hint

Common Mistakes

Using a wrong variable name in the WHERE clause.

Not aliasing the ROW_COUNT() result.

The DELETE statement uses emp_id to identify the row, and affected_rows is the alias for the count of deleted rows.