Test Your MySQL Stored Procedure Knowledge with Our SQL Quiz

Progress

True / False

A MySQL stored procedure can be used to encapsulate SQL statements for reuse.

True False

Stored procedures allow you to save a series of SQL statements and reuse them, which can simplify complex operations and improve code maintainability.

True / False

In MySQL, a stored procedure cannot accept input parameters.

True False

MySQL stored procedures can accept input parameters, which allows for dynamic execution based on the provided inputs.

True / False

MySQL stored procedures are only available in the Enterprise edition of MySQL.

True False

MySQL stored procedures are available in both the Community and Enterprise editions of MySQL.

True / False

A MySQL stored procedure can return multiple result sets.

True False

MySQL stored procedures can return multiple result sets by executing multiple SELECT statements within the procedure.

True / False

MySQL, you can use the DELIMITER keyword to change the delimiter while defining a stored

True False

The DELIMITER keyword is used in MySQL to change the statement delimiter temporarily, allowing the procedure to be defined without interference from the default delimiter ;.

True / False

MySQL stored procedures cannot include transaction control statements like COMMIT and ROLLBACK.

True False

MySQL stored procedures can include transaction control statements such as COMMIT and ROLLBACK, allowing for better control over transaction management within the procedure.

True / False

MySQL stored procedures support error handling using the DECLARE ... HANDLER statement

True False

MySQL allows for error handling in stored procedures using the DECLARE ... HANDLER statement to define actions for specific error conditions.

True / False

In MySQL, you cannot call a stored procedure from within another stored procedure.

True False

MySQL supports nested stored procedure calls, meaning you can call a stored procedure from within another stored procedure.

True / False

MySQL stored procedures can be used to implement complex business logic that cannot be achieved with simple SQL queries.

True False

Stored procedures can include complex control flow logic, loops, conditionals, and other programming constructs that enable the implementation of sophisticated business logic.

Q10

True / False

MySQL, the INVOKER security context is the default for stored procedures unless otherwise specified.

True False

The default security context for MySQL stored procedures is DEFINER, not INVOKER. This means the stored procedure executes with the privileges of the user who defined it, unless explicitly set to INVOKER.

Q11

Single Choice

What is a stored procedure in MySQL?

A predefined function that performs a specific task A scheduled task in MySQL A predefined SQL query that is stored in the database and can be reusedA temporary table in MySQL

A stored procedure is a set of SQL statements that can be stored in the database and executed repeatedly.

Q12

Single Choice

How do you create a stored procedure in MySQL?

CREATE FUNCTION CREATE PROCEDURE CREATE TRIGGERCREATE EVENT

The CREATE PROCEDURE statement is used to create a new stored procedure in MySQL.

Q13

Single Choice

Which command is used to execute a stored procedure in MySQL?

EXECUTE RUN CALLPERFORM

The CALL statement is used to execute a stored procedure in MySQL.

Q14

Single Choice

How do you define an input parameter in a MySQL stored procedure?

IN parameter_name datatype INPUT parameter_name datatype PARAMETER parameter_name datatypeINPUT_PARAM parameter_name datatype

In MySQL, input parameters are defined using the IN keyword followed by the parameter name and data type.

Q15

Single Choice

How can you handle errors in a MySQL stored procedure?

Using TRY...CATCH blocks Using ERROR...RESUME blocks Using DECLARE CONTINUE HANDLERUsing IF...ELSE blocks

MySQL uses DECLARE CONTINUE HANDLER or DECLARE EXIT HANDLER to handle errors within a stored procedure.

Q16

Single Choice

Which keyword is used to define a variable inside a MySQL stored procedure?

DECLARE DEFINE SETASSIGN

The DECLARE keyword is used to define variables inside a MySQL stored procedure.

Q17

Single Choice

How do you return multiple result sets from a MySQL stored procedure?

By using multiple SELECT statements By using multiple RETURN statements By using the OUTPUT keywordBy using the GET_RESULT keyword

In MySQL, multiple result sets can be returned by using multiple SELECT statements in the stored procedure.

Q18

Single Choice

How do you create a stored procedure with optional parameters in MySQL?

By defining the parameters with default values By using the OPTIONAL keyword By using the DEFAULT keywordBy using the OPTION keyword

Optional parameters in MySQL stored procedures are created by defining parameters with default values.

Q19

Single Choice

Which command is used to delete a stored procedure in MySQL?

DELETE PROCEDURE DROP PROCEDURE REMOVE PROCEDURETRUNCATE PROCEDURE

The DROP PROCEDURE statement is used to delete a stored procedure from the database in MySQL.

Q20

Single Choice

Which privilege is required to create a stored procedure in MySQL?

CREATE FUNCTION CREATE ROUTINE CREATE PROCEDURECREATE VIEW

