Joins in SQL combine rows from two or more tables based on a related column. The main types are:

- INNER JOIN: Returns only rows where there is a match in both tables.
- LEFT JOIN (LEFT OUTER JOIN): Returns all rows from the left table and matched rows from the right table; NULL for non-matching right table rows.
- RIGHT JOIN (RIGHT OUTER JOIN): Returns all rows from the right table and matched rows from the left table; NULL for non-matching left table rows.
- FULL JOIN (FULL OUTER JOIN): Returns all rows from both tables, with NULLs for non-matching rows.
- CROSS JOIN: Returns the Cartesian product of both tables (all possible combinations).

Joins are used to retrieve related data across tables, such as combining employee and department information.
Example Sql Server:

-- INNER JOIN
SELECT e.EmployeeID, e.Name, d.DepartmentName
FROM Employees e
INNER JOIN Departments d ON e.DepartmentID = d.DepartmentID;
-- LEFT JOIN
SELECT e.EmployeeID, e.Name, d.DepartmentName
FROM Employees e
LEFT JOIN Departments d ON e.DepartmentID = d.DepartmentID;
-- RIGHT JOIN
SELECT e.EmployeeID, e.Name, d.DepartmentName
FROM Employees e
RIGHT JOIN Departments d ON e.DepartmentID = d.DepartmentID;
-- FULL JOIN
SELECT e.EmployeeID, e.Name, d.DepartmentName
FROM Employees e
FULL JOIN Departments d ON e.DepartmentID = d.DepartmentID;
-- CROSS JOIN
SELECT e.Name, d.DepartmentName
FROM Employees e
CROSS JOIN Departments d;

Example MySql:

-- INNER JOIN
SELECT e.EmployeeID, e.Name, d.DepartmentName
FROM Employees e
INNER JOIN Departments d ON e.DepartmentID = d.DepartmentID;
-- LEFT JOIN
SELECT e.EmployeeID, e.Name, d.DepartmentName
FROM Employees e
LEFT JOIN Departments d ON e.DepartmentID = d.DepartmentID;
-- RIGHT JOIN
SELECT e.EmployeeID, e.Name, d.DepartmentName
FROM Employees e
RIGHT JOIN Departments d ON e.DepartmentID = d.DepartmentID;
-- FULL JOIN (MySQL 8.0.14+)
SELECT e.EmployeeID, e.Name, d.DepartmentName
FROM Employees e
FULL JOIN Departments d ON e.DepartmentID = d.DepartmentID;
-- CROSS JOIN
SELECT e.Name, d.DepartmentName
FROM Employees e
CROSS JOIN Departments d;

Indexes improve SELECT query performance but impact write operations:
- INSERT: Indexes must be updated to include the new row, increasing write time. More indexes mean higher overhead.
- UPDATE: If indexed columns are modified, the index must be updated, adding overhead. Non-indexed column updates are unaffected.
- DELETE: Indexes must be updated to remove references to deleted rows, increasing operation time.

Trade-offs:
- Indexes speed up reads but slow down writes due to maintenance.
- Over-indexing can degrade performance for write-heavy applications.

Mitigation:
- Index only frequently queried columns.
- Use composite indexes for multiple-column queries.
- Periodically rebuild fragmented indexes (e.g., ALTER INDEX REBUILD in SQL Server).
Example Sql Server:

-- Create table with index
CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    Name VARCHAR(50)
);
CREATE NONCLUSTERED INDEX IX_Employees_Name ON Employees(Name);
-- INSERT (index updated)
INSERT INTO Employees VALUES (1, 'John');
-- UPDATE (index updated if Name changes)
UPDATE Employees SET Name = 'Jane' WHERE EmployeeID = 1;
-- DELETE (index updated)
DELETE FROM Employees WHERE EmployeeID = 1;

Example MySql:

-- Create table with index
CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    Name VARCHAR(50)
);
CREATE INDEX IX_Employees_Name ON Employees(Name);
-- INSERT (index updated)
INSERT INTO Employees VALUES (1, 'John');
-- UPDATE (index updated if Name changes)
UPDATE Employees SET Name = 'Jane' WHERE EmployeeID = 1;
-- DELETE (index updated)
DELETE FROM Employees WHERE EmployeeID = 1;

These window functions assign rankings to rows within a window:
- ROW_NUMBER(): Assigns a unique sequential number to each row, even for ties.
- RANK(): Assigns the same rank to tied rows, with gaps in the sequence for subsequent ranks (e.g., 1, 1, 3).
- DENSE_RANK(): Assigns the same rank to tied rows, without gaps in the sequence (e.g., 1, 1, 2).

Key Differences:
- ROW_NUMBER() ensures unique numbers.
- RANK() leaves gaps after ties; DENSE_RANK() does not.

Use Cases:
- ROW_NUMBER(): Paginating results or assigning unique IDs.
- RANK(): Ranking with gaps (e.g., competition rankings).
- DENSE_RANK(): Ranking without gaps (e.g., tiered classifications).
Example Sql Server:

SELECT EmployeeID, Name, Salary,
    ROW_NUMBER() OVER (ORDER BY Salary DESC) AS RowNum,
    RANK() OVER (ORDER BY Salary DESC) AS Rank,
    DENSE_RANK() OVER (ORDER BY Salary DESC) AS DenseRank
FROM Employees;

Example MySql:

