3️⃣ Find employees categorized by salary range.

SELECT Name, 
CASE  
    WHEN Salary >= 70000 THEN 'High Salary'
    WHEN Salary >= 50000 THEN 'Medium Salary'
    ELSE 'Low Salary'
END AS Salary_Category
FROM Employees;

📌 Your Tasks for Today
✅ Practice `IS NULL`, `COALESCE()`, and `CASE` statements.
✅ Run the example queries in SQL.
✅ Solve at least 2 SQL problems from LeetCode.
✅ Comment "Done ✅" once you complete today’s practice!

Tomorrow, we’ll dive into subqueries and correlated subqueries!

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

As I was having Uni exam, therefore, will continue this series from tomorrow.

Happy Learning!!

Let's continue our learning!!

Day 11: Understanding Subqueries and Correlated Subqueries in SQL

Welcome back! Today, we are going to explore an advanced yet essential SQL concept: Subqueries and Correlated Subqueries.

By the end of today’s session, you will understand:
✔️ What subqueries are and how they work.
✔️ The difference between subqueries and correlated subqueries.
✔️ When and how to use them in SQL queries.

---

📌 What is a Subquery in SQL?
A subquery is a query inside another query.
- It is also called a nested query.
- It helps fetch data from multiple tables in a structured way.
- The result of the subquery is used in the main query.

🛠 Basic Syntax of a Subquery

SELECT column1, column2 
FROM main_table 
WHERE column_name OPERATOR ( 
    SELECT column_name FROM sub_table WHERE condition 
);

- The inner query (subquery) runs first.
- The outer query then uses the subquery’s result.

---

🔹 Example 1: Find Employees Who Earn More Than the Average Salary
🎯 Problem Statement:
We want to find employees whose salaries are higher than the average salary of all employees.

🚀 SQL Query:

SELECT Name, Salary 
FROM Employees 
WHERE Salary > (SELECT AVG(Salary) FROM Employees);

🔍 How it Works?
1. Subquery: SELECT AVG(Salary) FROM Employees;
- Finds the average salary from the Employees table.
2. Main Query:
- Selects employees whose salaries are greater than this average.

✅ Example Output:
| Name | Salary |
|---------|--------|
| Charlie | 60000 |

---

🔹 Example 2: Find Customers Who Placed Orders
🎯 Problem Statement:
We want to find customers who have placed at least one order.

🚀 SQL Query:

SELECT CustomerID, CustomerName 
FROM Customers 
WHERE CustomerID IN (SELECT DISTINCT CustomerID FROM Orders);

🔍 How it Works?
1. Subquery: SELECT DISTINCT CustomerID FROM Orders;
- Finds unique customers who placed orders.
2. Main Query:
- Selects those customers from the Customers table.

✅ Example Output:
| CustomerID | CustomerName |
|-----------|--------------|
| 101 | Alice |
| 102 | Bob |

---

📌 Types of Subqueries in SQL
There are three types of subqueries:
1️⃣ Single-Row Subquery: Returns one value.
2️⃣ Multi-Row Subquery: Returns multiple values.
3️⃣ Multi-Column Subquery: Returns multiple columns.

🔹 Single-Row Subquery Example
Find employees whose salaries are equal to the highest salary.

SELECT Name, Salary 
FROM Employees 
WHERE Salary = (SELECT MAX(Salary) FROM Employees);

✅ Returns one row because MAX(Salary) is a single value.

---

🔹 Multi-Row Subquery Example
Find all employees who work in departments located in New York.

SELECT Name, DepartmentID 
FROM Employees 
WHERE DepartmentID IN (SELECT DepartmentID FROM Departments WHERE Location = 'New York');

✅ The subquery returns multiple DepartmentIDs, so we use IN.

---

🔹 Multi-Column Subquery Example
Find employees whose department and location match the highest-paid employee.

SELECT Name, DepartmentID, Location 
FROM Employees 
WHERE (DepartmentID, Location) = 
      (SELECT DepartmentID, Location FROM Employees ORDER BY Salary DESC LIMIT 1);

✅ The subquery returns two columns, so we use a tuple (DepartmentID, Location).

---

