Advanced Database Management Systems: Joins, Subqueries, Views, Stored Procedures, Triggers, DCL, and TCL

This chapter explores advanced SQL operations, server-side programming constructs, and database administration commands. We progress from multi-table joins and virtual tables to automated triggers, transaction control language (TCL), and data control language (DCL).

1. Advanced SQL Joins: Left, Right, Full, Cross, and Self Joins

Building upon basic inner joins, relational databases support complex multi-table relationships. The positioning of tables in the query (LEFT TABLE vs. RIGHT TABLE) dictates how unmatched rows are handled.

Types of Joins and Their Behaviors

2. Subqueries: Nested Queries in SQL

A subquery is a query nested inside a larger, outer SQL statement (such as SELECT, INSERT, UPDATE, or DELETE). Subqueries allow you to execute dynamic filtering where criteria depend on aggregated or calculated results derived on the fly.

Mental Model & Execution Flow

Outer Query (SELECT employees WHERE...)
   |
   | evaluates condition using data from
   v
Inner Subquery (SELECT AVG(age) FROM employees)

Practical Example

Consider finding all employees whose age is greater than the average age across the entire organization:

SELECT employee_id, name, age
FROM employees
WHERE age > (
    SELECT AVG(age) 
    FROM employees
);

3. Database Views: Virtual Tables for Security and Simplicity

A view is a saved, named SELECT query stored in the database catalog. It acts as a virtual table: it does not physically store the underlying data itself (with the exception of materialized views in specific database engines), but rather executes the underlying query whenever the view is queried.

Key Benefits of Views

• Security: Restrict direct user access to sensitive tables by exposing only specific columns or rows via a view.
• Simplicity: Hide complex multi-table joins behind a clean, simple virtual table interface.
• Consistency: Present a standardized pre-aggregated or pre-filtered snapshot of data to application developers and reporting tools.

Syntax and Example

-- Creating a virtual table view
CREATE VIEW cs_student_view AS
SELECT student_id, name, major
FROM students
WHERE major = 'Computer Science';

-- Querying the view just like a regular table
SELECT * FROM cs_student_view WHERE name LIKE 'A%';

Note: A standard view captures the result structure of the query at execution time. If underlying base tables receive new data insertions later, whether those records appear depends on the dynamic evaluation of the underlying SELECT statement when the view is queried.

4. Stored Procedures: Precompiled Server-Side Logic

A stored procedure is a precompiled collection of SQL statements and procedural logic (loops, conditionals) stored directly inside the database server.

Why Use Stored Procedures?

• Reduced Network Traffic: Instead of sending multiple raw SQL queries back and forth across the network from the client application, you send a single execution command.
• Code Reusability: Centralize business logic inside the database so multiple applications (Java, Python, PHP) can invoke the exact same routine.
• Precompilation: Database engines optimize and compile the execution plan ahead of time, accelerating repeated executions.

Syntax Structure

Using custom delimiters (such as `

CREATE PROCEDURE GetHighSalaryEmployees(IN min_salary DECIMAL(10,2)) BEGIN SELECT employee_id, name, salary FROM employees WHERE salary >= min_salary; END

5. Database Triggers: Automated Event-Driven Execution

A trigger is a specialized stored procedure that executes automatically ("behind the scenes") in response to specific data modification events (INSERT, UPDATE, DELETE) on a designated table.

Common Use Cases

• Auditing & Logging: Automatically recording change histories into an audit table whenever a row is deleted or modified.
• Data Validation: Enforcing complex business rules before or after data mutations occur.
• Event Dispatching: Sending notifications or logging system events.

Anatomy of a Trigger

DELIMITER 
$$

CREATE TRIGGER before_student_delete
BEFORE DELETE ON students
FOR EACH ROW
BEGIN
    -- Insert audit record into student_logs before the actual delete happens
    INSERT INTO student_logs (student_id, deleted_at, deleted_by)
    VALUES (OLD.id, NOW(), CURRENT_USER());
END
$$

DELIMITER ;

Trigger Timing Events

• BEFORE INSERT / AFTER INSERT
• BEFORE UPDATE / AFTER UPDATE
• BEFORE DELETE / AFTER DELETE

6. Data Control Language (DCL): User Access and Privileges

DCL commands are utilized by database administrators (DBAs) and system architects to manage user security, permissions, and access rights across database objects.

Core DCL Commands

• GRANT: Confers specific privileges (e.g., SELECT, INSERT, UPDATE) on users.
• REVOKE: Removes previously granted privileges.

Practical Example

-- Granting read and insert permissions on a specific table to a user
GRANT SELECT, INSERT ON company_db.employees TO 'app_user'@'localhost';

-- Revoking delete privileges to prevent accidental data loss
REVOKE DELETE ON company_db.employees FROM 'app_user'@'localhost';

7. Transaction Control Language (TCL) and ACID Safety

TCL manages transactions within a database, ensuring that groups of SQL operations execute reliably using ACID properties (Atomicity, Consistency, Isolation, Durability).

Real-World Analogy: The Banking Transfer Crisis

Imagine a banking software executing a money transfer across multiple accounts. The operation takes 50 seconds and involves updating four separate ledger tables.

• After 10 seconds (mid-way through updating table two), the database server crashes due to a power outage.
• Tables 1 and 2 have been updated, but Tables 3 and 4 remain unchanged.
• Without transaction management, money has effectively vanished from account A without appearing in account B.

The Role of ROLLBACK and COMMIT

To prevent partial, corrupted database states, transactions bundle operations together:

• COMMIT: Permanently saves all changes made during the current transaction to the database.
• ROLLBACK: Reverts all changes made during the current transaction back to the last stable saved state if any failure or exception occurs.

-- Starting and managing a safe transaction block
START TRANSACTION;

UPDATE accounts SET balance = balance - 500 WHERE account_id = 101;
UPDATE accounts SET balance = balance + 500 WHERE account_id = 202;

-- If everything succeeds without error:
COMMIT;

-- If an error occurs halfway through:
-- ROLLBACK;

8. Summary and Key Takeaways

• Multi-Table Joins: Master INNER, LEFT, RIGHT, FULL, and CROSS joins by carefully tracking which table acts as the reference baseline and how NULL values populate unmatched rows.
• Subqueries: Nest queries inside parentheses to drive dynamic filtering based on runtime aggregates (e.g., WHERE salary > (SELECT AVG(...) FROM ...)).
• Views: Create virtual tables using CREATE VIEW to encapsulate complex multi-table joins, enhance data security, and simplify reporting queries.
• Stored Procedures: Encapsulate precompiled, reusable server-side SQL blocks using DELIMITER and BEGIN...END blocks to minimize network overhead.
• Triggers: Automate background actions (BEFORE or AFTER INSERT/UPDATE/DELETE) to maintain audit logs and enforce validation rules.
• DCL: Use GRANT and REVOKE to restrict or permit operational access (SELECT, INSERT, DELETE) for database users.
• TCL & Rollbacks: Ensure data integrity across multi-step operations using START TRANSACTION, COMMIT, and ROLLBACK to prevent partial updates during unexpected system crashes.