SELECT EmployeeID, Name, Salary,
    ROW_NUMBER() OVER (ORDER BY Salary DESC) AS RowNum,
    RANK() OVER (ORDER BY Salary DESC) AS Rank,
    DENSE_RANK() OVER (ORDER BY Salary DESC) AS DenseRank
FROM Employees;

- Clustered Index: Determines the physical order of data in a table. Only one per table, as it dictates how data is stored. Typically created on the primary key.
- Non-Clustered Index: A separate structure containing pointers to the table’s data. Multiple non-clustered indexes can exist. Contains a copy of indexed columns and a reference to the row.

Key Differences:
- Storage: Clustered index stores data rows; non-clustered index stores pointers.
- Quantity: One clustered index per table; multiple non-clustered indexes.
- Performance: Clustered index is faster for range queries; non-clustered is better for specific lookups.

Use Cases:
- Clustered: Primary key or frequently ranged columns (e.g., date ranges).
- Non-Clustered: Columns used in WHERE, JOIN, or ORDER BY (e.g., name, email).
Example Sql Server:

-- Clustered Index (on primary key)
CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY CLUSTERED,
    Name VARCHAR(50)
);
-- Non-Clustered Index
CREATE NONCLUSTERED INDEX IX_Employees_Name ON Employees(Name);

Example MySql:

-- Clustered Index (InnoDB, implicit with primary key)
CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    Name VARCHAR(50)
);
-- Non-Clustered Index
CREATE INDEX IX_Employees_Name ON Employees(Name);

Window functions perform calculations across a set of rows (a 'window') related to the current row, without collapsing rows like aggregate functions. They are defined using the OVER clause.

Common Window Functions:
- ROW_NUMBER(): Assigns a unique number to each row.
- RANK(), DENSE_RANK(): Assign ranks based on a column.
- SUM(), AVG(): Calculate aggregates over a window.
- LEAD(), LAG(): Access data from the next or previous row.

Use Cases:
- Ranking employees by salary within each department.
- Calculating running totals or moving averages.
- Comparing current row values with previous/next rows (e.g., sales trends).

Syntax: `FUNCTION() OVER (PARTITION BY column ORDER BY column)`
Example Sql Server:

-- Rank employees by salary within department
SELECT EmployeeID, Name, DepartmentID, Salary,
    ROW_NUMBER() OVER (PARTITION BY DepartmentID ORDER BY Salary DESC) AS RowNum,
    RANK() OVER (PARTITION BY DepartmentID ORDER BY Salary DESC) AS Rank
FROM Employees;
-- Running total of salaries
SELECT EmployeeID, Name, Salary,
    SUM(Salary) OVER (ORDER BY EmployeeID) AS RunningTotal
FROM Employees;

Example MySql:

-- Rank employees by salary within department
SELECT EmployeeID, Name, DepartmentID, Salary,
    ROW_NUMBER() OVER (PARTITION BY DepartmentID ORDER BY Salary DESC) AS RowNum,
    RANK() OVER (PARTITION BY DepartmentID ORDER BY Salary DESC) AS Rank
FROM Employees;
-- Running total of salaries
SELECT EmployeeID, Name, Salary,
    SUM(Salary) OVER (ORDER BY EmployeeID) AS RunningTotal
FROM Employees;

Optimizing a slow SQL query involves analyzing and improving its performance. Steps include:
1. Analyze Execution Plan: Use EXPLAIN (MySQL) or Query Execution Plan (SQL Server) to identify bottlenecks like full table scans.
2. Add Indexes: Create indexes on columns used in WHERE, JOIN, or ORDER BY clauses.
3. Avoid SELECT * : Specify only needed columns to reduce data transfer.
4. Optimize Joins: Ensure joins use indexed columns and avoid unnecessary joins.
5. Filter Early: Apply WHERE conditions to reduce the dataset before joining or grouping.
6. Use Proper Data Types: Match column data types to reduce implicit conversions.
7. Avoid Functions on Indexed Columns: Functions (e.g., UPPER(column)) prevent index usage.
8. Partition Large Tables: Split large tables into smaller partitions for faster access.
9. Cache Results: Use materialized views or caching for frequently accessed data.
10. Update Statistics: Ensure database statistics are current for optimal query plans.

Tools: SQL Server Profiler, MySQL EXPLAIN ANALYZE.
Example Sql Server:

-- Before optimization
SELECT * FROM Employees e
JOIN Departments d ON e.DepartmentID = d.DepartmentID
WHERE UPPER(e.Name) = 'JOHN';
-- After optimization
CREATE INDEX IX_Employees_Name ON Employees(Name);
SELECT e.EmployeeID, e.Name, d.DepartmentName
FROM Employees e
JOIN Departments d ON e.DepartmentID = d.DepartmentID
WHERE e.Name = 'John';

Example MySql:

-- Before optimization
SELECT * FROM Employees e
JOIN Departments d ON e.DepartmentID = d.DepartmentID
WHERE UPPER(e.Name) = 'JOHN';
-- After optimization
CREATE INDEX IX_Employees_Name ON Employees(Name);
SELECT e.EmployeeID, e.Name, d.DepartmentName
FROM Employees e
JOIN Departments d ON e.DepartmentID = d.DepartmentID
WHERE e.Name = 'John';

A Common Table Expression (CTE) is a temporary result set defined within a query using the WITH clause, which can be referenced multiple times in the main query.

