A foreign key is a column (or set of columns) in one table that references the primary key or unique key of another table, establishing a relationship. It ensures referential integrity by enforcing that the foreign key value must exist in the referenced table’s primary/unique key or be NULL (if allowed).

How It Works:
- Prevents insertion of invalid foreign key values.
- Restricts deletion or updates in the referenced table if related rows exist in the referencing table (unless CASCADE is used).
- Maintains consistency across related tables.
Example Sql Server:

CREATE TABLE Departments (
    DepartmentID INT PRIMARY KEY,
    Name VARCHAR(50)
);
CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    DepartmentID INT,
    FOREIGN KEY (DepartmentID) REFERENCES Departments(DepartmentID)
);
-- Valid insert
INSERT INTO Departments VALUES (1, 'HR');
INSERT INTO Employees VALUES (1, 1);
-- Invalid insert (violates referential integrity)
-- INSERT INTO Employees VALUES (2, 999); -- Error

Example MySql:

CREATE TABLE Departments (
    DepartmentID INT PRIMARY KEY,
    Name VARCHAR(50)
);
CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    DepartmentID INT,
    FOREIGN KEY (DepartmentID) REFERENCES Departments(DepartmentID)
);
-- Valid insert
INSERT INTO Departments VALUES (1, 'HR');
INSERT INTO Employees VALUES (1, 1);
-- Invalid insert (violates referential integrity)
-- INSERT INTO Employees VALUES (2, 999); -- Error

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

Types:
1. First Normal Form (1NF): Ensures no repeating groups or arrays; each column contains atomic values, and each row is unique.
2. Second Normal Form (2NF): Requires 1NF and that all non-key attributes are fully dependent on the entire primary key (eliminates partial dependency).
3. Third Normal Form (3NF): Requires 2NF and that non-key attributes are not dependent on other non-key attributes (eliminates transitive dependency).
4. Boyce-Codd Normal Form (BCNF): A stricter 3NF, ensuring every determinant is a candidate key.

Normalization reduces data anomalies during insertions, updates, and deletions.
Example Sql Server:

-- 1NF: Remove repeating groups
CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    CustomerID INT,
    Product VARCHAR(50) -- No arrays
);
-- 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,
    CustomerName VARCHAR(50),
    City VARCHAR(50) -- Moved from Orders
);

Example MySql:

-- 1NF: Remove repeating groups
CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    CustomerID INT,
    Product VARCHAR(50) -- No arrays
);
-- 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,
    CustomerName VARCHAR(50),
    City VARCHAR(50) -- Moved from Orders
);

- INNER JOIN: Returns only the rows where there is a match in both tables based on the join condition.
- LEFT JOIN (or LEFT OUTER JOIN): Returns all rows from the left table and the matched rows from the right table. If no match exists, NULL values are returned for columns from the right table.

Key Difference: INNER JOIN excludes non-matching rows; LEFT JOIN includes all left table rows, with NULLs for non-matching right table rows.
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;

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;

SQL (Structured Query Language) is a standard language for managing and manipulating relational databases. It is used to create, read, update, and delete data, as well as manage database structures and permissions.

Main Types of SQL Commands:
1. DDL (Data Definition Language): Defines and modifies database structures (e.g., CREATE, ALTER, DROP).
2. DML (Data Manipulation Language): Manipulates data within a database (e.g., SELECT, INSERT, UPDATE, DELETE).
3. DCL (Data Control Language): Manages access and permissions (e.g., GRANT, REVOKE).
4. TCL (Transaction Control Language): Manages transactions (e.g., COMMIT, ROLLBACK, SAVEPOINT).

Both SQL Server and MySQL support these command types with minor syntax differences.
Example Sql Server:

-- DDL: Create table
CREATE TABLE Employees (EmployeeID INT PRIMARY KEY, Name VARCHAR(50));
-- DML: Insert data
INSERT INTO Employees VALUES (1, 'John');
-- DCL: Grant permission
GRANT SELECT ON Employees TO User1;
-- TCL: Commit transaction
BEGIN TRANSACTION;
INSERT INTO Employees VALUES (2, 'Jane');
COMMIT;