📌 What is a Correlated Subquery?
A correlated subquery is different from a regular subquery:
✅ The subquery depends on the outer query.
✅ The subquery runs once for each row in the main query.
✅ It is usually used with EXISTS, NOT EXISTS, or WHERE clauses.

---

🔹 Example 3: Correlated Subquery
🎯 Problem Statement:
Find employees who earn more than the average salary of their own department.

🚀 SQL Query:

SELECT e1.Name, e1.DepartmentID, e1.Salary 
FROM Employees e1 
WHERE Salary > (
    SELECT AVG(e2.Salary) 
    FROM Employees e2 
    WHERE e1.DepartmentID = e2.DepartmentID
);

🔍 How it Works?
1. The subquery depends on the outer query.
2. It calculates the average salary per department.
3. The main query checks if each employee's salary is higher than their department’s average.

✅ Example Output:
| Name | DepartmentID | Salary |
|---------|-------------|--------|
| Alice | 101 | 50000 |
| Charlie | 102 | 60000 |

---

📌 Difference Between Subquery and Correlated Subquery
| Feature | Subquery | Correlated Subquery |
|---------|----------|--------------------|
| Runs | Runs once | Runs once per row in main query |
| Dependent on Outer Query? | No | Yes |
| Performance | Faster | Slower |
| Example Usage | Find employees with salary above average | Find employees earning above their department’s average |

---

🎯 Hands-On Practice: Try These Queries
1️⃣ Find all employees who have the same salary as Bob.

SELECT Name FROM Employees WHERE Salary = 
(SELECT Salary FROM Employees WHERE Name = 'Bob');

2️⃣ Find all orders placed in 2024 by customers who live in New York.

SELECT OrderID FROM Orders 
WHERE CustomerID IN (SELECT CustomerID FROM Customers WHERE City = 'New York') 
AND OrderDate BETWEEN '2024-01-01' AND '2024-12-31';

3️⃣ Find employees whose salary is higher than the average salary in their department.

SELECT Name, Salary FROM Employees e1 
WHERE Salary > (SELECT AVG(Salary) FROM Employees e2 WHERE e1.DepartmentID = e2.DepartmentID);

---

📌 Your Tasks for Today
✅ Practice at least 3 subquery-based problems.
✅ Try writing a correlated subquery.
✅ Comment "Done ✅" once you complete today’s practice!

Tomorrow, we will explore JOINS and their types in SQL!

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

Day 12: Understanding SQL JOINS (Beginner to Advanced)

Welcome to Day 12 of your SQL learning journey! 🚀 Today, we are diving deep into one of the most important concepts in SQL – JOINS.

By the end of today’s session, you will understand:
✔️ What JOINS are and why we use them
✔️ Types of SQL JOINS and their differences
✔️ How to use JOINS in real-world scenarios
✔️ Practical SQL examples for each type of JOIN

---

# 📌 What is a JOIN in SQL?
A JOIN in SQL is used to combine data from multiple tables based on a common column.

🔍 Why Do We Need JOINS?
- Databases store data in multiple tables to maintain efficiency.
- To retrieve meaningful information, we need to combine data from different tables.
- JOINS help us link related records in different tables.

🛠 Basic Syntax of a JOIN

SELECT columns
FROM table1
JOIN table2
ON table1.common_column = table2.common_column;

- JOIN tells SQL to combine data from both tables.
- ON specifies the matching condition.

---

📌 Types of SQL JOINS

There are four main types of JOINS in SQL:
| Type of JOIN | Returns |
|-------------|---------|
| INNER JOIN | Only matching rows from both tables |
| LEFT JOIN (or LEFT OUTER JOIN) | All rows from the left table + matching rows from the right table |
| RIGHT JOIN (or RIGHT OUTER JOIN) | All rows from the right table + matching rows from the left table |
| FULL JOIN (or FULL OUTER JOIN) | All rows from both tables (matches + non-matches) |

Let’s explore each type with real-world examples!

---

🔹 INNER JOIN (Most Common JOIN)
🎯 Problem Statement:
Find the names of customers who placed orders.

🚀 SQL Query:

SELECT Customers.CustomerID, Customers.CustomerName, Orders.OrderID 
FROM Customers  
INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID;

🔍 How it Works?
- The INNER JOIN returns only rows where there is a match in both tables.
- If a customer has not placed an order, they will not appear in the result.

✅ Example Output:
| CustomerID | CustomerName | OrderID |
|-----------|--------------|----------|
| 101 | Alice | 5001 |
| 102 | Bob | 5002 |

---

🔹 LEFT JOIN (LEFT OUTER JOIN)
🎯 Problem Statement:
Find all customers, even those who have not placed any orders.

🚀 SQL Query:

SELECT Customers.CustomerID, Customers.CustomerName, Orders.OrderID 
FROM Customers  
LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID;

🔍 How it Works?
- Returns all customers from the Customers table.
- If a customer has not placed an order, the OrderID will be NULL.

✅ Example Output:
| CustomerID | CustomerName | OrderID |
|-----------|--------------|----------|
| 101 | Alice | 5001 |
| 102 | Bob | 5002 |
| 103 | Charlie | NULL | ❌ (No orders)

---

🔹 RIGHT JOIN (RIGHT OUTER JOIN)
🎯 Problem Statement:
Find all orders, even those placed by non-existing customers.

🚀 SQL Query:

SELECT Customers.CustomerID, Customers.CustomerName, Orders.OrderID 
FROM Customers  
RIGHT JOIN Orders ON Customers.CustomerID = Orders.CustomerID;

🔍 How it Works?
- Returns all orders from the Orders table.
- If an order has no matching customer, CustomerName will be NULL.

✅ Example Output:
| CustomerID | CustomerName | OrderID |
|-----------|--------------|----------|
| 101 | Alice | 5001 |
| 102 | Bob | 5002 |
| NULL | NULL | 5003 | ❌ (Order placed by a deleted customer)

---

🔹 FULL JOIN (FULL OUTER JOIN)
🎯 Problem Statement:
Find all customers and all orders, even if there is no match.

🚀 SQL Query:

SELECT Customers.CustomerID, Customers.CustomerName, Orders.OrderID 
FROM Customers  
FULL JOIN Orders ON Customers.CustomerID = Orders.CustomerID;

🔍 How it Works?
- Returns all customers and all orders, including non-matching records.

✅ Example Output:
| CustomerID | CustomerName | OrderID |
|-----------|--------------|----------|
| 101 | Alice | 5001 |
| 102 | Bob | 5002 |
| 103 | Charlie | NULL | ❌ (No orders)
| NULL | NULL | 5003 | ❌ (Order placed by a deleted customer)

---

📌 Difference Between JOINs
| JOIN Type | Returns |
|----------|---------|
| INNER JOIN | Only matching records |
| LEFT JOIN | All records from the left table + matches from the right |
| RIGHT JOIN | All records from the right table + matches from the left |
| FULL JOIN | All records from both tables |

---

🔹 CROSS JOIN (Bonus JOIN!)
🎯 Problem Statement:
Find all possible customer and product combinations.

🚀 SQL Query:

SELECT Customers.CustomerName, Products.ProductName 
FROM Customers  
CROSS JOIN Products;

🔍 How it Works?
- Returns every possible combination of Customers and Products.

✅ Example Output:
| CustomerName | ProductName |
|-------------|--------------|
| Alice | Laptop |
| Alice | Mobile |
| Bob | Laptop |
| Bob | Mobile |

🚨 Warning: CROSS JOIN creates a huge number of rows!

---

🎯 Hands-On Practice: Try These Queries
1️⃣ Find customers who have placed at least one order.

SELECT Customers.CustomerName 
FROM Customers 
INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID;

2️⃣ Find all employees and their department names.

SELECT Employees.Name, Departments.DepartmentName 
FROM Employees 
LEFT JOIN Departments ON Employees.DepartmentID = Departments.DepartmentID;

3️⃣ Find all products that have never been ordered.

SELECT Products.ProductName 
FROM Products 
LEFT JOIN Orders ON Products.ProductID = Orders.ProductID 
WHERE Orders.OrderID IS NULL;

---

