1. SQL Basics

   What is SQL?

   SQL (Structured Query Language) is the standard language used to interact with relational databases. It allows you to create, read, update, and delete data (CRUD operations) efficiently.

   Key points:

   • SQL is declarative, meaning you tell the database what you want, not how to get it.

   • It works with tables that store data in rows and columns.

Difference between SQL, MySQL, PostgreSQL, etc.

Think of SQL as the language and MySQL/PostgreSQL as the software that understands this language.

Database vs Table vs Schema

• Database: A container for storing all your data (like a filing cabinet).

• Table: A structured collection of related data inside a database (like a drawer in the cabinet).

• Schema: A way to logically group tables and objects inside a database (like folders inside the drawer).

Example:

    -- Database creation
    CREATE DATABASE SchoolDB;

    -- Schema creation
    CREATE SCHEMA StudentSchema;

    -- Table creation
    CREATE TABLE StudentSchema.Students (
        StudentID INT PRIMARY KEY,
        Name VARCHAR(50),
        Age INT
    );

Case Sensitivity in SQL

• SQL keywords (SELECT, FROM, WHERE) are not case sensitive.

• Table and column names may be case sensitive depending on the database system.

  • MySQL: Case-sensitive on Linux.

  • PostgreSQL: Always case-sensitive if quoted.

Basic SQL Syntax and Structure

A typical SQL query has this structure:

    SELECT column1, column2
    FROM table_name
    WHERE condition
    ORDER BY column1;

• SELECT → Specifies which columns to retrieve.

• FROM → Specifies the table.

• WHERE → Filters rows based on conditions.

• ORDER BY → Sorts the results.

Data Types

NULL Values and Handling NULLs

• NULL represents missing or unknown data.

• Use IS NULL or IS NOT NULL to check for NULLs.

• COALESCE can replace NULL with a default value.

Example:

    -- Check for NULLs
    SELECT Name FROM Students WHERE Age IS NULL;

    -- Replace NULL with a default
    SELECT Name, COALESCE(Age, 18) AS Age FROM Students;

---

2. SQL Commands

   1. DDL – Data Definition Language: Defines or alters database structures.

      • CREATE, ALTER, DROP

   2. DML – Data Manipulation Language: Manipulates the data inside tables.

      • INSERT, UPDATE, DELETE

   3. DCL – Data Control Language: Manages permissions.

      • GRANT, REVOKE

   4. TCL – Transaction Control Language: Controls transactions.

      • COMMIT, ROLLBACK, SAVEPOINT

Data Constraints

Constraints ensure data integrity:

Filtering and Sorting

• WHERE clause

• Comparison operators (=, >, <, <>)

• Logical operators (AND, OR, NOT)

• BETWEEN, IN, LIKE, IS NULL

• ORDER BY (ASC, DESC)

• DISTINCT keyword

• LIMIT / TOP

---

3. SQL Joins – Combining Data from Multiple Tables

   What are Joins?

   Joins allow you to combine rows from two or more tables based on a related column. They are fundamental when working with relational databases because data is often stored across multiple tables.

   

   Types of Joins

   1. INNER JOIN

   Returns rows where matching values exist in both tables.

    SELECT Students.Name, Classes.ClassName
    FROM Students
    INNER JOIN Classes
    ON Students.ClassID = Classes.ClassID;
   

   Only students with a valid ClassID in Classes table are returned.

   2. LEFT JOIN (LEFT OUTER JOIN)

   Returns all rows from the left table and matching rows from the right table. If no match, NULL is shown.

    SELECT Students.Name, Classes.ClassName
    FROM Students
    LEFT JOIN Classes
    ON Students.ClassID = Classes.ClassID;
   

   Returns all students, even if they don’t belong to any class.

   3. RIGHT JOIN (RIGHT OUTER JOIN)

   Returns all rows from the right table and matching rows from the left table.

    SELECT Students.Name, Classes.ClassName
    FROM Students
    RIGHT JOIN Classes
    ON Students.ClassID = Classes.ClassID;
   

   Returns all classes, even if no student is assigned.

   4. FULL OUTER JOIN

   Returns all rows from both tables; unmatched rows have NULLs.

    SELECT Students.Name, Classes.ClassName
    FROM Students
    FULL OUTER JOIN Classes
    ON Students.ClassID = Classes.ClassID;
   

   Combines LEFT JOIN + RIGHT JOIN.

   5. CROSS JOIN

   Returns Cartesian product of two tables (every combination of rows).

    SELECT Students.Name, Classes.ClassName
    FROM Students
    CROSS JOIN Classes;
   

   Use carefully; the result grows quickly with table size.

   6. SELF JOIN

   A table is joined with itself, often using aliases.

    SELECT A.Name AS Employee, B.Name AS Manager
    FROM Employees A
    INNER JOIN Employees B
    ON A.ManagerID = B.EmployeeID;
   

   ---

   Join Conditions and Aliasing (AS)

   • Use ON to specify join conditions.

   • Aliases (AS) make queries shorter and more readable.

    SELECT S.Name AS StudentName, C.ClassName AS ClassTitle
    FROM Students AS S
    INNER JOIN Classes AS C
    ON S.ClassID = C.ClassID;

