Quiz for Creating Stored Procedures

Q1: Which SQL keyword is used to create a stored procedure?

A. MAKE PROCEDURE B. NEW PROCEDURE C. CREATE PROCEDURE D. INIT PROCEDURE

Q2: Which clause is used to define input parameters in a stored procedure?

A. IN B. OUT C. INOUT D. ALL OF THE ABOVE

Q3: How do you begin the body of a stored procedure in SQL?

A. BEGIN B. START C. OPEN D. RUN

Q4: Which SQL delimiter is often changed when defining a stored procedure?

A. ; B. : C. . D. ,

Q5: Which statement ends the stored procedure body?

A. END B. CLOSE C. FINISH D. EXIT

Q6: Which of the following can be used inside stored procedures?

A. IF statements B. LOOPs C. DECLAREs D. All of the above

Q7: What is the purpose of the DELIMITER command while creating stored procedures?

A. To change the default statement delimiter B. To comment code C. To end procedure D. To escape characters

Q8: Which command can be used to view all stored procedures?

A. SHOW PROCEDURE STATUS B. SHOW FUNCTIONS C. SHOW TRIGGERS D. SHOW EVENTS

Q9: What happens if you create a procedure with an existing name?

A. Error is thrown B. Old procedure is overwritten C. Creates a duplicate D. Renames it automatically

Q10: What is used to exit a procedure early?

A. LEAVE B. RETURN C. EXIT D. BREAK

Q11: What keyword is used to define a local variable in a stored procedure?

A. DECLARE B. SET C. DEFINE D. LOCAL

Q12: Which of the following is a valid way to call a stored procedure?

A. CALL procedure_name() B. EXEC procedure_name() C. RUN procedure_name() D. START procedure_name()

Q13: Which datatype can be used for procedure parameters?

A. INT B. VARCHAR C. DATE D. All of the above

Q14: What clause follows a CREATE PROCEDURE to define parameters?

A. (parameter list) B. [parameter list] C. WHERE D. WITH

Q15: Which privilege is required to create stored procedures?

A. CREATE ROUTINE B. CREATE TRIGGER C. CREATE VIEW D. CREATE TABLE

Q16: Can stored procedures return result sets?

A. Yes, using SELECT B. No C. Only with OUTPUT D. Only with CURSOR

Q17: What is the scope of a variable declared inside a stored procedure?

A. Local to procedure B. Global to all sessions C. Session-level D. Database-wide

Q18: What does the keyword DETERMINISTIC signify in stored procedures?

A. Procedure always returns same output for same input B. Procedure may change output C. Procedure cannot be run D. Procedure runs faster

Q19: Can stored procedures be recursive?

A. Yes, with certain limitations B. No C. Only in functions D. Only in triggers

Q20: What is the role of SQL_MODE in stored procedure creation?

A. Affects behavior of SQL syntax B. Sets user roles C. Changes permissions D. Disables procedures