Differences from Subquery:
- Readability: CTEs are more readable, especially for complex queries, as they separate logic into named blocks.
- Reusability: CTEs can be referenced multiple times in the same query; subqueries are defined inline and cannot be reused.
- Scope: CTEs are defined at the start of a query; subqueries are embedded in WHERE, SELECT, or FROM clauses.
- Recursion: CTEs support recursive queries; subqueries do not.

Use Cases:
- Simplify complex queries.
- Enable recursive queries (e.g., hierarchical data).
- Break down multi-step transformations.
Example Sql Server:

-- CTE
WITH HighEarners AS (
    SELECT EmployeeID, Name, Salary
    FROM Employees
    WHERE Salary > 50000
)
SELECT e.Name, d.DepartmentName
FROM HighEarners e
JOIN Departments d ON e.EmployeeID = d.DepartmentID;
-- Subquery
SELECT e.Name, d.DepartmentName
FROM (SELECT EmployeeID, Name FROM Employees WHERE Salary > 50000) e
JOIN Departments d ON e.EmployeeID = d.DepartmentID;

Example MySql:

-- CTE
WITH HighEarners AS (
    SELECT EmployeeID, Name, Salary
    FROM Employees
    WHERE Salary > 50000
)
SELECT e.Name, d.DepartmentName
FROM HighEarners e
JOIN Departments d ON e.EmployeeID = d.DepartmentID;
-- Subquery
SELECT e.Name, d.DepartmentName
FROM (SELECT EmployeeID, Name FROM Employees WHERE Salary > 50000) e
JOIN Departments d ON e.EmployeeID = d.DepartmentID;

Advantages:
- Readability: Improves query clarity by breaking complex logic into named, reusable blocks.
- Reusability: Can be referenced multiple times in the same query, reducing redundancy.
- Recursion: Supports recursive queries for hierarchical data (e.g., org charts).
- Maintainability: Easier to modify and debug than nested subqueries.

Limitations:
- Performance: CTEs are not materialized (stored) in memory, so the CTE query may be re-executed, impacting performance for large datasets.
- Scope: Limited to the query they are defined in; cannot be reused across queries.
- No Indexing: Unlike temporary tables, CTEs cannot be indexed.
- Complexity: Recursive CTEs can be hard to optimize for large hierarchies.

Use Cases: Use CTEs for readable, recursive, or multi-step queries; use temporary tables for performance-critical scenarios.
Example Sql Server:

-- Reusable CTE
WITH SalesByRegion AS (
    SELECT Region, SUM(Amount) AS TotalSales
    FROM Sales
    GROUP BY Region
)
SELECT Region, TotalSales, TotalSales * 0.1 AS Tax
FROM SalesByRegion
WHERE TotalSales > 10000;

Example MySql:

-- Reusable CTE
WITH SalesByRegion AS (
    SELECT Region, SUM(Amount) AS TotalSales
    FROM Sales
    GROUP BY Region
)
SELECT Region, TotalSales, TotalSales * 0.1 AS Tax
FROM SalesByRegion
WHERE TotalSales > 10000;

A recursive CTE is a CTE that references itself to process hierarchical or recursive data, such as organizational charts or bill of materials. It consists of:
- Anchor Member: The initial query that returns the base result.
- Recursive Member: The query that references the CTE, executed repeatedly until no new rows are returned.
- UNION ALL: Combines anchor and recursive results.

Steps:
1. Define the CTE with WITH RECURSIVE (MySQL) or WITH (SQL Server).
2. Specify the anchor and recursive members.
3. Use a termination condition to prevent infinite recursion.

Use Case: Traverse an employee hierarchy to list all subordinates.
Example Sql Server:

-- Recursive CTE for employee hierarchy
WITH EmployeeHierarchy AS (
    -- Anchor: Top-level employees (no manager)
    SELECT EmployeeID, Name, ManagerID, 0 AS Level
    FROM Employees
    WHERE ManagerID IS NULL
    UNION ALL
    -- Recursive: Employees reporting to managers
    SELECT e.EmployeeID, e.Name, e.ManagerID, eh.Level + 1
    FROM Employees e
    INNER JOIN EmployeeHierarchy eh ON e.ManagerID = eh.EmployeeID
)
SELECT EmployeeID, Name, ManagerID, Level
FROM EmployeeHierarchy
ORDER BY Level, Name;

Example MySql:

-- Recursive CTE for employee hierarchy
WITH RECURSIVE EmployeeHierarchy AS (
    -- Anchor: Top-level employees (no manager)
    SELECT EmployeeID, Name, ManagerID, 0 AS Level
    FROM Employees
    WHERE ManagerID IS NULL
    UNION ALL
    -- Recursive: Employees reporting to managers
    SELECT e.EmployeeID, e.Name, e.ManagerID, eh.Level + 1
    FROM Employees e
    INNER JOIN EmployeeHierarchy eh ON e.ManagerID = eh.EmployeeID
)
SELECT EmployeeID, Name, ManagerID, Level
FROM EmployeeHierarchy
ORDER BY Level, Name;

Normalization is the process of organizing a database to eliminate redundancy and ensure data integrity by dividing tables into smaller, related tables based on normal forms.

Normal Forms:
1. 1NF: No repeating groups; all columns are atomic, and rows are unique.
2. 2NF: 1NF + no partial dependency; non-key attributes fully depend on the entire primary key.
3. 3NF: 2NF + no transitive dependency; non-key attributes do not depend on other non-key attributes.
4. BCNF: Stricter 3NF; every determinant is a candidate key.

