Unit 4: SQL for Data Analysis

Structured Query Language (SQL) is the foundational declarative language for interacting with relational database management systems (RDBMS). Formulated by Edgar F. Codd in 1970, the relational model mathematically abstracts data storage into formal relations, liberating analytical queries from low-level physical file-storage mechanics.

Relational Model Terminology

• Table (Relation): A structured, two-dimensional matrix containing a collection of related records conforming to a common schema.
• Row (Tuple / Record): A single instance representing a real-world entity containing an attribute value for each defined column.
• Column (Attribute / Field): A typed vertical entity representing a specific measurable characteristic across all records.
• Schema: The formal blueprint defining table structures, column datatypes, primary keys, and cross-table foreign key relationships.

Primary Keys, Foreign Keys & Referential Integrity

    TABLE: students (Primary Entity)                 TABLE: enrollments (Foreign Junction Entity)
+---------------+---------------------+           +---------------+--------------------+
| student_id    | PK, INT, AUTO_INC   |<--+       | enrollment_id | PK, INT, AUTO_INC  |
| full_name     | VARCHAR(100)        |   |       | student_id    | FK, INT (Ref)      |----+
| email         | VARCHAR(150), UNIQUE|   +=======| course_id     | FK, INT (Ref)      |--+ |
| registered_at | TIMESTAMP           |           | enrolled_at   | TIMESTAMP          |  | |
+---------------+---------------------+           +---------------+--------------------+  | |
                                                                                          | |
                                                  TABLE: courses (Referenced Entity)      | |
                                              +---------------+--------------------+      | |
                                              | course_id     | PK, INT, AUTO_INC  |<=====+ |
                                              | title         | VARCHAR(100)       |        |
                                              | price_paise   | INT                |        |
                                              +---------------+--------------------+        |
                                                                                            |
                                      Referential Constraint: An enrollment record CANNOT   |
                                      exist with a student_id that does not exist in students<--+
        

• Primary Key (PK): A column (or minimal combination of columns) whose values uniquely identify every row in a table. It strictly enforces Entity Integrity: values must be unique and can never contain NULL.
• Foreign Key (FK): A column in one table that references the Primary Key of another table, enforcing Referential Integrity. If a parent record is deleted, foreign key constraints govern whether child records are deleted automatically (ON DELETE CASCADE), set to null (ON DELETE SET NULL), or strictly forbidden from deletion (ON DELETE RESTRICT).

SQL Environments: PostgreSQL vs. MySQL

Two database engines dominate modern technology infrastructure:

• PostgreSQL: An advanced, standards-compliant object-relational database known for powerful analytical window functions, native JSONB document support, rich concurrency (MVCC), and complex statistical query capabilities.
• MySQL: A fast, battle-tested relational database engine prevalent in high-read web applications and standard content platforms.

Standard SQL Data Types

SQL operations are divided into functional sub-languages. The two most fundamental are Data Definition Language (DDL), which defines table architecture, and Data Manipulation Language (DML), which inserts, modifies, and deletes records.