Joining More Than 2 Tables

You can chain multiple joins:

    SELECT S.Name, C.ClassName, T.TeacherName
    FROM Students S
    INNER JOIN Classes C ON S.ClassID = C.ClassID
    INNER JOIN Teachers T ON C.TeacherID = T.TeacherID;

Each join adds another table to the query, enabling complex data analysis.

---

4. SQL Aggregate Functions & Grouping

   AGGREGATE FUNCTIONS

   Used to calculate a single value from multiple rows

   • COUNT(): Counts the number of rows.

     • Example: SELECT COUNT(*) FROM Students;

   • SUM(): Sums up the values.

     • Example: SELECT SUM(Salary) FROM Employees;

   • AVG(): Calculates the average value.

     • Example: SELECT AVG(Age) FROM Students;

   • MIN(): Finds the minimum value.

     • Example: SELECT MIN(Salary) FROM Employees;

   • MAX(): Finds the maximum value.

     • Example: SELECT MAX(Age) FROM Students;

---

GROUP BY

Groups rows based on a column before applying aggregates.

    SELECT ClassID, COUNT(*) AS StudentCount
    FROM Students
    GROUP BY ClassID;

Counts the number of students in each class.

HAVING Clause

Filters groups (unlike WHERE, which filters rows).

    SELECT ClassID, COUNT(*) AS StudentCount
    FROM Students
    GROUP BY ClassID
    HAVING COUNT(*) > 10;

Shows only classes with more than 10 students.

---

Using Aggregates with Filters

Combine WHERE and GROUP BY for targeted analysis:

    SELECT ClassID, AVG(Age) AS AvgAge
    FROM Students
    WHERE Age > 18
    GROUP BY ClassID
    HAVING AVG(Age) < 25;

Calculates average age for students above 18, but only for classes where the average is below 25.

---

5. SQL Subqueries – Nested Queries Explained

   What is a Subquery?

   A subquery is a query inside another query. It allows you to use the result of one query as input for another, making complex queries easier to write.

   Use Cases:

   • Filtering data dynamically

   • Calculating aggregates before the main query

   • Comparing data between tables

---

IN, ANY, ALL, EXISTS with Subqueries

→ IN

Checks if a value exists in a set returned by a subquery.

    SELECT Name
    FROM Students
    WHERE ClassID IN (SELECT ClassID FROM Classes WHERE ClassName LIKE 'Math%');

→ ANY / SOME

Compares a value to any value in a subquery.

    SELECT Name, Age
    FROM Students
    WHERE Age > ANY (SELECT Age FROM Students WHERE ClassID = 1);

→ ALL

Compares a value to all values returned by a subquery.

    SELECT Name, Age
    FROM Students
    WHERE Age > ALL (SELECT Age FROM Students WHERE ClassID = 1);

→ EXISTS

Checks if a subquery returns at least one row.

    SELECT Name
    FROM Students S
    WHERE EXISTS (SELECT 1 FROM Classes C WHERE C.ClassID = S.ClassID AND C.ClassName = 'Math');

---

Correlated Subqueries

A correlated subquery refers to the outer query and is evaluated row by row.

    SELECT Name, Age
    FROM Students S
    WHERE Age > (SELECT AVG(Age) FROM Students WHERE ClassID = S.ClassID);

Calculates the average age per class dynamically.

---

Using Subqueries in SELECT, FROM, WHERE, HAVING

• SELECT: To calculate derived columns

    SELECT Name, (SELECT COUNT(*) FROM Classes WHERE TeacherID = Students.TeacherID) AS NumClasses
    FROM Students;

• FROM: Treat subquery as a temporary table

    SELECT AvgAgePerClass.ClassID, AvgAgePerClass.AvgAge
    FROM (SELECT ClassID, AVG(Age) AS AvgAge FROM Students GROUP BY ClassID) AS AvgAgePerClass;

• WHERE / HAVING: Filter results based on subquery results (examples shown above)

---

6. SQL Set Operators

   → UNION vs UNION ALL

   • UNION: Combines results of two queries and removes duplicates

   • UNION ALL: Combines results including duplicates

    SELECT Name FROM Students WHERE ClassID = 1
    UNION
    SELECT Name FROM Students WHERE ClassID = 2;

→ INTERSECT

Returns only rows common to both queries.

    SELECT Name FROM Students WHERE ClassID = 1
    INTERSECT
    SELECT Name FROM Students WHERE Age > 18;

→ EXCEPT / MINUS

Returns rows from the first query not present in the second.

    -- SQL Server / PostgreSQL
    SELECT Name FROM Students WHERE ClassID = 1
    EXCEPT
    SELECT Name FROM Students WHERE Age < 18;

    -- Oracle uses MINUS instead of EXCEPT

Rules for Combining Result Sets

1. Queries must have same number of columns.

2. Data types must be compatible.

3. Column names from the first query are used in the final result.

---

7. SQL Views – Simplifying Data Access

   What is a View?

   A view is like a virtual table based on the result of a query.

   • It does not store data physically (except for materialized views).

   • It lets you reuse queries easily and present data in a simpler way.

