📌 Summary Table: SQL Set Operators
| Operator | Description | Removes Duplicates? |
|-----------|------------|---------------------|
| UNION | Combines results from two queries | ✅ Yes |
| UNION ALL | Combines results, including duplicates | ❌ No |
| INTERSECT | Returns only common records | ✅ Yes |
| EXCEPT | Returns records in the first query but not in the second | ✅ Yes |

---

📌 Real-Life Use Cases of Set Operators

🔹 Merging Employee Data
Find all employees from multiple company branches without duplicates.

SELECT EmployeeID, Name FROM BranchA  
UNION  
SELECT EmployeeID, Name FROM BranchB;

🔹 Finding Common Customers
Identify customers who bought from both Amazon and Flipkart.

SELECT CustomerID FROM AmazonOrders  
INTERSECT  
SELECT CustomerID FROM FlipkartOrders;

🔹 Detecting Unused Emails
Find email addresses in the registration list that haven’t been used to place an order.

SELECT Email FROM Registrations  
EXCEPT  
SELECT Email FROM Orders;

---

📌 Your Tasks for Today
✅ Try out the queries on your SQL editor.
✅ Comment "Done ✅" once you complete today’s practice!

Tomorrow, we will explore Views and Indexes in SQL!

👉 Like ❤️ and Share if you're excited for Day 15! 😊

Day 15: Common Table Expressions (CTEs) – WITH Clause and Recursive Queries

Welcome to Day 15 of your SQL learning journey! 🚀 Today, we will dive into Common Table Expressions (CTEs), an advanced SQL concept that makes queries easier to read and write.

📌 What is a CTE?
A Common Table Expression (CTE) is a temporary result set that you can reference within a SELECT, INSERT, UPDATE, or DELETE statement.

✅ CTEs make complex queries simpler and more readable
✅ They help break down large queries into smaller parts
✅ CTEs are similar to temporary tables but exist only during query execution

---

🔹 1. The WITH Clause – Creating a Simple CTE
The WITH clause is used to define a Common Table Expression (CTE).

🚀 Basic Syntax of CTE

WITH CTE_Name AS (
    SELECT column1, column2
    FROM table_name
    WHERE condition
)
SELECT * FROM CTE_Name;

- WITH CTE_Name AS (...) – Creates a temporary result set (CTE).
- The SELECT * FROM CTE_Name statement retrieves data from the CTE.

🔍 Example: Using CTE to Simplify Queries
Imagine we have a Sales table:

| OrderID | Customer | Amount | OrderDate |
|---------|---------|--------|------------|
| 1 | Alice | 500 | 2024-01-05 |
| 2 | Bob | 700 | 2024-02-10 |
| 3 | Charlie | 300 | 2024-02-15 |
| 4 | Alice | 800 | 2024-03-01 |

We want to find customers who spent more than $600 in total.

Instead of writing a complex query, we use a CTE:

WITH TotalSales AS (
    SELECT Customer, SUM(Amount) AS TotalAmount
    FROM Sales
    GROUP BY Customer
)
SELECT Customer, TotalAmount 
FROM TotalSales
WHERE TotalAmount > 600;

✅ Output:
| Customer | TotalAmount |
|---------|------------|
| Alice | 1300 |
| Bob | 700 |

🔍 How It Works?
- The CTE TotalSales calculates the total spending per customer.
- The final query filters customers with TotalAmount > 600.
- Without a CTE, this would require a nested subquery, making it harder to read!

---

🔹 2. Recursive CTEs – Handling Hierarchical Data
A recursive CTE is a special type of CTE that refers to itself. It is useful for handling hierarchical data like:
✅ Employee-Manager relationships
✅ Category-Subcategory structures
✅ Family trees

🚀 Example: Employee Hierarchy
Imagine a CompanyEmployees table:

| EmployeeID | Name | ManagerID |
|-----------|--------|----------|
| 1 | CEO | NULL |
| 2 | Alice | 1 |
| 3 | Bob | 1 |
| 4 | Charlie | 2 |
| 5 | David | 2 |

Here, the CEO (EmployeeID 1) manages Alice and Bob, while Alice manages Charlie and David.

🔍 Recursive CTE to Find Employee Hierarchy

WITH EmployeeHierarchy AS (
    -- Base Case: Select the CEO (Top-level manager)
    SELECT EmployeeID, Name, ManagerID, 1 AS Level
    FROM CompanyEmployees
    WHERE ManagerID IS NULL  

    UNION ALL  

    -- Recursive Case: Select employees and increase hierarchy level
    SELECT e.EmployeeID, e.Name, e.ManagerID, h.Level + 1
    FROM CompanyEmployees e
    INNER JOIN EmployeeHierarchy h
    ON e.ManagerID = h.EmployeeID
)
SELECT * FROM EmployeeHierarchy;

✅ Output:
| EmployeeID | Name | ManagerID | Level |
|-----------|--------|----------|------|
| 1 | CEO | NULL | 1 |
| 2 | Alice | 1 | 2 |
| 3 | Bob | 1 | 2 |
| 4 | Charlie | 2 | 3 |
| 5 | David | 2 | 3 |

🔍 How It Works?
1️⃣ Base Case: The CEO (ManagerID NULL) starts at Level 1.
2️⃣ Recursive Case: Employees reporting to the CEO are Level 2.
3️⃣ Recursion continues until all employee levels are calculated.

---

📌 When to Use CTEs?
✅ When you need to simplify complex queries
✅ When working with hierarchical data (Employee-Manager, Categories, etc.)
✅ When breaking large queries into readable parts
✅ When avoiding repetitive subqueries

---

📌 Summary Table: CTE vs. Subquery vs. Temporary Table
| Feature | CTE (WITH) | Subquery | Temporary Table |
|----------|-------------|----------|----------------|
| Exists Temporarily? | ✅ Yes | ❌ No | ❌ No |
| Improves Readability? | ✅ Yes | ❌ No | ✅ Yes |
| Supports Recursion? | ✅ Yes | ❌ No | ❌ No |
| Requires Explicit Deletion? | ❌ No | ❌ No | ✅ Yes |

---

📌 Your Tasks for Today
✅ Try out the queries in your SQL editor
✅ Practice a recursive CTE with your own dataset
✅ Comment "Done ✅" once you complete today’s practice!

Tomorrow, we will explore Indexes and Performance Optimization in SQL!

👉 Like ❤️ and Share if you're excited for Day 16! 😊

