@Codingdidi

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

Tomorrow, we will explore Advanced String Functions in SQL! 👉 Like ❤️ and Share if you're excited for Day 14! 😊

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!

✅ 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 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: | 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! 😊

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.

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

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! 😊

Day 10: Working with NULL Values & Using CASE Statements in SQL Welcome back! Today, we’re going to cover two important SQL topics: ✔️ NULL values – What they are and how to handle them. ✔️ CASE statements – A powerful way to apply conditional logic in SQL. By the end of today’s lesson, you’ll understand how to deal with missing data (NULL) and how to write conditional logic directly in SQL queries. --- 📌 What is NULL in SQL? 🔹 In SQL, NULL means "missing" or "unknown" data. 🔹 It is not the same as 0 or an empty string (""). 🔹 When a column has NULL, it means no value has been assigned yet. 🎯 Example: Employee Salaries | Employee_ID | Name | Salary | |------------|--------|--------| | 1 | Alice | 50000 | | 2 | Bob | NULL | | 3 | Charlie| 60000 | | 4 | David | NULL | Bob and David have NULL salaries, meaning we don’t know their salary yet. --- 🔹 How to Handle NULL Values in SQL 1️⃣ Checking for NULL using `IS NULL` and `IS NOT NULL` 🚀 Find employees whose salary is missing:

SELECT Name 
FROM Employees 
WHERE Salary IS NULL;

🔹 Output: | Name | |-------| | Bob | | David | 🚀 Find employees whose salary is known:

SELECT Name, Salary 
FROM Employees 
WHERE Salary IS NOT NULL;

🔹 Output: | Name | Salary | |--------|--------| | Alice | 50000 | | Charlie| 60000 | 📌 Important: You cannot check NULL with = NULL or != NULL. Always use IS NULL or IS NOT NULL. --- 2️⃣ Replacing NULL Values Using `COALESCE()` 🔹 COALESCE() replaces `NULL` with a default value. 🚀 Example: Replace NULL salaries with "Not Assigned"

SELECT Name, COALESCE(Salary, 'Not Assigned') AS Salary 
FROM Employees;

🔹 Output: | Name | Salary | |---------|-------------| | Alice | 50000 | | Bob | Not Assigned | | Charlie | 60000 | | David | Not Assigned | 💡 `COALESCE()` takes multiple values and returns the first non-NULL value. --- 3️⃣ Handling NULL in Aggregations 🔹 Aggregate functions (SUM, AVG, COUNT, etc.) automatically ignore NULLs. 🚀 Find total salary of all employees:

SELECT SUM(Salary) AS Total_Salary 
FROM Employees;

🔹 Output: | Total_Salary | |-------------| | 110000 | 📌 NULL salaries are ignored in SUM. 🚀 Find average salary (excluding NULL values):

SELECT AVG(Salary) AS Avg_Salary 
FROM Employees;

🔹 Output: | Avg_Salary | |-----------| | 55000 | 🔹 If you want to include NULL as 0 in calculations, use COALESCE(Salary, 0):

SELECT AVG(COALESCE(Salary, 0)) AS Avg_Salary 
FROM Employees;

🔹 Output: | Avg_Salary | |-----------| | 36666.67 | Now, let's move on to CASE statements. --- 🔹 Using CASE Statements in SQL 🔹 CASE is like an IF-ELSE statement in SQL. 🔹 It allows you to return different values based on conditions. 🎯 Example: Categorizing Employees by Salary

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

🔹 Output: | Name | Salary | Salary_Category | |---------|--------|----------------| | Alice | 50000 | Medium Salary | | Bob | NULL | Low Salary | | Charlie | 60000 | High Salary | | David | NULL | Low Salary | 📌 NULL values are treated as `ELSE` conditions unless handled separately. 🚀 Handling NULL separately in CASE:

