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

Provides a default value for a column when no value is specified.
✔️ Helps in avoiding NULL values in columns where default values make sense.

✅ Example of DEFAULT Constraint

CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    CustomerName VARCHAR(50),
    OrderDate DATE DEFAULT CURRENT_DATE
);

✔️ If OrderDate is not provided, it automatically gets the current date.

✅ Inserting Data (Valid Cases)

INSERT INTO Orders (OrderID, CustomerName) VALUES (1, 'Alice'); -- ✅ OrderDate is set to today's date
INSERT INTO Orders (OrderID, CustomerName, OrderDate) VALUES (2, 'Bob', '2025-01-01'); -- ✅ Valid, custom date given

---

🔹 Summary of SQL Constraints

| Constraint | Ensures | Can Be NULL? | Allows Multiple in a Table? |
|--------------|---------------------------------|--------------|--------------------------|
| PRIMARY KEY | Uniqueness & Non-null values | ❌ No | ❌ No (Only One) |
| FOREIGN KEY | Referential Integrity | ✅ Yes (If NULL allowed) | ✅ Yes |
| UNIQUE | Uniqueness (without Primary Key) | ✅ Yes | ✅ Yes |
| CHECK | Enforces condition on values | ✅ Yes | ✅ Yes |
| DEFAULT | Provides a default value | ✅ Yes | ✅ Yes |

---

🔹 Your Task for Today
✅ Create a Students & Courses database using all the constraints.
✅ Try inserting valid & invalid values to see how constraints work.
✅ Comment below if you have any questions! 💬

---

🔹 What’s Next?
Tomorrow, we will learn Creating and Managing Indexes! Stay tuned! 🚀

💡 Like ❤️ & Share if you're enjoying this SQL series! 😊

#DataScience #DataScience #DataAnalytics

Day 24: Creating and Managing Indexes & Understanding Query Execution Plans 🚀

Welcome to Day 24 of your SQL learning journey! Today, we will dive into two crucial topics that help in improving database performance:

✔️ Indexes – How to create and manage them for faster queries.
✔️ Query Execution Plans – How SQL processes queries behind the scenes.

---

🔹 What is an Index in SQL?

An index is like a table of contents in a book. It helps SQL quickly find the required data instead of searching the entire table row by row.

👉 Without an index, SQL scans every row in the table (Full Table Scan).
👉 With an index, SQL jumps to the required rows faster, improving query performance.

---

🔹 Types of Indexes in SQL

There are 5 main types of indexes:

| Index Type | Description |
|--------------|----------------|
| Primary Index | Automatically created when a PRIMARY KEY is defined. |
| Unique Index | Ensures that values in a column are unique. |
| Clustered Index | Sorts and stores table data physically based on the index column. |
| Non-Clustered Index | Creates a separate structure for faster lookups, without changing the table's physical order. |
| Full-Text Index | Used for searching large text fields like documents, blogs, etc. |

---

🔹 How to Create an Index?
✅ 1. Creating a Simple Index

CREATE INDEX idx_customer_name ON Customers(Name);

✔️ This index helps SQL search for customer names faster.

✅ 2. Creating a Unique Index

CREATE UNIQUE INDEX idx_unique_email ON Employees(Email);

✔️ Ensures that Email values are unique and speeds up searches.

✅ 3. Creating a Composite Index (Multiple Columns)

CREATE INDEX idx_order ON Orders(CustomerID, OrderDate);

✔️ This speeds up searches involving CustomerID and OrderDate together.

---

🔹 How to Drop (Remove) an Index?
If an index is not improving performance, you can delete it:

DROP INDEX idx_customer_name ON Customers;

---

🔹 Clustered vs. Non-Clustered Index

| Feature | Clustered Index | Non-Clustered Index |
|------------|------------------|--------------------|
| Storage | Physically reorders table data | Creates a separate structure |
| Speed | Faster for retrieving ranges of data | Faster for searching specific values |
| Number per Table | Only one per table | Multiple allowed |

✅ Example: Clustered vs. Non-Clustered Index

CREATE CLUSTERED INDEX idx_emp_id ON Employees(EmployeeID); -- Clustered Index
CREATE NONCLUSTERED INDEX idx_emp_name ON Employees(Name); -- Non-Clustered Index

---

🔹 Understanding Query Execution Plans

A Query Execution Plan shows how SQL runs a query step by step.