Example:

    -- Create a view for active students
    CREATE VIEW ActiveStudents AS
    SELECT Name, Age, ClassID
    FROM Students
    WHERE Status = 'Active';

    -- Query the view
    SELECT * FROM ActiveStudents;

Every time you query ActiveStudents, the database runs the underlying query on the Students table.

Advantages of Using Views

1. Simplify Complex Queries: Use a single view instead of repeating a complex query.

2. Security: Restrict access to certain columns or rows.

3. Abstraction: Hides the underlying table structure from users.

---

Types of Views

1. Regular (Standard) View

• Virtual table that calculates results every time you query it.

• Best for simple queries or security purposes.

Example:

    CREATE VIEW StudentNames AS
    SELECT Name, Age
    FROM Students;

2. Materialized View

• Stores the query result physically on disk.

• Speeds up queries for large datasets or heavy calculations.

• Needs refreshing to reflect updated data.

Example:

    CREATE MATERIALIZED VIEW ProductSales AS
    SELECT ProductID, SUM(SalesAmount) AS TotalSales
    FROM Sales
    GROUP BY ProductID;

    -- Query
    SELECT * FROM ProductSales;

    -- Refresh to update
    REFRESH MATERIALIZED VIEW ProductSales;

3. Updatable View

• Allows INSERT, UPDATE, DELETE operations through the view.

• Works only for simple views without joins or aggregations.

Example:

    CREATE VIEW StudentInfo AS
    SELECT Name, Age
    FROM Students;

    -- Update through the view
    UPDATE StudentInfo
    SET Age = 20
    WHERE Name = 'Abhishek';

4. Read-Only View

• Cannot be updated.

• Useful for reporting or restricting access.

5. Join View

• Combines data from multiple tables for easier querying.

Example:

    CREATE VIEW StudentClass AS
    SELECT S.Name, C.ClassName
    FROM Students S
    INNER JOIN Classes C ON S.ClassID = C.ClassID;

6. Aggregate View

• Precomputes SUM, AVG, COUNT, etc., to simplify reporting queries.

Example:

    CREATE VIEW AvgAgePerClass AS
    SELECT ClassID, AVG(Age) AS AvgAge
    FROM Students
    GROUP BY ClassID;

---

Key Differences Between Views

---

Summary

• Views are a powerful tool to simplify queries, improve security, and organize data.

• Regular Views: Virtual, recalculated every time.

• Materialized Views: Physical, precomputed, high performance.

• Updatable Views: Can modify data.

---

8. SQL Indexes – Speeding Up Queries

   When working with large databases, queries can get slow. Imagine trying to find a name in a huge phone book without the index—it would take forever! That’s exactly what happens in databases without indexes.

   An SQL index is like the index in a book: it helps the database find data quickly without scanning every row.

   Why Use Indexes?

   • Faster SELECT queries

   • Efficient searching, filtering, and sorting

   • Speeds up JOIN operations

     But remember: Indexes take extra storage and slow down data updates (INSERT, UPDATE, DELETE) because the index also needs to be updated.

Creating an Index

    -- Create an index on the Name column of Students table
    CREATE INDEX idx_student_name ON Students(Name);

• idx_student_name → name of the index

• Students(Name) → table and column to index

Dropping an Index

    DROP INDEX idx_student_name;

• Removes the index when it’s no longer needed

---

Clustered vs Non-clustered Index

Think of clustered index as organizing the actual pages in order, and non-clustered as a list pointing to data.

---

Composite Index

• Index on more than one column for queries filtering multiple columns

    CREATE INDEX idx_class_age ON Students(ClassID, Age);

Helps when searching by ClassID and Age together.

---

9. SQL Constraints (Deep Dive) — Keeping Your Data Clean and Reliable

   When working with databases, it’s not enough just to store data — you must ensure that data is accurate, consistent, and meaningful.
   That’s where constraints come in.

   Think of constraints as the rules and boundaries that maintain data integrity in a database table.

   What Are Constraints in SQL?

   A constraint is a rule enforced on a column or table to ensure valid data.
   They prevent invalid data from entering the database.

   For example:

   • You can’t insert a student record without a roll number.

   • You can’t have two employees with the same email.

   • You can’t delete a department if employees still belong to it.

Types of Constraints

Implementing Constraints

1. Adding Constraints When Creating a Table

    CREATE TABLE Employees (
        EmpID INT PRIMARY KEY,
        Name VARCHAR(100) NOT NULL,
        Email VARCHAR(100) UNIQUE,
        Salary DECIMAL(10,2) CHECK (Salary > 0),
        DeptID INT,
        FOREIGN KEY (DeptID) REFERENCES Departments(DeptID)
    );

2. Adding Constraints to an Existing Table

    ALTER TABLE Employees
    ADD CONSTRAINT fk_dept FOREIGN KEY (DeptID)
    REFERENCES Departments(DeptID);

3. Dropping Constraints

    ALTER TABLE Employees
    DROP CONSTRAINT fk_dept;

PRIMARY KEY vs UNIQUE Constraint

Example:

    CREATE TABLE Students (
        StudentID INT PRIMARY KEY,
        Email VARCHAR(100) UNIQUE
    );

Here, StudentID uniquely identifies a student, while Email must also be unique but can exist independently of the primary key.

---

Cascading Rules — Maintaining Relationships