-- 1. Create Students Table
CREATE TABLE students (
    student_id SERIAL PRIMARY KEY,
    full_name VARCHAR(100) NOT NULL,
    email VARCHAR(150) UNIQUE NOT NULL,
    graduation_year INT CHECK (graduation_year >= 2020),
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- 2. Create Enrollments Table with Foreign Key Integrity
CREATE TABLE enrollments (
    enrollment_id SERIAL PRIMARY KEY,
    student_id INT NOT NULL REFERENCES students(student_id) ON DELETE CASCADE,
    course_name VARCHAR(100) NOT NULL,
    amount_paid DECIMAL(10, 2) CHECK (amount_paid >= 0),
    enrolled_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- INSERT: Populate records
INSERT INTO students (full_name, email, graduation_year)
VALUES
    ('Aarav Sharma', 'aarav@example.com', 2025),
    ('Diya Patel', 'diya@example.com', 2026);

-- UPDATE: Modify records safely with a targeted WHERE clause
UPDATE students
SET is_active = FALSE
WHERE graduation_year < 2024;

-- DELETE: Remove targeted records
DELETE FROM enrollments
WHERE amount_paid = 0.00;

One of the most critical concepts in SQL data analysis is understanding that the syntactic written order of a query does not match the logical execution order processed by the database query optimizer.

WRITTEN SYNTAX (Lexical Order):              DATABASE EXECUTION PIPELINE (Logical Order):
1. SELECT column1, AGG(column2)              1. FROM table_name           (Locate base table)
2. FROM table_name                           2. JOIN other_table ON cond  (Join & build cartesian set)
3. JOIN other_table ON condition             3. WHERE filtering_condition (Filter raw input rows)
4. WHERE row_condition                       4. GROUP BY column_name      (Collapse into grouped rows)
5. GROUP BY column_name                      5. HAVING group_condition    (Filter grouped aggregations)
6. HAVING group_condition                    6. SELECT expressions/window (Compute selected columns)
7. ORDER BY sort_column                      7. DISTINCT                  (Deduplicate identical rows)
8. LIMIT n;                                  8. ORDER BY sort_column      (Sort resulting rows)
                                             9. LIMIT / OFFSET n;         (Slice final page)

CRITICAL TAKEAWAY: You CANNOT use column aliases defined in SELECT inside the WHERE clause
because WHERE executes at Step 3, long before SELECT evaluates at Step 6!
        

Filtering with WHERE

The WHERE clause filters rows before any grouping or aggregation takes place. It supports rich logical combinations:

• Comparison Operators: =, != (or <>), <, >, <=, >=.
• Range & Set Membership: BETWEEN low AND high (inclusive), IN ('val1', 'val2').
• Pattern Matching: LIKE (case-sensitive) and ILIKE (case-insensitive in Postgres). The % wildcard matches zero or more characters; _ matches exactly one character.
• Handling NULLs: In SQL three-valued logic (TRUE, FALSE, UNKNOWN), comparing anything with NULL via = NULL yields UNKNOWN. Always use IS NULL or IS NOT NULL.

Sorting with ORDER BY

Relational tables are unordered sets by definition. Unless an explicit ORDER BY clause is appended, the database returns rows in arbitrary physical storage order. You can sort by multiple columns, toggle ASC (default) or DESC, and explicitly control null placement with NULLS FIRST or NULLS LAST.

Aggregation transforms granular transaction rows into high-level business intelligence metrics by summarizing multiple values into a single scalar result.

The Five Standard Aggregate Functions

WHERE vs. HAVING: The Definitive Distinction

SELECT
    course_name,
    COUNT(student_id) AS total_students,
    AVG(amount_paid) AS avg_revenue_per_student,
    SUM(amount_paid) AS total_course_revenue
FROM enrollments
-- WHERE filters RAW INDIVIDUAL ROWS before aggregation
WHERE enrolled_at >= '2026-01-01'
GROUP BY course_name
-- HAVING filters AGGREGATED METRICS after grouping
HAVING COUNT(student_id) >= 10 AND SUM(amount_paid) > 50000.00
ORDER BY total_course_revenue DESC;

Relational databases normalize data across separate tables to prevent data redundancy. Joins combine columns from two or more tables based on a shared relational predicate (usually primary key to foreign key matches).

    TABLE A (Left Table)                 TABLE B (Right Table)

1. INNER JOIN (Intersection):
   (       [A & B]       )  ===> Returns ONLY rows where matching keys exist in BOTH tables.
                                Unmatched rows from both Table A and Table B are discarded.

2. LEFT OUTER JOIN:
   ( [All A] [A & B]     )  ===> Returns ALL rows from Table A.
                                If a match exists in Table B, appends columns; otherwise fills NULL.

3. RIGHT OUTER JOIN:
   (     [A & B] [All B] )  ===> Returns ALL rows from Table B.
                                If a match exists in Table A, appends columns; otherwise fills NULL.

4. FULL OUTER JOIN:
   ( [All A] [A & B] [All B] ) ===> Complete Union: Returns all rows from both tables.
                                Unmatched attributes on either side are populated with NULL.
        

SELECT
    s.student_id,
    s.full_name,
    s.email,
    c.title AS course_title,
    e.amount_paid,
    e.enrolled_at
FROM students s
-- LEFT JOIN preserves all students even if they haven't enrolled yet
LEFT JOIN enrollments e ON s.student_id = e.student_id
-- INNER JOIN pulls course details only for valid enrollments
INNER JOIN courses c ON e.course_id = c.course_id
WHERE s.is_active = TRUE;

SELECT s.student_id, s.full_name
FROM students s
LEFT JOIN enrollments e ON s.student_id = e.student_id
WHERE e.enrollment_id IS NULL;

Complex analytical transformations require multi-step decomposition. SQL enables modular query design via Subqueries and Common Table Expressions (CTEs).

1. Subqueries

A subquery is an inner SQL query nested inside a larger outer query. Subqueries can return:

• Scalar Subqueries: Returns exactly one single value (1 row, 1 column), valid inside SELECT expressions or comparison filters: WHERE salary > (SELECT AVG(salary) FROM employees).
• Multi-Row Subqueries: Evaluated using set operators: IN, ANY, or ALL.
• Correlated Subqueries: An inner subquery that references a column from the outer query for each evaluated row, executing repeatedly.

2. Common Table Expressions (The WITH Clause)

A Common Table Expression (CTE) defines a temporary named result set that exists only during the execution scope of a single query. CTEs drastically improve readability, eliminate duplicate subqueries, and can be chained sequentially:

-- Define stage 1: Aggregate spending per student
WITH student_spending AS (
    SELECT
        student_id,
        COUNT(enrollment_id) AS total_enrollments,
        SUM(amount_paid) AS total_spent
    FROM enrollments
    GROUP BY student_id
),
-- Define stage 2: Calculate global cohort benchmarks
benchmark AS (
    SELECT AVG(total_spent) AS avg_cohort_spend
    FROM student_spending
)
-- Final consumption query joining both CTEs
SELECT
    s.student_id,
    s.full_name,
    sp.total_enrollments,
    sp.total_spent,
    b.avg_cohort_spend,
    CASE
        WHEN sp.total_spent >= (b.avg_cohort_spend * 1.5) THEN 'High-Value Customer'
        ELSE 'Standard Customer'
    END AS customer_segment
FROM students s
INNER JOIN student_spending sp ON s.student_id = sp.student_id
CROSS JOIN benchmark b
ORDER BY sp.total_spent DESC;

In traditional SQL, a GROUP BY clause collapses individual rows into a single aggregated summary row. Window Functions perform calculations across a set of table rows related to the current row, while retaining the unique identity and granularity of every individual row.

The OVER Clause Anatomy

Syntax: FUNCTION() OVER (PARTITION BY partition_col ORDER BY sort_col ROWS frame_clause)

• PARTITION BY: Divides rows into isolated groups (similar to GROUP BY, but does not collapse rows).
• ORDER BY: Establishes logical ordering of rows inside each partition.
• ROWS BETWEEN: Defines the physical sliding frame of preceding and following rows to include.

TABLE ROWS (Order Stream):
Date        Region    Revenue    Partitioning Window: PARTITION BY Region ORDER BY Date
-----------------------------------------------------------------------------------------
Oct 01      North     $100       Partition "North" [Row 1] ===> Running Total: $100
Oct 02      North     $250       Partition "North" [Row 2] ===> Running Total: $350
Oct 03      North     $150       Partition "North" [Row 3] ===> Running Total: $500
--------------------------------- Boundary resets for new partition!
Oct 01      South     $400       Partition "South" [Row 1] ===> Running Total: $400
Oct 02      South     $300       Partition "South" [Row 2] ===> Running Total: $700
        

Ranking Functions: ROW_NUMBER, RANK & DENSE_RANK

Navigation & Running Metrics: LAG, LEAD & Rolling Averages

SELECT
    transaction_date,
    daily_revenue,
    -- 1. Running Total across all time
    SUM(daily_revenue) OVER (
        ORDER BY transaction_date
    ) AS cumulative_revenue,

    -- 2. 7-Day Centered Moving Average
    AVG(daily_revenue) OVER (
        ORDER BY transaction_date
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) AS rolling_7day_avg,

    -- 3. Previous Day Revenue via LAG for Month-over-Month analysis
    LAG(daily_revenue, 1) OVER (
        ORDER BY transaction_date
    ) AS prior_day_revenue
FROM daily_sales;

Building scalable analytics infrastructure requires writing queries that are maintainable, performant, and resilient against data quality corruption.

Views vs. Materialized Views

• Standard Views (CREATE VIEW): A saved virtual query. It stores no physical data on disk. Every time the view is queried, the database dynamically executes the underlying SQL statement. Useful for encapsulating complex join logic and enforcing row/column security access.
• Materialized Views (CREATE MATERIALIZED VIEW): Executes the query once and persists the physical result set on disk like a real table. Read queries are instantaneous. Must be periodically refreshed via batch commands (e.g., REFRESH MATERIALIZED VIEW). Ideal for expensive aggregate reporting dashboards updated once daily.

Database Indexes: B-Tree Performance Mechanics

Without an index, finding a specific record requires a Full Table Scan: the database must read every single block on disk from start to finish, an order of n operation.

A B-Tree (Balanced Tree) Index creates a self-balancing sorted tree data structure that allows the query engine to pinpoint rows in order of log n time:

-- Single Column Index for fast foreign key lookups
CREATE INDEX idx_enrollments_student_id ON enrollments(student_id);

-- Composite Index optimized for filtered sorting
-- Follows Leftmost Prefix Rule: Accelerates queries filtering on status OR (status AND created_at)
CREATE INDEX idx_students_status_created ON students(is_active, created_at);

Production SQL Data Cleansing Patterns

Data analysts spend substantial time remediating data entry errors and duplicate payloads directly in SQL:

-- 1. Standardizing Strings & Imputing Null Values
SELECT
    student_id,
    -- Trim whitespace and force lowercase email
    LOWER(TRIM(email)) AS clean_email,
    -- Coalesce fallback for missing phone numbers
    COALESCE(phone_number, 'Unknown') AS contact_phone,
    -- Convert string timestamps to native dates
    CAST(registration_date AS DATE) AS valid_reg_date
FROM raw_student_leads;

-- 2. Deduplication Pattern using CTE & ROW_NUMBER
WITH ranked_leads AS (
    SELECT
        lead_id,
        email,
        phone,
        created_at,
        ROW_NUMBER() OVER (
            PARTITION BY email 
            ORDER BY created_at DESC -- Keep the latest lead
        ) AS row_rank
    FROM raw_student_leads
)
SELECT lead_id, email, phone, created_at
FROM ranked_leads
WHERE row_rank = 1; -- Strips all duplicate submissions cleanly