🔹 Why Check Execution Plans?
✔️ Helps find slow-running queries.
✔️ Shows index usage (or missing indexes).
✔️ Suggests performance improvements.

✅ Viewing an Execution Plan in SQL Server

EXPLAIN ANALYZE
SELECT * FROM Customers WHERE Name = 'Alice';

✔️ This command displays the execution plan and shows if indexes are used.

---

🔹 How to Optimize Queries Using Indexes?

✅ Example of a Slow Query (Without Index)

SELECT * FROM Employees WHERE Name = 'John';

🔴 Problem: If Employees table has millions of records, SQL will scan the entire table, making it slow.

✅ Optimized Query Using Index

CREATE INDEX idx_emp_name ON Employees(Name);
SELECT * FROM Employees WHERE Name = 'John';

✔️ Now, SQL can directly use the index, making the search much faster!

---

🔹 When NOT to Use Indexes?

🔴 Indexes are powerful, but they should NOT be overused. Too many indexes can slow down INSERT, UPDATE, DELETE operations.

⚡️ Avoid indexes when:
❌ The table has very few records (scanning is faster than using an index).
❌ The column has many duplicate values (e.g., "Gender" with only 'Male' & 'Female').
❌ The table is frequently updated (indexes slow down modifications).

---

🔹 Summary of Today's Topics

| Concept | Key Takeaways |
|------------|-------------------|
| Indexes | Speed up search queries by organizing data efficiently. |
| Types of Indexes | Clustered, Non-Clustered, Unique, Composite, Full-Text. |
| Execution Plan | Helps analyze SQL performance and optimize queries. |
| Best Practices | Use indexes wisely to balance speed and performance. |

---

🔹 Your Task for Today

✅ Create a table and add indexes to test query speeds.
✅ Check the execution plan of queries before and after adding indexes.
✅ Drop an index and observe performance changes.

---

💡 What’s Next?
Tomorrow, we will learn SQL Backup and Restore Strategies and Role-Based Permissions. Stay tuned! 🚀

💬 Comment below if you have questions! Like ❤️ & Share if you're enjoying this SQL series! 😊

Day 25: Backup and Restore Strategies in SQL & Role-Based Permissions 🚀

Welcome to Day 25 of your SQL learning journey! Today, we’ll explore two important topics:

✔️ Backup and Restore Strategies – How to protect and recover your database.
✔️ Role-Based Permissions – How to manage user access in SQL.

---
🔹 Part 1: Backup and Restore Strategies in SQL

Imagine you are working on a critical database with customer information. What if:
🔴 The server crashes?
🔴 Someone accidentally deletes data?
🔴 A cyber-attack corrupts your data?

⚡️ Solution: Always take regular backups so you can restore data when needed!

---

🔹 Why Are Backups Important?
✔️ Data Protection – Prevents data loss due to failures.
✔️ Disaster Recovery – Restores data if something goes wrong.
✔️ Version Control – Helps retrieve past data when required.

---

🔹 Types of Backups in SQL

| Backup Type | Description |
|--------------|----------------|
| Full Backup | Copies the entire database (all tables, indexes, views, etc.). |
| Differential Backup | Saves only the changes made since the last full backup. |
| Transaction Log Backup | Captures all changes (INSERT, UPDATE, DELETE) since the last backup. |

---

🔹 How to Take a Backup in SQL?

✅ 1. Full Backup (Recommended for complete database protection)

BACKUP DATABASE MyDatabase  
TO DISK = 'C:\Backup\MyDatabase_Full.bak'
WITH FORMAT;

✔️ This saves the entire database as a .bak file.

✅ 2. Differential Backup (Stores only changes after the last full backup)

BACKUP DATABASE MyDatabase  
TO DISK = 'C:\Backup\MyDatabase_Diff.bak'
WITH DIFFERENTIAL;

✔️ Faster than full backup and reduces storage space.

✅ 3. Transaction Log Backup (Captures ongoing changes)

BACKUP LOG MyDatabase  
TO DISK = 'C:\Backup\MyDatabase_Log.bak';

✔️ Useful for point-in-time recovery.

---

🔹 How to Restore a Database from Backup?

✅ 1. Restore Full Backup

RESTORE DATABASE MyDatabase  
FROM DISK = 'C:\Backup\MyDatabase_Full.bak'
WITH REPLACE;