When you use a FOREIGN KEY, SQL provides cascading actions to control what happens in related tables when data changes.

1. ON DELETE CASCADE

If a parent record is deleted, child records are automatically deleted.

    CREATE TABLE Orders (
        OrderID INT PRIMARY KEY,
        CustomerID INT,
        FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
        ON DELETE CASCADE
    );

If a customer is deleted, all their orders will be deleted too.

2. ON UPDATE CASCADE

If the parent key changes, the foreign key in the child table updates automatically.

    CREATE TABLE Payments (
        PaymentID INT PRIMARY KEY,
        CustomerID INT,
        FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
        ON UPDATE CASCADE
    );

If CustomerID changes in the Customers table, it also updates in Payments.

3. ON DELETE SET NULL / ON UPDATE SET NULL

If the parent record is deleted or updated, the child’s foreign key becomes NULL.

    FOREIGN KEY (CustomerID)
    REFERENCES Customers(CustomerID)
    ON DELETE SET NULL;

This is useful when you want to keep the record but mark the relationship as missing.

---

Modifying Constraints

You can modify constraints by dropping and recreating them:

    ALTER TABLE Employees
    DROP CONSTRAINT fk_dept;

    ALTER TABLE Employees
    ADD CONSTRAINT fk_dept
    FOREIGN KEY (DeptID)
    REFERENCES Departments(DeptID)
    ON DELETE CASCADE;

---

10. SQL Transactions and TCL — Ensuring Data Consistency Like a Pro

    When working with databases, multiple operations often need to happen together — and either all succeed or none should.
    For example, when transferring money from one bank account to another, you can’t debit one account without crediting the other.

    That’s where transactions and Transaction Control Language (TCL) come into play.

    What is a Transaction?

    A transaction is a group of SQL statements that are executed as a single unit of work.

    It ensures that either all operations succeed (COMMIT) or none take effect (ROLLBACK).

    Example:

    BEGIN TRANSACTION;
    
    UPDATE Accounts
    SET Balance = Balance - 500
    WHERE AccountID = 101;
    
    UPDATE Accounts
    SET Balance = Balance + 500
    WHERE AccountID = 202;
    
    COMMIT;
    

    If both updates succeed → changes are saved.
    If one fails → we can rollback to undo all changes.

    ---

    The Four ACID Properties

    Transactions follow ACID principles — ensuring reliability and consistency even in case of failure.

    1. Atomicity: This property ensures that a transaction is all-or-nothing. If any part of the transaction fails, the entire transaction is rolled back, and the database remains unchanged. For example, in a bank transfer, both the debit from one account and the credit to another must succeed, or neither will happen.

    2. Consistency: This ensures that a transaction brings the database from one valid state to another, maintaining database rules. For instance, the total balance in a bank system should remain the same before and after a transfer, ensuring no money is lost or created.

    3. Isolation: This property ensures that transactions are executed independently without interference. For example, if two users are transferring money simultaneously, their transactions will not affect each other, preventing data corruption.

    4. Durability: Once a transaction is committed, it is permanently recorded in the database, even in the event of a system failure. For example, a power loss won't undo a completed bank transfer, as the transaction data is saved securely.

---

Transaction Control Commands (TCL)

1. BEGIN TRANSACTION

Starts a new transaction block.

    BEGIN TRANSACTION;

All SQL statements after this point are part of the transaction until you COMMIT or ROLLBACK.

2. COMMIT

Saves all changes made during the transaction permanently.

    COMMIT;

Example:

    BEGIN TRANSACTION;
    UPDATE Employees SET Salary = Salary + 1000 WHERE DeptID = 5;
    COMMIT;

Once committed, changes are written to disk.

3. ROLLBACK

Undoes all changes made during the current transaction.

    ROLLBACK;

Example:

    BEGIN TRANSACTION;
    UPDATE Employees SET Salary = Salary + 1000 WHERE DeptID = 5;

    -- Something goes wrong!
    ROLLBACK;

All updates in this transaction are canceled, and the database returns to its previous state.

4. SAVEPOINT

Creates a checkpoint within a transaction to which you can roll back partially, without undoing the entire transaction.

Example:

    BEGIN TRANSACTION;

    UPDATE Products SET Stock = Stock - 10 WHERE ProductID = 101;
    SAVEPOINT after_product1;

    UPDATE Products SET Stock = Stock - 20 WHERE ProductID = 102;

    -- Roll back only to the savepoint
    ROLLBACK TO after_product1;

    COMMIT;

This keeps the first update but cancels the second.

---

Putting It All Together

Example scenario: Bank Transaction

    BEGIN TRANSACTION;

    UPDATE Accounts
    SET Balance = Balance - 1000
    WHERE AccountID = 1;

    SAVEPOINT after_debit;

    UPDATE Accounts
    SET Balance = Balance + 1000
    WHERE AccountID = 2;

    IF @@ERROR != 0  -- (SQL Server)
        ROLLBACK TO after_debit;
    ELSE
        COMMIT;

This ensures that if something fails while crediting, we can roll back only part of the transaction safely.

---

Best Practices

1. Always use transactions for multi-step updates.

2. Keep transactions short — long-running ones can lock rows.