Day 16: Window Functions – ROW_NUMBER, RANK, DENSE_RANK, NTILE

Welcome to Day 16 of your SQL learning journey! 🚀 Today, we will explore Window Functions, which help perform calculations across a specific group of rows without collapsing them into a single value.

📌 What are Window Functions?
A Window Function allows us to perform calculations over a subset of rows while keeping all original rows in the result.

✅ They do not group the data like `GROUP BY`
✅ They provide ranking, numbering, and distribution calculations
✅ Each row retains its identity while calculations are performed on a subset of data

---

🔹 1. Understanding the OVER() Clause
All window functions work using the OVER() clause, which defines how the calculation is applied.

🚀 Basic Syntax of a Window Function

SELECT column_name, 
       window_function() OVER (PARTITION BY column_name ORDER BY column_name)
FROM table_name;

- window_function() – The function you want to apply (ROW_NUMBER(), RANK(), etc.).
- OVER() – Defines how the function operates.
- PARTITION BY column_name – Groups data into partitions (optional).
- ORDER BY column_name – Specifies row order for ranking or numbering.

---

🔹 2. ROW_NUMBER() – Assigning Unique Row Numbers
The ROW_NUMBER() function assigns a unique number to each row within a partition, starting from 1.

🔍 Example: Assigning Row Numbers
Imagine we have a Students table:

| StudentID | Name | Subject | Marks |
|-----------|------|---------|-------|
| 1 | Alice | Math | 90 |
| 2 | Bob | Math | 85 |
| 3 | Charlie | Math | 85 |
| 4 | Alice | Science | 88 |
| 5 | Bob | Science | 92 |

We want to assign a row number to each student per subject based on marks.

SELECT Name, Subject, Marks,
       ROW_NUMBER() OVER (PARTITION BY Subject ORDER BY Marks DESC) AS RowNum
FROM Students;

✅ Output:
| Name | Subject | Marks | RowNum |
|---------|---------|-------|--------|
| Alice | Math | 90 | 1 |
| Bob | Math | 85 | 2 |
| Charlie | Math | 85 | 3 |
| Bob | Science | 92 | 1 |
| Alice | Science | 88 | 2 |

🔍 How It Works?
- `PARTITION BY Subject` → Groups rows by subject.
- `ORDER BY Marks DESC` → Orders students by highest marks.
- Each student gets a unique row number for their subject.

---

🔹 3. RANK() – Assigning Rank with Gaps
The RANK() function assigns ranks to rows, but it allows gaps if two rows have the same value.

🔍 Example: Assigning Ranks with Gaps

SELECT Name, Subject, Marks,
       RANK() OVER (PARTITION BY Subject ORDER BY Marks DESC) AS RankNum
FROM Students;

✅ Output:
| Name | Subject | Marks | RankNum |
|---------|---------|-------|--------|
| Alice | Math | 90 | 1 |
| Bob | Math | 85 | 2 |
| Charlie | Math | 85 | 2 |
| Bob | Science | 92 | 1 |
| Alice | Science | 88 | 2 |

🔍 How It Works?
- `Bob` and `Charlie` have the same marks (85), so they get the same rank (2).
- Since ranks are skipped (`2 → 4` instead of `2 → 3`), gaps appear in the ranking.

---

🔹 4. DENSE_RANK() – Assigning Rank Without Gaps
The DENSE_RANK() function is similar to RANK(), but it does not leave gaps in the ranking.

🔍 Example: Assigning Dense Ranks

SELECT Name, Subject, Marks,
       DENSE_RANK() OVER (PARTITION BY Subject ORDER BY Marks DESC) AS DenseRankNum
FROM Students;

✅ Output:
| Name | Subject | Marks | DenseRankNum |
|---------|---------|-------|-------------|
| Alice | Math | 90 | 1 |
| Bob | Math | 85 | 2 |
| Charlie | Math | 85 | 2 |
| Bob | Science | 92 | 1 |
| Alice | Science | 88 | 2 |

🔍 How It Works?
- Like `RANK()`, `Bob` and `Charlie` get the same rank (2).
- Unlike `RANK()`, the next rank continues (no gaps).

---

🔹 5. NTILE(N) – Dividing Rows into Equal Groups
The NTILE(N) function divides rows into N equal groups.

🔍 Example: Dividing Students into 2 Groups (NTILE(2))

SELECT Name, Subject, Marks,
       NTILE(2) OVER (PARTITION BY Subject ORDER BY Marks DESC) AS TileNum
FROM Students;

✅ Output:
| Name | Subject | Marks | TileNum |
|---------|---------|-------|--------|
| Alice | Math | 90 | 1 |
| Bob | Math | 85 | 1 |
| Charlie | Math | 85 | 2 |
| Bob | Science | 92 | 1 |
| Alice | Science | 88 | 2 |

🔍 How It Works?
- Rows are divided into 2 equal groups (`NTILE(2)`).
- If the number of rows isn't perfectly divisible, some groups may have one extra row.

---

📌 Summary of Window Functions
| Function | Purpose | Handles Ties? | Gaps in Ranking? |
|--------------|--------------------------------|-------------|---------------|
| ROW_NUMBER() | Assigns unique row numbers | ❌ No | ❌ No |
| RANK() | Assigns ranks with gaps | ✅ Yes | ✅ Yes |
| DENSE_RANK() | Assigns ranks without gaps | ✅ Yes | ❌ No |
| NTILE(N) | Divides rows into N groups | ❌ No | ❌ No |

---

📌 When to Use Window Functions?
✅ When you need row numbers or rankings without grouping data.
✅ When working with ranked results like leaderboards.
✅ When dividing data into equal groups using NTILE().

---

📌 Your Tasks for Today
✅ Try out the queries in your SQL editor
✅ Experiment with `ROW_NUMBER()`, `RANK()`, `DENSE_RANK()`, and `NTILE(4)`
✅ Comment "Done ✅" once you complete today’s practice!

Tomorrow, we will explore Aggregate Functions in SQL!

👉 Like ❤️ and Share if you're excited for Day 17! 😊

Day 17: More Window Functions – LEAD, LAG, FIRST_VALUE, LAST_VALUE

Welcome to Day 17 of your SQL learning journey! 🚀 Today, we will explore advanced Window Functions, including LEAD, LAG, FIRST_VALUE, and LAST_VALUE. These functions help us compare rows within a partition by looking at previous and next values in a dataset.

📌 What are Window Functions?
Window functions perform calculations over a specific group of rows, similar to aggregate functions (SUM, AVG, etc.), but they do not collapse rows into a single output. Instead, they return results for each row individually while considering values from other rows.