SELECT Name, 
CASE  
    WHEN Salary IS NULL THEN 'Salary Not Available'
    WHEN Salary >= 60000 THEN 'High Salary'
    WHEN Salary >= 40000 THEN 'Medium Salary'
    ELSE 'Low Salary'
END AS Salary_Category
FROM Employees;

🔹 Now, NULL values are labeled as "Salary Not Available". --- 🎯 Hands-On Practice: Try These Queries 1️⃣ Find all employees whose salary is NULL.

SELECT Name FROM Employees WHERE Salary IS NULL;

2️⃣ Replace NULL salaries with 0.

SELECT Name, COALESCE(Salary, 0) AS Salary FROM Employees;

📌 Key Takeaway: Use SELF JOIN when working with hierarchical data like employees and managers, product categories, etc. --- 🎯 Hands-On Practice: Try These Queries 1️⃣ Get all students and their courses using RIGHT JOIN.

SELECT Students.Name, Courses.Course_Name
FROM Students
RIGHT JOIN Courses
ON Students.Course_ID = Courses.Course_ID;

2️⃣ List all students and all courses, even if there is no match (FULL OUTER JOIN).

SELECT Students.Name, Courses.Course_Name
FROM Students
FULL OUTER JOIN Courses
ON Students.Course_ID = Courses.Course_ID;

3️⃣ Find employee-manager relationships using SELF JOIN.

SELECT E1.Name AS Employee, E2.Name AS Manager
FROM Employees E1
LEFT JOIN Employees E2
ON E1.Manager_ID = E2.Employee_ID;

--- 📌 Your Tasks for Today ✅ Revise RIGHT JOIN, FULL OUTER JOIN, and SELF JOIN. ✅ Run the practice queries in SQL. ✅ Try at least 3 SQL JOIN problems on HackerRank or LeetCode. ✅ Comment "Done ✅" once you complete today's practice! Tomorrow, we’ll explore NULL values and how to handle them in SQL! 👉 Like ❤️ and Share if you’re excited for Day 10! 😊

Day 9: SQL JOINS Continued – RIGHT JOIN, FULL OUTER JOIN, and SELF JOIN 🔗 Welcome back! Yesterday, we learned about INNER JOIN and LEFT JOIN, which help us combine data from multiple tables. Today, we’ll continue exploring more SQL JOINS, including: ✔️ RIGHT JOIN – Opposite of LEFT JOIN ✔️ FULL OUTER JOIN – Returns all records from both tables ✔️ SELF JOIN – Joining a table with itself By the end of today’s lesson, you’ll have a strong understanding of how to combine data across tables efficiently. --- 📌 Recap of JOINS 🔹 INNER JOIN → Returns only matching records from both tables. 🔹 LEFT JOIN → Returns all records from the left table, with matching records from the right table. Now, let’s move on to RIGHT JOIN, FULL OUTER JOIN, and SELF JOIN. --- 🔹 RIGHT JOIN – The Opposite of LEFT JOIN 🔹 RIGHT JOIN (or RIGHT OUTER JOIN) returns: ✅ All records from the right table ✅ Matching records from the left table ✅ If there’s no match, the left table will show NULL values 🎯 Example: Students and Courses | Students Table | | Courses Table | |------------------|-----------------|------------------| | Student_ID | Name | Course_ID | Course_ID | Course_Name | |-----------|--------|----------|----------|-------------| | 1 | Alice | C101 | C101 | Python | | 2 | Bob | C102 | C102 | SQL | | 3 | Charlie| C103 | C103 | Power BI | | 4 | David | NULL | C104 | JavaScript | 🔹 C104 (JavaScript) has no students enrolled. 👉 RIGHT JOIN Query:

SELECT Students.Name, Courses.Course_Name
FROM Students
RIGHT JOIN Courses
ON Students.Course_ID = Courses.Course_ID;