3. Always COMMIT or ROLLBACK explicitly to release locks.

4. Use SAVEPOINTs for better control in complex logic.

Summary Table

---

11. SQL DCL (Data Control Language) — Managing User Access and Security

    Databases store valuable information — from user details to financial data.
    To keep this data safe, SQL provides a special set of commands under Data Control Language (DCL) to manage user access and permissions.

    If DDL defines the structure and DML manipulates data, then DCL controls who can do what inside the database.

    What is DCL (Data Control Language)?

    DCL is used to control access and privileges in a database.
    It helps administrators grant or revoke permissions to different users or roles.

    Two main DCL commands are:

    1. GRANT → Give permissions

    2. REVOKE → Take back permissions

---

1. GRANT — Giving Access

The GRANT command gives specific privileges to a user or role on database objects like tables, views, or schemas.

Syntax:

    GRANT privilege_name
    ON object_name
    TO user_name;

Example:

    GRANT SELECT, INSERT
    ON Employees
    TO user_readwrite;

This allows the user user_readwrite to read (SELECT) and add (INSERT) data into the Employees table.

Granting All Privileges

    GRANT ALL PRIVILEGES
    ON Students
    TO admin_user;

The user admin_user can perform any operation on the Students table.

Granting with the Option to Grant Further

    GRANT SELECT
    ON Employees
    TO team_lead
    WITH GRANT OPTION;

This allows team_lead not only to use SELECT but also to grant SELECT permission to other users.

---

2. REVOKE — Removing Access

When you need to take away a user’s privileges, use the REVOKE command.

Syntax:

    REVOKE privilege_name
    ON object_name
    FROM user_name;

Example:

    REVOKE INSERT
    ON Employees
    FROM user_readwrite;

This removes the INSERT privilege, but the user can still SELECT data.

Revoking All Privileges

    REVOKE ALL PRIVILEGES
    ON Employees
    FROM admin_user;

Removes all permissions from admin_user on the Employees table.

---

User Privileges and Roles

Most databases (like MySQL, PostgreSQL, Oracle, and SQL Server) have users and roles to organize permissions efficiently.

Users

Each person or application connecting to the database is a user with specific privileges.

    CREATE USER Abhishek IDENTIFIED BY 'secure123';

Roles

A role is a collection of privileges that can be assigned to multiple users.

Example:

    CREATE ROLE read_only;
    GRANT SELECT ON Students TO read_only;

    GRANT read_only TO Abhishek;

Now, Abhishek automatically inherits all privileges from the read_only role.

---

Common Privileges in SQL

Security Best Practices

1. Principle of Least Privilege:
   Give users only the permissions they need — nothing more.

2. Use Roles Instead of Individual Grants:
   Easier to manage and review access levels.

3. Regularly Audit Privileges:
   Remove unnecessary or unused permissions.

4. Avoid GRANT ALL to Everyone:
   It’s risky! Especially in production environments.

5. Use Strong Password Policies:
   Always combine privileges with secure user authentication.

6. Revoke Before Removing Users:
   Clean up privileges before deleting user accounts.

Summary Table

In Short

DCL ensures that only the right people have the right access to your database.
With GRANT and REVOKE, you can protect your data from unauthorized access while keeping your team productive and safe.

“Data security isn’t just about encryption — it starts with who can access what.”

---

12. CTEs (Common Table Expressions) in SQL — Simplifying Complex Queries

    Have you ever written a long, messy SQL query filled with multiple subqueries and felt lost halfway through it?
    If yes — then CTEs (Common Table Expressions) are about to become your new best friend.

    CTEs make SQL cleaner, more readable, and easier to debug — especially when dealing with complex joins or recursive relationships like organizational hierarchies.

    What is a CTE (Common Table Expression)?

    A CTE is a temporary, named result set that you can reference within a single SQL statement.
    It’s defined using the WITH clause and behaves like a short-lived table created for that query.

    Basic Syntax

    WITH cte_name AS (
        SELECT column1, column2
        FROM table_name
        WHERE condition
    )
    SELECT *
    FROM cte_name
    WHERE column2 > 100;
    

    1. Non-Recursive CTEs

    These are the most common type — used to simplify complex queries by breaking them into readable chunks.

    Example: Find Employees Earning More Than the Average Salary

    WITH AvgSalary AS (
        SELECT AVG(Salary) AS avg_sal
        FROM Employees
    )
    SELECT Name, Salary
    FROM Employees, AvgSalary
    WHERE Employees.Salary > AvgSalary.avg_sal;
    

    How it works:

    • The CTE AvgSalary calculates the average salary.

    • Then, in the main query, we use that result to find employees earning above average.

      Without a CTE, you’d have to repeat the average calculation or write a nested subquery — much harder to read!

2. Recursive CTEs

A recursive CTE refers to itself and is mainly used to work with hierarchical or tree-structured data, such as employee-manager relationships or folder structures.

Example: Employee Hierarchy

Suppose you have a table:

We want to find all employees under Alice.

    WITH EmployeeHierarchy AS (
        -- Anchor member (base case)
        SELECT EmpID, EmpName, ManagerID
        FROM Employees
        WHERE ManagerID IS NULL  -- Start from top-level manager

        UNION ALL

        -- Recursive member
        SELECT e.EmpID, e.EmpName, e.ManagerID
        FROM Employees e
        INNER JOIN EmployeeHierarchy eh
        ON e.ManagerID = eh.EmpID
    )
    SELECT * FROM EmployeeHierarchy;

Explanation:

• The first query selects the root (Alice).

• The recursive part keeps joining employees with their managers.

• SQL continues the recursion until no more matching records exist.

Output:

    EmpID | EmpName | ManagerID
    1     | Alice   | NULL
    2     | Bob     | 1
    3     | Charlie | 2
    4     | David   | 2

Just like recursion in programming, it repeats until the condition is no longer met.

Benefits of Using CTEs Over Subqueries

Example: Same Query Without CTE

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

Works fine for simple cases, but imagine stacking three or four of these — it quickly becomes unreadable!

When to Use a CTE

When you want to:

• Break down long SQL queries into logical parts

• Avoid repeating the same subquery multiple times

• Handle hierarchical data (like organization charts)

• Improve readability and maintainability

Key Takeaways

• CTE (Common Table Expression) = Temporary, named query result.

• Use the WITH clause to define it.

• Two types: Non-Recursive and Recursive.

• Improves readability, reusability, and maintainability of SQL code.

• Especially powerful for hierarchies and recursive logic.

In Short

CTEs make SQL clean, modular, and powerful — turning what used to be a spaghetti mess of nested queries into elegant, readable logic.

Think of a CTE as a "temporary helper table" that simplifies your SQL — no more headaches from nested subqueries!

---

13. Mastering SQL Window (Analytic) Functions — The Secret Behind Powerful Data Insights

    Have you ever wanted to calculate a running total, find rankings, or compare a row’s value with the previous or next row — without losing detail in your data?

    That’s where Window (Analytic) Functions come in.
    They let you perform calculations across sets of rows related to the current row, all while keeping your original rows intact.

    What Are Window (Analytic) Functions?

    A window function is a special kind of SQL function that lets you perform calculations across a set of rows related to the current row, without collapsing your data into a single result.

    In simple words:

    It’s like looking at a “window” of rows around your current row and doing calculations — like ranking, running totals, or moving averages — while keeping all the original rows visible.

    You can even simplify it further for your Medium audience:

    Window functions let you analyze data across rows while still keeping each row in your results.

    You use them with the OVER clause, which defines that window.

    Basic Syntax

    function_name(column)
    OVER (
        PARTITION BY column_to_group
        ORDER BY column_to_sort
    )
    

    Example:

    SELECT
        Name,
        Department,
        Salary,
        AVG(Salary) OVER (PARTITION BY Department) AS avg_dept_salary
    FROM Employees;
    

    This calculates the average salary per department, but still shows every employee row.

    Understanding the “Window”

    The window defines the range of rows the function will look at for each calculation.
    It’s created using the OVER clause.

    You can:

    • PARTITION BY → divide data into groups (like GROUP BY)

    • ORDER BY → define the order of rows within that partition

Unlike GROUP BY, window functions do not collapse rows — they simply add new columns with computed values.

---

1. Ranking Functions

These functions assign a ranking or position to each row based on the defined order.

ROW_NUMBER()

Gives a unique sequential number to each row in the partition.

    SELECT
        Name,
        Department,
        Salary,
        ROW_NUMBER() OVER (PARTITION BY Department ORDER BY Salary DESC) AS row_num
    FROM Employees;

---

RANK()

Gives a rank with gaps if there are ties.

    SELECT
        Name,
        Department,
        Salary,
        RANK() OVER (PARTITION BY Department ORDER BY Salary DESC) AS rank
    FROM Employees;

Notice: rank 3 is skipped because two employees share rank 2.

DENSE_RANK()

Gives rank without gaps.

    DENSE_RANK() OVER (PARTITION BY Department ORDER BY Salary DESC)

Here, there’s no skipped number — ranking is dense and continuous.

---

2. Aggregate Over Window

You can use aggregate functions like SUM, AVG, COUNT — but instead of grouping rows, they calculate values across the defined window.

SUM() OVER() — Running Total Example

    SELECT
        OrderID,
        CustomerID,
        OrderDate,
        SUM(TotalAmount) OVER (PARTITION BY CustomerID ORDER BY OrderDate) AS running_total
    FROM Orders;

Calculates the cumulative (running) total of orders per customer.