---

🔹 1. LEAD() – Get the Next Row’s Value
The LEAD() function fetches the value from the next row within the same partition. It is useful for comparing current and next row values.

🔍 Example: Finding Next Month’s Sales
Imagine we have a Sales table:

| Month | Sales |
|--------|-------|
| Jan | 1000 |
| Feb | 1200 |
| Mar | 1500 |
| Apr | 1300 |

We want to compare this month’s sales with next month’s sales.

SELECT Month, Sales, 
       LEAD(Sales) OVER (ORDER BY Month) AS Next_Month_Sales
FROM Sales;

✅ Output:
| Month | Sales | Next_Month_Sales |
|-------|-------|------------------|
| Jan | 1000 | 1200 |
| Feb | 1200 | 1500 |
| Mar | 1500 | 1300 |
| Apr | 1300 | NULL |

🔍 How It Works?
- LEAD(Sales) OVER (ORDER BY Month) → Looks at the next row’s sales value.
- April has NULL because there is no next row.

✅ Use Cases:
- Comparing sales of the current and next month.
- Finding future trends in data.

---

🔹 2. LAG() – Get the Previous Row’s Value
The LAG() function fetches the value from the previous row within the same partition. It helps in comparing a current row with its previous row.

🔍 Example: Comparing Current Sales with Previous Month

SELECT Month, Sales, 
       LAG(Sales) OVER (ORDER BY Month) AS Prev_Month_Sales
FROM Sales;

✅ Output:
| Month | Sales | Prev_Month_Sales |
|-------|-------|------------------|
| Jan | 1000 | NULL |
| Feb | 1200 | 1000 |
| Mar | 1500 | 1200 |
| Apr | 1300 | 1500 |

🔍 How It Works?
- LAG(Sales) OVER (ORDER BY Month) → Looks at the previous row’s sales value.
- January has NULL because there is no previous row.

✅ Use Cases:
- Comparing current and past values in sales, stock prices, etc.
- Analyzing month-over-month or year-over-year changes.

---

🔹 3. FIRST_VALUE() – Get the First Row’s Value
The FIRST_VALUE() function returns the first value in a partition. It is useful when you need to reference the earliest or starting value.

🔍 Example: Finding the First Month’s Sales

SELECT Month, Sales, 
       FIRST_VALUE(Sales) OVER (ORDER BY Month) AS First_Month_Sales
FROM Sales;

✅ Output:
| Month | Sales | First_Month_Sales |
|-------|-------|------------------|
| Jan | 1000 | 1000 |
| Feb | 1200 | 1000 |
| Mar | 1500 | 1000 |
| Apr | 1300 | 1000 |

🔍 How It Works?
- FIRST_VALUE(Sales) OVER (ORDER BY Month) → Returns the first row’s sales value (1000) for every row.

✅ Use Cases:
- Finding the initial value for a dataset.
- Comparing each row to the starting value.

---

🔹 4. LAST_VALUE() – Get the Last Row’s Value
The LAST_VALUE() function returns the last value in a partition. It helps in identifying the latest value in an ordered dataset.

🔍 Example: Finding the Latest Month’s Sales

SELECT Month, Sales, 
       LAST_VALUE(Sales) OVER (ORDER BY Month ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS Last_Month_Sales
FROM Sales;

✅ Output:
| Month | Sales | Last_Month_Sales |
|-------|-------|-----------------|
| Jan | 1000 | 1300 |
| Feb | 1200 | 1300 |
| Mar | 1500 | 1300 |
| Apr | 1300 | 1300 |

🔍 How It Works?
- LAST_VALUE(Sales) OVER (...) → Looks at the last row’s sales value (1300).
- ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ensures it considers all rows.

✅ Use Cases:
- Finding the latest recorded value.
- Comparing current values to the last recorded value.

---

📌 Summary of Window Functions
| Function | Purpose | Example Use |
|--------------|---------------------------------|----------------------------|
| LEAD() | Get the next row’s value | Comparing current vs next row |
| LAG() | Get the previous row’s value | Comparing current vs previous row |
| FIRST_VALUE() | Get the first row’s value | Finding the starting value |
| LAST_VALUE() | Get the last row’s value | Finding the latest value |

---

📌 When to Use These Functions?
✅ When comparing current and previous row values (LAG()).
✅ When comparing current and next row values (LEAD()).
✅ When finding the first value in a dataset (FIRST_VALUE()).
✅ When finding the latest value in a dataset (LAST_VALUE()).

---

📌 Your Tasks for Today
✅ Try out the queries in your SQL editor.
✅ Experiment with `LEAD()`, `LAG()`, `FIRST_VALUE()`, and `LAST_VALUE()`.
✅ Comment "Done ✅" once you complete today’s practice!

Tomorrow, we will explore Advanced Joins in SQL!

👉 Like ❤️ and Share if you're excited for Day 18! 😊

I hope you all are finding this series interesting and helpful at the same time.

IF there's anything that you guys want to add, do let me know.

Happy Learning!!

# Day 18: Creating and Managing Views, Temporary Tables, and Table Variables

Welcome to Day 18 of your SQL learning journey! 🚀 Today, we will explore:
✔️ Views – A way to store queries as virtual tables.
✔️ Temporary Tables – Short-term tables used for temporary data storage.
✔️ Table Variables – Variables that store table-like structures in memory.

Each of these concepts helps us organize data, improve query performance, and manage temporary results efficiently. Let’s dive in!

---

🔹 1. What is a VIEW in SQL?

A VIEW is a virtual table created from a SQL query. It does not store actual data but fetches it from the original table(s) whenever queried.

📌 Why Use Views?
✅ Simplifies Complex Queries – You can save a query as a view and reuse it.
✅ Security – You can restrict access by exposing only necessary columns.
✅ Consistency – Ensures that all users work with the same data representation.

---

🔍 Example: Creating a VIEW
Let’s say we have a table called Employees:

| EmployeeID | Name | Department | Salary |
|-----------|------|------------|--------|
| 1 | John | HR | 50000 |
| 2 | Alice | IT | 70000 |
| 3 | Bob | Finance | 60000 |

Now, we create a VIEW that shows only IT department employees:

CREATE VIEW IT_Employees AS
SELECT EmployeeID, Name, Salary
FROM Employees
WHERE Department = 'IT';

✅ Now, instead of writing the same query again, we can just run:

SELECT * FROM IT_Employees;

Output:

| EmployeeID | Name | Salary |
|-----------|------|--------|
| 2 | Alice | 70000 |

📌 Managing Views
✔️ Updating a View:

ALTER VIEW IT_Employees AS
SELECT EmployeeID, Name, Salary, Department
FROM Employees
WHERE Department = 'IT';

✔️ Deleting a View:

DROP VIEW IT_Employees;

✔️ Updating Data in a View:
- If the view is based on a single table, you can update data directly:

UPDATE IT_Employees 
SET Salary = 75000 
WHERE EmployeeID = 2;

- If the view is based on multiple tables, updating may not be allowed.

---

🔹 2. What are Temporary Tables in SQL?

A Temporary Table is a table that exists only for the duration of a session. Once the session ends, the table is automatically deleted.

📌 Why Use Temporary Tables?
✅ Store Intermediate Data – Useful when working with complex calculations or large datasets.
✅ Improve Performance – Speeds up queries by reducing redundant computations.
✅ Prevents Changes to Main Tables – Temporary tables do not affect original data.

---

🔍 Example: Creating a Temporary Table
A temporary table name starts with `#`.

CREATE TABLE #TempEmployees (
    EmployeeID INT,
    Name VARCHAR(50),
    Salary DECIMAL(10,2)
);

