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.

Practice

1. Why are stored procedures useful in a database system?

easy

A. They slow down the database to prevent overload.

B. They make the database look colorful and pretty.

C. They allow users to change data without any rules.

D. They save time by running pre-written commands quickly.

Solution

1. Step 1: Understand stored procedures purpose

   Stored procedures are saved sets of commands that run quickly when called.

2. Step 2: Identify the benefit in options

   They save time by running pre-written commands quickly. says they save time by running pre-written commands, which matches the purpose.

3. Final Answer:

   They save time by running pre-written commands quickly. -> Option D

4. Quick Check:

   Stored procedures save time = A [OK]

Hint: Stored procedures run saved commands fast [OK]

Common Mistakes:

• Thinking stored procedures change database appearance
• Believing stored procedures allow rule-free data changes
• Assuming stored procedures slow down the system

2. Which of the following is the correct way to call a stored procedure named GetUserData in SQL?

easy

A. CALL GetUserData();

B. EXECUTE GetUserData;

C. RUN PROCEDURE GetUserData();

D. START GetUserData();

Solution

1. Step 1: Recall stored procedure call syntax

   Standard SQL uses the CALL keyword followed by procedure name and parentheses.

2. Step 2: Match syntax with options

   CALL GetUserData(); uses CALL GetUserData(); which is correct syntax.

3. Final Answer:

   CALL GetUserData(); -> Option A

4. Quick Check:

   CALL syntax = D [OK]

Hint: Use CALL to run stored procedures [OK]

Common Mistakes:

• Using EXECUTE without parentheses
• Using RUN PROCEDURE which is invalid
• Using START which is not SQL syntax

3. Consider this stored procedure that returns the count of users older than 30:

CREATE PROCEDURE CountOlderUsers()
BEGIN
  SELECT COUNT(*) AS UserCount FROM Users WHERE Age > 30;
END;

What will be the output when you run CALL CountOlderUsers(); if the Users table has 5 users older than 30?

medium

A. UserCount = 5

B. UserCount = 0

C. Syntax error

D. No output

Solution

1. Step 1: Understand the procedure logic

   The procedure counts users with Age > 30 and returns that count as UserCount.

2. Step 2: Apply given data

   Since 5 users are older than 30, the count returned will be 5.

3. Final Answer:

   UserCount = 5 -> Option A

4. Quick Check:

   Count of users older than 30 = 5 [OK]

Hint: Count matches condition in SELECT query [OK]

Common Mistakes:

• Assuming no output because procedure has no RETURN
• Thinking syntax error due to missing parameters
• Confusing count with total users

4. You wrote this stored procedure:

CREATE PROCEDURE UpdateSalary()
BEGIN
  UPDATE Employees SET Salary = Salary + 1000
END;

When you try to run CALL UpdateSalary();, you get an error. What is the likely problem?

medium

A. Procedure name cannot have uppercase letters

B. Missing semicolon after UPDATE statement

C. CALL keyword is incorrect

D. Salary column cannot be updated

Solution

1. Step 1: Check procedure syntax

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

2. Step 2: Identify missing semicolon

   The UPDATE statement lacks a semicolon at the end, causing syntax error.

3. Final Answer:

   Missing semicolon after UPDATE statement -> Option B

4. Quick Check:

   Semicolon missing = B [OK]

Hint: End each SQL statement with semicolon inside procedures [OK]

Common Mistakes:

• Thinking procedure names can't have uppercase letters
• Believing CALL keyword is wrong
• Assuming Salary column is read-only

5. A company wants to ensure that every time a new order is added, the total sales are updated automatically. Which benefit of stored procedures best explains why using a stored procedure for this task is a good idea?

hard

A. Stored procedures allow users to edit data without restrictions.

B. Stored procedures slow down the database to avoid errors.

C. Stored procedures help keep data safe and organized by controlling updates.

D. Stored procedures make the database design more colorful.

Solution

1. Step 1: Understand the task requirement

   The company wants automatic, controlled updates to total sales when orders are added.

2. Step 2: Match benefit of stored procedures

   Stored procedures help keep data safe and organized by controlling how updates happen, which fits the need.

3. Final Answer:

   Stored procedures help keep data safe and organized by controlling updates. -> Option C

4. Quick Check:

   Data safety and control = C [OK]

Hint: Stored procedures control and protect data updates [OK]

Common Mistakes:

• Thinking stored procedures allow unrestricted data edits
• Believing stored procedures slow down the system
• Confusing appearance with functionality