Saturday, January 25, 2025

Advanced SQL (CTE, Indexes, Transactions, Views, Stored Procedures, Triggers)


 ebook - Mastering SQL: A Comprehensive Guide to Database Mastery

Common Table Expressions (CTEs)

Common Table Expressions (CTEs) simplify complex queries by creating temporary result sets that can be referenced within a SELECT, INSERT, UPDATE, or DELETE statement. They improve query readability and can be recursive.

Syntax:

WITH cte_name (column1, column2, ...) AS (
    SELECT ...  -- Your Query
)
SELECT * FROM cte_name;

Types of CTEs

1. Simple CTEs: Used for breaking down complex queries into smaller, more readable parts.

Example:

WITH EmployeeCTE AS (
    SELECT department_id, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department_id
)
SELECT * FROM EmployeeCTE WHERE avg_salary > 50000;

2. Recursive CTEs: Used for hierarchical or iterative data.

Example:

WITH RECURSIVE EmployeeHierarchy AS (
    SELECT employee_id, manager_id, 1 AS level
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    SELECT e.employee_id, e.manager_id, eh.level + 1
    FROM employees e
    INNER JOIN EmployeeHierarchy eh
    ON e.manager_id = eh.employee_id
)
SELECT * FROM EmployeeHierarchy;

Benefits of CTEs

  • Improves query readability and modularity.

  • Enables reuse of query logic.

  • Simplifies recursive queries for hierarchical data.

Practical Use Cases

  • Summarizing data and using it in subsequent queries.

  • Hierarchical reporting, like organizational structures.

  • Temporary table-like operations without creating a permanent table.

CTEs are powerful for simplifying and organizing SQL queries, especially for complex or recursive operations. They enhance readability and maintainability, making them essential for efficient query writing.

Indexes and Performance Optimization

Indexes in SQL are database objects that improve the speed of data retrieval. They act like pointers to the data, allowing faster access without scanning the entire table. Proper use of indexes can significantly enhance query performance.

Indexes are created on one or more columns of a table. They can be of various types, such as:

  • Clustered Index: Sorts and stores data rows in the table based on key values. A table can have only one clustered index.

  • Non-Clustered Index: Maintains a separate structure from the data, providing pointers to the actual data.

Creating Indexes:

CREATE INDEX idx_column_name ON table_name(column_name);

Benefits of Indexes:

  • Speeds up SELECT queries, especially on large tables.

  • Enables efficient sorting and filtering.

  • Improves performance of joins and aggregate functions.

Drawbacks of Indexes:

  • Slower INSERT, UPDATE, and DELETE operations due to index maintenance.

  • Additional storage space requirements.

Best Practices for Indexing:

  • Create indexes on columns used in WHERE, JOIN, ORDER BY, and GROUP BY clauses.

  • Avoid excessive indexing to reduce overhead.

  • Regularly monitor and rebuild fragmented indexes.

Example: Indexing a frequently searched column:

CREATE INDEX idx_last_name ON employees(last_name);

SELECT * FROM employees WHERE last_name = 'Smith';

Properly designed indexes play a critical role in performance optimization, ensuring fast and efficient data retrieval.

Transactions and Concurrency in SQL

Transactions

A transaction is a sequence of one or more SQL operations treated as a single, indivisible unit of work. Transactions ensure data consistency and integrity, even in the presence of failures or errors.

ACID Properties of Transactions:

  1. Atomicity: All operations in a transaction are completed, or none are executed.

  2. Consistency: A transaction ensures that the database transitions from one valid state to another.

  3. Isolation: Transactions are executed independently, without interfering with each other.

  4. Durability: Once a transaction is committed, its changes are permanent, even in case of a system failure.

Transaction Control Commands:

COMMIT: Saves all changes made in a transaction to the database.

COMMIT;

ROLLBACK: Reverts all changes made in a transaction since the last COMMIT or SAVEPOINT.

ROLLBACK;


SAVEPOINT: Creates a point within a transaction to which you can roll back.

SAVEPOINT sp1;

SET TRANSACTION: Sets properties like isolation level for a transaction.


Concurrency

Concurrency refers to multiple transactions being executed simultaneously in a database system. To ensure data consistency and prevent conflicts, concurrency is managed through isolation levels and locking mechanisms.

Problems in Concurrent Transactions:

  1. Dirty Reads: One transaction reads uncommitted data from another transaction.

  2. Non-Repeatable Reads: A value read by a transaction is modified by another before the first completes.

  3. Phantom Reads: A transaction retrieves different sets of rows for the same query due to changes by another transaction.


Isolation Levels:

SQL defines isolation levels to balance concurrency and consistency:

  1. Read Uncommitted: Allows dirty reads; highest concurrency, lowest consistency.

  2. Read Committed: Prevents dirty reads but allows non-repeatable reads.

  3. Repeatable Read: Prevents dirty and non-repeatable reads but not phantom reads.

  4. Serializable: Ensures full isolation; prevents all concurrency issues.

Example: Setting the isolation level to Serializable:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;


Proper management of transactions and concurrency is crucial to ensure data integrity while supporting efficient multi-user database access.


Views in SQL

A view in SQL is a virtual table created based on the result of a query. It does not store data physically but provides a way to simplify complex queries and enhance data security by controlling access to specific parts of a table.