Now, we insert some values:

INSERT INTO #TempEmployees (EmployeeID, Name, Salary) 
VALUES (1, 'John', 50000),
       (2, 'Alice', 70000);

✅ Fetching Data from Temporary Table

SELECT * FROM #TempEmployees;

Output:

| EmployeeID | Name | Salary |
|-----------|------|--------|
| 1 | John | 50000 |
| 2 | Alice | 70000 |

✅ Deleting a Temporary Table

DROP TABLE #TempEmployees;

💡 Note: Temporary tables are deleted automatically once the session ends!

---

🔹 3. What are Table Variables?

A Table Variable is a special type of variable in SQL that holds table-like data. Unlike temporary tables, table variables do not persist beyond the batch execution.

📌 Why Use Table Variables?
✅ Uses Less System Resources – Stored in memory, making operations faster.
✅ Does Not Require Explicit Deletion – Automatically removed after execution.
✅ Better for Small Datasets – Works best when dealing with a few rows of data.

---

🔍 Example: Creating a Table Variable

DECLARE @EmpTable TABLE (
    EmployeeID INT,
    Name VARCHAR(50),
    Salary DECIMAL(10,2)
);

✅ Inserting Data

INSERT INTO @EmpTable (EmployeeID, Name, Salary) 
VALUES (1, 'John', 50000),
       (2, 'Alice', 70000);

✅ Fetching Data from a Table Variable

SELECT * FROM @EmpTable;

Output:

| EmployeeID | Name | Salary |
|-----------|------|--------|
| 1 | John | 50000 |
| 2 | Alice | 70000 |

💡 Difference from Temporary Tables:
- Table Variables are limited to the batch they are declared in.
- Table Variables cannot be dropped, they disappear automatically.

---

📌 Summary: Views vs. Temporary Tables vs. Table Variables

| Feature | Views | Temporary Tables | Table Variables |
|----------------|-----------------|-----------------|----------------|
| Stores Data? | ❌ (Virtual Table) | ✅ Yes (Temporary) | ✅ Yes (In Memory) |
| Persists? | ✅ Yes (Until Dropped) | ❌ No (Deleted when session ends) | ❌ No (Deleted after batch ends) |
| Performance | 🔹 Faster (No Data Stored) | 🔹 Good for Large Data | 🔹 Best for Small Data |
| Used For? | Read-Only Queries | Storing Intermediate Data | Storing Small Data Temporarily |

---

📌 When to Use These Features?
✅ Use VIEWS to simplify complex queries and improve security.
✅ Use TEMPORARY TABLES for storing large intermediate results.
✅ Use TABLE VARIABLES for storing small datasets within a session.

---

📌 Your Tasks for Today
✅ Create a VIEW and query it
✅ Experiment with TEMPORARY TABLES
✅ Practice using TABLE VARIABLES

Tomorrow, we will explore Stored Procedures and Functions in SQL!

👉 Like ❤️ and Share if you're excited for Day 19! 😊

Hi, all

here's the tutorial in hindi, where you can learn data cleaning with python.

https://youtu.be/OkD5kGQdXJY

Let me know if you're liking this playlist and what you want me to add in this...!!

Day 19: Transactions, ACID Properties, and Indexing for Query Optimization

Welcome to Day 19 of your SQL journey! 🚀 Today, we will explore two important topics:

1️⃣ Transactions & ACID Properties – Ensuring data consistency and integrity in databases.
2️⃣ Indexes – Speeding up queries and optimizing performance.

Both of these concepts are essential for maintaining a reliable and efficient database system. Let’s dive in!

---

🔹 1. What is a Transaction in SQL?

A Transaction is a set of one or more SQL operations that are executed as a single unit of work.

✅ If all operations are successful, the transaction is committed (saved permanently).
❌ If any operation fails, the transaction is rolled back (changes are undone).

---

🔍 Example: Why Use Transactions?
Imagine a bank transfer:
1. Withdraw ₹5000 from Account A
2. Deposit ₹5000 to Account B

If the withdrawal is successful but the deposit fails, the money is lost! 😱

💡 Solution? Use Transactions!

START TRANSACTION;

UPDATE Accounts SET Balance = Balance - 5000 WHERE AccountID = 1;  -- Withdraw
UPDATE Accounts SET Balance = Balance + 5000 WHERE AccountID = 2;  -- Deposit

COMMIT;  -- Save changes if both updates succeed

✅ If anything goes wrong, we can ROLLBACK:

ROLLBACK;  -- Undo changes if an error occurs

---

🔹 2. Understanding ACID Properties

SQL transactions follow ACID properties to ensure reliability:

| ACID Property | Meaning | Explanation |
|---------------|---------|-------------|
| A**tomicity | All or Nothing | Either the entire transaction succeeds or everything is rolled back. |
| **C**onsistency | Valid Data Only | The database remains in a valid state before and after the transaction. |
| **I**solation | No Interference | Transactions do not affect each other while executing. |
| **D**urability | Changes are Permanent | Once committed, data changes **persist even after a system crash. |

💡 Example of ACID in Action:

START TRANSACTION;

UPDATE Orders SET Status = 'Shipped' WHERE OrderID = 101;
UPDATE Inventory SET Stock = Stock - 1 WHERE ProductID = 5;