✔️ Restores the database to its last full backup state.

✅ 2. Restore Differential Backup (After Full Backup)

RESTORE DATABASE MyDatabase  
FROM DISK = 'C:\Backup\MyDatabase_Full.bak'  
WITH NORECOVERY;

RESTORE DATABASE MyDatabase  
FROM DISK = 'C:\Backup\MyDatabase_Diff.bak'  
WITH RECOVERY;

✔️ This applies the latest differential backup on top of the full backup.

✅ 3. Restore Transaction Log Backup (Point-in-Time Recovery)

RESTORE LOG MyDatabase  
FROM DISK = 'C:\Backup\MyDatabase_Log.bak'  
WITH RECOVERY;

✔️ This restores the latest transactions after a backup.

---

🔹 Best Practices for SQL Backups

✔️ Schedule backups regularly (Daily full, hourly differential, frequent transaction logs).
✔️ Store backups in multiple locations (Cloud, external drives, etc.).
✔️ Automate backups using SQL Jobs to prevent manual errors.
✔️ Test restore process regularly to ensure recovery works correctly.

---

🔹 Part 2: Role-Based Permissions in SQL

In a real-world database, not everyone should have the same level of access.

👨‍💼 Admins should have full access.
👩‍💻 Developers may need read and write access.
📊 Analysts may only need read access.

⚡️ Solution: SQL Role-Based Access Control (RBAC) allows assigning permissions based on user roles.

---

🔹 Common SQL Roles and Permissions

| Role | Description |
|---------|---------------|
| Admin | Full control over the database (CREATE, DELETE, UPDATE). |
| Developer | Can INSERT, UPDATE, DELETE data but not modify structure. |
| Analyst | Read-only access (SELECT). |
| Guest | Limited access to specific tables. |

---

🔹 How to Create a New User in SQL?

✅ 1. Creating a User

CREATE LOGIN dev_user WITH PASSWORD = 'StrongPassword123';
CREATE USER dev_user FOR LOGIN dev_user;

✔️ This creates a new SQL user.

---

🔹 Granting Permissions to Users

✅ 2. Grant Read-Only Access

GRANT SELECT ON Customers TO dev_user;

✔️ The user can only read data but not modify it.

✅ 3. Grant Read & Write Access

GRANT SELECT, INSERT, UPDATE, DELETE ON Customers TO dev_user;

✔️ Now, the user can modify data but cannot delete tables.

✅ 4. Grant Full Control

GRANT ALL PRIVILEGES ON Customers TO dev_user;

✔️ The user has full access to the table.

---

🔹 Revoking Permissions

If a user no longer needs access, you can revoke their permissions.

✅ 5. Revoke Read Access

REVOKE SELECT ON Customers FROM dev_user;

✔️ Now, the user cannot view customer data.

✅ 6. Remove a User

DROP USER dev_user;
DROP LOGIN dev_user;

✔️ This completely removes the user from the database.

---

🔹 Best Practices for Role-Based Permissions

✔️ Follow the Principle of Least Privilege (PoLP) – Give users only the necessary access.
✔️ Use predefined roles like db_owner, db_datareader, and db_datawriter in SQL Server.
✔️ Regularly review user access to ensure security.
✔️ Use stored procedures instead of granting direct access to tables.

---

🔹 Summary of Today's Topics

| Concept | Key Takeaways |
|------------|-------------------|
| Backups | Protect data from accidental loss and corruption. |
| Restore | Recovers database from full, differential, and transaction log backups. |
| User Permissions | Control database access for different users. |
| Best Practices | Automate backups, store in multiple locations, follow least privilege for security. |

---

🔹 Your Task for Today

✅ Take a full backup of a sample database.
✅ Restore the database from the backup.
✅ Create a new user and assign permissions.
✅ Practice granting and revoking permissions for better security.

---

💡 What’s Next?
Tomorrow, we will learn Pivoting & Unpivoting Data and Working with JSON & XML in SQL. Stay tuned! 🚀

💬 Comment below if you have questions! Like ❤️ & Share if you're enjoying this SQL series! 😊

Day 26: Pivoting and Unpivoting Data & Working with JSON and XML in SQL 🚀

Welcome to Day 26 of your SQL learning journey! Today, we will cover two important advanced topics:

✔️ Pivoting and Unpivoting Data – How to transform rows into columns and vice versa.
✔️ Working with JSON and XML in SQL – How to store, query, and manipulate structured data formats.

---
🔹 Part 1: Pivoting and Unpivoting Data in SQL

In many real-world scenarios, we need to reshape data for better analysis and reporting.

👉 Pivoting: Converting rows into columns (Summarizing data in a more readable format).
👉 Unpivoting: Converting columns back into rows (Making data easier to process).

---

🔹 Understanding Pivoting with an Example

Imagine we have a Sales Table like this:

| SalesPerson | Month | SalesAmount |
|------------|--------|------------|
| John | Jan | 1000 |
| John | Feb | 1200 |
| Jane | Jan | 1500 |
| Jane | Feb | 1300 |

Goal: Convert it into this format using PIVOT:

| SalesPerson | Jan | Feb |
|------------|------|------|
| John | 1000 | 1200 |
| Jane | 1500 | 1300 |

---

✅ How to Use PIVOT in SQL?

SELECT SalesPerson, [Jan], [Feb]
FROM 
(
    SELECT SalesPerson, Month, SalesAmount  
    FROM Sales
) AS SourceTable  
PIVOT  
(
    SUM(SalesAmount)  
    FOR Month IN ([Jan], [Feb])  
) AS PivotTable;

🔹 Explanation:
✔️ SourceTable – Selects the raw data.
✔️ PIVOT – Converts rows into columns.
✔️ SUM(SalesAmount) – Aggregates values per salesperson.
✔️ FOR Month IN ([Jan], [Feb]) – Defines new columns.

---

🔹 Understanding Unpivoting with an Example

Now, let’s take our pivoted table and transform it back to rows.

| SalesPerson | Jan | Feb |
|------------|------|------|
| John | 1000 | 1200 |
| Jane | 1500 | 1300 |

Goal: Convert it into this format using UNPIVOT:

| SalesPerson | Month | SalesAmount |
|------------|-------|------------|
| John | Jan | 1000 |
| John | Feb | 1200 |
| Jane | Jan | 1500 |
| Jane | Feb | 1300 |

---

✅ How to Use UNPIVOT in SQL?

SELECT SalesPerson, Month, SalesAmount  
FROM  
(
    SELECT SalesPerson, [Jan], [Feb]  
    FROM SalesPivotTable
) AS PivotedTable  
UNPIVOT  
(
    SalesAmount  
    FOR Month IN ([Jan], [Feb])  
) AS UnpivotTable;

🔹 Explanation:
✔️ PivotedTable – Selects the table with columns that need to be transformed.
✔️ UNPIVOT – Converts columns back into rows.
✔️ FOR Month IN ([Jan], [Feb]) – Specifies which columns to unpivot.

---

🔹 When to Use Pivot and Unpivot?

| Scenario | Use |
|-------------|--------|
| Need to create summary reports | PIVOT |
| Need to normalize data for processing | UNPIVOT |

---

🔹 Part 2: Working with JSON and XML in SQL

Modern applications often store data in JSON (JavaScript Object Notation) and XML (Extensible Markup Language). SQL supports querying and manipulating both formats.

---

🔹 Working with JSON in SQL

✅ Storing JSON in SQL

SQL Server provides the NVARCHAR data type to store JSON.

CREATE TABLE Customers (
    ID INT PRIMARY KEY,
    Name NVARCHAR(50),
    OrderDetails NVARCHAR(MAX) -- Stores JSON data
);

Now, insert JSON data into the table:

INSERT INTO Customers (ID, Name, OrderDetails)
VALUES (1, 'John', '{"Product": "Laptop", "Price": 1000, "Quantity": 2}');

---

🔹 Querying JSON Data

To retrieve JSON fields, use the JSON_VALUE() function:

SELECT Name, JSON_VALUE(OrderDetails, '$.Product') AS Product
FROM Customers;

✔️ $.Product extracts the Product value from JSON.

---

🔹 Parsing Complex JSON with OPENJSON

If JSON contains nested arrays, OPENJSON helps extract data into tabular format.

SELECT *
FROM OPENJSON('[
    {"Product": "Laptop", "Price": 1000},
    {"Product": "Phone", "Price": 500}
]')  
WITH (Product NVARCHAR(50), Price INT);

✔️ Converts JSON into rows and columns.

---

🔹 Working with XML in SQL

Similar to JSON, we can store and query XML data in SQL.