Why Use Views?

  1. Simplification: Simplifies complex queries by encapsulating them into a reusable structure.

  2. Security: Restricts user access to specific columns or rows in a table.

  3. Data Abstraction: Hides the complexity of the underlying database schema.

  4. Reusability: Reduces redundancy by reusing the same query logic.


Creating a View

The CREATE VIEW statement is used to define a view.

Syntax:

CREATE VIEW view_name AS
SELECT column1, column2
FROM table_name
WHERE condition;

Example:
Creating a view to display only high-salary employees:

CREATE VIEW high_salary_employees AS
SELECT name, salary
FROM employees
WHERE salary > 50000;

Now, querying the view is similar to querying a table:

SELECT * FROM high_salary_employees;



Updating a View

To modify an existing view, use the CREATE OR REPLACE VIEW statement.

Example:

CREATE OR REPLACE VIEW high_salary_employees AS
SELECT name, salary, department
FROM employees
WHERE salary > 60000;


Dropping a View

To delete a view, use the DROP VIEW statement.

Example:

DROP VIEW high_salary_employees;


Limitations of Views

  1. Some views, especially those using joins or aggregate functions, may not be updatable.

  2. Views are dependent on the base tables. Changes to the structure of base tables can invalidate views.

Views are a powerful tool in SQL to simplify, secure, and organize access to data without altering the underlying tables.

Stored Procedures and Functions in SQL

Stored Procedures and Functions are reusable SQL code blocks that perform specific tasks. They simplify complex operations, improve performance, and enhance database management by centralizing logic.


Stored Procedures

A stored procedure is a precompiled set of SQL statements that can perform operations like inserting, updating, or retrieving data. It can accept input parameters and return multiple outputs.

Why Use Stored Procedures?

  • Improves performance by reducing query compilation time.

  • Centralizes business logic for consistent database operations.

  • Enhances security by limiting direct access to underlying tables.

Syntax:

CREATE PROCEDURE procedure_name (parameters)
BEGIN
    SQL statements;
END;

Example:

A procedure to retrieve employees from a specific department:

CREATE PROCEDURE GetEmployeesByDepartment(IN dept_name VARCHAR(50))
BEGIN
    SELECT name, salary
    FROM employees
    WHERE department = dept_name;
END;

To execute:

CALL GetEmployeesByDepartment('HR');


Functions

A function is similar to a procedure but is designed to return a single value. It is used for calculations or to perform operations that return a result based on input.

Why Use Functions?

  • Ideal for computations or transformations.

  • Can be used directly in SQL queries.

Syntax:

CREATE FUNCTION function_name (parameters)
RETURNS return_data_type
BEGIN
    SQL statements;
    RETURN value;
END;

Example:

A function to calculate annual salary:

CREATE FUNCTION GetAnnualSalary(monthly_salary DECIMAL(10, 2))
RETURNS DECIMAL(12, 2)
BEGIN
    RETURN monthly_salary * 12;
END;

To use:

SELECT GetAnnualSalary(salary) AS annual_salary FROM employees;


Differences Between Stored Procedures and Functions

Aspect

Stored Procedure

Function

Return Value

Can return multiple values via parameters

Returns a single value only

Usage in Queries

Cannot be used in SELECT statements

Can be used in SELECT or WHERE clauses

Purpose

Used for performing operations

Used for computations


Stored procedures and functions are essential tools for modularizing database logic, enhancing efficiency, and maintaining consistency in SQL-based applications.


Triggers in SQL

A trigger in SQL is a special kind of stored procedure that is automatically executed (or "triggered") in response to specific events occurring in a database, such as an INSERT, UPDATE, or DELETE operation on a table.


Why Use Triggers?

  1. Automation: Automatically perform actions in response to data changes.

  2. Data Integrity: Enforce business rules or constraints at the database level.

  3. Auditing: Track changes to critical data for security and compliance.


Types of Triggers

  1. Before Triggers: Execute before the triggering event occurs.

  2. After Triggers: Execute after the triggering event occurs.


Syntax

CREATE TRIGGER trigger_name
{ BEFORE | AFTER } { INSERT | UPDATE | DELETE }
ON table_name
FOR EACH ROW
BEGIN
    SQL statements;
END;


Example: Auditing Changes in a Table

Requirement: Track changes to the employees table in an audit log.

Trigger Creation:

CREATE TRIGGER log_employee_update
AFTER UPDATE ON employees
FOR EACH ROW
BEGIN
    INSERT INTO audit_log (employee_id, old_salary, new_salary, updated_at)
    VALUES (OLD.id, OLD.salary, NEW.salary, NOW());
END;


Here:

  • OLD refers to the data before the update.

  • NEW refers to the data after the update.

  • The trigger inserts details into the audit_log table after an update on the employees table.


Dropping a Trigger

To delete a trigger, use the DROP TRIGGER command:

DROP TRIGGER log_employee_update;



Use Cases for Triggers

  1. Maintaining Logs: Automatically log changes for auditing purposes.

  2. Enforcing Rules: Prevent invalid operations or enforce custom constraints.

  3. Synchronizing Data: Automatically update related tables.


Limitations

  • Overuse of triggers can make debugging and maintenance complex.

  • Triggers can impact performance, especially if they execute complex operations.


Triggers are powerful tools for automating database actions, maintaining data integrity, and auditing changes. However, they should be used judiciously to avoid unnecessary complexity.

No comments:

Search This Blog