Advanced Interview Questions

A stored procedure is a set of SQL statements that can be saved and reused. They encapsulate business logic within the database, improve performance, and reduce network traffic. You can call them with parameters to execute complex operations. For example: ```sql CREATE PROCEDURE GetEmployeeDetails(@id INT) AS BEGIN SELECT * FROM employees WHERE id = @id; END; ```

A transaction is a sequence of SQL operations executed as a single unit of work. Transactions ensure ACID properties (Atomicity, Consistency, Isolation, Durability), meaning that either all operations in a transaction are completed successfully, or none are. You can control transactions with `BEGIN`, `COMMIT`, and `ROLLBACK` commands.

Normalization is the process of organizing a database to reduce redundancy and improve data integrity. It involves dividing large tables into smaller tables and defining relationships between them. The normal forms (1NF, 2NF, 3NF, etc.) are guidelines for designing a normalized database.

An index in SQL is a database object that improves the speed of data retrieval operations on a table by providing a fast lookup mechanism. Indexes are typically created on columns that are frequently queried. However, they can also slow down `INSERT` and `UPDATE` operations.

A foreign key is a column (or a set of columns) in one table that references the primary key in another table. It establishes a relationship between the two tables, ensuring referential integrity. For example, the `department_id` in an `employees` table might be a foreign key referencing the `id` column in a `departments` table.