Example MySql:

-- DDL: Create table
CREATE TABLE Employees (EmployeeID INT PRIMARY KEY, Name VARCHAR(50));
-- DML: Insert data
INSERT INTO Employees VALUES (1, 'John');
-- DCL: Grant permission
GRANT SELECT ON Employees TO 'User1'@'localhost';
-- TCL: Commit transaction
START TRANSACTION;
INSERT INTO Employees VALUES (2, 'Jane');
COMMIT;

- WHERE: Filters rows before grouping, applied to individual rows in a table. Used with SELECT, UPDATE, DELETE.
- HAVING: Filters groups after GROUP BY, applied to aggregated data (e.g., SUM, COUNT). Used only with SELECT and GROUP BY.

Key Difference: WHERE is applied before aggregation, HAVING after. WHERE cannot use aggregate functions directly; HAVING can.
Example Sql Server:

-- WHERE: Filter individual rows
SELECT EmployeeID, Salary
FROM Employees
WHERE Salary > 50000;
-- HAVING: Filter groups
SELECT Department, AVG(Salary) AS AvgSalary
FROM Employees
GROUP BY Department
HAVING AVG(Salary) > 60000;

Example MySql:

-- WHERE: Filter individual rows
SELECT EmployeeID, Salary
FROM Employees
WHERE Salary > 50000;
-- HAVING: Filter groups
SELECT Department, AVG(Salary) AS AvgSalary
FROM Employees
GROUP BY Department
HAVING AVG(Salary) > 60000;

- Primary Key: A column (or set of columns) that uniquely identifies each row in a table. It cannot contain NULL values and enforces entity integrity. Only one primary key per table.
- Unique Key: A column (or set of columns) that ensures all values are unique. It can contain NULL values (in SQL Server, one NULL; in MySQL, multiple NULLs). Multiple unique keys are allowed per table.

Key Differences:
- Primary key does not allow NULLs; unique key does.
- Primary key implies a clustered index in SQL Server; unique key creates a non-clustered index by default.
- Primary key is used for referential integrity (foreign key relationships).
Example Sql Server:

-- Primary Key
CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    Email VARCHAR(100) UNIQUE
);
-- Unique Key allows NULL
INSERT INTO Employees (EmployeeID, Email) VALUES (1, NULL);

Example MySql:

-- Primary Key
CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    Email VARCHAR(100) UNIQUE
);
-- Unique Key allows NULL
INSERT INTO Employees (EmployeeID, Email) VALUES (1, NULL);

Denormalization is the process of intentionally introducing redundancy into a database by combining tables or adding redundant data to improve read performance at the cost of write efficiency and storage.

When Used:
- In read-heavy applications (e.g., reporting, analytics) to reduce complex joins.
- In data warehouses where query speed is critical.
- When hardware resources (storage, CPU) can handle the redundancy.
- To simplify queries for specific use cases (e.g., dashboards).

Denormalization trades data integrity and storage efficiency for faster query execution.
Example Sql Server:

-- Normalized
CREATE TABLE Customers (CustomerID INT PRIMARY KEY, Name VARCHAR(50));
CREATE TABLE Orders (OrderID INT PRIMARY KEY, CustomerID INT, OrderDate DATE);
-- Denormalized
CREATE TABLE DenormOrders (
    OrderID INT PRIMARY KEY,
    CustomerID INT,
    CustomerName VARCHAR(50), -- Redundant
    OrderDate DATE
);

Example MySql:

-- Normalized
CREATE TABLE Customers (CustomerID INT PRIMARY KEY, Name VARCHAR(50));
CREATE TABLE Orders (OrderID INT PRIMARY KEY, CustomerID INT, OrderDate DATE);
-- Denormalized
CREATE TABLE DenormOrders (
    OrderID INT PRIMARY KEY,
    CustomerID INT,
    CustomerName VARCHAR(50), -- Redundant
    OrderDate DATE
);

