Relational Databases & SQL

The Language of Structured Logic

To analyze data at a global scale, you must first master the language of **Relational Databases (RDBMS)**. Whether it's **Visa** storing billions of transaction records or **Walmart** managing a decentralized inventory, SQL is the universal tool for extracting, grouping, and transforming raw tables into meaningful insights.

In this chapter, we move beyond simple spreadsheets to explore **Primary Keys**, **Normalization**, and the powerful **Window Functions** that allow us to calculate running totals and rankings in real-time across massive datasets.

Relational Concepts & Normalization

For **Visa**, data integrity is non-negotiable. They use **Normalization** to ensure that a merchant's name is stored in exactly one place, linked via a **Foreign Key** to millions of individual transactions. This prevents "Update Anomalies"—where changing a merchant's address in one record doesn't reflect in others.

code Interactive Scenario

# Visa Database Architecture: Schema Enforcement
class SchemaArchitect:
    def __init__(self):
        # Normalized Tables: Merchants vs. Transactions
        self.merchants = {101: "Luxe Tokyo", 102: "Neo Dubai"}
        self.transactions = [
            {"id": "tx_001", "merchant_id": 101, "amount": 250},
            {"id": "tx_002", "merchant_id": 101, "amount": 50},
            {"id": "tx_003", "merchant_id": 101, "amount": 120},
            {"id": "tx_004", "merchant_id": 102, "amount": 1200}
        ]

def query_merchant_totals(self):
        print("SQL Simulation: SELECT MerchantName, SUM(Amount) FROM Transactions...")
        results = {}
        for tx in self.transactions:
            m_name = self.merchants[tx['merchant_id']]
            results[m_name] = results.get(m_name, 0) + tx['amount']
        
        for name, total in sorted(results.items()):
            print(" - {0}: 1".format(name, total))

# Scenario: Auditing merchant sales
architect = SchemaArchitect()
architect.query_merchant_totals()

Basic Querying: SELECT to HAVING

At **Walmart**, analysts use SQL to filter out the noise. The **WHERE** clause filters data before it is grouped, while the **HAVING** clause filters the results *after* grouping. This allows a logistics manager to ask: *"Show me only the warehouses that have an average inventory level GREATER than 10,000 units."*

code Interactive Scenario

# Walmart Logistics: Filtering with HAVING
class InventoryQueries:
    def __init__(self, stock_reports):
        self.data = stock_reports # List of (Warehouse, Stock)

def filter_critical_hubs(self, min_threshold):
        # Group By Simulation
        grouped = {}
        for hub, stock in self.data:
            grouped[hub] = grouped.get(hub, 0) + stock
            
        print("SQL Logic: SELECT Hub, SUM(Stock) FROM Inventory GROUP BY Hub HAVING SUM(Stock) > {0}".format(min_threshold))
        
        for hub in sorted(grouped.keys()):
            total = grouped[hub]
            if total > min_threshold:
                print(" - ALERT: {0} exceeds capacity (Total: {1} units)".format(hub, total))
            else:
                print(" - {0}: OK ({1} units)".format(hub, total))

# Scenario: Identifying overstocked regions
stock_reports = [("Jaipur_01", 12000), ("Berlin_01", 8000), ("Delhi_01", 15000), ("Jaipur_01", 500)]
analyst = InventoryQueries(stock_reports)
analyst.filter_critical_hubs(min_threshold=10000)

Advanced: Window Functions & CTEs

Modern SQL uses **Window Functions** (like `PARTITION BY`) to perform calculations across a set of rows related to the current row. For **Visa**, this is essential for "Running Totals"—calculating a user's total spending *as each transaction arrives* without collapsing the rows into a single sum.

code Interactive Scenario

The 20 Essential SQL Commands (Part 1: DDL & DML)

Every data analyst must know these 20 SQL commands by heart. Part 1 covers **Data Definition Language (DDL)** commands like CREATE, ALTER, DROP, and TRUNCATE, plus **Data Manipulation Language (DML)** commands like INSERT, UPDATE, DELETE, and MERGE. For **Walmart**, these are used to build and maintain the schema of a global inventory system.

code Interactive Scenario

# Walmart & Visa: 20 Essential SQL Commands (Part 1)
class SQLCommandReference:
    def __init__(self):
        self.commands = []