Practical Recommendation: Most applications normalize up to 3NF, as it balances data integrity and query performance. Higher forms (BCNF, 4NF) are used in specialized cases but may increase complexity or require denormalization for performance.

Use Case: Normalize customer and order data to avoid update anomalies.
Example Sql Server:

-- 1NF: No repeating groups
CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    CustomerID INT,
    Product VARCHAR(50)
);
-- 2NF: Remove partial dependency
CREATE TABLE OrderDetails (
    OrderID INT,
    ProductID INT,
    Quantity INT,
    PRIMARY KEY (OrderID, ProductID)
);
-- 3NF: Remove transitive dependency
CREATE TABLE Customers (
    CustomerID INT PRIMARY KEY,
    Name VARCHAR(50),
    City VARCHAR(50)
);

Example MySql:

-- 1NF: No repeating groups
CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    CustomerID INT,
    Product VARCHAR(50)
);
-- 2NF: Remove partial dependency
CREATE TABLE OrderDetails (
    OrderID INT,
    ProductID INT,
    Quantity INT,
    PRIMARY KEY (OrderID, ProductID)
);
-- 3NF: Remove transitive dependency
CREATE TABLE Customers (
    CustomerID INT PRIMARY KEY,
    Name VARCHAR(50),
    City VARCHAR(50)
);

ACID properties ensure reliable database transactions:
1. Atomicity: Guarantees all operations in a transaction complete successfully or none are applied (all-or-nothing).
2. Consistency: Ensures the database remains in a valid state before and after the transaction, adhering to constraints, indexes, and rules.
3. Isolation: Ensures transactions are executed independently, preventing interference (e.g., partial changes are not visible to other transactions).
4. Durability: Guarantees committed transactions are permanently saved, even in case of system failure.

Use Case: ACID is critical for financial systems, ensuring transfers are reliable and consistent.
Example Sql Server:

-- Demonstrating ACID with a transaction
BEGIN TRANSACTION;
BEGIN TRY
    INSERT INTO Orders (OrderID, CustomerID) VALUES (1, 101);
    INSERT INTO OrderDetails (OrderID, ProductID, Quantity) VALUES (1, 501, 10);
    COMMIT; -- Atomic, Consistent, Isolated, Durable
END TRY
BEGIN CATCH
    ROLLBACK; -- Ensures Atomicity on failure
    SELECT ERROR_MESSAGE();
END CATCH;

Example MySql:

-- Demonstrating ACID with a transaction
START TRANSACTION;
INSERT INTO Orders (OrderID, CustomerID) VALUES (1, 101);
INSERT INTO OrderDetails (OrderID, ProductID, Quantity) VALUES (1, 501, 10);
COMMIT; -- Atomic, Consistent, Isolated, Durable
-- On error: ROLLBACK;

A transaction is a sequence of SQL operations treated as a single unit, ensuring data consistency. Transactions are managed with:
- BEGIN TRANSACTION (SQL Server) or START TRANSACTION (MySQL): Starts a transaction.
- COMMIT: Saves changes permanently.
- ROLLBACK: Undoes changes if an error occurs.
- SAVEPOINT (optional): Sets a point to roll back to.

Best Practice:
- Use try-catch (SQL Server) or error handling (MySQL) to manage errors.
- Keep transactions short to minimize locking.
- Test with small datasets first.

Use Case: Transfer money between accounts, ensuring both updates succeed or fail together.
Example Sql Server:

BEGIN TRANSACTION;
BEGIN TRY
    UPDATE Accounts
    SET Balance = Balance - 100
    WHERE AccountID = 1;
    UPDATE Accounts
    SET Balance = Balance + 100
    WHERE AccountID = 2;
    COMMIT;
END TRY
BEGIN CATCH
    ROLLBACK;
    SELECT ERROR_MESSAGE();
END CATCH;

Example MySql:

START TRANSACTION;
UPDATE Accounts
SET Balance = Balance - 100
WHERE AccountID = 1;
UPDATE Accounts
SET Balance = Balance + 100
WHERE AccountID = 2;
COMMIT;
-- On error, manually rollback
-- ROLLBACK;

- EXISTS: Tests for the existence of rows in a subquery. Returns true if the subquery returns at least one row. Typically used with correlated subqueries.
- IN: Checks if a value matches any value in a subquery or list. Returns true if the value is found.

Key Differences:
- Performance: EXISTS stops processing once a match is found; IN evaluates the entire subquery result.
- Correlated Subqueries: EXISTS is better suited for correlated subqueries; IN is less efficient.
- NULL Handling: IN with NULL values can lead to unexpected results; EXISTS is safer.

When to Use:
- Prefer EXISTS for large datasets or correlated subqueries, as it’s often faster.
- Use IN for small, static lists or simple subqueries.
- Check query plans to confirm performance.
Example Sql Server:

-- EXISTS: Check for orders
SELECT Name
FROM Employees e
WHERE EXISTS (SELECT 1 FROM Orders o WHERE o.EmployeeID = e.EmployeeID);
-- IN: Check for departments
SELECT Name
FROM Employees
WHERE DepartmentID IN (SELECT DepartmentID FROM Departments WHERE Location = 'NY');

Example MySql:

-- EXISTS: Check for orders
SELECT Name
FROM Employees e
WHERE EXISTS (SELECT 1 FROM Orders o WHERE o.EmployeeID = e.EmployeeID);
-- IN: Check for departments
SELECT Name
FROM Employees
WHERE DepartmentID IN (SELECT DepartmentID FROM Departments WHERE Location = 'NY');