In MySQL, the CREATE ROUTINE privilege is required to create stored procedures and functions.

Q21

Multiple Choice

Identify the correct SQL statement to create a stored procedure that inserts a new record into a table.

SQL Code

CREATE PROCEDURE insert_employee (IN emp_name VARCHAR(100), IN emp_salary DECIMAL(10,2))
BEGIN
INSERT INTO employees (name, salary)
VALUES (emp_name, emp_salary);
END;

CREATE PROCEDURE insert_employee (IN emp_name VARCHAR(100), IN emp_salary DECIMAL(10,2)) BEGIN INSERT INTO employees (name, salary) VALUES (emp_name, emp_salary); END; CREATE PROCEDURE insert_employee (emp_name VARCHAR(100), emp_salary DECIMAL(10,2)) BEGIN INSERT INTO employees (name, salary) VALUES (emp_name, emp_salary); END; CREATE PROCEDURE insert_employee (IN emp_name VARCHAR(100), emp_salary DECIMAL(10,2)) BEGIN INSERT INTO employees (name, salary) VALUES (emp_name, emp_salary); END;CREATE PROCEDURE insert_employee (IN emp_name VARCHAR(100), IN emp_salary DECIMAL(10,2)) BEGIN INSERT INTO employees (name, salary) VALUES (emp_name, emp_salary);

This stored procedure takes two input parameters and inserts them into the employees table.

Q22

Multiple Choice

Choose the correct SQL statement to create a stored procedure that updates an employee's salary based on their ID.

SQL Code

CREATE PROCEDURE update_salary (IN emp_id INT, IN new_salary DECIMAL(10,2))
BEGIN
UPDATE employees
SET salary = new_salary
WHERE id = emp_id;
END;

CREATE PROCEDURE update_salary (IN emp_id INT, IN new_salary DECIMAL(10,2)) BEGIN UPDATE employees SET salary = new_salary WHERE id = emp_id; END; CREATE PROCEDURE update_salary (emp_id INT, new_salary DECIMAL(10,2)) BEGIN UPDATE employees SET salary = new_salary WHERE id = emp_id; END; CREATE PROCEDURE update_salary (IN emp_id INT, IN new_salary DECIMAL(10,2)) BEGIN UPDATE employees SET salary = new_salary WHERE id = emp_id;CREATE PROCEDURE update_salary (IN emp_id INT, IN new_salary DECIMAL(10,2)) BEGIN SET salary = new_salary WHERE id = emp_id; END;

This stored procedure takes an employee ID and a new salary as input and updates the corresponding record in the employees table.

Q23

Multiple Choice

Determine the correct SQL statement to create a stored procedure that deletes an employee record based on their ID.

SQL Code

CREATE PROCEDURE delete_employee (IN emp_id INT)
BEGIN
DELETE FROM employees
WHERE id = emp_id;
END;

CREATE PROCEDURE delete_employee (IN emp_id INT) BEGIN DELETE FROM employees WHERE id = emp_id; END; CREATE PROCEDURE delete_employee (emp_id INT) BEGIN DELETE FROM employees WHERE id = emp_id; END; CREATE PROCEDURE delete_employee (IN emp_id INT) BEGIN DELETE FROM employees WHERE emp_id = id; END;CREATE PROCEDURE delete_employee (IN emp_id INT) BEGIN DELETE * FROM employees WHERE id = emp_id; END;

This stored procedure deletes a record from the employees table where the employee ID matches the provided input.

Q24

Multiple Choice

Which SQL statement correctly creates a stored procedure that retrieves an employee's details based on their ID?

SQL Code

CREATE PROCEDURE get_employee_details (IN emp_id INT)
BEGIN
SELECT * FROM employees
WHERE id = emp_id;
END;

CREATE PROCEDURE get_employee_details (IN emp_id INT) BEGIN SELECT * FROM employees WHERE id = emp_id; END; CREATE PROCEDURE get_employee_details (IN emp_id INT) BEGIN SELECT * FROM employees WHERE emp_id = id; END; CREATE PROCEDURE get_employee_details (emp_id INT) BEGIN SELECT * FROM employees WHERE id = emp_id; END;CREATE PROCEDURE get_employee_details (IN emp_id INT) BEGIN SELECT ALL FROM employees WHERE id = emp_id; END;

This stored procedure selects all columns from the employees table for a specific employee ID.

Q25

Multiple Choice

Select the correct SQL statement to create a stored procedure that counts the number of employees in a department.

SQL Code

CREATE PROCEDURE count_employees_in_department (IN dept_id INT)
BEGIN
SELECT COUNT(*) FROM employees
WHERE department_id = dept_id;
END;