🔹 Output | Name | Course_Name | |-------|------------| | Alice | Python | | Bob | SQL | | Charlie | Power BI | | NULL | JavaScript | 🚀 JavaScript is included, but no student is enrolled (NULL in Name column). 📌 Key Takeaway: Use RIGHT JOIN when you want all data from the right table and matching data from the left. --- 🔹 FULL OUTER JOIN – All Data from Both Tables 🔹 FULL OUTER JOIN returns: ✅ All records from both tables ✅ If there’s no match, NULL values will be filled 👉 FULL OUTER JOIN Query:

SELECT Students.Name, Courses.Course_Name
FROM Students
FULL OUTER JOIN Courses
ON Students.Course_ID = Courses.Course_ID;

🔹 Output | Name | Course_Name | |-------|------------| | Alice | Python | | Bob | SQL | | Charlie | Power BI | | David | NULL | | NULL | JavaScript | 🚀 David (who has no course) and JavaScript (which has no students) are both included. 📌 Key Takeaway: Use FULL OUTER JOIN when you need all data from both tables, even if there’s no match. ❗️ Note: Not all databases support FULL OUTER JOIN. If yours doesn’t, you can simulate it using `UNION`:

SELECT Students.Name, Courses.Course_Name
FROM Students
LEFT JOIN Courses ON Students.Course_ID = Courses.Course_ID
UNION
SELECT Students.Name, Courses.Course_Name
FROM Students
RIGHT JOIN Courses ON Students.Course_ID = Courses.Course_ID;

--- 🔹 SELF JOIN – When a Table Joins Itself 🔹 Sometimes, a table contains hierarchical or related data within itself. 🔹 A SELF JOIN allows a table to join with itself to compare data in different rows. 🎯 Example: Employee Hierarchy | Employee_ID | Name | Manager_ID | |------------|--------|------------| | 1 | Alice | NULL | | 2 | Bob | 1 | | 3 | Charlie| 1 | | 4 | David | 2 | 🔹 Here, Bob and Charlie report to Alice, and David reports to Bob. 👉 SELF JOIN Query to Find Employee-Manager Relationship:

SELECT E1.Name AS Employee, E2.Name AS Manager
FROM Employees E1
LEFT JOIN Employees E2
ON E1.Manager_ID = E2.Employee_ID;

🔹 Output | Employee | Manager | |----------|---------| | Alice | NULL | | Bob | Alice | | Charlie | Alice | | David | Bob | 🚀 Alice has no manager (NULL), Bob & Charlie report to Alice, and David reports to Bob.

Day 8: SQL JOINS – Combining Data from Multiple Tables 🔗 Welcome to Week 2 of your SQL journey! 🚀 So far, we have learned how to retrieve, filter, sort, and aggregate data from a single table. But in real-world databases, data is stored across multiple tables to maintain efficiency and reduce redundancy. 💡 How do we fetch related data from multiple tables? 👉 Using SQL JOINS! Today, we will cover: ✔️ What are JOINS? ✔️ Types of JOINS (Focusing on INNER JOIN & LEFT JOIN today). ✔️ Real-life examples with simple explanations. ✔️ Hands-on SQL queries. --- 📌 What are SQL JOINS? A JOIN is used to combine rows from two or more tables based on a related column. Imagine you have: - Students Table (Stores student details). - Courses Table (Stores course details). If you want to find which student is enrolled in which course, you need to JOIN these two tables on a common column (e.g., Student_ID). --- 🔹 Types of SQL JOINS 1️⃣ INNER JOIN – Returns only matching records from both tables. 2️⃣ LEFT JOIN – Returns all records from the left table and matching records from the right table. 3️⃣ RIGHT JOIN – Returns all records from the right table and matching records from the left table. 4️⃣ FULL JOIN – Returns all records from both tables (matching and non-matching). --- 🔹 Understanding INNER JOIN (Most Common JOIN) INNER JOIN returns only the matching records from both tables. 🎯 Example: Students and Courses | Students Table | | Courses Table | |------------------|-----------------|------------------| | Student_ID | Name | Course_ID | Course_ID | Course_Name | |-----------|--------|----------|----------|-------------| | 1 | Alice | C101 | C101 | Python | | 2 | Bob | C102 | C102 | SQL | | 3 | Charlie| C103 | C103 | Power BI | | 4 | David | NULL | | | 🔹 David has no course assigned (NULL value in Course_ID). 🔹 To get only students who are enrolled in a course, we use INNER JOIN.