Pagination retrieves a subset of rows from a result set, typically for displaying data in pages (e.g., 10 rows per page).

SQL Server:
- Use `OFFSET-FETCH` with `ORDER BY` (SQL Server 2012+).
- Syntax: `OFFSET {start} ROWS FETCH NEXT {page_size} ROWS ONLY`.

MySQL:
- Use `LIMIT` and `OFFSET` with `ORDER BY`.
- Syntax: `LIMIT {page_size} OFFSET {start}`.

Best Practice:
- Always use ORDER BY for consistent results.
- Index columns used in ORDER BY for performance.
- Use ROW_NUMBER() for complex pagination scenarios.

Use Case: Display paginated employee lists in a web application.
Example Sql Server:

-- Page 2, 10 rows per page
SELECT EmployeeID, Name, Salary
FROM Employees
ORDER BY Salary DESC
OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY;

Example MySql:

-- Page 2, 10 rows per page
SELECT EmployeeID, Name, Salary
FROM Employees
ORDER BY Salary DESC
LIMIT 10 OFFSET 10;

Soft deletes mark records as deleted without physically removing them, typically using a flag (e.g., IsDeleted column) or a deletion timestamp.

Implementation:
1. Add an `IsDeleted` (BIT/BOOLEAN) or `DeletedAt` (DATETIME) column to the table.
2. Update queries to filter out soft-deleted records (e.g., `WHERE IsDeleted = 0`).
3. Modify DELETE operations to update the flag instead of removing rows.
4. Use triggers or constraints to enforce soft delete logic (optional).

Benefits:
- Retains historical data for auditing or recovery.
- Avoids cascading deletes in foreign key relationships.

Use Case: E-commerce systems to retain deleted orders for auditing.
Example Sql Server:

-- Create table with soft delete
CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    Name VARCHAR(50),
    IsDeleted BIT DEFAULT 0
);
-- Soft delete
UPDATE Employees
SET IsDeleted = 1
WHERE EmployeeID = 1;
-- Query active records
SELECT * FROM Employees WHERE IsDeleted = 0;

Example MySql:

-- Create table with soft delete
CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    Name VARCHAR(50),
    IsDeleted BOOLEAN DEFAULT FALSE
);
-- Soft delete
UPDATE Employees
SET IsDeleted = TRUE
WHERE EmployeeID = 1;
-- Query active records
SELECT * FROM Employees WHERE IsDeleted = FALSE;

A deadlock occurs when two or more transactions block each other, each waiting for a resource the other holds, preventing progress.

Resolution:
- SQL Server detects deadlocks and terminates one transaction (the 'victim'), rolling back its changes.
- The application should retry the failed transaction.

Prevention:
- Access Resources in Consistent Order: Ensure transactions lock resources (e.g., tables) in the same order.
- Minimize Transaction Scope: Keep transactions short to reduce locking duration.
- Use Lower Isolation Levels: Use READ COMMITTED or SNAPSHOT isolation instead of SERIALIZABLE when possible.
- Avoid User Input in Transactions: Prevent delays from user interaction.
- Monitor Deadlocks: Use SQL Server Profiler or Extended Events to trace deadlocks.

Use Case: Prevent deadlocks in high-concurrency systems like banking apps.
Example Sql Server:

-- Example to avoid deadlock by consistent locking order
BEGIN TRANSACTION;
UPDATE Employees SET Name = 'John' WHERE EmployeeID = 1;
UPDATE Departments SET Name = 'HR' WHERE DepartmentID = 1;
COMMIT;
-- Monitor deadlocks
SELECT * FROM sys.dm_tran_locks WHERE resource_type = 'OBJECT';

Example MySql:

-- MySQL: Use consistent locking order
START TRANSACTION;
UPDATE Employees SET Name = 'John' WHERE EmployeeID = 1;
UPDATE Departments SET Name = 'HR' WHERE DepartmentID = 1;
COMMIT;
-- Check for deadlocks (InnoDB)
SHOW ENGINE INNODB STATUS;

To find the second highest salary, you can use several approaches:
1. Using LIMIT/OFFSET: Order by salary descending and skip the first row.
2. Using MAX: Find the maximum salary less than the highest.
3. Using ROW_NUMBER() or DENSE_RANK(): Rank salaries and select the second rank.

Best Practice:
- Use DENSE_RANK() for handling ties correctly.
- Index the salary column for performance.

Use Case: Identify the second highest-paid employee for reporting.
Example Sql Server:

-- Using DENSE_RANK
WITH RankedSalaries AS (
    SELECT EmployeeID, Name, Salary,
           DENSE_RANK() OVER (ORDER BY Salary DESC) AS SalaryRank
    FROM Employees
)
SELECT EmployeeID, Name, Salary
FROM RankedSalaries
WHERE SalaryRank = 2;
-- Using MAX
SELECT MAX(Salary) AS SecondHighest
FROM Employees
WHERE Salary < (SELECT MAX(Salary) FROM Employees);

Example MySql:

-- Using DENSE_RANK
WITH RankedSalaries AS (
    SELECT EmployeeID, Name, Salary,
           DENSE_RANK() OVER (ORDER BY Salary DESC) AS SalaryRank
    FROM Employees
)
SELECT EmployeeID, Name, Salary
FROM RankedSalaries
WHERE SalaryRank = 2;
-- Using LIMIT
SELECT Salary
FROM Employees
ORDER BY Salary DESC
LIMIT 1 OFFSET 1;