COMMIT;

✔️ Atomicity: Both Orders and Inventory updates must succeed.
✔️ Consistency: Inventory should never go below 0.
✔️ Isolation: Other transactions shouldn’t affect this process.
✔️ Durability: The changes remain even after a crash.

---

🔹 3. What is Indexing in SQL?

An Index is a database structure that improves the speed of data retrieval.

💡 Think of it like the index page of a book – Instead of searching every page, you jump directly to the right section!

📌 Why Use Indexes?
✅ Faster SELECT queries
✅ Efficient data lookup
✅ Improves JOIN performance

---

🔹 4. Types of Indexes in SQL

| Index Type | Description | Best Use Case |
|------------|-------------|---------------|
| Primary Index | Automatically created for Primary Key | Unique row identification |
| Unique Index | Ensures values in a column are unique | Email, Username fields |
| Clustered Index | Arranges data physically in table order | Sorting large datasets |
| Non-Clustered Index | Creates a separate lookup table | Speeding up searches |
| Full-Text Index | Used for searching text-heavy columns | Searching articles or descriptions |

---

🔹 5. Creating and Using Indexes

📌 Example: Creating an Index on a Single Column

CREATE INDEX idx_customer_lastname 
ON Customers (LastName);

✔️ Now, searching for a customer by LastName will be faster!

SELECT * FROM Customers WHERE LastName = 'Sharma';

---

📌 Example: Creating a Unique Index
To ensure that Email IDs are unique, use:

CREATE UNIQUE INDEX idx_unique_email 
ON Customers (Email);

✔️ Prevents duplicate entries!

---

📌 Example: Creating a Composite Index (Multiple Columns)
If we often search by FirstName + LastName, we create a composite index:

CREATE INDEX idx_name 
ON Customers (FirstName, LastName);

✔️ Now, queries like:

SELECT * FROM Customers WHERE FirstName = 'Amit' AND LastName = 'Kumar';

will be much faster! 🚀

---

🔹 6. When to Use and Avoid Indexes

✅ Use Indexes When:
✔️ Searching large datasets frequently (WHERE, JOIN, ORDER BY)
✔️ Columns contain unique values (Email, ID)

❌ Avoid Indexes When:
✘ The table has frequent INSERT/UPDATE/DELETE (Indexes slow down writes)
✘ The column has low uniqueness (Gender: Male/Female, Status: Active/Inactive)

---

🔹 7. Deleting Indexes

If an index isn’t needed anymore, we can remove it:

DROP INDEX idx_customer_lastname;

✔️ This frees up storage and can speed up write operations.

---

📌 Summary: Transactions vs. Indexing

| Feature | Transactions | Indexing |
|----------|-------------|----------|
| Purpose | Ensures data integrity | Improves query speed |
| Key Concept | ACID Properties | Search Optimization |
| Benefits | Reliable database operations | Faster data retrieval |
| Example | Bank Transfers | Searching Customers |

---

📌 Your Tasks for Today
✅ Practice Transactions: Try COMMIT and ROLLBACK
✅ Create an Index: Test a Single and Composite Index
✅ Experiment: Measure Query Speed with and without Indexes

Tomorrow, we will learn Stored Procedures and Functions in SQL! 🚀

👉 Like ❤️ and Share if you’re excited for Day 20! 😊

Day 20: Error Handling in SQL & Writing Dynamic SQL Queries

Welcome to Day 20 of your SQL learning journey! 🚀 Today, we will focus on two important topics:

1️⃣ Error Handling in SQL – How to manage and handle errors properly.
2️⃣ Dynamic SQL Queries – Writing flexible and customizable SQL queries.

These concepts will help you write robust, flexible, and efficient SQL code. Let’s dive in!

---

🔹 1. Error Handling in SQL

❓ What is Error Handling?
Error handling in SQL is the process of detecting and responding to errors that occur during query execution.

Errors can happen due to:
✅ Invalid data (e.g., inserting a string into a number column)
✅ Violations of constraints (e.g., primary key duplication)
✅ Syntax errors (e.g., missing commas, typos)
✅ Deadlocks (when two transactions block each other)

To handle errors properly, SQL provides:
✔️ TRY…CATCH blocks
✔️ RAISEERROR (SQL Server) or SIGNAL (MySQL)
✔️ @@ERROR (SQL Server)

---

🔹 2. TRY...CATCH in SQL (Error Handling Block)

The TRY…CATCH block helps catch errors and take appropriate action.

📌 Syntax for TRY…CATCH in SQL Server:

BEGIN TRY
    -- SQL Statements that might cause an error
END TRY
BEGIN CATCH
    -- Code to handle the error
    PRINT 'An error occurred: ' + ERROR_MESSAGE();
END CATCH;

---

📌 Example: Handling a Division by Zero Error

BEGIN TRY
    DECLARE @result INT;
    SET @result = 10 / 0;  -- This will cause an error (division by zero)
END TRY
BEGIN CATCH
    PRINT 'Error: Division by zero is not allowed!';
END CATCH;

✔️ The CATCH block prevents the program from crashing and shows an error message.

---

🔹 3. Using RAISEERROR in SQL Server

RAISEERROR is used to manually generate an error message.

📌 Example: Raising a Custom Error

RAISEERROR ('Custom error: Something went wrong!', 16, 1);

✔️ 16 is the severity level (1 to 25)
✔️ 1 is the state (used for debugging)

---

🔹 4. Using SIGNAL in MySQL

In MySQL, the SIGNAL statement is used for error handling.

📌 Example: Raising an Error in MySQL

SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'Custom error: Invalid operation!';

✔️ 45000 represents a user-defined error.

---

🔹 5. Writing Dynamic SQL Queries

❓ What is Dynamic SQL?
Dynamic SQL constructs SQL statements at runtime instead of using static queries.

✅ Why Use Dynamic SQL?
✔️ Flexible Queries – Generate different queries dynamically
✔️ Conditional Execution – Run different SQL commands based on inputs
✔️ Table and Column Selection – Choose tables or columns dynamically

---

📌 Example: Static vs. Dynamic SQL

Static SQL (Fixed Query)

SELECT * FROM Customers WHERE City = 'New York';

❌ Only works for New York – cannot change dynamically.

---

Dynamic SQL (Flexible Query)

DECLARE @CityName NVARCHAR(50);
SET @CityName = 'Los Angeles';