SELECT Students.Name, Courses.Course_Name
FROM Students
INNER JOIN Courses
ON Students.Course_ID = Courses.Course_ID;

🔹 Output: | Name | Course_Name | |-------|------------| | Alice | Python | | Bob | SQL | | Charlie | Power BI | 🚀 David is not included because he has no matching Course_ID. --- 🔹 Understanding LEFT JOIN 🔹 LEFT JOIN returns ALL records from the left table (Students) and matching records from the right table (Courses). 🔹 If there is no match, NULL is returned.

SELECT Students.Name, Courses.Course_Name
FROM Students
LEFT JOIN Courses
ON Students.Course_ID = Courses.Course_ID;

🔹 Output (Now including David too): | Name | Course_Name | |-------|------------| | Alice | Python | | Bob | SQL | | Charlie | Power BI | | David | NULL | 🚀 David is included, but Course_Name is NULL because there is no matching Course_ID. --- 🎯 Hands-On Practice: Solve These SQL Problems 1️⃣ Find all students who are enrolled in a course (Use INNER JOIN).

SELECT Students.Name, Courses.Course_Name
FROM Students
INNER JOIN Courses
ON Students.Course_ID = Courses.Course_ID;

2️⃣ Find all students, even those without courses (Use LEFT JOIN).

SELECT Students.Name, Courses.Course_Name
FROM Students
LEFT JOIN Courses
ON Students.Course_ID = Courses.Course_ID;

3️⃣ Find courses that have students enrolled (Use INNER JOIN with Course as the left table).

SELECT Courses.Course_Name, Students.Name
FROM Courses
INNER JOIN Students
ON Students.Course_ID = Courses.Course_ID;

📌 Your Tasks for Today ✅ Revise INNER JOIN & LEFT JOIN. ✅ Run the practice queries in SQL. ✅ Try at least 3 SQL JOIN problems on HackerRank or LeetCode. ✅ Comment "Done ✅" once you complete today's practice! Tomorrow, we’ll explore RIGHT JOIN, FULL JOIN, and SELF JOIN! 👉 Like ❤️ and Share if you’re excited for Day 9! 😊

Tomorrow, we move on to SQL Joins (INNER JOIN, LEFT JOIN, etc.) – a crucial topic for combining multiple tables! 🚀 Like ❤️ and Share if you're excited for Day 8! 😊

Day 7: SQL Hands-On Practice – Strengthening Your Basics 🎯 Congratulations! 🎉 You've completed the first 6 days of SQL learning! Now, before moving forward to more complex topics, it's important to review, practice, and solidify what we've learned so far. Today, we will: ✔️ Revise the key SQL concepts covered this week. ✔️ Practice with real SQL queries. ✔️ Solve challenges on SQL platforms like HackerRank, LeetCode, and W3Schools. --- 📌 Quick Revision of Week 1 Topics Let’s quickly go over what we learned in the past 6 days. 🔹 Day 1: Introduction to SQL & Databases - SQL (Structured Query Language) is used to store, retrieve, and manage data in databases. - Popular database systems: MySQL, PostgreSQL, SQL Server. - SQL operates on tables that store data in rows and columns. 🔹 Day 2: SQL Data Types & Basic Queries - SQL supports different data types: - INT (integer numbers) - VARCHAR(n) (text) - DATE (dates) - DECIMAL(p,s) (for precise numbers like currency). - Basic queries:

  SELECT column1, column2 FROM table_name;
  

- SELECT retrieves specific columns. - FROM specifies the table. 🔹 Day 3: Filtering Data with `WHERE` - The WHERE clause filters rows based on conditions. - Comparison operators: =, >, <, >=, <=, != - Logical operators: AND, OR, NOT