CREATE PROCEDURE count_employees_in_department (IN dept_id INT) BEGIN SELECT COUNT(*) FROM employees WHERE department_id = dept_id; END; CREATE PROCEDURE count_employees_in_department (dept_id INT) BEGIN SELECT COUNT(*) FROM employees WHERE department_id = dept_id; END; CREATE PROCEDURE count_employees_in_department (IN dept_id INT) BEGIN SELECT COUNT(*) FROM employees WHERE dept_id = department_id; END;CREATE PROCEDURE count_employees_in_department (IN dept_id INT) BEGIN SELECT COUNT(*) FROM employees WHERE department_id = dept_id;

This stored procedure counts the number of employees in a specific department by using the department ID as input.

Q26

Multiple Choice

Determine the correct SQL statement to create a stored procedure that calculates the total sales for a given month.

SQL Code

CREATE PROCEDURE calculate_total_sales (IN sale_month DATE)
BEGIN
DECLARE total_sales DECIMAL(10,2);
SELECT SUM(amount) INTO total_sales
FROM sales
WHERE MONTH(sale_date) = MONTH(sale_month);
RETURN total_sales;
END;

CREATE PROCEDURE calculate_total_sales (IN sale_month DATE) BEGIN DECLARE total_sales DECIMAL(10,2); SELECT SUM(amount) INTO total_sales FROM sales WHERE MONTH(sale_date) = MONTH(sale_month); RETURN total_sales; END; CREATE PROCEDURE calculate_total_sales (sale_month DATE) BEGIN DECLARE total_sales DECIMAL(10,2); SELECT SUM(amount) INTO total_sales FROM sales WHERE MONTH(sale_date) = MONTH(sale_month); RETURN total_sales; END; CREATE PROCEDURE calculate_total_sales (IN sale_month DATE) BEGIN DECLARE total_sales DECIMAL(10,2); SELECT SUM(amount) FROM sales WHERE MONTH(sale_date) = MONTH(sale_month); RETURN total_sales; END;CREATE PROCEDURE calculate_total_sales (IN sale_month DATE) BEGIN DECLARE total_sales DECIMAL(10,2); SELECT SUM(amount) INTO total_sales FROM sales WHERE MONTH(sale_date) = MONTH(sale_month); END;

This stored procedure calculates the total sales for the given month by summing the amounts from the sales table.

Q27

Multiple Choice

Which SQL statement correctly creates a stored procedure that logs changes made to employee salaries?

SQL Code

CREATE PROCEDURE log_salary_changes (IN emp_id INT, IN old_salary DECIMAL(10,2), IN new_salary DECIMAL(10,2))
BEGIN
INSERT INTO salary_log (employee_id, old_salary, new_salary, change_date)
VALUES (emp_id, old_salary, new_salary, NOW());
END;

CREATE PROCEDURE log_salary_changes (IN emp_id INT, IN old_salary DECIMAL(10,2), IN new_salary DECIMAL(10,2)) BEGIN INSERT INTO salary_log (employee_id, old_salary, new_salary, change_date) VALUES (emp_id, old_salary, new_salary, NOW()); END; CREATE PROCEDURE log_salary_changes (emp_id INT, old_salary DECIMAL(10,2), new_salary DECIMAL(10,2)) BEGIN INSERT INTO salary_log (employee_id, old_salary, new_salary, change_date) VALUES (emp_id, old_salary, new_salary, NOW()); END; CREATE PROCEDURE log_salary_changes (IN emp_id INT, IN old_salary DECIMAL(10,2), IN new_salary DECIMAL(10,2)) BEGIN INSERT INTO salary_log (employee_id, old_salary, new_salary, change_date) VALUES (emp_id, old_salary, new_salary, CURDATE()); END;CREATE PROCEDURE log_salary_changes (IN emp_id INT, IN old_salary DECIMAL(10,2), IN new_salary DECIMAL(10,2)) BEGIN INSERT INTO salary_log (employee_id, old_salary, new_salary, change_date) VALUES (emp_id, old_salary, new_salary, SYSDATE()); END;

This stored procedure logs the changes made to an employee's salary in the salary_log table, recording the old and new salaries along with the change date.

Q28

Multiple Choice

Identify the correct SQL statement to create a stored procedure that checks for duplicate entries before inserting a new record.

SQL Code

CREATE PROCEDURE insert_unique_employee (IN emp_name VARCHAR(100), IN emp_salary DECIMAL(10,2))
BEGIN
IF NOT EXISTS (SELECT 1 FROM employees WHERE name = emp_name) THEN
INSERT INTO employees (name, salary)
VALUES (emp_name, emp_salary);
END IF;
END;

