Stored Procedures Quiz - Languages SQL

Free practice quiz: Stored Procedures (Languages SQL). 49 multiple-choice questions with answers and explanations. Play online, no sign-up needed.

Sample questions

1. Which keyword is used to define a stored procedure in SQL?

   1. CREATE PROCEDURE
   2. DEFINE PROCEDURE
   3. MAKE PROCEDURE
   4. INSERT PROCEDURE
   Show answer

   Answer: A. CREATE PROCEDURE

   The `CREATE PROCEDURE` statement is the standard SQL syntax for defining a stored procedure.

2. What is the difference between a stored procedure and a function in SQL?

   1. Functions can only return single scalar values, procedures can return multiple values or result sets
   2. Procedures cannot accept parameters, functions can
   3. Functions are compiled, procedures are not
   4. Procedures are always faster than functions
   Show answer

   Answer: A. Functions can only return single scalar values, procedures can return multiple values or result sets

   While there are nuances across different SQL dialects, a common distinction is that SQL functions are designed to return a single scalar value (or a table in some dialects), whereas stored procedures can return multiple result sets, output parameters, and have more control flow.

3. What does it mean for a stored procedure to be 'idempotent'?

   1. It can only be executed once
   2. Executing it multiple times with the same input produces the same result and state change
   3. It always returns a different result
   4. It requires specific hardware to run
   Show answer

   Answer: B. Executing it multiple times with the same input produces the same result and state change

   An idempotent operation is one that can be applied multiple times without changing the result beyond the initial application. This is a desirable property for procedures that modify data.

4. What is a 'cursor' in the context of stored procedures?

   1. A statement that returns a result set
   2. A mechanism to iterate through rows of a result set one by one
   3. A type of stored procedure parameter
   4. A temporary storage area for data
   Show answer

   Answer: B. A mechanism to iterate through rows of a result set one by one

   Cursors allow for row-by-row processing of data returned by a query within a stored procedure, though they are generally less efficient than set-based operations.

5. What is a common stored procedure used for managing permissions or executing administrative tasks?

   1. `SELECT * FROM SomeTable`
   2. `EXEC sp_configure` (SQL Server)
   3. `INSERT INTO LogTable`
   4. `CREATE TABLE TemporaryData`
   Show answer

   Answer: B. `EXEC sp_configure` (SQL Server)

   System stored procedures, like `sp_configure` in SQL Server, are often used for administrative and configuration tasks. Note that system procedure names and availability vary by RDBMS.

More quizzes

• DDL
• DML
• Indexes
• Joins

Browse all SQL quizzes