EXEC ('SELECT * FROM Customers WHERE City = ''' + @CityName + '''');

✔️ The query adapts to different cities dynamically!

---

🔹 6. Using EXECUTE with Dynamic SQL

`EXEC` is used to run dynamically built queries.

📌 Example: Using EXEC to Build a Query

DECLARE @TableName NVARCHAR(50);
SET @TableName = 'Customers';

EXEC ('SELECT * FROM ' + @TableName);

✔️ This query will run:

SELECT * FROM Customers;

---

🔹 7. Using sp_executesql (More Secure)

`sp_executesql` is a safer way to run dynamic SQL because it allows parameterized queries.

📌 Example: Using sp_executesql for Dynamic SQL

DECLARE @SQLQuery NVARCHAR(1000);
DECLARE @CityName NVARCHAR(50);
SET @CityName = 'Chicago';

SET @SQLQuery = 'SELECT * FROM Customers WHERE City = @City';

EXEC sp_executesql @SQLQuery, N'@City NVARCHAR(50)', @CityName;

✔️ Prevents SQL injection (unlike EXEC)
✔️ More readable and maintainable

---

🔹 8. When to Use Dynamic SQL?

✅ Use Dynamic SQL When:
✔️ Query structure changes based on user input
✔️ Tables or columns change dynamically
✔️ Running complex search filters

❌ Avoid Dynamic SQL When:
✘ Queries are simple and don’t need customization
✘ Can use stored procedures instead
✘ Security is a concern (improper EXEC usage can cause SQL injection attacks)

---

📌 Summary: Error Handling & Dynamic SQL

| Feature | Error Handling | Dynamic SQL |
|----------|---------------|-------------|
| Purpose | Manage & respond to errors | Generate flexible queries dynamically |
| Key Techniques | TRY…CATCH, RAISEERROR, SIGNAL | EXEC, sp_executesql |
| Best Use Cases | Prevent query failures | Handle dynamic tables & user inputs |
| Security Concern | Avoid crashes & failures | Prevent SQL Injection |

---

📌 Your Tasks for Today
✅ Write a TRY…CATCH block to handle errors in a SQL statement
✅ Practice using RAISEERROR or SIGNAL to generate custom error messages
✅ Create a dynamic SQL query using EXEC and sp_executesql

Tomorrow, we will explore Stored Procedures and Functions in SQL! 🚀

👉 Like ❤️ and Share if you’re excited for Day 21! 😊

Day 21: Week 3 Review & Advanced SQL Challenges

Welcome to Day 21 of your SQL journey! 🚀 You’ve covered some powerful SQL concepts in Week 3. Today, we will:

✅ Review key topics from Week 3
✅ Practice complex SQL queries
✅ Solve intermediate to advanced SQL challenges

Let’s strengthen your skills and boost your confidence with real-world SQL problems! 💪

---

🔹 Week 3 Review: What We Covered

📌 Day 15: Common Table Expressions (CTEs)
CTEs allow you to create temporary result sets inside a query.

Example: Simple CTE

WITH EmployeeCTE AS (
    SELECT EmployeeID, Name, Salary 
    FROM Employees 
    WHERE Salary > 50000
)
SELECT * FROM EmployeeCTE;

✔️ Makes queries more readable and reusable!

---

📌 Day 16-17: Window Functions
Window functions help perform ranking, calculations, and comparisons across rows.

Example: Ranking Employees by Salary

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

✔️ Assigns a rank based on salary, without grouping the data!

---

📌 Day 18: Views & Temporary Tables
- Views: Virtual tables based on a SQL query.
- Temporary Tables: Store data for a session.

Example: Creating a View for High-Salary Employees

CREATE VIEW HighSalaryEmployees AS
SELECT EmployeeID, Name, Salary 
FROM Employees 
WHERE Salary > 60000;

✔️ Helps simplify complex queries!

---

📌 Day 19: Transactions & Indexes
- Transactions ensure data consistency with ACID properties.
- Indexes speed up queries by optimizing searches.

Example: Using Transactions

BEGIN TRANSACTION;
UPDATE Accounts SET Balance = Balance - 500 WHERE AccountID = 1;
UPDATE Accounts SET Balance = Balance + 500 WHERE AccountID = 2;
COMMIT;

✔️ Ensures money transfer happens completely or not at all!

---

📌 Day 20: Error Handling & Dynamic SQL
- Error Handling: TRY…CATCH, RAISEERROR, SIGNAL.
- Dynamic SQL: Generates queries at runtime.

Example: Dynamic SQL to Filter Customers by City

DECLARE @CityName NVARCHAR(50);
SET @CityName = 'Los Angeles';

EXEC ('SELECT * FROM Customers WHERE City = ''' + @CityName + '''');

✔️ Allows queries to change dynamically based on user input!

---

🔹 SQL Challenge Practice: Intermediate to Advanced

Let’s apply these concepts with real-world SQL challenges! 🏆

---

Challenge 1: Find Employees with the Second Highest Salary
💡 Problem: Retrieve the second highest salary from the Employees table.

📌 Solution: Using DISTINCT & LIMIT

SELECT DISTINCT Salary 
FROM Employees 
ORDER BY Salary DESC 
LIMIT 1 OFFSET 1;

✔️ `OFFSET 1` skips the highest salary and picks the second-highest!

📌 Solution: Using `RANK()`

WITH SalaryRanking AS (
    SELECT Salary, RANK() OVER (ORDER BY Salary DESC) AS rnk
    FROM Employees
)
SELECT Salary FROM SalaryRanking WHERE rnk = 2;

✔️ More efficient when handling duplicate salaries!

---

Challenge 2: Find Departments with More than 5 Employees
💡 Problem: Retrieve all departments where employee count is greater than 5.

📌 Solution: Using GROUP BY & HAVING

SELECT DepartmentID, COUNT(*) AS EmployeeCount
FROM Employees
GROUP BY DepartmentID
HAVING COUNT(*) > 5;

✔️ HAVING filters results after aggregation!

---

Challenge 3: Find Consecutive Employee Absences
💡 Problem: Identify employees who were absent 3 days in a row.

📌 Solution: Using `LAG()`

WITH AbsenceData AS (
    SELECT EmployeeID, AbsenceDate, 
           LAG(AbsenceDate, 1) OVER (PARTITION BY EmployeeID ORDER BY AbsenceDate) AS PrevDay1,
           LAG(AbsenceDate, 2) OVER (PARTITION BY EmployeeID ORDER BY AbsenceDate) AS PrevDay2
    FROM Attendance
)
SELECT EmployeeID, AbsenceDate
FROM AbsenceData
WHERE DATEDIFF(AbsenceDate, PrevDay1) = 1
AND DATEDIFF(PrevDay1, PrevDay2) = 1;

✔️ `LAG()` compares current and previous rows!

---

Challenge 4: Detect Duplicate Customer Records
💡 Problem: Find duplicate customers in the Customers table.

📌 Solution: Using GROUP BY

SELECT Name, Email, COUNT(*) AS DuplicateCount
FROM Customers
GROUP BY Name, Email
HAVING COUNT(*) > 1;

✔️ Helps identify and clean duplicate data!

---

Challenge 5: Monthly Revenue Analysis
💡 Problem: Find total revenue per month in the Sales table.

📌 Solution: Using `DATE_FORMAT()` (MySQL)

SELECT DATE_FORMAT(SaleDate, '%Y-%m') AS Month, SUM(Revenue) AS TotalRevenue
FROM Sales
GROUP BY Month;

✔️ Extracts month-year format and calculates revenue!

---
🔹 What You Achieved Today! 🎯
✅ Reviewed all major topics from Week 3
✅ Practiced writing advanced SQL queries
✅ Solved real-world SQL problems

---

🔹 Your Tasks for Today
1️⃣ Try the SQL challenges above in your database.
2️⃣ Modify queries to test different scenarios.
3️⃣ Post your SQL solutions & doubts in the comments!

Tomorrow, we move to Stored Procedures & Functions in SQL! 🚀

👉 Like ❤️ & Share if you're excited for Day 22! 😊

Day 22: Database Design & Normalization

Welcome to Day 22 of your SQL learning journey! 🚀
Today, we will cover Database Design and Normalization, which helps in organizing data efficiently in a database.

🔹 What is Normalization?
🔹 Why do we need it?
🔹 Understanding 1NF, 2NF, 3NF, and BCNF with simple explanations and examples.

By the end of this lesson, you will be able to design efficient databases that reduce redundancy and improve performance! 🎯

---

🔹 What is Normalization?

🔹 Normalization is the process of organizing database tables to:
✔️ Reduce data redundancy (duplicate data).
✔️ Improve data integrity (accuracy & consistency).
✔️ Avoid insertion, update, and deletion anomalies.
✔️ Make queries faster and efficient.

---

🔹 Why Do We Need Normalization?

💡 Problem Without Normalization

Imagine a Students table with the following data:

| StudentID | Name | Course | Instructor | InstructorPhone |
|-----------|--------|-------------|-------------|-----------------|
| 1 | John | SQL Basics | Mr. Smith | 9876543210 |
| 2 | Alice | Python | Ms. Brown | 8765432109 |
| 3 | John | Python | Ms. Brown | 8765432109 |

🔴 Issues in this table:
1️⃣ Duplicate data (John appears twice).
2️⃣ Update Anomaly: If Ms. Brown’s phone number changes, we must update multiple records.
3️⃣ Insertion Anomaly: If a new instructor is added but no student has enrolled yet, we cannot store their details.
4️⃣ Deletion Anomaly: If the last student in a course is removed, we lose instructor information.

👉 Solution? Apply Normalization!

---
🔹 Types of Normal Forms (NF)

✅ 1NF (First Normal Form) – Remove Duplicate Columns & Ensure Atomicity

A table is in 1NF if:
✔️ All columns contain atomic values (indivisible).
✔️ Each column has only one value per row.
✔️ There are no duplicate columns.

🔴 Problem (Not in 1NF)
| StudentID | Name | Courses | Instructor |
|-----------|-------|--------------------|---------------|
| 1 | John | SQL, Python | Smith, Brown |
| 2 | Alice | Python | Brown |

🔴 Issues:
✔️ Courses contain multiple values in one column (SQL, Python).
✔️ Instructors are not separated properly.

✅ Solution (1NF Table)
| StudentID | Name | Course | Instructor |
|-----------|-------|---------|------------|
| 1 | John | SQL | Smith |
| 1 | John | Python | Brown |
| 2 | Alice | Python | Brown |

✔️ Each value is atomic.
✔️ No multiple values in a single column.

---

✅ 2NF (Second Normal Form) – Remove Partial Dependency

A table is in 2NF if:
✔️ It is already in 1NF.
✔️ Every non-key column is fully dependent on the primary key.

🔴 Problem (Not in 2NF)
| StudentID | Name | Course | Instructor | InstructorPhone |
|-----------|-------|---------|------------|-----------------|
| 1 | John | SQL | Smith | 9876543210 |
| 1 | John | Python | Brown | 8765432109 |
| 2 | Alice | Python | Brown | 8765432109 |

🔴 Issues:
✔️ Instructor’s phone number depends only on Instructor, not StudentID.
✔️ If an instructor's phone number changes, we must update multiple records.

✅ Solution (2NF Tables - Splitting into Two Tables)

👉 Students Table
| StudentID | Name | Course | Instructor |
|-----------|-------|---------|------------|
| 1 | John | SQL | Smith |
| 1 | John | Python | Brown |
| 2 | Alice | Python | Brown |

👉 Instructors Table
| Instructor | InstructorPhone |
|-----------|-----------------|
| Smith | 9876543210 |
| Brown | 8765432109 |

✔️ Now, InstructorPhone depends only on Instructor, not StudentID!

---

✅ 3NF (Third Normal Form) – Remove Transitive Dependency

A table is in 3NF if:
✔️ It is already in 2NF.
✔️ No non-key column depends on another non-key column.

🔴 Problem (Not in 3NF)
| StudentID | Name | Course | Instructor | InstructorPhone | InstructorEmail |
|-----------|-------|---------|------------|-----------------|-----------------|
| 1 | John | SQL | Smith | 9876543210 | smith@email.com |
| 1 | John | Python | Brown | 8765432109 | brown@email.com |

🔴 Issue:
✔️ InstructorPhone & InstructorEmail depend on Instructor, not StudentID.

✅ Solution (3NF - Creating a Separate Instructor Table)

👉 Students Table
| StudentID | Name | Course | Instructor |
|-----------|-------|---------|------------|
| 1 | John | SQL | Smith |
| 1 | John | Python | Brown |
| 2 | Alice | Python | Brown |

👉 Instructors Table
| Instructor | InstructorPhone | InstructorEmail |
|------------|----------------|-----------------|
| Smith | 9876543210 | smith@email.com |
| Brown | 8765432109 | brown@email.com |

✔️ Now, no non-key column depends on another non-key column!

---

✅ BCNF (Boyce-Codd Normal Form) – Stronger than 3NF

A table is in BCNF if:
✔️ It is already in 3NF.
✔️ Every determinant is a candidate key (i.e., no partial dependency at all).

🔴 Problem (Not in BCNF)
| Course | Instructor | Room |
|--------|------------|------|
| SQL | Smith | A101 |
| Python | Brown | A102 |
| Python | Lee | A102 |

🔴 Issue:
✔️ Room depends on Course & Instructor, not on Course alone.

✅ Solution (BCNF - Splitting the Table)
👉 Courses Table
| Course | Instructor |
|---------|------------|
| SQL | Smith |
| Python | Brown |
| Python | Lee |

👉 Room Assignment Table
| Instructor | Room |
|------------|------|
| Smith | A101 |
| Brown | A102 |
| Lee | A102 |

✔️ Now, every determinant is a candidate key!

---

# 🔹 Summary

| Normalization Level | Fixes |
|----------------------|--------------------------------|
| 1NF | Remove duplicate columns & ensure atomicity |
| 2NF | Remove partial dependency |
| 3NF | Remove transitive dependency |
| BCNF | Remove all redundancy |

---

🔹 Your Task for Today
✅ Normalize a messy database you’ve worked with before.
✅ Share your doubts or SQL questions in the comments!

Tomorrow, we move to Constraints in SQL! 🚀

👉 Like ❤️ & Share if you're enjoying the SQL series! 😊

Day 23: Constraints in SQL 🚀

Welcome to Day 23 of your SQL journey! Today, we will cover SQL Constraints in depth.

🔹 What are Constraints in SQL?
Constraints control the rules for the data stored in a database table. They ensure accuracy, integrity, and consistency of the data.

👉 Constraints help in preventing invalid data from being inserted.

📌 Today, we will learn about:
✔️ PRIMARY KEY
✔️ FOREIGN KEY
✔️ UNIQUE
✔️ CHECK
✔️ DEFAULT

---

🔹 1. PRIMARY KEY Constraint

A PRIMARY KEY uniquely identifies each record in a table.
✔️ A table can have only one PRIMARY KEY.
✔️ The PRIMARY KEY column cannot have NULL values.
✔️ It must be unique for every row.

✅ Example of PRIMARY KEY

CREATE TABLE Students (
    StudentID INT PRIMARY KEY,
    Name VARCHAR(50),
    Age INT
);

✔️ StudentID is the PRIMARY KEY, meaning each student must have a unique ID and it cannot be NULL.

✅ Inserting Data (Valid & Invalid Cases)

INSERT INTO Students (StudentID, Name, Age) VALUES (1, 'Alice', 20); -- ✅ Valid
INSERT INTO Students (StudentID, Name, Age) VALUES (2, 'Bob', 22);   -- ✅ Valid
INSERT INTO Students (StudentID, Name, Age) VALUES (1, 'Charlie', 25); -- ❌ Error! Duplicate StudentID
INSERT INTO Students (StudentID, Name, Age) VALUES (NULL, 'David', 23); -- ❌ Error! NULL not allowed

---

🔹 2. FOREIGN KEY Constraint

A FOREIGN KEY creates a link between two tables.
✔️ It ensures referential integrity (data in the foreign key column must exist in the referenced table).
✔️ It prevents orphan records (a record that refers to a non-existing row in another table).

✅ Example of FOREIGN KEY

CREATE TABLE Courses (
    CourseID INT PRIMARY KEY,
    CourseName VARCHAR(50)
);

CREATE TABLE Enrollments (
    EnrollmentID INT PRIMARY KEY,
    StudentID INT,
    CourseID INT,
    FOREIGN KEY (StudentID) REFERENCES Students(StudentID),
    FOREIGN KEY (CourseID) REFERENCES Courses(CourseID)
);

✔️ `StudentID` in Enrollments table must exist in the Students table.
✔️ `CourseID` in Enrollments table must exist in the Courses table.

✅ Inserting Data (Valid & Invalid Cases)

INSERT INTO Courses (CourseID, CourseName) VALUES (101, 'SQL Basics'); -- ✅ Valid
INSERT INTO Enrollments (EnrollmentID, StudentID, CourseID) VALUES (1, 1, 101); -- ✅ Valid (Student 1 exists)
INSERT INTO Enrollments (EnrollmentID, StudentID, CourseID) VALUES (2, 5, 101); -- ❌ Error! Student 5 does not exist

---

🔹 3. UNIQUE Constraint

Ensures all values in a column are unique, but it allows NULL values (unlike PRIMARY KEY).
✔️ Prevents duplicate values in a column.
✔️ A table can have multiple UNIQUE constraints (unlike PRIMARY KEY).

✅ Example of UNIQUE Constraint

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    Email VARCHAR(100) UNIQUE,
    Phone VARCHAR(15) UNIQUE
);

✔️ EmployeeID is the PRIMARY KEY (must be unique & non-null).
✔️ Email and Phone must be unique, but they can be NULL.

✅ Inserting Data (Valid & Invalid Cases)

INSERT INTO Employees (EmployeeID, Email, Phone) VALUES (1, 'alice@email.com', '1234567890'); -- ✅ Valid
INSERT INTO Employees (EmployeeID, Email, Phone) VALUES (2, 'bob@email.com', '9876543210'); -- ✅ Valid
INSERT INTO Employees (EmployeeID, Email, Phone) VALUES (3, 'alice@email.com', '5678901234'); -- ❌ Error! Duplicate Email
INSERT INTO Employees (EmployeeID, Email, Phone) VALUES (4, NULL, '5678901234'); -- ✅ Valid (NULL allowed in UNIQUE)

---

🔹 4. CHECK Constraint

CHECK ensures that column values meet specific conditions.
✔️ Used to enforce business rules (e.g., Age must be greater than 18).

✅ Example of CHECK Constraint

CREATE TABLE Customers (
    CustomerID INT PRIMARY KEY,
    Name VARCHAR(50),
    Age INT CHECK (Age >= 18)
);

✔️ Ensures that Age must be 18 or older.

✅ Inserting Data (Valid & Invalid Cases)

INSERT INTO Customers (CustomerID, Name, Age) VALUES (1, 'Alice', 25); -- ✅ Valid
INSERT INTO Customers (CustomerID, Name, Age) VALUES (2, 'Bob', 17); -- ❌ Error! Age must be >= 18

---

🔹 5. DEFAULT Constraint