AVG() OVER() — Moving Average Example

    SELECT
        OrderDate,
        AVG(Sales) OVER (ORDER BY OrderDate ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg
    FROM SalesData;

This gives the 3-day moving average (current + 2 previous days).

COUNT() OVER()

    COUNT(*) OVER (PARTITION BY Department)

Returns how many employees exist per department, while showing all rows.

---

3. Value Access Functions: LAG() and LEAD()

These are used to compare current row values with previous or next row values — perfect for trends or differences.

LAG() — Get Previous Row’s Value

    SELECT
        Name,
        Month,
        Salary,
        LAG(Salary) OVER (ORDER BY Month) AS prev_salary
    FROM EmployeeSalary;

Useful for calculating month-over-month changes.

LEAD() — Get Next Row’s Value

    LEAD(Salary) OVER (ORDER BY Month) AS next_salary

Similar to LAG(), but looks ahead instead of behind.

---

4. Running Totals & Moving Averages

Window functions make it easy to calculate dynamic totals and averages.

Running Total

    SUM(Sales) OVER (ORDER BY OrderDate) AS running_total

Moving Average (Last 3 Rows)

    AVG(Sales) OVER (ORDER BY OrderDate ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)

The ROWS BETWEEN clause defines how many previous (or following) rows to include in the calculation window.

Why Use Window Functions?

Key Takeaways

• Window functions let you perform calculations across related rows without collapsing data.

• Use the OVER clause to define partitions and order.

• Ranking functions: ROW_NUMBER, RANK, DENSE_RANK

• Aggregates: SUM, AVG, COUNT — great for running totals or averages.

• Value access: LAG, LEAD — perfect for comparing current vs previous/next values.

In Short

Window functions turn SQL into a powerful analytical tool — no need for messy subqueries or joins.
They give you the ability to analyze data horizontally — across rows — while keeping your dataset fully visible.

“If GROUP BY summarizes data, Window Functions analyze it.”

---

14. SQL Functions — Making Data Work for You

    In SQL, functions are predefined operations or custom logic that help you manipulate data, perform calculations, or transform values easily.
    They save time and make queries more powerful and readable.

    1. Built-in Scalar Functions

    Scalar functions operate on single values and return a single result. They are grouped into String, Numeric, Date, and Conversion functions.

    A. String Functions

    Used to manipulate text data.

    • CONCAT: Used to combine strings. For example, CONCAT('John', ' ', 'Doe') results in 'John Doe'.

    • SUBSTRING: Extracts a part of a string. For example, SUBSTRING('Hello', 2, 3) results in 'ell'.

    • LENGTH: Gets the length of a string. For example, LENGTH('SQL') results in 3.

    • LOWER: Converts text to lowercase. For example, LOWER('SQL') results in 'sql'.

    • UPPER: Converts text to uppercase. For example, UPPER('sql') results in 'SQL'.

    • TRIM: Removes spaces from the start and end of a string. For example, TRIM(' SQL ') results in 'SQL'.

Example in a query:

    SELECT CONCAT(FirstName, ' ', LastName) AS FullName,
           UPPER(FirstName) AS UpperName
    FROM Employees;

B. Numeric Functions

Used for calculations or rounding numbers.

• ROUND: Rounds a number to the nearest specified decimal place.
  Example: ROUND(12.345, 2) → 12.35

• CEIL / CEILING: Always rounds a number up to the next whole integer, no matter the decimal part.
  Example: CEIL(12.3) → 13

• FLOOR: Rounds a number down to the nearest whole integer.
  Example: FLOOR(12.7) → 12

Example:

    SELECT ROUND(Salary, 0) AS RoundedSalary,
           CEIL(Salary) AS SalaryUp,
           FLOOR(Salary) AS SalaryDown
    FROM Employees;

C. Date Functions

Used to work with dates and times.

• CURRENT_DATE: Get today’s date
  Example: CURRENT_DATE → 2025-10-18

• DATEADD: Add an interval to a date
  Example: DATEADD(DAY, 7, '2025-10-01') → '2025-10-08'

• DATEDIFF: Calculate the difference between dates
  Example: DATEDIFF(DAY, '2025-10-01', '2025-10-18') → 17

Example:

    SELECT EmployeeID,
           CURRENT_DATE AS Today,
           DATEADD(MONTH, 3, JoiningDate) AS ProbationEnd,
           DATEDIFF(DAY, JoiningDate, CURRENT_DATE) AS DaysWorked
    FROM Employees;

D. Conversion Functions

Used to convert data types.

• CAST: Convert one type to another
  Example: CAST('123' AS INT) → 123

• CONVERT: Convert between types (DB-specific)
  Example: CONVERT(VARCHAR, 123) → '123'

Example:

    SELECT CAST(Salary AS VARCHAR) AS SalaryText
    FROM Employees;

---

2. User-Defined Functions (UDFs)

Sometimes, built-in functions aren’t enough.
You can create your own function — called a User-Defined Function (UDF) — to reuse logic across multiple queries.

Types of UDFs

1. Scalar UDF: Returns a single value

2. Table-Valued UDF: Returns a table result

Example: Scalar UDF

    CREATE FUNCTION dbo.GetFullName(@FirstName VARCHAR(50), @LastName VARCHAR(50))
    RETURNS VARCHAR(101)
    AS
    BEGIN
        RETURN CONCAT(@FirstName, ' ', @LastName);
    END;

Use it in a query:

    SELECT dbo.GetFullName(FirstName, LastName) AS FullName
    FROM Employees;

Example: Table-Valued UDF

    CREATE FUNCTION dbo.GetHighSalaryEmployees(@MinSalary DECIMAL)
    RETURNS TABLE
    AS
    RETURN
    (
        SELECT * FROM Employees WHERE Salary > @MinSalary
    );

Use it:

    SELECT * FROM dbo.GetHighSalaryEmployees(50000);

---

15. Stored Procedures & Triggers in SQL — Automating Your Database

    When working with SQL, you often need to run repetitive queries or automate responses to data changes.
    That’s where Stored Procedures and Triggers come into play.

    They make your database smarter, faster, and more maintainable.

    1. Stored Procedures

    A stored procedure is a predefined SQL program stored in the database.
    It allows you to execute a set of SQL statements as a single unit.

    Think of it like a function in programming, but it lives in the database.

    Benefits of Stored Procedures

    • Reduce repetition in queries

    • Improve performance (compiled once, executed multiple times)

    • Centralize business logic in the database

    • Enhance security (users can execute procedures without direct table access)

Creating a Stored Procedure

    CREATE PROCEDURE GetEmployeeByDept
        @DeptID INT
    AS
    BEGIN
        SELECT EmployeeID, Name, Salary
        FROM Employees
        WHERE DepartmentID = @DeptID;
    END;

• GetEmployeeByDept → Procedure name

• @DeptID → Input parameter

• SQL inside BEGIN ... END executes when the procedure is called

Executing a Stored Procedure

    EXEC GetEmployeeByDept @DeptID = 5;

Returns all employees in department 5.

Stored Procedures with Multiple Parameters

    CREATE PROCEDURE UpdateSalary
        @EmpID INT,
        @Increment DECIMAL(10,2)
    AS
    BEGIN
        UPDATE Employees
        SET Salary = Salary + @Increment
        WHERE EmployeeID = @EmpID;
    END;

    EXEC UpdateSalary @EmpID = 101, @Increment = 1000;

---

2. Triggers

A trigger is a special kind of stored procedure that automatically executes in response to certain events on a table or view, like INSERT, UPDATE, or DELETE.

Think of it as an automatic action — like a “listener” in your database.

Types of Triggers

Row-Level vs Statement-Level Triggers

Example: AFTER INSERT Trigger

    CREATE TRIGGER trg_AfterInsertEmployee
    ON Employees
    AFTER INSERT
    AS
    BEGIN
        PRINT 'A new employee was added!';
        -- You could also insert into an audit table
        INSERT INTO EmployeeAudit(EmployeeID, Action)
        SELECT EmployeeID, 'INSERT' FROM inserted;
    END;

Fires automatically after a new employee is added.

Example: BEFORE UPDATE Trigger

    CREATE TRIGGER trg_BeforeUpdateSalary
    ON Employees
    INSTEAD OF UPDATE
    AS
    BEGIN
        PRINT 'Salary update is being processed';
        -- Prevent salary from being set below minimum
        UPDATE Employees
        SET Salary = CASE 
                        WHEN inserted.Salary < 30000 THEN 30000
                        ELSE inserted.Salary
                     END
        FROM inserted
        WHERE Employees.EmployeeID = inserted.EmployeeID;
    END;

Key Points

• Stored Procedures: Execute manually; can have parameters; reusable; improve performance.

• Triggers: Execute automatically; respond to table events; can enforce rules or log changes.

• BEFORE vs AFTER: Controls when the trigger runs relative to the action.

• Row vs Statement Level: Controls how often the trigger fires — per row or per statement.

Summary Table

💡 In Short

Stored procedures save repetitive work and centralize business logic.
Triggers automate responses to data changes, keeping your database consistent and smart.

“Think of procedures as your database tools, and triggers as the automatic sensors that keep everything in check.”

---

16. Database Design & Modeling — Building Strong Foundations for Your Data

    A well-designed database is like a strong building foundation — it keeps your data organized, consistent, and easy to manage.

    Database design helps prevent data redundancy, ensures data integrity, and makes queries faster and easier.

    1. ER Diagrams (Entity-Relationship Diagrams)

    An ER Diagram is a visual representation of your database.
    It shows:

    • Entities → Objects or tables (e.g., Employee, Department)

    • Attributes → Columns or properties (e.g., EmployeeID, Name)

    • Relationships → How entities are connected (e.g., Employee belongs to Department)

Example:

    Employee (EmpID, Name, DeptID)
    Department (DeptID, DeptName)
    Employee → belongs to → Department

ER diagrams help plan your database structure before creating tables.

2. Relationship Types

Relationships define how entities relate to each other.

M:N relationships usually require a junction table to break them into two 1:N relationships.

3. Referential Integrity

Referential integrity ensures that relationships between tables remain consistent.

• A foreign key in one table must match a primary key in the related table.

• Prevents orphan records — e.g., an employee cannot belong to a non-existent department.

Example:

    CREATE TABLE Department (
        DeptID INT PRIMARY KEY,
        DeptName VARCHAR(100)
    );

    CREATE TABLE Employee (
        EmpID INT PRIMARY KEY,
        Name VARCHAR(100),
        DeptID INT,
        FOREIGN KEY (DeptID) REFERENCES Department(DeptID)
    );

This ensures every employee’s DeptID exists in the Department table.

4. Keys in Database Design

Keys are essential to identify records uniquely and maintain relationships.

Best Practices for Database Design

1. Normalize your data — remove redundancy, reduce anomalies

2. Define primary keys for every table

3. Use foreign keys to enforce relationships

4. Choose surrogate keys for simplicity in large datasets

5. Plan relationships carefully (1:1, 1:N, M:N)

6. Use ER diagrams to visualize and communicate design

Summary Table

---