SQL: Queries, Views, Triggers, Stored Procedures & Transactions

TAKEAWAYS:

• SQL is the universal language for defining, querying, and managing relational databases, divided into DDL (CREATE, ALTER, DROP), DML (SELECT, INSERT, UPDATE, DELETE), and DCL (GRANT, REVOKE).
• Views act as virtual tables, simplifying complex queries and enforcing security (e.g., hiding sensitive columns from users).
• Triggers automate actions (e.g., logging changes) when database events (INSERT/UPDATE/DELETE) occur, but can degrade performance if overused.
• Stored procedures bundle SQL logic into reusable modules, improving security and performance (e.g., CALL procedure_name()).
• Transactions ensure ACID (Atomicity, Consistency, Isolation, Durability) properties; use COMMIT/ROLLBACK to manage changes atomically.
• Query processing follows a pipeline: parsing → optimization → execution, visualized as an operator tree (e.g., for SELECT with JOIN/WHERE).

1. SQL Overview: The Language of Databases

SQL (Structured Query Language) is the standardized language for interacting with relational databases. It is categorized into three main types:

How SQL Works: Query Processing

When you execute a SQL query, the database follows these steps:

1. Parsing: Checks syntax and validates objects (tables, columns).
2. Optimization: Converts SQL into an execution plan (e.g., choosing the fastest JOIN strategy).
3. Execution: Runs the plan using the database engine.

Visual: Operator Tree for Query Processing

graph TD
    A["SELECT customer_name"] --> B["FROM branch, account, depositor"]
    B --> C["WHERE branch_city='btl' AND balance>2000"]
    C --> D["JOIN account ON branch.branch_id = account.branch_id"]
    C --> E["JOIN depositor ON account.account_id = depositor.account_id"]
    D & E --> F["Filter: branch_city='btl' AND balance>2000"]
    F --> G["Project: customer_name"]

Example: For the query:

SELECT customer_name
FROM branch, account, depositor
WHERE branch_city='btl' AND balance>2000;

The operator tree shows how the database breaks it into steps: joins, filters, and projections.

2. Data Definition Language (DDL)

DDL commands define the structure of the database.

Key DDL Commands

Worked Example: Creating a Database and Table

-- Create a database
CREATE DATABASE Company;

-- Use the database
USE Company;

-- Create an Employees table
CREATE TABLE Employees (
    E_id INT PRIMARY KEY,
    Name VARCHAR(50) NOT NULL,
    Dept VARCHAR(30),
    Salary FLOAT,
    Join_Date DATE
);

3. Data Manipulation Language (DML)

DML commands retrieve or modify data.

Key DML Commands

Worked Example: Queries on Employees Table

-- Find all employees in the IT department
SELECT Name, Salary
FROM Employees
WHERE Dept='IT';

-- Update salary for an employee
UPDATE Employees
SET Salary=60000
WHERE E_id=2;

-- Delete an employee (use with caution!)
DELETE FROM Employees
WHERE E_id=3;

## In the real world

• eSewa (Nepal): Uses SQL to manage bill payments, transactions, and user accounts. When you pay an electricity bill, eSewa runs a SELECT to check your balance, then UPDATE to deduct the amount and INSERT a new transaction record.
• Khalti (Nepal): Employs SQL for fraud detection by querying transaction logs (SELECT * FROM Transactions WHERE Amount > 100000 AND Status='Pending'). Triggers automatically flag suspicious activities.
• Daraz (Nepal): Uses SQL to handle order processing. A TRIGGER might update inventory (UPDATE Products SET Stock=Stock-1 WHERE ProductID=123) when an order is placed (INSERT INTO Orders VALUES (...)).

4. Views in SQL

A view is a virtual table based on a SQL query. It simplifies complex queries and enforces security.

Why Use Views?

• Simplify queries: Hide complex joins/subqueries.
• Security: Restrict access to sensitive columns (e.g., Salary).
• Data consistency: Ensure users always see aggregated data (e.g., SUM(Salary)).

