Advanced SQL and Database Programming

TL;DR

You'll learn how to write more powerful and flexible SQL using advanced features like window functions, stored procedures, and triggers. These tools let you handle complex data analysis, automate tasks, and enforce data rules directly within your database. Mastering them will make your database applications more efficient and robust.

1. The Mental Model

Think of advanced SQL as adding a powerful toolkit to your basic SQL skills. You're moving beyond simple queries to leverage the database itself for more sophisticated data processing, automation, and integrity management, making your applications smarter and faster.

2. The Core Material

2.1 Window Functions: Analyzing Data in Groups Without Grouping

Photo by Kampus Production on Pexels

Window functions let you perform calculations across a set of table rows that are related to the current row, without collapsing those rows into a single output row (like GROUP BY does). This is incredibly useful for ranking, running totals, and calculating moving averages.

A window function has three main parts:
- OVER(): This clause defines the "window" or set of rows the function operates on.
- PARTITION BY: (Optional) Divides the rows into groups within the window. The function restarts for each partition.
- ORDER BY: (Optional) Orders the rows within each partition. This is crucial for functions like ROW_NUMBER() or LAG().

Common window functions:
- ROW_NUMBER(): Assigns a unique, sequential integer to each row within its partition, based on the ORDER BY clause.
- RANK()/DENSE_RANK(): Assigns ranks to rows within a partition. RANK() leaves gaps, DENSE_RANK() doesn't.
- LEAD(column, offset, default)/LAG(column, offset, default): Accesses data from a subsequent (LEAD) or preceding (LAG) row within the window.
- SUM(), AVG(), COUNT(): Aggregate functions used as window functions, calculating their results over the window (or partition) rather than the entire result set or a GROUP BY group.

-- Example: Rank products by price within each category
SELECT
    product_name,
    category,
    price,
    RANK() OVER (PARTITION BY category ORDER BY price DESC) AS price_rank_in_category
FROM
    products;

-- Example: Calculate a running total of sales for each day
SELECT
    sale_date,
    amount,
    SUM(amount) OVER (ORDER BY sale_date) AS running_total_sales
FROM
    sales;

2.2 Stored Procedures: Encapsulating Logic for Reuse

Photo by Engin Akyurt on Pexels

A stored procedure is a prepared SQL code block that you save in the database. You can execute it repeatedly by calling its name. They offer several benefits:
- Performance: Pre-compiled execution plans.
- Reusability: Write once, call many times.
- Security: Grant permissions to execute procedures without granting direct table access.
- Reduced Network Traffic: Execute complex logic with a single call from the application.

-- Example (SQL Server/MySQL syntax, syntax varies by DB):
DELIMITER //
CREATE PROCEDURE GetCustomerOrders (IN customerID INT)
BEGIN
    SELECT *
    FROM orders
    WHERE customer_id = customerID;
END //
DELIMITER ;

-- How to call it:
CALL GetCustomerOrders(101);

2.3 Triggers: Automating Actions on Data Changes

Photo by Markus Winkler on Pexels

Triggers are special stored procedures that automatically execute when a specified event occurs on a table (e.g., INSERT, UPDATE, DELETE). They're powerful for enforcing complex business rules, maintaining audit trails, or synchronizing data.

Events can be BEFORE or AFTER the actual data modification.
- BEFORE triggers are good for data validation or modification before the change is committed.
- AFTER triggers are good for auditing or propagating changes after the change has occurred.

-- Example (PostgreSQL syntax, syntax varies by DB):
-- First, define a function that will be the trigger's logic
CREATE OR REPLACE FUNCTION audit_user_changes()
RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO user_audit (user_id, changed_on, action)
    VALUES (NEW.id, NOW(), TG_OP); -- TG_OP is a special variable for the operation type
    RETURN NEW; -- For BEFORE triggers, you might return NEW or OLD; for AFTER, it often doesn't matter
END;
$$ LANGUAGE plpgsql;

-- Then, create the trigger itself
CREATE TRIGGER log_user_updates
AFTER UPDATE OR INSERT ON users
FOR EACH ROW
EXECUTE FUNCTION audit_user_changes();

Here's a diagram illustrating the trigger process:

