WorksheetsWeek 9 Quiz 3 Stored Procedures UDF
Total questions: 15
Worksheet time: 9mins
What is the term for a pre-compiled set of one or more SQL statements stored in a database server that can be executed as a single unit?
(a)
What is a key benefit of using stored procedures, as discussed in the presentation?
They can be used directly in a WHERE clause.
They are always faster than a series of individual SQL statements.
They enhance security by allowing users to access data without direct table permissions.
They can only return a single value.
What is the primary difference between a stored procedure and a user-defined function in terms of their return value?
Stored procedures can only return an integer, while functions can return any data type.
Stored procedures can only return one value, while functions can return multiple values.
A stored procedure may or may not return a value, while a user-defined function must return a value.
There is no difference in their return values.
Which SQL command is used to execute a stored procedure?
(a)
What type of User-Defined Function (UDF) is designed to return a single value, such as a string, integer, or date?
Inline Table-Valued Function
Multi-Statement Table-Valued Function
Scalar Function
Aggregate Function
Which of the following is a capability of a stored procedure but is generally not a capability of a User-Defined Function?
Reading from a database table.
Returning multiple result sets.
Accepting input parameters.
Using a SELECT statement.
What is a key difference in how Stored Procedures and User-Defined Functions handle their use in SQL statements?
Stored procedures can be used directly in a SELECT statement, while UDFs cannot.
Stored procedures are executed with EXEC, while UDFs are called by their name within a SELECT statement's FROM or WHERE clause.
UDFs can modify the database with INSERT, UPDATE, or DELETE, while stored procedures cannot.
Stored procedures must be executed in a separate SQL statement, while a UDF can be used within a SELECT statement's FROM or WHERE clause.
What is the purpose of the DELIMITER command when creating a stored procedure or UDF in some SQL environments?
It defines the character set for the procedure.
It changes the statement terminator from a semicolon to a different character.
It specifies the database schema for the procedure to a different character.
It sets the access permissions for the procedure.
When should you consider using a stored procedure instead of a function?
When you need to perform data manipulation like INSERT or UPDATE.
When you need a result to be used in a WHERE and FIND clause.
When you need to return a single, atomic value.
When you are performing a complex calculation that doesn't involve data manipulation.
What is a key advantage of using parameters with stored procedures?
It makes the procedure non-reusable.
It prevents the procedure from executing, but more flexible and reusable.
It allows for dynamic input, more flexible and reusable.
It restricts the procedure to a single, static value.
Scenario: You are developing a database application where you need to create a reusable component that performs several INSERT and UPDATE operations on different tables as a single transaction. You also need to control access to this operation so that users can execute it without having direct table modification permissions. Question: Based on this scenario, which is the most appropriate database object to create?
A User-Defined Function, because it can perform complex logic.
A View, because it simplifies data retrieval.
A Stored Procedure, because it supports DML statements and offers a security layer.
A Scalar Function, because it returns a single value after a calculation.
Scenario: A developer writes a SQL query that uses a subquery to calculate a value and filter a result set. They find that this subquery is used in multiple different reports and queries. To improve maintainability and performance, they want to replace the subquery with a more efficient object. Question: What is the best choice to replace this recurring subquery and why?
A VIEW because it can encapsulate the subquery logic.
A SCALAR FUNCTION because it can encapsulate the subquery logic and return a single value for use in a WHERE clause.
A STORED PROCEDURE because it is pre-compiled and offers better performance.
An INLINE TABLE-VALUED FUNCTION because it can be used in the FROM clause and is often more efficient than a subquery.
Scenario: A database contains a Customers table. You need to create a routine that takes a CustomerID as input and returns a count of all orders placed by that customer. This routine will be used within a SELECT statement to show each customer's order count in a report. Question: What is the most appropriate database object to create for this task?
A Stored Procedure, as it can accept input.
A Scalar User-Defined Function, as it can be used in a SELECT statement and returns a single value.
A Table-Valued User-Defined Function, as it can return multiple rows.
A simple SQL query without a function or procedure.
Scenario: A stored procedure is created to update an employee's salary and takes two parameters: @EmployeeID and @NewSalary. A developer calls the procedure using EXEC UpdateEmployeeSalary 101, 75000. Question: Which of the following is the most likely purpose of this stored procedure call?
To return a list of employees with salaries less than 75000.
To calculate the total salary of employee ID 101.
To update the salary of employee with ID 101 to 75000.
To insert a new employee record with a salary of 75000.
Scenario: A UDF is created to calculate an employee's annual bonus based on their performance score. The function is called within a SELECT statement on the Employees table: SELECT EmployeeName, dbo.CalculateBonus(PerformanceScore) AS Bonus FROM Employees;. Question: Why is this a valid use case for a UDF instead of a stored procedure?
Stored procedures cannot be used in a SELECT statement, whereas UDFs can.
UDFs are faster than stored procedures for calculations.
Stored procedures cannot accept parameters like PerformanceScore.
UDFs can perform DML operations, which are needed for this calculation.