- Surrogate Key: An artificial key (e.g., auto-incremented ID) created to uniquely identify rows, independent of business data. Typically numeric and system-generated.
- Natural Key: A key derived from existing business data (e.g., email, SSN) that naturally identifies a row.

Differences:
- Origin: Surrogate keys are system-generated; natural keys are based on real-world data.
- Stability: Surrogate keys are immutable; natural keys may change (e.g., email updates).
- Uniqueness: Surrogate keys guarantee uniqueness; natural keys may require validation.
- Performance: Surrogate keys (e.g., INT) are compact and efficient for indexing; natural keys (e.g., VARCHAR) may be larger.

Use Cases:
- Surrogate: Primary keys for most tables (e.g., EmployeeID).
- Natural: Unique constraints for business identifiers (e.g., email).
Example Sql Server:

-- Surrogate Key
CREATE TABLE Employees (
    EmployeeID INT IDENTITY(1,1) PRIMARY KEY, -- Surrogate
    Email VARCHAR(100) UNIQUE -- Natural
);
-- Insert
INSERT INTO Employees (Email) VALUES ('[email protected]');

Example MySql:

-- Surrogate Key
CREATE TABLE Employees (
    EmployeeID INT AUTO_INCREMENT PRIMARY KEY, -- Surrogate
    Email VARCHAR(100) UNIQUE -- Natural
);
-- Insert
INSERT INTO Employees (Email) VALUES ('[email protected]');

A trigger is a special stored procedure that automatically executes in response to specific events (INSERT, UPDATE, DELETE) on a table.

Types:
- AFTER Triggers: Execute after the event (e.g., log changes).
- INSTEAD OF Triggers: Execute instead of the event (e.g., custom logic).

Real-World Use Case: Audit changes to employee salaries by logging updates to an audit table.

Best Practice:
- Keep triggers lightweight to avoid performance issues.
- Avoid recursive triggers unless necessary.
- Document trigger logic clearly.

Note: MySQL does not support INSTEAD OF triggers; SQL Server does.
Example Sql Server:

-- Audit table
CREATE TABLE SalaryAudit (
    AuditID INT IDENTITY PRIMARY KEY,
    EmployeeID INT,
    OldSalary INT,
    NewSalary INT,
    ChangeDate DATETIME
);
-- Trigger
CREATE TRIGGER trg_AfterUpdateSalary
ON Employees
AFTER UPDATE
AS
BEGIN
    INSERT INTO SalaryAudit (EmployeeID, OldSalary, NewSalary, ChangeDate)
    SELECT i.EmployeeID, d.Salary, i.Salary, GETDATE()
    FROM inserted i
    JOIN deleted d ON i.EmployeeID = d.EmployeeID
    WHERE i.Salary != d.Salary;
END;
-- Test
UPDATE Employees SET Salary = 60000 WHERE EmployeeID = 1;

Example MySql:

-- Audit table
CREATE TABLE SalaryAudit (
    AuditID INT AUTO_INCREMENT PRIMARY KEY,
    EmployeeID INT,
    OldSalary INT,
    NewSalary INT,
    ChangeDate DATETIME
);
-- Trigger
DELIMITER //
CREATE TRIGGER trg_AfterUpdateSalary
AFTER UPDATE ON Employees
FOR EACH ROW
BEGIN
    IF NEW.Salary != OLD.Salary THEN
        INSERT INTO SalaryAudit (EmployeeID, OldSalary, NewSalary, ChangeDate)
        VALUES (NEW.EmployeeID, OLD.Salary, NEW.Salary, NOW());
    END IF;
END //
DELIMITER ;
-- Test
UPDATE Employees SET Salary = 60000 WHERE EmployeeID = 1;

Data integrity across related tables is maintained using:
1. Primary Keys: Ensure each row is uniquely identifiable.
2. Foreign Keys: Enforce referential integrity by linking tables.
3. Constraints:
- NOT NULL: Prevent missing values.
- UNIQUE: Ensure unique values.
- CHECK: Enforce specific conditions (e.g., positive values).
4. Triggers: Enforce complex rules or cascading updates.
5. Transactions: Ensure atomic updates across tables.
6. Cascading Actions: Use ON DELETE/UPDATE CASCADE to propagate changes.

Best Practice:
- Define foreign keys with appropriate cascading rules.
- Use transactions for multi-table updates.
- Validate data at the application layer before insertion.

Use Case: Ensure orders and order details remain consistent.
Example Sql Server:

CREATE TABLE Departments (
    DepartmentID INT PRIMARY KEY,
    Name VARCHAR(50) NOT NULL
);
CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    DepartmentID INT,
    Salary INT CHECK (Salary > 0),
    FOREIGN KEY (DepartmentID) REFERENCES Departments(DepartmentID) ON DELETE CASCADE
);
-- Transaction for integrity
BEGIN TRANSACTION;
INSERT INTO Departments VALUES (1, 'HR');
INSERT INTO Employees VALUES (1, 1, 50000);
COMMIT;

Example MySql:

CREATE TABLE Departments (
    DepartmentID INT PRIMARY KEY,
    Name VARCHAR(50) NOT NULL
);
CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    DepartmentID INT,
    Salary INT CHECK (Salary > 0),
    FOREIGN KEY (DepartmentID) REFERENCES Departments(DepartmentID) ON DELETE CASCADE
);
-- Transaction for integrity
START TRANSACTION;
INSERT INTO Departments VALUES (1, 'HR');
INSERT INTO Employees VALUES (1, 1, 50000);
COMMIT;