SELECT * FROM Students WHERE Age > 18;

Filters students where `Age` is greater than 18. 🔹 Day 4: Sorting & Limiting Data - ORDER BY sorts data in ascending (`ASC`) or descending (`DESC`) order. - LIMIT restricts the number of rows. - DISTINCT removes duplicates.

SELECT Name, Grade FROM Students ORDER BY Grade DESC LIMIT 3;

🔹 Finds the top 3 students with the highest grades. 🔹 Day 5: Aggregate Functions - COUNT(), SUM(), AVG(), MIN(), MAX() summarize data.

SELECT COUNT(*) FROM Students WHERE Grade = 'A';

🔹 Counts how many students got Grade A. 🔹 Day 6: Grouping Data with `GROUP BY` & `HAVING` - GROUP BY groups rows based on a column. - HAVING filters groups (used instead of WHERE for aggregated data).

SELECT Grade, AVG(Fees_Paid) FROM Students GROUP BY Grade HAVING AVG(Fees_Paid) > 1000;

🔹 Finds grades where the average fees paid is greater than 1000. --- 🎯 Hands-On Practice: Solve These SQL Problems Problem 1: Find the Total Fees Paid by Each Grade

SELECT Grade, SUM(Fees_Paid) AS Total_Fees
FROM Students
GROUP BY Grade;

🔹 Expected Output: | Grade | Total_Fees | |-------|-----------| | A | 1900 | | B | 2300 | | C | NULL | Task: Run this query and check if your database returns the correct results. --- Problem 2: List Students Who Are 18 Years or Older

SELECT Name, Age FROM Students WHERE Age >= 18;

Expected Output: All students aged 18 or older. --- Problem 3: Count How Many Students Are in Each Grade

SELECT Grade, COUNT(*) AS Student_Count
FROM Students
GROUP BY Grade;

Expected Output: | Grade | Student_Count | |-------|--------------| | A | 2 | | B | 2 | | C | 1 | --- Problem 4: Get the Top 2 Youngest Students

SELECT Name, Age FROM Students ORDER BY Age ASC LIMIT 2;

Expected Output: The 2 youngest students. --- Problem 5: Find the Maximum Fees Paid by Any Student

SELECT MAX(Fees_Paid) AS Max_Fees FROM Students;