✅ Storing XML Data

CREATE TABLE Orders (
    ID INT PRIMARY KEY,
    OrderDetails XML
);

Now, insert XML data:

INSERT INTO Orders (ID, OrderDetails)
VALUES (1, '<Order><Product>Laptop</Product><Price>1000</Price></Order>');

---

🔹 Querying XML Data

To extract XML values, use the .value() function:

SELECT OrderDetails.value('(/Order/Product)[1]', 'NVARCHAR(50)') AS Product  
FROM Orders;

✔️ Extracts the Product name from XML.

---

## 🔹 Converting Table Data to JSON & XML

✅ Convert Table to JSON

SELECT ID, Name FROM Customers  
FOR JSON AUTO;

✔️ Converts table rows into JSON format.

---

✅ Convert Table to XML

SELECT ID, Name FROM Customers  
FOR XML AUTO;

✔️ Converts table rows into XML format.

---

🔹 Summary of Today's Topics

| Concept | Key Takeaways |
|------------|-------------------|
| PIVOT | Converts rows into columns for summary reports. |
| UNPIVOT | Converts columns back into rows for data normalization. |
| JSON in SQL | Stores and queries structured data in JSON format. |
| XML in SQL | Stores and retrieves hierarchical data using XML. |

---

🔹 Your Task for Today

✅ Practice pivoting and unpivoting a sample dataset.
✅ Store and query JSON data in a SQL table.
✅ Store and query XML data in a SQL table.

---

💡 What’s Next?
Tomorrow, we will learn Stored Procedures, Functions, and Triggers in SQL. Stay tuned! 🚀

💬 Comment below if you have questions! Like ❤️ & Share if you're enjoying this SQL series! 😊

Day 27: Writing Stored Procedures and Functions & Automating Processes with Triggers 🚀

Welcome to Day 27 of your SQL journey! Today, we will learn:

✔️ Stored Procedures – Predefined SQL code that can be executed multiple times.
✔️ Functions – SQL code that returns a single value or table.
✔️ Triggers – Automated actions that execute when a certain event occurs.

Let’s break these down step by step! 👇

---

🔹 Part 1: Writing Stored Procedures

✅ What is a Stored Procedure?

A Stored Procedure is a set of SQL statements stored in the database that can be executed whenever needed.

🔹 Why Use Stored Procedures?
✔️ Reusability – Write once, use multiple times.
✔️ Security – Prevent SQL injection.
✔️ Performance – Optimized query execution.

---

🔹 Creating a Simple Stored Procedure

Let’s create a stored procedure to retrieve all customers from a Customers table.

CREATE PROCEDURE GetAllCustomers  
AS  
BEGIN  
    SELECT * FROM Customers;  
END;

🔹 How to Execute a Stored Procedure?

EXEC GetAllCustomers;

✔️ The procedure fetches all customer records when executed.

---

🔹 Stored Procedure with Parameters

Let’s create a procedure to get customers from a specific country.

CREATE PROCEDURE GetCustomersByCountry  
@Country NVARCHAR(50)  
AS  
BEGIN  
    SELECT * FROM Customers WHERE Country = @Country;  
END;

🔹 Execute with a Parameter

EXEC GetCustomersByCountry 'USA';

✔️ This retrieves all customers from the USA.

---

🔹 Stored Procedure with Output Parameter

Stored procedures can return values using output parameters.

CREATE PROCEDURE GetTotalCustomers  
@Total INT OUTPUT  
AS  
BEGIN  
    SELECT @Total = COUNT(*) FROM Customers;  
END;

🔹 Execute and Get the Output

DECLARE @TotalCustomers INT;
EXEC GetTotalCustomers @TotalCustomers OUTPUT;
PRINT @TotalCustomers;

✔️ This counts and prints the total number of customers.

---

🔹 Part 2: Writing Functions in SQL

✅ What is a Function?

A Function in SQL is a reusable block of code that returns a value.

🔹 Types of Functions in SQL:
1️⃣ Scalar Functions – Return a single value.
2️⃣ Table-Valued Functions – Return a table.

---

🔹 Creating a Scalar Function

Let’s create a function that calculates the total price after tax for a given price.

CREATE FUNCTION CalculateTax(@Price DECIMAL(10,2))  
RETURNS DECIMAL(10,2)  
AS  
BEGIN  
    RETURN @Price * 1.10;  -- Adding 10% tax  