Creating and Using Views

-- Create a view showing only names and departments
CREATE VIEW EmployeeDeptView AS
SELECT Name, Dept FROM Employees;

-- Query the view
SELECT * FROM EmployeeDeptView WHERE Dept='Finance';

Worked Example: View for HR Reports

-- View for HR to see only department-wise employee counts
CREATE VIEW DeptEmployeeCount AS
SELECT Dept, COUNT(*) AS EmployeeCount
FROM Employees
GROUP BY Dept;

-- Query the view
SELECT * FROM DeptEmployeeCount;

## In the real world

• Nepal Stock Exchange (NEPSE): Uses views to display stock prices without exposing raw transaction data. Investors see SELECT Symbol, Price, Volume FROM StockView but not the underlying Transactions table.
• Ncell (Nepal): Views hide customer details from call-center agents. Agents see SELECT CustomerID, Balance FROM CustomerView but cannot access PIN or Address.

5. Triggers in SQL

A trigger is a stored procedure that automatically executes in response to database events (INSERT, UPDATE, DELETE).

Syntax

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

Worked Example: Audit Trigger

-- Log changes to the Employees table
DELIMITER //
CREATE TRIGGER LogEmployeeChanges
AFTER UPDATE ON Employees
FOR EACH ROW
BEGIN
    INSERT INTO EmployeeAuditLog (E_id, OldSalary, NewSalary, ChangeDate)
    VALUES (OLD.E_id, OLD.Salary, NEW.Salary, NOW());
END //
DELIMITER ;

Advantages/Disadvantages of Triggers

## In the real world

• Banks (e.g., NMB, Global IME): Use triggers to update loan interest automatically. When a loan payment is recorded (INSERT INTO Payments), a trigger recalculates the remaining balance (UPDATE Loans SET RemainingAmount=RemainingAmount-Payment).
• Pathao (Nepal): Triggers ensure ride fares are updated in real-time. When a ride starts (INSERT INTO Rides), a trigger deducts the fare from the driver’s balance (UPDATE Drivers SET Balance=Balance-Fare).

6. Stored Procedures

A stored procedure is a precompiled SQL script stored in the database, improving performance and security.

Why Use Stored Procedures?

• Reusability: Execute the same logic multiple times.
• Security: Restrict direct table access; users call procedures instead.
• Performance: Compiled once, executed faster.

Syntax

DELIMITER //
CREATE PROCEDURE ProcedureName (params)
BEGIN
    -- SQL statements
END //
DELIMITER ;

Worked Example: Employee Salary Update Procedure

DELIMITER //
CREATE PROCEDURE UpdateSalary(IN emp_id INT, IN new_salary FLOAT)
BEGIN
    UPDATE Employees SET Salary=new_salary WHERE E_id=emp_id;
    -- Log the change
    INSERT INTO SalaryHistory (E_id, OldSalary, NewSalary, UpdateDate)
    VALUES (emp_id, (SELECT Salary FROM Employees WHERE E_id=emp_id), new_salary, NOW());
END //
DELIMITER ;

-- Call the procedure
CALL UpdateSalary(1, 60000);

## In the real world

• NTC (Nepal): Uses stored procedures to process bulk electricity bill payments. Instead of exposing the Bills table, NTC provides CALL ProcessPayment(customer_id, amount).
• YouTube (Global): Stored procedures handle video recommendations. When a user watches a video (INSERT INTO WatchHistory), a procedure updates their profile (CALL UpdateRecommendations(user_id)).

7. Transactions and ACID Properties

A transaction is a sequence of operations executed as a single unit. SQL ensures ACID properties:

Transaction Control Commands

Worked Example: Bank Transfer Transaction

BEGIN TRANSACTION;
    -- Deduct from sender
    UPDATE Accounts SET Balance=Balance-1000 WHERE AccountID=1;
    -- Credit to receiver
    UPDATE Accounts SET Balance=Balance+1000 WHERE AccountID=2;
    -- Log the transfer
    INSERT INTO Transactions (FromAccount, ToAccount, Amount, Date)
    VALUES (1, 2, 1000, NOW());