Aggregate functions perform calculations on a set of values and return a single value. They are typically used with GROUP BY to summarize data.

Examples:
- COUNT: Counts rows or non-NULL values.
- SUM: Calculates the total of numeric values.
- AVG: Computes the average of numeric values.
- MAX: Returns the highest value.
- MIN: Returns the lowest value.

Aggregate functions ignore NULL values except for COUNT(*).
Example Sql Server:

SELECT 
    COUNT(*) AS TotalEmployees,
    SUM(Salary) AS TotalSalary,
    AVG(Salary) AS AvgSalary,
    MAX(Salary) AS MaxSalary,
    MIN(Salary) AS MinSalary
FROM Employees;

Example MySql:

SELECT 
    COUNT(*) AS TotalEmployees,
    SUM(Salary) AS TotalSalary,
    AVG(Salary) AS AvgSalary,
    MAX(Salary) AS MaxSalary,
    MIN(Salary) AS MinSalary
FROM Employees;

A subquery is a query nested inside another query, enclosed in parentheses, used to return data that the outer query processes.

Types of Subqueries:
1. Single-Row Subquery: Returns one row, one column. Used with operators like =, >, <.
2. Multi-Row Subquery: Returns multiple rows, one column. Used with IN, ANY, ALL.
3. Correlated Subquery: References columns from the outer query, executed repeatedly for each outer row.
4. Multiple-Column Subquery: Returns multiple columns (less common).

Subqueries can appear in SELECT, WHERE, or FROM clauses.
Example Sql Server:

-- Single-Row Subquery
SELECT Name
FROM Employees
WHERE Salary = (SELECT MAX(Salary) FROM Employees);
-- Multi-Row Subquery
SELECT Name
FROM Employees
WHERE DepartmentID IN (SELECT DepartmentID FROM Departments WHERE Location = 'NY');
-- Correlated Subquery
SELECT Name
FROM Employees e
WHERE EXISTS (SELECT 1 FROM Orders o WHERE o.EmployeeID = e.EmployeeID);

Example MySql:

-- Single-Row Subquery
SELECT Name
FROM Employees
WHERE Salary = (SELECT MAX(Salary) FROM Employees);
-- Multi-Row Subquery
SELECT Name
FROM Employees
WHERE DepartmentID IN (SELECT DepartmentID FROM Departments WHERE Location = 'NY');
-- Correlated Subquery
SELECT Name
FROM Employees e
WHERE EXISTS (SELECT 1 FROM Orders o WHERE o.EmployeeID = e.EmployeeID);

- DELETE: Removes specific rows from a table based on a condition (DML). It is logged, supports transactions, and triggers are fired.
- TRUNCATE: Removes all rows from a table without logging individual row deletions (DDL). Faster than DELETE, resets identity columns, but cannot be used with WHERE.
- DROP: Deletes the entire table structure and data (DDL). Irreversible without backups, removes all associated constraints and indexes.

Key Differences:
- DELETE is reversible within a transaction; TRUNCATE and DROP are not (TRUNCATE can be rolled back in SQL Server).
- TRUNCATE is faster but less flexible than DELETE.
- DROP removes the table entirely, unlike DELETE and TRUNCATE, which preserve structure.
Example Sql Server:

-- DELETE: Remove specific rows
DELETE FROM Employees WHERE Salary < 30000;
-- TRUNCATE: Remove all rows
TRUNCATE TABLE Employees;
-- DROP: Remove table
DROP TABLE Employees;

Example MySql:

-- DELETE: Remove specific rows
DELETE FROM Employees WHERE Salary < 30000;
-- TRUNCATE: Remove all rows
TRUNCATE TABLE Employees;
-- DROP: Remove table
DROP TABLE Employees;

The GROUP BY clause groups rows with the same values in specified columns into summary rows, typically used with aggregate functions (e.g., COUNT, SUM). It organizes data into groups based on one or more columns and applies the aggregate function to each group.

