Interview question and answer on SQL Stored proc, sql Function, sql View, Sql indexes and sql triggers with full explanation and code example

Here are the top 50 interview questions and answers on SQL stored procedures, SQL functions, SQL views, SQL indexes, and SQL triggers, along with explanations and code examples:

1. What is a stored procedure in SQL? A stored procedure is a prepared SQL code that can be stored and executed on the database server. It provides a way to group multiple SQL statements into a single unit and can accept input parameters and return output parameters or result sets.

2. How do you create a stored procedure? To create a stored procedure, you can use the CREATE PROCEDURE statement in SQL. Here's an example:

sql
CREATE PROCEDURE GetEmployeeCount
AS
BEGIN
    SELECT COUNT(*) AS EmployeeCount FROM Employees;
END

3. What are the advantages of using stored procedures?

• Improved performance: Stored procedures are precompiled and stored on the server, reducing network traffic and improving execution speed.
• Code reusability: Stored procedures can be called from multiple applications, promoting code reuse and maintainability.
• Enhanced security: Access to the database can be controlled through stored procedures, limiting direct access to tables.
• Transaction management: Stored procedures allow for transaction handling, ensuring data integrity.

4. How do you execute a stored procedure? To execute a stored procedure, you can use the EXEC or EXECUTE statement in SQL. Here's an example:

sql
EXEC GetEmployeeCount;

5. What are SQL functions? SQL functions are predefined or user-defined routines that accept input parameters, perform an action, and return a value. They can be used within SQL statements to manipulate data or perform calculations.

6. What is the difference between a stored procedure and a function? Stored procedures do not return a value directly, while functions return a value. Functions can be used in SQL statements, such as SELECT, WHERE, and JOIN, while stored procedures are called separately using the EXEC or EXECUTE statement.

7. How do you create a SQL function? To create a SQL function, you can use the CREATE FUNCTION statement. Here's an example of creating a function to calculate the average salary:

sql
CREATE FUNCTION CalculateAverageSalary()
RETURNS DECIMAL(10, 2)
AS
BEGIN
    DECLARE @AverageSalary DECIMAL(10, 2);
    
    SELECT @AverageSalary = AVG(Salary) FROM Employees;
    
    RETURN @AverageSalary;
END

8. How do you execute a SQL function? To execute a SQL function, you can use it within a SQL statement. Here's an example:

sql
SELECT CalculateAverageSalary() AS AverageSalary;

9. What are SQL views? SQL views are virtual tables derived from the result of a predefined SQL query. They are named and can be used like regular tables, but they don't store any data themselves. Views provide an abstraction layer, simplifying complex queries and enhancing security.

10. How do you create a SQL view? To create a SQL view, you can use the CREATE VIEW statement. Here's an example:

sql
CREATE VIEW EmployeeView AS
SELECT EmployeeID, FirstName, LastName
FROM Employees
WHERE IsActive = 1;

11. How do you select data from a SQL view? To select data from a SQL view, you can use the SELECT statement as you would with a regular table. Here's an example:

sql
SELECT * FROM EmployeeView;

12. What are SQL indexes? SQL indexes are database structures that improve the performance of data retrieval operations. They allow for faster searching, sorting, and filtering of data by creating a copy of selected columns or expressions.

13. What types of indexes are commonly used in SQL? Common types of indexes in SQL include:

• Clustered index: Determines the physical order of rows in a table.
• Non-clustered index: Contains a copy of selected columns or expressions with a pointer to the actual data.
• Unique index: Ensures the uniqueness of values in the indexed columns.

14. How do you create an index on a table? To create an index on a table, you can use the CREATE INDEX statement. Here's an example:

sql
CREATE INDEX IX_Employee_LastName ON Employees (LastName);

15. What are SQL triggers? SQL triggers are stored procedures that are automatically executed in response to specific events, such as INSERT, UPDATE, or DELETE operations on a table. They are used to enforce business rules, maintain data integrity, or perform additional actions.

16. How do you create a SQL trigger? To create a SQL trigger, you can use the CREATE TRIGGER statement. Here's an example of creating an AFTER INSERT trigger:

sql
CREATE TRIGGER EmployeeAuditTrigger
AFTER INSERT
ON Employees
FOR EACH ROW
BEGIN
    INSERT INTO EmployeeAudit (EmployeeID, Action) VALUES (NEW.EmployeeID, 'Inserted');
END

17. What are the different types of SQL triggers? There are three types of SQL triggers:

• BEFORE trigger: Executed before the triggering event occurs.
• AFTER trigger: Executed after the triggering event occurs.
• INSTEAD OF trigger: Replaces the triggering event and performs a different action.

18. How do you drop a stored procedure? To drop a stored procedure, you can use the DROP PROCEDURE statement. Here's an example:

sql
DROP PROCEDURE GetEmployeeCount;

19. How do you drop a function? To drop a function, you can use the DROP FUNCTION statement. Here's an example:

sql
DROP FUNCTION CalculateAverageSalary;

20. How do you drop a view? To drop a view, you can use the DROP VIEW statement. Here's an example:

sql
DROP VIEW EmployeeView;

21. How do you drop an index? To drop an index, you can use the DROP INDEX statement. Here's an example:

sql
DROP INDEX IX_Employee_LastName ON Employees;

22. How do you drop a trigger? To drop a trigger, you can use the DROP TRIGGER statement. Here's an example:

sql
DROP TRIGGER EmployeeAuditTrigger;

23. What is the purpose of the NOLOCK hint in SQL? The NOLOCK hint is used to allow reading uncommitted data from a table during a query. It can improve query performance but may result in reading uncommitted or inconsistent data.

24. What is the purpose of the ROW_NUMBER() function in SQL? The ROW_NUMBER() function assigns a unique sequential number to each row within a specific result set. It is often used for pagination, ranking, or identifying duplicates.

25. What is a scalar function in SQL? A scalar function is a SQL function that returns a single value. It takes one or more input parameters and performs a calculation or manipulation to produce the result.

26. What is a table-valued function in SQL? A table-valued function is a SQL function that returns a table as its result. It can be used in the FROM clause of a SELECT statement and can accept input parameters.

27. What is the purpose of the EXISTS keyword in SQL? The EXISTS keyword is used to check the existence of rows returned by a subquery. It returns true if the subquery returns any rows; otherwise, it returns false.

28. What is the purpose of the HAVING clause in SQL? The HAVING clause is used to filter data based on the result of an aggregate function in a GROUP BY query. It allows specifying conditions on aggregated data.

29. What is a correlated subquery in SQL? A correlated subquery is a subquery that references columns from the outer query. The subquery is executed for each row of the outer query, allowing the subquery to depend on the values of the outer query.

30. What is the purpose of the COALESCE function in SQL? The COALESCE function is used to return the first non-null expression from a list of expressions. It is often used to handle null values and provide a default value.

31. What is the purpose of the UNION operator in SQL? The UNION operator is used to combine the result sets of two or more SELECT statements into a single result set. It removes duplicate rows from the final result.

32. What is the purpose of the INNER JOIN in SQL? The INNER JOIN is used to retrieve records that have matching values in both tables being joined. It combines rows from multiple tables based on a related column.

33. What is the purpose of the LEFT JOIN in SQL? The LEFT JOIN is used to retrieve all records from the left table and matching records from the right table. If no match is found, NULL values are returned for the right table.

34. What is the purpose of the GROUP BY clause in SQL? The GROUP BY clause is used to group rows based on one or more columns and apply aggregate functions, such as SUM, AVG, COUNT, etc., to each group.

35. What is the purpose of the ORDER BY clause in SQL? The ORDER BY clause is used to sort the result set based on one or more columns in ascending or descending order.

36. What is the purpose of the LIKE operator in SQL? The LIKE operator is used to perform pattern matching on a column value. It is often used with wildcard characters (% and _) to match specific patterns.

37. What is the purpose of the BETWEEN operator in SQL? The BETWEEN operator is used to select values within a specified range. It is inclusive, meaning the boundary values are included in the result.

38. What is the purpose of the CASE statement in SQL? The CASE statement is used to perform conditional logic within SQL queries. It allows for multiple conditions and returns different values based on the evaluation of those conditions.

39. What is normalization in database design? Normalization is the process of organizing data in a database to eliminate redundancy and dependency issues. It involves breaking down a database into smaller tables and defining relationships between them.

40. What are the different normalization forms in database design? The different normalization forms are:

• First Normal Form (1NF)
• Second Normal Form (2NF)
• Third Normal Form (3NF)
• Fourth Normal Form (4NF)
• Fifth Normal Form (5NF)

41. What is an ACID transaction in SQL? ACID stands for Atomicity, Consistency, Isolation, and Durability. An ACID transaction ensures that a group of database operations are executed in a reliable and consistent manner.

42. What is a deadlock in SQL? A deadlock occurs when two or more processes are waiting for each other to release resources, resulting in a situation where none of them can proceed. It can lead to a system halt or significant performance degradation.

43. What is database indexing? Database indexing is the process of creating data structures, called indexes, to improve the speed and efficiency of data retrieval operations. Indexes provide quick access to specific data in a database table.

44. What is a primary key in SQL? A primary key is a column or a set of columns that uniquely identifies each row in a database table. It ensures data integrity and provides a fast way to access specific records.

45. What is a foreign key in SQL? A foreign key is a column or a set of columns that establishes a link or relationship between two tables. It ensures referential integrity by enforcing that values in the foreign key column(s) match values in the referenced primary key column(s).

46. What is the purpose of the CASCADE option in a foreign key constraint? The CASCADE option, when specified in a foreign key constraint, ensures that any changes made to the referenced primary key values are automatically propagated to the foreign key values. It allows for automatic updates or deletions of related data.

47. What is the purpose of the DISTINCT keyword in SQL? The DISTINCT keyword is used to eliminate duplicate rows from the result set of a SELECT statement. It returns only unique values.

48. What is the purpose of the GROUP BY ROLLUP modifier in SQL? The GROUP BY ROLLUP modifier is used to generate multiple levels of grouping in a GROUP BY query. It provides subtotals and grand totals for the grouped data.

49. What is the purpose of the TRUNCATE TABLE statement in SQL? The TRUNCATE TABLE statement is used to quickly delete all rows from a table, while keeping the table structure intact. It is faster than using the DELETE statement for large tables.

50. What is the purpose of the WITH TIES option in the TOP clause in SQL? The WITH TIES option, when used with the TOP clause, ensures that if multiple rows have the same value and match the specified number of rows, they are all included in the result set.