📌 Your Tasks for Today
✅ Practice at least 3 JOIN queries
✅ Comment "Done ✅" once you complete today’s practice!

Tomorrow, we will explore GROUP BY, HAVING, and Aggregate Functions in SQL!

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

Day 13: Date and Time Functions in SQL

Welcome to Day 13 of your SQL learning journey! 🚀 Today, we will explore Date and Time functions in SQL.

By the end of this session, you will understand:
✔️ Why Date & Time functions are important
✔️ Common Date & Time functions: NOW, CURDATE, DATEDIFF, DATEADD
✔️ How to use them with practical examples

---

📌 Why Do We Need Date & Time Functions?
Many databases store date and time-related information, such as:
- Birthdays, order dates, or event schedules 📅
- Tracking when records were created or updated ⏳
- Performing calculations like age, time difference, and future dates

SQL provides built-in Date and Time functions to handle such operations efficiently.

---

🔹 1. NOW() – Get the Current Date & Time
The NOW() function returns the current date and time in the format YYYY-MM-DD HH:MI:SS.

🚀 SQL Query:

SELECT NOW() AS CurrentDateTime;

✅ Example Output:
| CurrentDateTime |
|-------------------------|
| 2025-02-18 14:30:00 |

🔍 How It Works?
- Retrieves the exact timestamp when the query is executed.
- Useful for logging user activity, order timestamps, or event tracking.

---

🔹 2. CURDATE() – Get the Current Date (Without Time)
The CURDATE() function returns the current date in the format YYYY-MM-DD, without the time.

🚀 SQL Query:

SELECT CURDATE() AS TodayDate;

✅ Example Output:
| TodayDate |
|------------|
| 2025-02-18 |

🔍 How It Works?
- Retrieves only the date (without time).
- Useful for birthday reminders, daily reports, and scheduling.

---

🔹 3. DATEDIFF() – Find the Difference Between Two Dates
The DATEDIFF() function calculates the difference (in days) between two dates.

🎯 Problem Statement:
Find how many days are left until New Year’s Day 2026.

🚀 SQL Query:

SELECT DATEDIFF('2026-01-01', CURDATE()) AS DaysUntilNewYear;

✅ Example Output:
| DaysUntilNewYear |
|------------------|
| 317 |

🔍 How It Works?
- Subtracts the second date (CURDATE()) from the first date (2026-01-01).
- Returns the number of days between them.
- Useful for calculating deadlines, project durations, and upcoming events.

---

🔹 4. DATEADD() – Add or Subtract Days from a Date
The DATEADD() function adds or subtracts a specific number of days, months, or years from a given date.

🚀 SQL Query (Adding 30 Days to Today’s Date):

SELECT DATE_ADD(CURDATE(), INTERVAL 30 DAY) AS FutureDate;

✅ Example Output:
| FutureDate |
|------------|
| 2025-03-20 |

🚀 SQL Query (Subtracting 7 Days from Today’s Date):

SELECT DATE_ADD(CURDATE(), INTERVAL -7 DAY) AS LastWeek;

✅ Example Output:
| LastWeek |
|------------|
| 2025-02-11 |

🔍 How It Works?
- INTERVAL X DAY adds/subtracts X days from the given date.
- Useful for calculating due dates, scheduling tasks, and checking expiry dates.

---

📌 Combining Date Functions in Real-Life Scenarios

🔹 Example 1: Find Employees Who Joined More Than 5 Years Ago

SELECT EmployeeName, HireDate 
FROM Employees 
WHERE DATEDIFF(CURDATE(), HireDate) > 1825;

✅ Finds employees hired **more than 5 years ago (5×365 = 1825 days).

---

🔹 Example 2: Get the Date 6 Months from Today

SELECT DATE_ADD(CURDATE(), INTERVAL 6 MONTH) AS SixMonthsLater;

✅ Useful for project deadlines, warranty periods, and subscription renewals.

---

🔹 Example 3: Find Orders Placed in the Last 7 Days

SELECT OrderID, OrderDate 
FROM Orders 
WHERE OrderDate >= DATE_ADD(CURDATE(), INTERVAL -7 DAY);

✅ Finds orders from the past week for reporting and analysis.