- Temporary Tables:
- Created with `CREATE TABLE #Temp` (local) or `##Temp` (global).
- Stored in tempdb, persist until the session ends (local) or explicitly dropped (global).
- Support indexes, constraints, and statistics.
- Suitable for large datasets or complex operations.
- Table Variables:
- Declared with `DECLARE @Table TABLE`.
- Stored in memory or tempdb, scoped to the batch or stored procedure.
- Limited indexing (only primary key or unique constraints).
- No statistics, leading to less accurate query plans for large data.

Key Differences:
- Performance: Temporary tables are better for large datasets due to statistics; table variables are faster for small datasets.
- Scope: Temporary tables are session-based; table variables are batch-based.
- Flexibility: Temporary tables support more features (e.g., explicit indexes).

Use Cases:
- Temporary tables: Complex stored procedures with joins.
- Table variables: Small, temporary data storage.
Example Sql Server:

-- Temporary Table
CREATE TABLE #TempEmployees (
    EmployeeID INT PRIMARY KEY,
    Name VARCHAR(50)
);
INSERT INTO #TempEmployees VALUES (1, 'John');
SELECT * FROM #TempEmployees;
DROP TABLE #TempEmployees;
-- Table Variable
DECLARE @TempTable TABLE (
    EmployeeID INT PRIMARY KEY,
    Name VARCHAR(50)
);
INSERT INTO @TempTable VALUES (1, 'John');
SELECT * FROM @TempTable;

Example MySql:

-- Temporary Table
CREATE TEMPORARY TABLE TempEmployees (
    EmployeeID INT PRIMARY KEY,
    Name VARCHAR(50)
);
INSERT INTO TempEmployees VALUES (1, 'John');
SELECT * FROM TempEmployees;
DROP TEMPORARY TABLE TempEmployees;
-- Table Variable (not supported, use temporary table)
CREATE TEMPORARY TABLE TempTable (
    EmployeeID INT PRIMARY KEY,
    Name VARCHAR(50)
);
INSERT INTO TempTable VALUES (1, 'John');
SELECT * FROM TempTable;

An indexing strategy optimizes database performance by selecting appropriate columns for indexing, balancing read and write performance.

Factors to Consider:
1. Query Patterns: Index columns used in WHERE, JOIN, GROUP BY, ORDER BY, or HAVING clauses.
2. Cardinality: Prefer high-cardinality columns (e.g., IDs) over low-cardinality ones (e.g., gender).
3. Selectivity: Index columns that filter many rows (e.g., email vs. status).
4. Write Frequency: Avoid over-indexing in write-heavy tables, as indexes slow INSERT/UPDATE/DELETE.
5. Composite Indexes: Use for queries involving multiple columns (e.g., WHERE Name = 'John' AND DepartmentID = 1).
6. Covering Indexes: Include frequently selected columns to avoid table access.

Decision Process:
- Analyze query execution plans to identify slow queries.
- Index foreign keys and primary keys (often automatic).
- Monitor index usage (e.g., sys.dm_db_index_usage_stats in SQL Server).
- Avoid indexing small tables or rarely queried columns.

Use Case: Index customer email for fast lookups in a CRM.
Example Sql Server:

-- Index for frequent query
CREATE NONCLUSTERED INDEX IX_Employees_Name_Dept
ON Employees(Name, DepartmentID);
-- Covering index
CREATE NONCLUSTERED INDEX IX_Employees_Cover
ON Employees(Name) INCLUDE (Salary, DepartmentID);
-- Check index usage
SELECT OBJECT_NAME(object_id), index_id, user_seeks, user_scans
FROM sys.dm_db_index_usage_stats;

Example MySql:

-- Index for frequent query
CREATE INDEX IX_Employees_Name_Dept
ON Employees(Name, DepartmentID);
-- Covering index (implicit in MySQL)
CREATE INDEX IX_Employees_Cover
ON Employees(Name, Salary, DepartmentID);
-- Check index usage
SELECT TABLE_NAME, INDEX_NAME, NON_UNIQUE
FROM INFORMATION_SCHEMA.STATISTICS
WHERE TABLE_NAME = 'Employees';

To identify gaps in a sequence (e.g., missing IDs), compare the sequence with expected values using:
1. Self-Join: Find gaps by joining the table to itself with a condition for missing IDs.
2. Sequence Generation: Use a numbers table or generate a sequence (e.g., ROW_NUMBER()) and LEFT JOIN to find missing values.
3. LEAD/LAG: Check for non-consecutive values using window functions.

Best Practice:
- Index the sequence column for performance.
- Use a numbers table for large sequences.
- Validate gaps with business rules.

Use Case: Identify missing order IDs in an e-commerce system.
Example Sql Server:

-- Using LEAD to find gaps
WITH SequenceCheck AS (
    SELECT ID, LEAD(ID) OVER (ORDER BY ID) AS NextID
    FROM Orders
)
SELECT ID, NextID, (NextID - ID - 1) AS GapSize
FROM SequenceCheck
WHERE NextID - ID > 1;
-- Using numbers table
WITH Numbers AS (
    SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS n
    FROM sys.all_objects
)
SELECT n AS MissingID
FROM Numbers n
LEFT JOIN Orders o ON o.ID = n
WHERE o.ID IS NULL AND n <= (SELECT MAX(ID) FROM Orders);

