Assignment: SQL Server Functions and Stored Procedures | SQL for Beginners - Software Development PDF Download

Multiple Choice Questions (MCQs)

Q1: What is the purpose of the COUNT() function in SQL Server?
(a) To return the total number of rows in a table
(b) To sum the values of a column
(c) To modify the values in a table
(d) To update a record
Ans: (a)

Q2: Which SQL function is used to find the highest value in a column?
(a) MAX()
(b) MIN()
(c) UPPER()
(d) LENGTH()
Ans: (a)

Q3: What is a stored procedure in SQL Server?
(a) A function used to format text
(b) A precompiled set of SQL statements stored in the database
(c) A security patch for SQL Server
(d) A method to delete records
Ans: (b)

Q4: How do you execute a stored procedure in SQL Server?
(a) CALL procedure_name
(b) EXEC procedure_name
(c) RUN procedure_name
(d) START procedure_name
Ans: (b)

Q5: What is the difference between scalar and table-valued functions in SQL Server?
(a) Scalar functions return a single value, while table-valued functions return a table
(b) They are both used for data deletion
(c) Scalar functions are faster than table-valued functions
(d) Table-valued functions cannot accept parameters
Ans: (a)

Q6: Which function is used to find the total sum of values in a column?
(a) COUNT()
(b) SUM()
(c) AVG()
(d) MAX()
Ans: (b)

Q7: What does the CHARINDEX() function do in SQL Server?
(a) Finds the position of a substring within a string
(b) Converts a string to uppercase
(c) Replaces a substring with another string
(d) Returns the length of a string
Ans: (a)

Q8: How can you modify an existing stored procedure in SQL Server?
(a) ALTER PROCEDURE
(b) UPDATE PROCEDURE
(c) MODIFY PROCEDURE
(d) CHANGE PROCEDURE
Ans: (a)

Higher Order Thinking Skills (HOTS)

Q1: How can stored procedures improve database security and performance in SQL Server?
Ans: Stored procedures enhance security by restricting direct access to tables, allowing execution of predefined queries with controlled permissions. Performance is improved through precompilation and reduced network traffic.
Example:
CREATE PROCEDURE GetEmployeeDetails
AS
BEGIN
    SELECT EmployeeID, Name, Salary FROM Employees WHERE Status = 'Active';
END;
GO
Security Benefit: Users with execution rights don’t need direct table access.
Performance Benefit: The procedure is precompiled, reducing execution time.

Q2: Why would a developer use a function instead of a stored procedure in SQL Server?
Ans: Functions return a value and can be used in SELECT statements, unlike stored procedures, which execute multiple operations but cannot be called directly in queries.
Example:
CREATE FUNCTION GetBonus(@Salary INT)
RETURNS INT
AS
BEGIN
    RETURN @Salary * 0.10;
END;
GO
SELECT EmployeeID, Name, Salary, dbo.GetBonus(Salary) AS Bonus FROM Employees;
Use Case: Functions work within queries, allowing inline computations.

Q3: What are the advantages of using system-defined functions in SQL Server?
Ans: System functions handle common operations efficiently, reducing custom logic and improving maintainability.
Example:
SELECT GETDATE() AS CurrentDate, LEN('SQLServer') AS StringLength;
Advantages:
✔ GETDATE() retrieves the current timestamp.
✔ LEN() finds string length efficiently.
✔ Built-in optimization for better performance.

Q4: How can you optimize stored procedure performance in a large-scale SQL Server database?
Ans: Optimization techniques include indexing, avoiding cursors, reducing dynamic SQL, and using proper JOINs.
Example:
CREATE PROCEDURE GetDepartmentEmployees @DeptID INT
AS
BEGIN
    SET NOCOUNT ON;

    SELECT EmployeeID, Name, Salary
    FROM Employees WITH (INDEX(idx_DeptID))
    WHERE DepartmentID = @DeptID;
END;
GO
Optimizations Applied:
✔ Index Usage: Indexed lookup speeds up queries.
✔ SET NOCOUNT ON reduces overhead from row count messages.
✔ Parameterized Query: Prevents SQL injection and enhances performance.

Q5: Explain the difference between inline table-valued functions and multi-statement table-valued functions in SQL Server.
Ans:
Inline Table-Valued Function (Faster, Optimized Query Execution):
CREATE FUNCTION GetActiveEmployees()
RETURNS TABLE
AS
RETURN (
    SELECT EmployeeID, Name FROM Employees WHERE Status = 'Active'
);
GO
Usage:
SELECT * FROM GetActiveEmployees();
✔ Optimized Execution: Works like a view, integrates into query plans.

Multi-Statement Table-Valued Function (More Flexible but Slower):
CREATE FUNCTION GetHighEarners()
RETURNS @Employees TABLE (EmployeeID INT, Name NVARCHAR(100))
AS
BEGIN
    INSERT INTO @Employees
    SELECT EmployeeID, Name FROM Employees WHERE Salary > 60000;
    RETURN;
END;
GO
Usage:
SELECT * FROM GetHighEarners();
✔ Slower Performance: Uses a table variable, causing additional processing overhead.

Fill in the Blanks

Q1: The AVG() function is used to calculate the average value of a column.
Ans: True

Q2: The GETDATE() function returns the current system date and time.
Ans: True

Q3: The STUFF() function inserts a string into another string at a specified position.
Ans: True

Q4: The TRY_CONVERT() function is used to attempt data type conversion and return NULL if the conversion fails.
Ans: True

Q5: The ISNULL() function replaces NULL values with a specified replacement value.
Ans: True

True or False

1. The LEN() function in SQL Server returns the length of a string.
Ans: True

2. A stored procedure cannot accept parameters.
Ans: False

3. The FORMAT() function can be used to format date and number values.
Ans: True

4. The CASE statement is only used for error handling in SQL Server.
Ans: False

5. A function in SQL Server can return multiple values.
Ans: False

Hands-On Questions

Q1: Write a SQL query to create a stored procedure that retrieves all employees with a salary greater than 60,000.
Ans: CREATE PROCEDURE GetHighPaidEmployees
AS
SELECT * FROM Employees WHERE salary > 60000;
GO

Q2: Write an SQL query to create a function that returns the square of a given number.
Ans: CREATE FUNCTION Square(@Number INT)
RETURNS INT
AS
BEGIN
RETURN @Number * @Number;
END

Q3: Write an SQL query to count the number of employees in each department using GROUP BY.
Ans: SELECT department, COUNT(*) AS employee_count
FROM Employees
GROUP BY department;

Q4: Write an SQL query to find the length of the longest employee name in the Employees table.
Ans: SELECT MAX(LEN(name)) AS longest_name_length
FROM Employees;

Q5: Write an SQL query to update a stored procedure to include an additional condition for filtering records.
Ans: ALTER PROCEDURE GetHighPaidEmployees
AS
SELECT * FROM Employees WHERE salary > 60000 AND department = 'IT';
GO