---

📌 Summary Table: Date & Time Functions
| Function | Description | Example Output |
|----------|------------|---------------|
| NOW() | Current date & time | 2025-02-18 14:30:00 |
| CURDATE() | Current date only | 2025-02-18 |
| DATEDIFF() | Difference between two dates | 317 |
| DATEADD() | Add/subtract days from a date | 2025-03-20 |

---

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

Tomorrow, we will explore Advanced String Functions in SQL!

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

Day 14: Combining Results in SQL – UNION, UNION ALL, INTERSECT, EXCEPT

Welcome to Day 14 of your SQL learning journey! 🚀 Today, we will learn how to combine results from multiple queries using SQL set operations:

✔️ UNION – Combines results and removes duplicates
✔️ UNION ALL – Combines results without removing duplicates
✔️ INTERSECT – Returns common records between two queries
✔️ EXCEPT – Returns records present in one query but not in the other

These operations are useful when working with data from multiple tables. Let’s break them down one by one.

---

📌 Why Do We Need Set Operators?

Imagine you have two tables:

🔹 Table 1: Customers in the USA
| CustomerID | Name | Country |
|------------|------|---------|
| 1 | Alice | USA |
| 2 | Bob | USA |
| 3 | Charlie | USA |

🔹 Table 2: Customers in Canada
| CustomerID | Name | Country |
|------------|------|---------|
| 4 | David | Canada |
| 5 | Alice | Canada |
| 6 | Emma | Canada |

Now, let's say you want to:
- Get a list of all customers from both tables.
- Find customers who are present in both the USA and Canada tables.
- Find customers who are only in the USA but not in Canada.

We can solve these problems using UNION, UNION ALL, INTERSECT, and EXCEPT!

---

🔹 1. UNION – Combine Results and Remove Duplicates
The UNION operator combines the results of two queries and removes duplicate records.

🚀 SQL Query:

SELECT Name, Country FROM USA_Customers  
UNION  
SELECT Name, Country FROM Canada_Customers;

✅ Example Output:
| Name | Country |
|---------|---------|
| Alice | USA |
| Bob | USA |
| Charlie | USA |
| David | Canada |
| Emma | Canada |

🔍 How It Works?
- Combines the records from both tables.
- Removes duplicates (Notice Alice appears only once).
- Useful when merging data from multiple sources without duplicates.

---

🔹 2. UNION ALL – Combine Results Without Removing Duplicates
The UNION ALL operator combines the results of two queries but keeps duplicate records.

🚀 SQL Query:

SELECT Name, Country FROM USA_Customers  
UNION ALL  
SELECT Name, Country FROM Canada_Customers;

✅ Example Output:
| Name | Country |
|---------|---------|
| Alice | USA |
| Bob | USA |
| Charlie | USA |
| David | Canada |
| Alice | Canada |
| Emma | Canada |

🔍 How It Works?
- Similar to UNION, but does not remove duplicates (Alice appears twice).
- Faster than UNION because it doesn’t check for duplicates.
- Useful when you want to keep all records, even duplicates.

---

🔹 3. INTERSECT – Find Common Records in Both Queries
The INTERSECT operator returns only the common records present in both queries.

🚀 SQL Query:

SELECT Name, Country FROM USA_Customers  
INTERSECT  
SELECT Name, Country FROM Canada_Customers;

✅ Example Output:
| Name | Country |
|------|---------|
| Alice | USA |

🔍 How It Works?
- Finds common values in both queries.
- Useful when you need to find customers, products, or employees that exist in both lists.

---

🔹 4. EXCEPT – Find Records Present in One Query But Not in the Other
The EXCEPT operator returns records from the first query that are NOT in the second query.

🚀 SQL Query (Find customers who are in the USA but not in Canada):

SELECT Name, Country FROM USA_Customers  
EXCEPT  
SELECT Name, Country FROM Canada_Customers;

✅ Example Output:
| Name | Country |
|---------|---------|
| Bob | USA |
| Charlie | USA |

🔍 How It Works?
- Returns records only present in the first query, but not in the second query.
- Useful for finding unique records that do not exist in another dataset.

---

📌 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...!!