Example MySql:

-- Using LEAD to find gaps
WITH SequenceCheck AS (
    SELECT ID, LEAD(ID) OVER (ORDER BY ID) AS NextID
    FROM Orders
)
SELECT ID, NextID, (NextID - ID - 1) AS GapSize
FROM SequenceCheck
WHERE NextID - ID > 1;
-- Using generated sequence
WITH RECURSIVE Numbers AS (
    SELECT 1 AS n
    UNION ALL
    SELECT n + 1
    FROM Numbers
    WHERE n < (SELECT MAX(ID) FROM Orders)
)
SELECT n AS MissingID
FROM Numbers n
LEFT JOIN Orders o ON o.ID = n
WHERE o.ID IS NULL;

Auditing table changes involves tracking INSERT, UPDATE, and DELETE operations, typically using:
1. Triggers: Automatically log changes to an audit table.
2. Audit Tables: Store historical data with timestamps and user info.
3. Temporal Tables (SQL Server): System-versioned tables to track changes.
4. Change Data Capture (CDC) (SQL Server): Capture changes in a separate log.
5. Manual Logging: Use stored procedures to log changes.

Best Practice:
- Include metadata (e.g., user, timestamp, operation type).
- Index audit tables for performance.
- Use temporal tables or CDC for built-in solutions (SQL Server).

Use Case: Track employee salary changes for compliance.
Example Sql Server:

-- Audit table
CREATE TABLE EmployeeAudit (
    AuditID INT IDENTITY PRIMARY KEY,
    EmployeeID INT,
    Operation VARCHAR(10),
    OldSalary INT,
    NewSalary INT,
    ChangeDate DATETIME,
    ChangedBy VARCHAR(50)
);
-- Trigger for UPDATE
CREATE TRIGGER trg_AuditEmployee
ON Employees
AFTER UPDATE
AS
BEGIN
    INSERT INTO EmployeeAudit (EmployeeID, Operation, OldSalary, NewSalary, ChangeDate, ChangedBy)
    SELECT i.EmployeeID, 'UPDATE', d.Salary, i.Salary, GETDATE(), SUSER_NAME()
    FROM inserted i
    JOIN deleted d ON i.EmployeeID = d.EmployeeID
    WHERE i.Salary != d.Salary;
END;
-- Test
UPDATE Employees SET Salary = 60000 WHERE EmployeeID = 1;

Example MySql:

-- Audit table
CREATE TABLE EmployeeAudit (
    AuditID INT AUTO_INCREMENT PRIMARY KEY,
    EmployeeID INT,
    Operation VARCHAR(10),
    OldSalary INT,
    NewSalary INT,
    ChangeDate DATETIME,
    ChangedBy VARCHAR(50)
);
-- Trigger for UPDATE
DELIMITER //
CREATE TRIGGER trg_AuditEmployee
AFTER UPDATE ON Employees
FOR EACH ROW
BEGIN
    IF NEW.Salary != OLD.Salary THEN
        INSERT INTO EmployeeAudit (EmployeeID, Operation, OldSalary, NewSalary, ChangeDate, ChangedBy)
        VALUES (NEW.EmployeeID, 'UPDATE', OLD.Salary, NEW.Salary, NOW(), USER());
    END IF;
END //
DELIMITER ;
-- Test
UPDATE Employees SET Salary = 60000 WHERE EmployeeID = 1;

- PIVOT: Transforms rows into columns, aggregating data to create a crosstab report (e.g., turning sales by month into columns).
- UNPIVOT: Converts columns into rows, reversing the pivot operation (e.g., turning monthly columns back into rows).

Use Cases:
- PIVOT: Generate reports (e.g., sales by region and month).
- UNPIVOT: Normalize pivoted data for analysis or migration.

Note:
- SQL Server has explicit PIVOT/UNPIVOT operators.
- MySQL lacks native PIVOT/UNPIVOT but can simulate them with CASE or GROUP_CONCAT.

Best Practice: Ensure data is clean and aggregated appropriately before pivoting.
Example Sql Server:

-- PIVOT
SELECT * FROM (
    SELECT EmployeeID, Month, Sales
    FROM Sales
) AS Source
PIVOT (
    SUM(Sales)
    FOR Month IN (Jan, Feb, Mar)
) AS PivotTable;
-- UNPIVOT
SELECT EmployeeID, Month, Sales
FROM (
    SELECT EmployeeID, Jan, Feb, Mar
    FROM PivotTable
) AS Source
UNPIVOT (
    Sales FOR Month IN (Jan, Feb, Mar)
) AS UnpivotTable;

Example MySql:

-- Simulated PIVOT
SELECT EmployeeID,
    SUM(CASE WHEN Month = 'Jan' THEN Sales END) AS Jan,
    SUM(CASE WHEN Month = 'Feb' THEN Sales END) AS Feb,
    SUM(CASE WHEN Month = 'Mar' THEN Sales END) AS Mar
FROM Sales
GROUP BY EmployeeID;
-- Simulated UNPIVOT
SELECT EmployeeID, 'Jan' AS Month, Jan AS Sales FROM PivotTable WHERE Jan IS NOT NULL
UNION
SELECT EmployeeID, 'Feb' AS Month, Feb AS Sales FROM PivotTable WHERE Feb IS NOT NULL
UNION
SELECT EmployeeID, 'Mar' AS Month, Mar AS Sales FROM PivotTable WHERE Mar IS NOT NULL;