🔹 Expected Output: The highest fee paid. --- 🌍 Platforms to Practice SQL If you don’t have SQL installed, you can practice online for free on these platforms: | Platform | Features | |--------------|-------------| | [HackerRank](https://www.hackerrank.com/domains/sql) | Beginner to advanced SQL challenges | | [LeetCode SQL](https://leetcode.com/problemset/database/) | Real-world SQL interview questions | | [W3Schools SQL](https://www.w3schools.com/sql/trysql.asp?filename=trysql_select_all) | Interactive SQL editor | --- 📌 Your Tasks for Today ✅ Revise all SQL topics from Day 1 – Day 6. ✅ Write and run the 5 practice queries above. ✅ Solve at least 3 SQL challenges on HackerRank, LeetCode, or W3Schools. ✅ Comment "Done ✅" if you've completed today's tasks!

🔍 Summary ✅ GROUP BY → Groups rows by a column. ✅ Aggregate functions (SUM, AVG, COUNT, etc.) summarize each group. ✅ HAVING filters groups after aggregation. ✅ WHERE filters before grouping. Tomorrow, we will learn about SQL Joins (INNER JOIN, LEFT JOIN, etc.) to combine data from multiple tables! 🚀 💡 Like & Comment if you're ready for Day 7! 😊❤️

Day 6: SQL `GROUP BY` and `HAVING` – Grouping and Filtering Data Welcome to Day 6 of learning SQL! 🎉 Yesterday, we learned about aggregate functions (COUNT, SUM, AVG, MIN, MAX) to summarize data. But what if we want to apply these functions to different groups of data? 🤔 For example, instead of finding the total fees paid by all students, what if we want to find the total fees paid per grade? That’s where GROUP BY comes in! 🚀 --- 🔹 Why Do We Need `GROUP BY`? GROUP BY helps us group rows with the same values in a column and then apply aggregate functions to each group separately. 📖 Syntax:

SELECT column_name, aggregate_function(column_name)
FROM table_name
GROUP BY column_name;

- column_name → The column used for grouping. - aggregate_function(column_name) → Applies an aggregate function (e.g., SUM, AVG, COUNT, etc.) to each group. --- 📌 `GROUP BY` Example – Total Fees Paid Per Grade We will use the same Students table: | Student_ID | Name | Age | Grade | Fees_Paid | |------------|---------------|-----|-------|----------| | 1 | John Doe | 18 | A | 1000 | | 2 | Emma Smith | 19 | B | 1200 | | 3 | Alex Brown | 20 | A | 900 | | 4 | Sophia Johnson | 18 | C | NULL | | 5 | Liam Davis | 19 | B | 1100 | --- 🔸 Find the Total Fees Paid Per Grade

SELECT Grade, SUM(Fees_Paid) AS Total_Fees
FROM Students
GROUP BY Grade;

🔹 Output: | Grade | Total_Fees | |-------|-----------| | A | 1900 | | B | 2300 | | C | NULL | ✅ Explanation: - The query groups students by Grade. - Then it calculates the total fees paid for each grade using SUM(Fees_Paid). - Grade C shows NULL because Sophia hasn't paid fees (NULL values are ignored in SUM()). --- 🔸 Count the Number of Students in Each Grade

SELECT Grade, COUNT(*) AS Total_Students
FROM Students
GROUP BY Grade;

🔹 Output: | Grade | Total_Students | |-------|---------------| | A | 2 | | B | 2 | | C | 1 | ✅ Explanation: - COUNT(*) counts all students in each grade. - GROUP BY Grade groups them by grade before counting. --- 📌 `HAVING` Clause – Filtering Groups The WHERE clause cannot be used with aggregate functions because it filters individual rows before grouping. 🚨 Use `HAVING` to filter groups after aggregation! 📖 Syntax:

SELECT column_name, aggregate_function(column_name)
FROM table_name
GROUP BY column_name
HAVING condition;

--- 🔸 Find Grades Where Total Fees Paid is More Than 2000

SELECT Grade, SUM(Fees_Paid) AS Total_Fees
FROM Students
GROUP BY Grade
HAVING SUM(Fees_Paid) > 2000;

🔹 Output: | Grade | Total_Fees | |-------|-----------| | B | 2300 | ✅ Explanation: - The query groups students by Grade and calculates total fees per grade. - HAVING SUM(Fees_Paid) > 2000 filters only groups where the total fees paid exceeds 2000. - Grade A (1900) is excluded, but Grade B (2300) is included. --- 🔸 Find Grades with More Than 1 Student

SELECT Grade, COUNT(*) AS Total_Students
FROM Students
GROUP BY Grade
HAVING COUNT(*) > 1;

🔹 Output: | Grade | Total_Students | |-------|---------------| | A | 2 | | B | 2 | ✅ Explanation: - COUNT(*) counts the number of students per grade. - HAVING COUNT(*) > 1 filters out grades that have only 1 student (Grade C). --- 📌 `WHERE` vs. `HAVING` – Key Differences | Feature | WHERE | HAVING | |----------|--------|---------| | Filters individual rows before grouping | ✅ Yes | ❌ No | | Filters groups after aggregation | ❌ No | ✅ Yes | | Works with aggregate functions (SUM, COUNT, etc.) | ❌ No | ✅ Yes | --- 🎯 Task for Today ✅ Find the total fees paid per grade but only show grades where fees are above 2000. ✅ Count the number of students per age and show only ages where students are more than 1. ✅ Find the average fees per grade and show only grades where the average fee is above 1000. ---