Why stored procedures are needed in SQL - Test Your Understanding

Practice - 5 Tasks

1fill in blank

easy

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

SQL

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

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

Adepartments

Bemployees

Csalaries

Dprojects

Attempts:

3 left

💡 Hint

Common Mistakes

Choosing a table unrelated to employees like 'departments'.

Using a table that does not exist in the database.

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.

SQL

CALL [1]();

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

ASelectEmployees

BListEmployees

CFetchEmployees

DGetAllEmployees

Attempts:

3 left

💡 Hint

Common Mistakes

Using a different procedure name that does not exist.

Forgetting to use CALL keyword.

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

3fill in blank

hard

Fix the error in the stored procedure creation by completing the missing keyword.

SQL

CREATE [1] GetEmployeeCount() BEGIN SELECT COUNT(*) FROM employees; END;

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

APROCEDURE

BEVENT

CTRIGGER

DFUNCTION

Attempts:

3 left

💡 Hint

Common Mistakes

Using FUNCTION instead of PROCEDURE.

Using TRIGGER or EVENT which are different database objects.

The correct keyword to create a stored procedure is PROCEDURE.

4fill in blank

hard

Fill both blanks to create a stored procedure that takes an employee ID and returns the employee's name.

SQL

CREATE PROCEDURE GetEmployeeName([1] INT) BEGIN SELECT name FROM employees WHERE id = [2]; END;

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

Aemp_id

Bemployee_id

Attempts:

3 left

💡 Hint

Common Mistakes

Using different names for the parameter and in the WHERE clause.

Using a name not declared as a parameter.

The parameter name emp_id is used both in the procedure definition and in the WHERE clause to filter by employee ID.

5fill in blank

hard

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

SQL

CREATE PROCEDURE AddEmployee([1] VARCHAR(50), [2] INT) BEGIN INSERT INTO employees (name, age) VALUES ([3], [2]); END;

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

Aemp_name

Bemp_age

Attempts:

3 left

💡 Hint

Common Mistakes

Using different names in the VALUES clause than in the parameters.

Mixing up the order of parameters.

The procedure takes two parameters: emp_name for the employee's name and emp_age for the age. The INSERT uses emp_name and emp_age accordingly.