END;

🔹 Using the Function

SELECT dbo.CalculateTax(100) AS PriceWithTax;

✔️ If the input is 100, the output will be 110.

---

🔹 Creating a Table-Valued Function

Let’s create a function that returns customers from a given country.

CREATE FUNCTION GetCustomersFromCountry(@Country NVARCHAR(50))  
RETURNS TABLE  
AS  
RETURN  
(
    SELECT * FROM Customers WHERE Country = @Country  
);

🔹 Using the Function

SELECT * FROM dbo.GetCustomersFromCountry('USA');

✔️ This retrieves all USA customers in a tabular format.

---

🔹 Part 3: Automating Processes with Triggers

✅ What is a Trigger?

A Trigger is a special type of stored procedure that executes automatically when a specific action occurs in the database.

🔹 Why Use Triggers?
✔️ Enforce Business Rules – Prevent invalid data entry.
✔️ Maintain Audit Logs – Track changes automatically.
✔️ Automate Actions – Example: Send a notification when a new record is inserted.

---

🔹 Types of Triggers in SQL

1️⃣ AFTER Triggers – Execute after an INSERT, UPDATE, or DELETE operation.
2️⃣ INSTEAD OF Triggers – Replace an operation with custom logic.

---

🔹 Creating an AFTER INSERT Trigger

Let’s create a trigger that logs new customer entries in an audit table.

CREATE TABLE CustomerLog (
    LogID INT IDENTITY PRIMARY KEY,
    CustomerID INT,
    ActionTaken NVARCHAR(50),
    ActionDate DATETIME DEFAULT GETDATE()
);

Now, let’s create the trigger:

CREATE TRIGGER trg_AfterCustomerInsert  
ON Customers  
AFTER INSERT  
AS  
BEGIN  
    INSERT INTO CustomerLog (CustomerID, ActionTaken)  
    SELECT ID, 'Inserted' FROM INSERTED;  
END;

🔹 How it Works?
✔️ When a new customer is added, an entry is automatically made in CustomerLog.

---

🔹 Creating an AFTER UPDATE Trigger

Let’s create a trigger to track salary changes in an Employees table.

CREATE TRIGGER trg_AfterSalaryUpdate  
ON Employees  
AFTER UPDATE  
AS  
BEGIN  
    IF UPDATE(Salary)  
    BEGIN  
        INSERT INTO SalaryAudit (EmployeeID, OldSalary, NewSalary, ChangeDate)  
        SELECT i.ID, d.Salary, i.Salary, GETDATE()  
        FROM INSERTED i  
        JOIN DELETED d ON i.ID = d.ID;  
    END  
END;

✔️ INSERTED Table – Holds new data after an update.
✔️ DELETED Table – Holds old data before the update.

---

🔹 Creating an INSTEAD OF DELETE Trigger

Let’s prevent accidental deletion of employees by marking them as "Inactive" instead of deleting.

CREATE TRIGGER trg_InsteadOfDelete  
ON Employees  
INSTEAD OF DELETE  
AS  
BEGIN  
    UPDATE Employees  
    SET IsActive = 0  
    WHERE ID IN (SELECT ID FROM DELETED);  
END;

✔️ Now, when someone tries to delete an employee, they are just marked as inactive instead.

---

🔹 Summary of Today’s Topics

| Concept | Key Takeaways |
|------------|-------------------|
| Stored Procedures | Predefined SQL queries that execute on demand. |
| Functions | Return a single value (scalar) or table (table-valued). |
| Triggers | Execute automatically when an INSERT, UPDATE, or DELETE happens. |

---

🔹 Your Task for Today

✅ Create a stored procedure to get customer orders.
✅ Write a function to calculate discounts.
✅ Create a trigger to track changes in a table.

---

💡 What’s Next?
Tomorrow, we will explore Integrating SQL with Python, Power BI, and Tableau & SQL in Big Data (NoSQL). 🚀

💬 Comment below if you have questions! Like ❤️ & Share if you're enjoying this SQL series! 😊

Day 28: Integrating SQL with Other Tools & SQL in Big Data (NoSQL) 🚀

Welcome to Day 28 of your SQL journey! Today, we will cover:

✔️ How SQL integrates with other tools like Python, Power BI, and Tableau.
✔️ SQL in Big Data – Introduction to NoSQL databases.