CREATE PROCEDURE insert_unique_employee (IN emp_name VARCHAR(100), IN emp_salary DECIMAL(10,2)) BEGIN IF NOT EXISTS (SELECT 1 FROM employees WHERE name = emp_name) THEN INSERT INTO employees (name, salary) VALUES (emp_name, emp_salary); END IF; END; CREATE PROCEDURE insert_unique_employee (IN emp_name VARCHAR(100), IN emp_salary DECIMAL(10,2)) BEGIN IF NOT EXISTS (SELECT name FROM employees WHERE name = emp_name) THEN INSERT INTO employees (name, salary) VALUES (emp_name, emp_salary); END IF; END; CREATE PROCEDURE insert_unique_employee (IN emp_name VARCHAR(100), IN emp_salary DECIMAL(10,2)) BEGIN IF NOT EXISTS (SELECT 1 FROM employees WHERE name = emp_name) THEN INSERT INTO employees (name, salary) VALUES (emp_name, emp_salary); END;CREATE PROCEDURE insert_unique_employee (IN emp_name VARCHAR(100), IN emp_salary DECIMAL(10,2)) BEGIN IF EXISTS (SELECT 1 FROM employees WHERE name = emp_name) THEN INSERT INTO employees (name, salary) VALUES (emp_name, emp_salary); END IF; END;

This stored procedure checks if an employee with the same name already exists in the employees table before inserting a new record.

Q29

Multiple Choice

Select the correct SQL statement to create a stored procedure that processes a batch of records in a loop.

SQL Code

CREATE PROCEDURE process_records ()
BEGIN
DECLARE finished INT DEFAULT 0;
DECLARE record_id INT;
DECLARE cur CURSOR FOR SELECT id FROM records WHERE processed = 0;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = 1;
OPEN cur;
record_loop: LOOP
FETCH cur INTO record_id;
IF finished THEN
LEAVE record_loop;
END IF;
-- Processing logic here
END LOOP;
CLOSE cur;
END;

CREATE PROCEDURE process_records () BEGIN DECLARE finished INT DEFAULT 0; DECLARE record_id INT; DECLARE cur CURSOR FOR SELECT id FROM records WHERE processed = 0; DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = 1; OPEN cur; record_loop: LOOP FETCH cur INTO record_id; IF finished THEN LEAVE record_loop; END IF; -- Processing logic here END LOOP; CLOSE cur; END; CREATE PROCEDURE process_records () BEGIN DECLARE finished INT DEFAULT 0; DECLARE record_id INT; DECLARE cur CURSOR FOR SELECT id FROM records WHERE processed = 0; DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = 1; OPEN cur; record_loop: LOOP FETCH cur INTO record_id; IF finished THEN LEAVE record_loop; END IF; END LOOP; CLOSE cur; END; CREATE PROCEDURE process_records () BEGIN DECLARE finished INT DEFAULT 0; DECLARE record_id INT; DECLARE cur CURSOR FOR SELECT id FROM records WHERE processed = 0; DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = 1; OPEN cur; record_loop: LOOP FETCH cur INTO record_id; IF finished THEN LEAVE record_loop; END IF; -- Processing logic here END LOOP; END;CREATE PROCEDURE process_records () BEGIN DECLARE finished INT DEFAULT 0; DECLARE record_id INT; DECLARE cur CURSOR FOR SELECT id FROM records WHERE processed = 0; DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = 1; OPEN cur; LOOP FETCH cur INTO record_id; IF finished THEN LEAVE record_loop; END IF; -- Processing logic here END LOOP; CLOSE cur; END;

This stored procedure uses a cursor to loop through unprocessed records, processing each record in turn until all have been handled.

Q30

Multiple Choice

Determine the correct SQL statement to create a stored procedure that rolls back a transaction if an error occurs.

SQL Code

CREATE PROCEDURE safe_transaction ()
BEGIN
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
ROLLBACK;
END;
START TRANSACTION;
-- Transaction logic here
COMMIT;
END;

CREATE PROCEDURE safe_transaction () BEGIN DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN ROLLBACK; END; START TRANSACTION; -- Transaction logic here COMMIT; END; CREATE PROCEDURE safe_transaction () BEGIN DECLARE CONTINUE HANDLER FOR SQLEXCEPTION BEGIN ROLLBACK; END; START TRANSACTION; -- Transaction logic here COMMIT; END; CREATE PROCEDURE safe_transaction () BEGIN DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN ROLLBACK; END; START TRANSACTION; -- Transaction logic here ROLLBACK; END;CREATE PROCEDURE safe_transaction () BEGIN DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN ROLLBACK; END; BEGIN TRANSACTION; -- Transaction logic here COMMIT; END;

This stored procedure uses an EXIT handler to catch SQL exceptions and roll back the transaction if any error occurs during the execution of the transaction logic.

MySQL Database Quiz Questions