graph TD
    A["User performs SQL operation (INSERT/UPDATE/DELETE)"] --> B{Does a TRIGGER exist for this event?};
    B -- Yes --> C{Is it a 'BEFORE' trigger?};
    C -- Yes --> D["Trigger Logic (e.g., Validate data, Modify NEW row)"];
    D --> E["Database proceeds with SQL operation (e.g., actual INSERT/UPDATE)"];
    C -- No (It's 'AFTER') --> E;
    E --> F{Is it an 'AFTER' trigger?};
    F -- Yes --> G["Trigger Logic (e.g., Log changes, Update related tables)"];
    G --> H["SQL operation complete"];
    F -- No --> H;
    B -- No --> H;

3. Worked Example

Let's use a window function to find the top 3 highest-paid employees in each department.

Assume you have an employees table:
| employee_id | employee_name | department | salary |
|-------------|---------------|------------|--------|
| 1 | Alice | Sales | 70000 |
| 2 | Bob | Marketing | 65000 |
| 3 | Charlie | Sales | 80000 |
| 4 | David | Marketing | 75000 |
| 5 | Eve | Sales | 72000 |
| 6 | Frank | HR | 60000 |
| 7 | Grace | Marketing | 68000 |
| 8 | Henry | Sales | 68000 |
| 9 | Ivy | HR | 62000 |

-- Create a temporary table for demonstration
CREATE TEMPORARY TABLE employees (
    employee_id INT PRIMARY KEY,
    employee_name VARCHAR(50),
    department VARCHAR(50),
    salary DECIMAL(10, 2)
);

INSERT INTO employees (employee_id, employee_name, department, salary) VALUES
(1, 'Alice', 'Sales', 70000.00),
(2, 'Bob', 'Marketing', 65000.00),
(3, 'Charlie', 'Sales', 80000.00),
(4, 'David', 'Marketing', 75000.00),
(5, 'Eve', 'Sales', 72000.00),
(6, 'Frank', 'HR', 60000.00),
(7, 'Grace', 'Marketing', 68000.00),
(8, 'Henry', 'Sales', 68000.00),
(9, 'Ivy', 'HR', 62000.00);

-- Query to find top 3 highest-paid employees per department
WITH RankedEmployees AS (
    SELECT
        employee_id,
        employee_name,
        department,
        salary,
        DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rank_in_department
    FROM
        employees
)
SELECT
    employee_id,
    employee_name,
    department,
    salary
FROM
    RankedEmployees
WHERE
    rank_in_department <= 3
ORDER BY
    department, rank_in_department;

-- Expected Output:
-- employee_id | employee_name | department | salary
-- -------------|---------------|------------|--------
-- 9           | Ivy           | HR         | 62000.00
-- 6           | Frank         | HR         | 60000.00
-- 4           | David         | Marketing  | 75000.00
-- 7           | Grace         | Marketing  | 68000.00
-- 2           | Bob           | Marketing  | 65000.00
-- 3           | Charlie       | Sales      | 80000.00
-- 5           | Eve           | Sales      | 72000.00
-- 1           | Alice         | Sales      | 70000.00

4. Key Takeaways

• Window functions let you perform calculations over a set of related rows without collapsing them, great for rankings and running totals.
• Stored procedures encapsulate SQL logic for reuse, boosting performance, security, and maintainability.
• Triggers automate actions on data modification events, enforcing complex business rules and auditing data changes.
• PARTITION BY and ORDER BY are crucial for defining the scope and sequence of window function calculations.
• Use BEFORE triggers for validation/modification before a change and AFTER triggers for logging/propagating changes.
• Advanced SQL features move significant application logic directly into the database, where it can often run more efficiently.

Common Mistakes to Avoid:
- Forgetting PARTITION BY in a window function, causing the function to operate over the entire result set.
- Overusing triggers for simple constraints that could be handled by CHECK constraints or foreign keys, which are often more efficient.
- Not handling errors or transactions properly within stored procedures, leading to inconsistent data.
- Writing overly complex stored procedures or triggers that are hard to debug and maintain.

5. Now Try It

Create a products table with columns product_id, product_name, category, and price. Insert at least 5 products across 2-3 categories. Then, write a SQL query using a window function that calculates the percentage of each product's price relative to the average price of products within its own category.

What success looks like: Your query returns the product details along with a new column showing the percentage (e.g., a product costing $120 in a category with an average price of $100 would show 120%).