How It Works:
- Rows are grouped by the columns in GROUP BY.
- Aggregate functions are applied to each group.
- The result contains one row per unique combination of grouped columns.
- Non-aggregated columns in SELECT must appear in GROUP BY.

Use HAVING to filter groups after aggregation.
Example Sql Server:

SELECT Department, COUNT(*) AS EmployeeCount, AVG(Salary) AS AvgSalary
FROM Employees
GROUP BY Department
HAVING COUNT(*) > 5;

Example MySql:

SELECT Department, COUNT(*) AS EmployeeCount, AVG(Salary) AS AvgSalary
FROM Employees
GROUP BY Department
HAVING COUNT(*) > 5;

A view is a virtual table defined by a stored query, presenting data from one or more tables without storing data physically.

Differences from a Table:
- Storage: Tables store data physically; views are virtual, storing only the query definition.
- Data Modification: Tables allow direct inserts, updates, and deletes; views may allow limited updates if based on a single table and meeting specific conditions.
- Purpose: Views simplify queries, enforce security (by restricting columns), or provide a tailored data perspective.
- Dependencies: Views depend on underlying tables; tables are independent.

Views are useful for abstracting complex queries or securing data access.
Example Sql Server:

-- Create Table
CREATE TABLE Employees (EmployeeID INT, Name VARCHAR(50), Salary INT);
-- Create View
CREATE VIEW HighEarners AS
SELECT EmployeeID, Name, Salary
FROM Employees
WHERE Salary > 50000;
-- Query View
SELECT * FROM HighEarners;

Example MySql:

-- Create Table
CREATE TABLE Employees (EmployeeID INT, Name VARCHAR(50), Salary INT);
-- Create View
CREATE VIEW HighEarners AS
SELECT EmployeeID, Name, Salary
FROM Employees
WHERE Salary > 50000;
-- Query View
SELECT * FROM HighEarners;

An index is a database structure that improves the speed of data retrieval operations on a table by creating a lookup for specific columns.

Why Used:
- Performance: Speeds up SELECT queries and WHERE clauses by reducing the number of rows scanned.
- Efficiency: Optimizes JOINs, ORDER BY, and GROUP BY operations.
- Trade-offs: Increases storage and slows down INSERT, UPDATE, DELETE operations due to index maintenance.

Types:
- Clustered: Determines physical order of data (one per table).
- Non-Clustered: Separate structure with pointers to data (multiple allowed).

Use indexes on frequently queried columns but avoid over-indexing to minimize write overhead.
Example Sql Server:

-- Create Table
CREATE TABLE Employees (EmployeeID INT, Name VARCHAR(50));
-- Create Index
CREATE NONCLUSTERED INDEX IX_Employees_Name ON Employees(Name);
-- Query using Index
SELECT Name FROM Employees WHERE Name = 'John';

Example MySql:

-- Create Table
CREATE TABLE Employees (EmployeeID INT, Name VARCHAR(50));
-- Create Index
CREATE INDEX IX_Employees_Name ON Employees(Name);
-- Query using Index
SELECT Name FROM Employees WHERE Name = 'John';

- CHAR: Fixed-length string data type. Always uses the specified length, padding with spaces if the data is shorter.
- VARCHAR: Variable-length string data type. Uses only the actual length of the data plus a small overhead (e.g., 2 bytes in SQL Server).

Key Differences:
- Storage: CHAR uses fixed storage (e.g., CHAR(10) always uses 10 bytes); VARCHAR uses variable storage based on data length.
- Performance: CHAR is slightly faster for fixed-length data (e.g., codes) due to predictable storage; VARCHAR is more space-efficient for variable-length data (e.g., names).
- Use Cases: Use CHAR for fixed-length data like postal codes; use VARCHAR for variable-length data like descriptions.
Example Sql Server:

CREATE TABLE Test (
    Fixed CHAR(5),
    Variable VARCHAR(5)
);
INSERT INTO Test VALUES ('ABC', 'ABC');
-- CHAR: 'ABC  ' (padded with spaces)
-- VARCHAR: 'ABC' (no padding)
SELECT DATALENGTH(Fixed), DATALENGTH(Variable) FROM Test; -- 5, 3

Example MySql:

CREATE TABLE Test (
    Fixed CHAR(5),
    Variable VARCHAR(5)
);
INSERT INTO Test VALUES ('ABC', 'ABC');
-- CHAR: 'ABC  ' (padded with spaces)
-- VARCHAR: 'ABC' (no padding)
SELECT LENGTH(Fixed), LENGTH(Variable) FROM Test; -- 5, 3

The LIKE operator is used in WHERE clauses to search for patterns in string data.

Pattern Symbols:
- %: Represents zero or more characters (wildcard).
- _: Represents exactly one character.

Use Cases:
- Find rows where a column matches a pattern (e.g., names starting with 'A').
- Useful for partial string matching in searches.

Note: LIKE is case-sensitive in MySQL by default but case-insensitive in SQL Server (depending on collation).
Example Sql Server:

SELECT Name
FROM Employees
WHERE Name LIKE 'A%'; -- Names starting with A
SELECT Name
FROM Employees
WHERE Name LIKE 'J_n'; -- e.g., Jon, Jan

Example MySql:

SELECT Name
FROM Employees
WHERE Name LIKE 'A%'; -- Names starting with A
SELECT Name
FROM Employees
WHERE Name LIKE 'J_n'; -- e.g., Jon, Jan

To fetch the top N records from a table:
- SQL Server: Use `TOP N` with `SELECT`.
- MySQL: Use `LIMIT N` with `SELECT`.

Optional: Use `ORDER BY` to specify the sort order before limiting results.

Use Case: Retrieve the top N highest-paid employees or most recent orders.
Example Sql Server:

-- Top 5 employees by salary
SELECT TOP 5 EmployeeID, Name, Salary
FROM Employees
ORDER BY Salary DESC;

Example MySql:

-- Top 5 employees by salary
SELECT EmployeeID, Name, Salary
FROM Employees
ORDER BY Salary DESC
LIMIT 5;

A constraint is a rule enforced on a table’s columns to ensure data integrity.

Types of Constraints:
1. PRIMARY KEY: Ensures unique, non-NULL values for a column(s).
2. FOREIGN KEY: Enforces referential integrity by linking to a primary/unique key in another table.
3. UNIQUE: Ensures all values in a column(s) are unique (allows NULL in SQL Server).
4. NOT NULL: Prevents NULL values in a column.
5. CHECK: Ensures values meet a specific condition (e.g., age > 18).
6. DEFAULT: Specifies a default value if no value is provided.

Constraints maintain data consistency and prevent invalid data entry.
Example Sql Server:

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    Name VARCHAR(50) NOT NULL,
    Email VARCHAR(100) UNIQUE,
    Age INT CHECK (Age >= 18),
    DepartmentID INT,
    Salary INT DEFAULT 30000,
    FOREIGN KEY (DepartmentID) REFERENCES Departments(DepartmentID)
);

Example MySql:

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    Name VARCHAR(50) NOT NULL,
    Email VARCHAR(100) UNIQUE,
    Age INT CHECK (Age >= 18),
    DepartmentID INT,
    Salary INT DEFAULT 30000,
    FOREIGN KEY (DepartmentID) REFERENCES Departments(DepartmentID)
);

To find duplicate records, use GROUP BY with HAVING to identify rows with the same values in specified columns appearing more than once. Alternatively, use a self-join or subquery to compare rows.

Common Approach:
- Group by the column(s) to check for duplicates.
- Use HAVING to filter groups with a count greater than 1.
Example Sql Server:

-- Find duplicate emails
SELECT Email, COUNT(*) AS Count
FROM Employees
GROUP BY Email
HAVING COUNT(*) > 1;
-- Get full duplicate rows
SELECT EmployeeID, Email
FROM Employees
WHERE Email IN (
    SELECT Email
    FROM Employees
    GROUP BY Email
    HAVING COUNT(*) > 1
)
ORDER BY Email;