Let’s break everything down step by step! 👇

---

🔹 Part 1: Integrating SQL with Other Tools

SQL is often used in combination with other tools for data analysis, reporting, and visualization.

🔹 Why Integrate SQL with Other Tools?
✔️ Automate Data Extraction 📥
✔️ Analyze Data in Python 🐍
✔️ Visualize Data in Power BI & Tableau 📊

---

🔹 SQL with Python

Python is widely used for data analysis, machine learning, and automation. To connect Python with SQL, we use libraries like:
✔️ sqlite3 – For SQLite databases
✔️ pyodbc – For MS SQL Server
✔️ mysql-connector-python – For MySQL
✔️ psycopg2 – For PostgreSQL

🔹 Connecting Python to SQL (Example: MySQL)

1️⃣ Install MySQL Connector

pip install mysql-connector-python

2️⃣ Connect Python to MySQL Database

import mysql.connector  

# Connect to database
conn = mysql.connector.connect(
    host="localhost",
    user="root",
    password="password",
    database="company"
)

cursor = conn.cursor()

# Execute a query
cursor.execute("SELECT * FROM Employees")

# Fetch and display data
for row in cursor.fetchall():
    print(row)

# Close the connection
conn.close()

✔️ This connects Python to MySQL and fetches employee data.

---

🔹 SQL with Power BI

Power BI is a powerful tool for data visualization and business intelligence.

### 🔹 Steps to Connect SQL with Power BI
1️⃣ Open Power BI
2️⃣ Click on “Get Data” → Select “SQL Server”
3️⃣ Enter Server Name & Database Name
4️⃣ Load the Data and start creating reports!

---

🔹 SQL with Tableau

Tableau is another great tool for data visualization.

🔹 Steps to Connect SQL with Tableau
1️⃣ Open Tableau
2️⃣ Click on “Connect” → Choose your SQL Database
3️⃣ Enter Server Credentials & Connect
4️⃣ Drag & Drop Tables to build interactive reports!

---

🔹 Part 2: SQL in Big Data & Introduction to NoSQL

🔹 What is Big Data?

Big Data refers to huge volumes of structured and unstructured data that traditional SQL databases cannot handle efficiently.

🔹 SQL vs NoSQL

| Feature | SQL (Relational DB) | NoSQL (Non-Relational DB) |
|---------|------------------|----------------------|
| Data Structure | Tables (Rows & Columns) | Documents, Key-Value, Graphs, etc. |
| Schema | Fixed Schema | Flexible Schema |
| Scalability | Vertical Scaling | Horizontal Scaling |
| Transactions | Follows ACID | BASE (Eventual Consistency) |
| Example DBs | MySQL, PostgreSQL, SQL Server | MongoDB, Firebase, Cassandra |

---

🔹 NoSQL Databases Overview

There are four types of NoSQL databases:

1️⃣ Document Databases (MongoDB, CouchDB) – Store data as JSON-like documents.
2️⃣ Key-Value Stores (Redis, DynamoDB) – Store data as key-value pairs.
3️⃣ Column-Family Stores (Cassandra, HBase) – Store data in columns instead of rows.
4️⃣ Graph Databases (Neo4j) – Store relationships between data nodes.

---

🔹 SQL vs NoSQL: When to Use What?

✔️ Use SQL when:
- Your data has a structured format.
- You need ACID transactions (Banking, ERP).

✔️ Use NoSQL when:
- Your data is unstructured (JSON, images, logs).
- You need high-speed scaling (Social Media, IoT).

---

🔹 Summary of Today’s Topics

| Concept | Key Takeaways |
|------------|-------------------|
| SQL with Python | Automates data analysis and machine learning. |
| SQL with Power BI & Tableau | Helps create business reports and dashboards. |
| NoSQL Databases | Handle Big Data and flexible schema structures. |
| SQL vs NoSQL | SQL is structured; NoSQL is flexible and scalable. |

---

🔹 Your Task for Today

✅ Connect SQL with Python & Fetch Data
✅ Load SQL Data in Power BI or Tableau
✅ Explore NoSQL by installing MongoDB & running basic queries

---

💡 What’s Next?
Tomorrow, we will focus on Query Performance Tuning & SQL Optimization Techniques! 🚀

💬 Comment below if you have questions! Like ❤️ & Share if you're enjoying this SQL series! 😊