COMMIT; -- All changes are saved

## In the real world

• Khalti (Nepal): Uses transactions for peer-to-peer transfers. If the debit fails, the credit is rolled back:

  BEGIN TRANSACTION;
      UPDATE Users SET Balance=Balance-500 WHERE UserID=101; -- Debit
      UPDATE Users SET Balance=Balance+500 WHERE UserID=102; -- Credit
  COMMIT;
  

• Daraz (Nepal): Transactions ensure order fulfillment. If stock is insufficient (Stock < Quantity), the order is rolled back:

  BEGIN TRANSACTION;
      UPDATE Products SET Stock=Stock-Quantity WHERE ProductID=123;
      INSERT INTO Orders (CustomerID, ProductID, Quantity, Status)
      VALUES (501, 123, 2, 'Processing');
  COMMIT;
  

8. SQL Joins: Combining Tables

Joins combine rows from multiple tables based on related columns.

Worked Example: Employee-Department Join

-- Find employees and their department names
SELECT Employees.Name, Departments.DeptName
FROM Employees
INNER JOIN Departments ON Employees.DeptID = Departments.DeptID;

Visual: SQL Join Types

erDiagram
    Employees ||--o{ Departments : "works_in"
    Employees {
        int E_id PK
        string Name
        int DeptID FK
    }
    Departments {
        int DeptID PK
        string DeptName
    }

9. Subqueries and Nested Queries

A subquery is a query inside another query. Used for filtering, calculations, or complex logic.

Types of Subqueries

Worked Example: Find Employees Earning Above Department Average

SELECT Name, Salary, Dept
FROM Employees E1
WHERE Salary > (
    SELECT AVG(Salary)
    FROM Employees E2
    WHERE E2.Dept = E1.Dept
);

10. Common Table Expressions (CTEs) and Window Functions

CTEs (WITH Clause)

A CTE is a temporary result set defined within a query (like a subquery but reusable).

WITH HighEarners AS (
    SELECT Name, Salary
    FROM Employees
    WHERE Salary > 50000
)
SELECT * FROM HighEarners;

Window Functions

Perform calculations across sets of rows (unlike GROUP BY, which collapses rows).

Worked Example: Rank Employees by Salary

SELECT
    Name,
    Salary,
    DENSE_RANK() OVER (ORDER BY Salary DESC) AS SalaryRank
FROM Employees;

Exam Tip

1. Syntax is Key: Memorize the exact syntax for CREATE TABLE, JOIN, GROUP BY, and HAVING. Exams often test minor syntax errors (e.g., missing commas, incorrect keywords).
2. Operator Trees: For query processing questions, draw the operator tree step-by-step. Example:

   graph TD
       A["SELECT Name"] --> B["FROM Employees"]
       B --> C["WHERE Salary > 50000"]
       C --> D["GROUP BY Dept"]
       D --> E["HAVING AVG(Salary) > 60000"]

3. Views vs. Tables: Views are not stored; they are virtual. Always clarify whether a question asks for a table or a view.
4. Triggers vs. Procedures:
   • Triggers fire automatically on events.
   • Procedures must be called explicitly (CALL).
5. Transactions: Always use BEGIN TRANSACTION, COMMIT, or ROLLBACK in multi-step operations. Examiners love testing ACID properties.
6. Joins: Practice INNER JOIN, LEFT JOIN, and SELF JOIN with real tables. Draw ER diagrams to visualize relationships.
7. Subqueries: Know when to use correlated vs. non-correlated subqueries. Correlated subqueries reference the outer query.
8. Window Functions: Distinguish between GROUP BY (collapses rows) and window functions (preserves rows). Use OVER() for window functions.

Final Note: SQL is the bridge between applications and databases. Mastering it means you can design queries, optimize performance, and ensure data integrity—skills every database professional needs. Practice with real datasets (e.g., SQLZoo or LeetCode) to build intuition.