Mastering SQL: The Ultimate Guide to Querying and Managing Relational Databases

📘 Chapter 5: Advanced SQL Features

🧠 Introduction

After mastering the basics of querying, manipulating, and joining data in SQL, developers and data analysts can benefit from learning advanced SQL features. These features include:

• Views
• Indexes
• Stored Procedures and Functions
• Triggers
• Window Functions
• Common Table Expressions (CTEs)
• Transactions and Locks
• Temporary Tables and Variables

These tools allow for more efficient, reusable, and scalable data operations in real-world applications.

👁️ 1. SQL Views

🔹 What is a View?

A View is a virtual table based on a SELECT query. It simplifies complex queries and enhances security.

✅ Creating a View

sql

CREATE VIEW ActiveCustomers AS

SELECT CustomerID, Name, Country

FROM Customers

WHERE Status = 'Active';

✅ Querying the View

sql

SELECT * FROM ActiveCustomers;

📋 View Advantages

⚡ 2. Indexes

🔹 What is an Index?

An index improves query performance by allowing faster data lookup, similar to a book index.

✅ Creating an Index

sql

CREATE INDEX idx_name ON Customers(Name);

📋 Types of Indexes

⚠️ Indexes speed up reads but can slow down writes (INSERT, UPDATE, DELETE).

🛠️ 3. Stored Procedures

🔹 What is a Stored Procedure?

A Stored Procedure is a precompiled SQL block stored in the database, reusable for multiple calls.

✅ Syntax Example

sql

CREATE PROCEDURE GetCustomersByCountry(IN countryName VARCHAR(50))

BEGIN

    SELECT * FROM Customers WHERE Country = countryName;

END;

✅ Calling the Procedure

sql

CALL GetCustomersByCountry('India');

📋 Benefits

🧮 4. SQL Functions

🔹 What is a User-Defined Function (UDF)?

Like procedures, functions return values and can be used in expressions.

✅ Example: Scalar Function

sql

CREATE FUNCTION GetDiscount(price DECIMAL(10,2))

RETURNS DECIMAL(10,2)

BEGIN

    RETURN price * 0.90;

END;

sql

SELECT Name, GetDiscount(Price) AS DiscountedPrice FROM Products;

🔁 5. Triggers

🔹 What is a Trigger?

A trigger is a special procedure that executes automatically in response to INSERT, UPDATE, or DELETE.

✅ Example: Track Changes

sql

CREATE TRIGGER after_update_order

AFTER UPDATE ON Orders

FOR EACH ROW

INSERT INTO AuditLog(OrderID, ChangedOn) VALUES (OLD.OrderID, CURRENT_TIMESTAMP);

📋 When to Use Triggers

📊 6. Window Functions

🔹 What Are Window Functions?

Window functions perform calculations across a set of rows related to the current row.

✅ Example: Running Total

sql

SELECT CustomerID, OrderID, Amount,

       SUM(Amount) OVER (PARTITION BY CustomerID ORDER BY OrderID) AS RunningTotal

FROM Orders;

📋 Common Window Functions

🔁 7. Common Table Expressions (CTEs)

🔹 What is a CTE?

A CTE is a temporary named result set you can reference in a query.

✅ Example:

sql

WITH HighValueOrders AS (

    SELECT * FROM Orders WHERE Amount > 500

)

SELECT * FROM HighValueOrders WHERE CustomerID = 1;

🔒 8. Transactions & Locking

🔹 What is a Transaction?

A transaction is a logical unit of work with ACID properties (Atomicity, Consistency, Isolation, Durability).

✅ Syntax

sql

START TRANSACTION;

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

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

COMMIT;

Use ROLLBACK; to undo changes on error.

📋 Transaction Isolation Levels

📁 9. Temporary Tables & Variables

🔹 Temporary Tables

sql

CREATE TEMPORARY TABLE TempData (

    ID INT,

    Info VARCHAR(50)

);

Useful for staging or intermediate data in complex queries.

🔹 Variables

sql

DECLARE @total INT;

SET @total = (SELECT COUNT(*) FROM Orders);

🧪 Real-World Application

Scenario: Multi-Step Discount Processing

1. Create a view for high-value customers.
2. Store their order data in a temp table.
3. Use a window function to apply ranked discount tiers.
4. Save results via a stored procedure.

✅ Summary Table