Example MySql:

-- Find duplicate emails
SELECT Email, COUNT(*) AS Count
FROM Employees
GROUP BY Email
HAVING COUNT(*) > 1;
-- Get full duplicate rows
SELECT EmployeeID, Email
FROM Employees
WHERE Email IN (
    SELECT Email
    FROM Employees
    GROUP BY Email
    HAVING COUNT(*) > 1
)
ORDER BY Email;

The DISTINCT keyword eliminates duplicate rows from the result set of a SELECT query, returning only unique rows.

Use Cases:
- Retrieve unique values in a column (e.g., unique departments).
- Combine with aggregate functions or joins to avoid duplicate records.

Note: DISTINCT applies to the entire row, not just one column, unless specified in a subquery or aggregate.
Example Sql Server:

-- Unique departments
SELECT DISTINCT Department
FROM Employees;
-- Unique employee-department combinations
SELECT DISTINCT EmployeeID, Department
FROM Employees;

Example MySql:

-- Unique departments
SELECT DISTINCT Department
FROM Employees;
-- Unique employee-department combinations
SELECT DISTINCT EmployeeID, Department
FROM Employees;

A self join is a regular join where a table is joined with itself, using aliases to distinguish the two instances of the table.

Use Cases:
- Hierarchical data (e.g., employees and their managers in the same table).
- Comparing rows within the same table (e.g., finding pairs of employees in the same department).
- Analyzing relationships within a single table (e.g., parent-child records).

Self joins are typically INNER or LEFT JOINs.
Example Sql Server:

-- Employees and their managers
SELECT e1.Name AS Employee, e2.Name AS Manager
FROM Employees e1
LEFT JOIN Employees e2 ON e1.ManagerID = e2.EmployeeID;

Example MySql:

-- Employees and their managers
SELECT e1.Name AS Employee, e2.Name AS Manager
FROM Employees e1
LEFT JOIN Employees e2 ON e1.ManagerID = e2.EmployeeID;

- UNION: Combines the result sets of two or more SELECT queries, removing duplicate rows. It implicitly performs a DISTINCT operation.
- UNION ALL: Combines result sets without removing duplicates, making it faster as it skips the deduplication step.

Key Differences:
- UNION is slower due to duplicate removal.
- UNION ALL preserves all rows, including duplicates.
- Both require the same number of columns with compatible data types.

Use Cases:
- Use UNION when uniqueness is required (e.g., unique customer lists).
- Use UNION ALL for better performance when duplicates are acceptable or impossible.
Example Sql Server:

-- UNION: Remove duplicates
SELECT Name FROM Employees
UNION
SELECT Name FROM Contractors;
-- UNION ALL: Keep duplicates
SELECT Name FROM Employees
UNION ALL
SELECT Name FROM Contractors;

Example MySql:

-- UNION: Remove duplicates
SELECT Name FROM Employees
UNION
SELECT Name FROM Contractors;
-- UNION ALL: Keep duplicates
SELECT Name FROM Employees
UNION ALL
SELECT Name FROM Contractors;

A stored procedure is a precompiled set of SQL statements stored in the database, executed by calling its name. It can perform complex operations, modify data, and return multiple result sets.

Differences from a Function:
- Return Value: Stored procedures do not require a return value and can return multiple result sets; functions must return a single value (scalar or table).
- Usage: Stored procedures are called with EXEC/CALL; functions are used in expressions or SELECT statements.
- Side Effects: Stored procedures can modify data (INSERT, UPDATE); functions typically should not (though SQL Server allows it).
- Transaction Control: Stored procedures support full transaction control; functions have limited transaction capabilities.

Use Cases:
- Stored procedures for complex business logic or ETL processes.
- Functions for reusable calculations or table-valued results.
Example Sql Server:

-- Stored Procedure
CREATE PROCEDURE GetHighEarners
    @MinSalary INT