def demonstrate(self):
        cmds = [
            ("1. CREATE TABLE", "CREATE TABLE warehouses (id INT PRIMARY KEY, name VARCHAR(100), region VARCHAR(50));", "Walmart: Define new inventory tables"),
            ("2. ALTER TABLE", "ALTER TABLE warehouses ADD COLUMN capacity INT DEFAULT 0;", "Walmart: Add capacity tracking"),
            ("3. DROP TABLE", "DROP TABLE IF EXISTS temp_imports;", "Clean up temporary staging tables"),
            ("4. TRUNCATE", "TRUNCATE TABLE daily_sensor_logs;", "Walmart: Clear yesterday's IoT data"),
            ("5. INSERT INTO", "INSERT INTO transactions (id, amount, merchant) VALUES (1, 250, 'Amazon');", "Visa: Record a new transaction"),
            ("6. UPDATE", "UPDATE warehouses SET capacity = 50000 WHERE name = 'Jaipur_01';", "Walmart: Update hub capacity"),
            ("7. DELETE", "DELETE FROM transactions WHERE status = 'cancelled';", "Visa: Remove voided transactions"),
            ("8. MERGE (UPSERT)", "MERGE INTO inventory USING staging ON inv.sku = stg.sku WHEN MATCHED THEN UPDATE ...;", "Walmart: Sync daily inventory feeds"),
            ("9. SELECT", "SELECT merchant, amount FROM transactions WHERE amount > 500;", "Visa: Find high-value transactions"),
            ("10. DISTINCT", "SELECT DISTINCT region FROM warehouses;", "Walmart: List all unique regions")
        ]
        
        print("SQL COMMAND REFERENCE (Part 1: DDL & DML):")
        print("=" * 55)
        for name, syntax, scenario in cmds:
            print("{0}".format(name))
            print("  SQL: {0}".format(syntax))
            print("  Use: {0}".format(scenario))
            print("")

ref = SQLCommandReference()
ref.demonstrate()

The 20 Essential SQL Commands (Part 2: Querying & Analytics)

Part 2 covers the analytical powerhouses: **WHERE**, **GROUP BY**, **ORDER BY**, **HAVING**, **JOIN** variants, **UNION**, **CASE WHEN**, **COALESCE**, **RANK()**, and **PARTITION BY**. These are the commands that turn raw tables into executive-level insights at **Visa** and **Netflix**.

code Interactive Scenario

# Visa & Netflix: 20 Essential SQL Commands (Part 2)
class SQLAnalyticsReference:
    def __init__(self):
        pass

def demonstrate(self):
        cmds = [
            ("11. WHERE", "SELECT * FROM transactions WHERE amount > 1000;", "Visa: Filter high-risk transactions"),
            ("12. GROUP BY", "SELECT merchant, COUNT(*) FROM transactions GROUP BY merchant;", "Visa: Count transactions per merchant"),
            ("13. ORDER BY", "SELECT * FROM transactions ORDER BY amount DESC;", "Visa: Rank largest transactions"),
            ("14. HAVING", "SELECT merchant, SUM(amount) FROM tx GROUP BY merchant HAVING SUM(amount) > 10000;", "Visa: Only merchants with >$10K revenue"),
            ("15. INNER JOIN", "SELECT t.amount, m.name FROM transactions t JOIN merchants m ON t.merchant_id = m.id;", "Visa: Combine transaction + merchant data"),
            ("16. LEFT JOIN", "SELECT u.name, w.title FROM users u LEFT JOIN watch_history w ON u.id = w.user_id;", "Netflix: Find users who haven't watched"),
            ("17. UNION", "SELECT name FROM visa_merchants UNION SELECT name FROM mc_merchants;", "Combined merchant directory"),
            ("18. CASE WHEN", "SELECT amount, CASE WHEN amount > 500 THEN 'HIGH' ELSE 'LOW' END AS risk FROM transactions;", "Visa: Flag risk levels inline"),
            ("19. COALESCE", "SELECT COALESCE(preferred_name, email, 'Anonymous') AS display FROM users;", "Netflix: Safe null handling for display"),
            ("20. RANK() OVER", "SELECT title, views, RANK() OVER (ORDER BY views DESC) AS rank FROM shows;", "Netflix: Top trending content")
        ]
        
        print("SQL COMMAND REFERENCE (Part 2: Analytics):")
        print("=" * 55)
        for name, syntax, scenario in cmds:
            print("{0}".format(name))
            print("  SQL: {0}".format(syntax))
            print("  Use: {0}".format(scenario))
            print("")

ref = SQLAnalyticsReference()
ref.demonstrate()

Practice Questions

Question 1

What is the primary difference between the WHERE and HAVING clauses in SQL?

Question 2

In the Visa running total example, why is a Window Function (OVER) superior to a simple SUM()?