AS
BEGIN
    SELECT EmployeeID, Name, Salary
    FROM Employees
    WHERE Salary > @MinSalary;
END;
EXEC GetHighEarners @MinSalary = 50000;

-- Function
CREATE FUNCTION GetAverageSalary (@DepartmentID INT)
RETURNS DECIMAL(10,2)
AS
BEGIN
    RETURN (SELECT AVG(Salary) FROM Employees WHERE DepartmentID = @DepartmentID);
END;
SELECT dbo.GetAverageSalary(1);

Example MySql:

-- Stored Procedure
DELIMITER //
CREATE PROCEDURE GetHighEarners(IN MinSalary INT)
BEGIN
    SELECT EmployeeID, Name, Salary
    FROM Employees
    WHERE Salary > MinSalary;
END //
DELIMITER ;
CALL GetHighEarners(50000);

-- Function
CREATE FUNCTION GetAverageSalary(DepartmentID INT)
RETURNS DECIMAL(10,2)
DETERMINISTIC
BEGIN
    DECLARE avgSalary DECIMAL(10,2);
    SELECT AVG(Salary) INTO avgSalary FROM Employees WHERE DepartmentID = DepartmentID;
    RETURN avgSalary;
END;
SELECT GetAverageSalary(1);

The UPDATE statement modifies existing rows in a table based on a condition specified in the WHERE clause.

Syntax:
- Specify the table to update.
- Use SET to assign new values to columns.
- Use WHERE to filter rows (optional; without WHERE, all rows are updated).

Best Practice:
- Always use WHERE to avoid unintended updates.
- Test with a SELECT query first.
- Use transactions for safety.
Example Sql Server:

-- Update salary for specific employee
UPDATE Employees
SET Salary = 60000
WHERE EmployeeID = 1;
-- Update multiple columns
UPDATE Employees
SET Salary = Salary + 5000, DepartmentID = 2
WHERE Name = 'John';

Example MySql:

-- Update salary for specific employee
UPDATE Employees
SET Salary = 60000
WHERE EmployeeID = 1;
-- Update multiple columns
UPDATE Employees
SET Salary = Salary + 5000, DepartmentID = 2
WHERE Name = 'John';

A transaction is a sequence of SQL operations treated as a single unit of work, ensuring data consistency. Transactions are managed with commands like BEGIN TRANSACTION, COMMIT, and ROLLBACK.

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

Transactions are critical for operations like financial transfers or multi-table updates.
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;

NULL represents missing or unknown data in SQL. Handling NULLs requires special operators and functions, as NULL is not equal to any value, including itself.

Techniques:
- IS NULL / IS NOT NULL: Check for NULL values in WHERE clauses.
- COALESCE: Returns the first non-NULL value from a list of arguments.
- NULLIF: Returns NULL if two expressions are equal.
- Aggregate Functions: Most ignore NULLs (e.g., SUM, AVG), except COUNT(*).
- Joins: NULLs in join conditions may exclude rows unless handled (e.g., LEFT JOIN).

Best Practice:
- Explicitly handle NULLs in queries to avoid unexpected results.
- Use COALESCE or ISNULL (SQL Server) for default values.
Example Sql Server:

-- Find NULLs
SELECT Name
FROM Employees
WHERE Email IS NULL;
-- Replace NULL with default
SELECT Name, COALESCE(Email, 'No Email') AS Email
FROM Employees;
-- ISNULL (SQL Server specific)
SELECT Name, ISNULL(Email, 'No Email') AS Email
FROM Employees;
-- NULLIF
SELECT Name, NULLIF(Salary, 0) AS Salary
FROM Employees;

Example MySql:

-- Find NULLs
SELECT Name
FROM Employees
WHERE Email IS NULL;
-- Replace NULL with default
SELECT Name, COALESCE(Email, 'No Email') AS Email
FROM Employees;
-- NULLIF
SELECT Name, NULLIF(Salary, 0) AS Salary
FROM Employees;