Cross‐Platform SQL Programming: A Unified Guide for Modern Data Engineers

Master Core Concepts with Implementations in T-SQL, PL/SQL, Python, and MySQL

Chapter 1: Introduction to SQL and Relational Databases

Cross-Platform SQL Programming: A Unified Guide for Modern Data Engineers

In This Chapter:
We lay the foundation for cross-platform data engineering by exploring what relational databases are, why SQL remains indispensable in coming years, and how procedural dialects (T-SQL, PL/SQL) extend SQL for real-world systems. You’ll set up local/cloud environments and write your first query across all four platforms — with attention to U.S. constraints like SOC 2, FedRAMP, and hybrid-cloud realities.

What is a Relational Database?

Theoretical Concept

A relational database stores data in tables (relations), where each table consists of rows (tuples) and columns (attributes). Relationships between tables are enforced via foreign keys, and data integrity is guaranteed by constraints (PK, FK, CHECK, etc.).
The "relational" part comes from relationships between tables, linked by keys – like how a customer's ID connects to their orders. This model, invented by Edgar F. Codd in 1970 at IBM (a U.S. powerhouse!), ensures data is consistent, non-redundant, and easy to query. Why fantastic? It powers 80% of U.S. enterprise systems, from Amazon's inventory to government's tax records, handling petabytes without breaking a sweat.

The relational model, defined by E.F. Codd in 1970, enforces:

Atomicity: Each cell holds a single value (1NF).
Consistency: Rules (e.g., CHECK Price > 0) prevent invalid states.
Set-based operations: SELECT, JOIN, GROUP BY operate on sets, not loops.

U.S. Context:
Despite NoSQL growth, >85% of mission-critical U.S. systems (healthcare, finance, government) still run on relational engines:

VA Hospitals: Oracle + Epic Cogito (FHIR/HL7 over SQL)

JPMorgan Chase: SQL Server + Oracle hybrid (SOX-compliant reporting)

IRS Modernization: MySQL on AWS GovCloud (FedRAMP High)

Why? ACID compliance, auditability, and decades of operational maturity.

SQL vs. Procedural Extensions (T-SQL, PL/SQL)

Theoretical Concept

SQL (Structured Query Language) is declarative: You specify what you want (SELECT * FROM Orders), not how to get it.
Procedural extensions add imperative control:
- T-SQL (Transact-SQL): Microsoft’s dialect (SQL Server/Azure SQL)
- PL/SQL (Procedural Language/SQL): Oracle’s dialect
- MySQL supports limited procedural logic via stored routines
- Python fills the “orchestration layer” — binding SQL to pipelines, APIs, and cloud services.

Feature	SQL (Standard)	T-SQL	PL/SQL	Python Role
Loops	❌	✅ `WHILE`	✅ `LOOP`	✅ `for`, `while`
Error Handling	❌	✅ `TRY/CATCH`	✅ `EXCEPTION`	✅ `try/except`
Variables	❌	✅ `@var`	✅ `v_var`	✅ native variables
Transaction Control	✅ `BEGIN/COMMIT`	✅ `BEGIN TRAN`	❌ (implicit)	✅ `.begin()` in SQLAlchemy

Best Practice:
Push set-based logic into SQL (joins, window functions); use Python for orchestration, retry logic, and integration.

Setting Up Development Environments

Local & Cloud Options (2025 U.S. Standards)

Platform	Local (Free)	Cloud (Production-Ready)	U.S. Compliance Notes
SQL Server	SQL Server Developer Ed + SSMS	Azure SQL Managed Instance	HIPAA BAA, FedRAMP High via Azure Gov
Oracle	Oracle Database 23c Free	Oracle Autonomous Database	FIPS 140-2, SOC 2 Type II
MySQL	MySQL Community Server	Amazon RDS for MySQL / Aurora	Encrypt at rest (KMS), audit logs
Python	`pip install sqlalchemy pandas pymysql pyodbc cx_oracle`	AWS Lambda / Azure Functions	Use IAM roles, never hardcode creds

✅ Quick Start: Docker Compose (All-in-One Dev Stack)

# docker-compose.yml
version: '3.8'
services:
  sqlserver:
    image: mcr.microsoft.com/mssql/server:2022-latest
    environment:
      ACCEPT_EULA: Y
      SA_PASSWORD: StrongP@ss123!
    ports: ["1433:1433"]

  oracle:
    image: container-registry.oracle.com/database/free:23.5
    ports: ["1521:1521"]
    environment:
      ORACLE_PWD: StrongP@ss123!

  mysql:
    image: mysql:8.0
    environment:
      MYSQL_ROOT_PASSWORD: StrongP@ss123!
    ports: ["3306:3306"]

  notebook:
    image: python:3.11-slim
    ports: ["8888:8888"]
    command: >
      bash -c "pip install pandas sqlalchemy pymysql pyodbc &&
               jupyter notebook --ip=0.0.0.0 --no-browser --allow-root"

Security Note:
For U.S. healthcare/finance work, never use default passwords in CI/CD. Use secrets managers (HashiCorp Vault, AWS Secrets Manager).

Syntax and Examples

As a beginner, you usually do not create the database yourself. However, for reference, the following examples show how professionals create databases in different technologies.

✅ 1. T-SQL (SQL Server)

CREATE DATABASE ECommerceDB;
GO

-- Switch to the new database
USE ECommerceDB;
GO

✅ 2. PL/SQL (Oracle)

-- Oracle uses schemas (users) instead of separate application databases
CREATE USER ecommerce IDENTIFIED BY securepass2025;
GRANT CREATE SESSION, CREATE TABLE TO ecommerce;

-- Connect as user `ecommerce` and begin working
-- Connect as user `ecommerce` and begin working

✅ 3. Python (with SQLAlchemy Core)

from sqlalchemy import create_engine

engine = create_engine(
    "mssql+pyodbc://user:password@server/database?driver=ODBC+Driver+17+for+SQL+Server"
)

engine.execute("CREATE DATABASE ECommerceDB")

✅ 4. MySQL

CREATE DATABASE ECommerceDB
CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci;

-- Switch to the database
USE ECommerceDB;

Simple Search

Requirement:
List all active products with a price greater than $100.

✅ 1. T-SQL (SQL Server — Azure SQL Optimized)

-- ✅ SOC 2: Use parameterized queries to prevent SQLi
-- ✅ HIPAA: Exclude test data via explicit filter
DECLARE @MinPrice DECIMAL(10,2) = 100.00;
SELECT ProductID, Name, Price
FROM Products
WHERE Price > @MinPrice
  AND IsActive = 1
  AND IsTestRecord = 0;  -- Critical for audit compliance

✅ 2. PL/SQL (Oracle — Autonomous DB)

-- ✅ FedRAMP: Use bind variables (not concatenation)
SELECT ProductID, Name, Price
FROM Products
WHERE Price > :min_price
  AND IsActive = 1
  AND NVL(IsTestRecord, 0) = 0;  -- Oracle: NULL-safe

✅ 3. Python (SQLAlchemy + Pandas — Cloud-Ready)

import sqlite3

# Connect to the database (example: SQLite)
# Replace 'ecommerce.db' with your actual database file
connection = sqlite3.connect("ecommerce.db")
cursor = connection.cursor()

# Simple search query:
# List all active products with price greater than $100
query = """
SELECT product_name, price
FROM Products
WHERE status = 'Active'
  AND price > 100;
"""

cursor.execute(query)

# Fetch and display results
results = cursor.fetchall()
for product_name, price in results:
    print(f"Product: {product_name}, Price: ${price}")

# Close connection
connection.close()

✅ 4. MySQL (RDS Optimized)

import mysql.connector

# Connect to MySQL database
connection = mysql.connector.connect(
    host="localhost",
    user="your_username",
    password="your_password",
    database="ecommerce_db"
)

cursor = connection.cursor()

# Simple search query:
# List all active products with price greater than $100
query = """
SELECT product_name, price
FROM Products
WHERE status = 'Active'
  AND price > 100;
"""

cursor.execute(query)

# Fetch and display results
for product_name, price in cursor.fetchall():
    print(f"Product: {product_name}, Price: ${price}")

# Close connection
cursor.close()
connection.close()

Chapter Summary

Relational databases remain the backbone of U.S. critical infrastructure due to ACID, auditability, and maturity.
SQL is declarative; T-SQL/PL/SQL add imperative control; Python orchestrates.
Always use parameterized queries — SQL injection causes ~65% of U.S. data breaches (Verizon DBIR 2025).
Local dev: Use Docker; Production: Prefer managed cloud DBs (RDS, Azure SQL, Autonomous DB).
Filter test data explicitly — SOC 2 auditors check for accidental PII exposure.

Review Questions

Why is SELECT * discouraged in production U.S. systems?
How does Oracle’s handling of NULL in WHERE IsTestRecord = 0 differ from MySQL?
What IAM best practice should you follow when connecting Python to AWS RDS?
Why is COALESCE(IsTestRecord, 0) safer than IsTestRecord = 0?
Name two U.S. regulations that require immutable audit trails for data access.

Exercises

Cross-Platform “Hello World”
- Install one local DB (SQL Server, Oracle, or MySQL).
- Create a hello table with message VARCHAR(50).
- Insert 'Hello from [Platform]!'.
- Query it in SQL and Python.
Secure Connection Drill
- Configure Python to connect to MySQL using SSL (RDS CA cert).
- Verify encryption: SHOW STATUS LIKE 'Ssl_cipher';
Compliance Prep
- Add IsTestRecord TINYINT DEFAULT 0 to your Products table in all 4 platforms.
- Write a query that excludes test records — using DB-specific null-handling (ISNULL, NVL, IFNULL, COALESCE).
FAANG Interview Drill

“A query runs fast in dev but slow in prod. What 3 things do you check first?”
Draft your answer (hint: stats, plan cache, parameter sniffing).

Chapter 2: Database Design Fundamentals: From Theory to Practice

Designing robust database structures is the foundation of reliable data systems. This chapter covers how to define databases, select appropriate data types, declare tables, and enforce integrity through constraints—implemented across four major platforms.

In This Chapter:
Designing robust database structures is the foundation of reliable data systems. This chapter covers how to define databases, select appropriate data types, declare tables, and enforce integrity through constraints—implemented across four major platforms.
Designing databases is like architecting a skyscraper – get the foundation wrong, and it crumbles under U.S.-scale data loads (think Black Friday sales spikes). We'll detail the theory for beginners, with step-by-step code to create compliant, efficient structures for 2025's AI-infused world. Final Takeaway: Invest time in thoughtful database design. The hours spent analyzing relationships and normalizing data will save days of debugging, weeks of optimization, and potentially millions in system failures as your application scales from hundreds to millions of users.

Good database design is the foundation of reliable, efficient, and scalable applications. It transforms chaotic data into structured information that systems can use effectively. Below is an expanded breakdown of why design choices matter, with deeper explanations and real-world implications.

Theoretical Concept

A database is a named container for related data objects (tables, views, procedures). Creating a database allocates physical storage and sets initial configuration (collation, filegroups, character set). While all RDBMS support this operation, syntax and options differ.

Core Principles of Good Database Design

1. Eliminating Redundancy Through Normalization

The Problem with Duplication When the same data is stored in multiple places (like a customer's address in every order record), it creates several critical issues:

Update Anomalies: Changing data requires multiple updates. If a customer moves, you must update every order record instead of one customer record.
Insertion Anomalies: You cannot add a new customer without them placing an order first.
Deletion Anomalies: Deleting all orders from a customer might accidentally remove their contact information entirely.

The Normalized Solution Normalization is the process of organizing data to minimize redundancy. The example from e-commerce illustrates this:

Before: Orders(order_id, customer_name, customer_phone, product, price)
Customer "John Doe" appears 50 times with his phone number.
After: Customers(customer_id, name, phone) and Orders(order_id, customer_id, product, price)
John's phone number exists once. Changing it updates all associated orders automatically through the relationship.

Real-World Impact: A hospital patient management system stores patient details (ID, name, birthdate) separately from visit records. This ensures a patient's critical allergy information is consistent across all visits and medical departments.

2. Ensuring Uniqueness with Primary Keys

Why Names Aren't Enough Using attributes like name or email as identifiers fails in practice:

Two students can have the same name.
A person might change their name (marriage, legal change).
Data entry typos create duplicate "John Smith" entries.

The Primary Key Solution A primary key is a guaranteed unique, non-null identifier for every row:

Surrogate Keys: Artificially created (auto-incrementing ID, UUID). These have no business meaning and don't change.
Natural Keys: Based on existing data (Social Security Number, ISBN). These can change or have privacy concerns.

Real-World Example: A national library system uses ISBN (International Standard Book Number) as a primary key for books. This globally unique identifier prevents confusion between different editions or books with similar titles.

3. Enforcing Accuracy with Appropriate Data Types

The Cost of "Stringly-Typed" Data Storing everything as text (VARCHAR) is a common but costly shortcut:

Validation Burden: The application must ensure "price" text contains only numbers and decimals.
Sorting Errors: Text sorts alphabetically: "$10", "$100", "$20" (since "1" < "2").
Storage & Performance: Numeric types are stored and processed more efficiently than text.

Choosing the Right Type

DECIMAL(10,2): For precise financial calculations (prices, accounts).
INT or BIGINT: For whole numbers (quantities, IDs).
DATE/DATETIME: For dates, enabling date arithmetic and proper range queries.
BOOLEAN: For true/false flags (is_active, is_completed).

Real-World Impact: A stock trading platform using DECIMAL for share prices ensures millisecond price comparisons and calculations are mathematically correct, not subject to text conversion errors.

4. Maintaining Relationships with Foreign Keys

The Chaos of Uncontrolled References Without formal relationships, databases become untrustworthy:

An order could reference "Customer_999" who doesn't exist.
Deleting a product could leave orders pointing to nothing.
There's no single source of truth for what a valid reference is.

Referential Integrity Foreign keys create an enforced link between tables:

-- The database WILL NOT allow this if customer_id 999 doesn't exist
INSERT INTO Orders (order_id, customer_id) VALUES (1001, 999);

-- The database CAN prevent this or cascade the deletion based on rules
DELETE FROM Customers WHERE customer_id = 123;

Real-World Example: An airline reservation system uses foreign keys to ensure every booked seat is linked to both a valid flight and a valid passenger. This prevents "ghost bookings" for non-existent flights.

5. Achieving Scalability through Logical Separation

The "Kitchen Sink" Table Problem A single table trying to do everything becomes unmanageable:

Adding a new feature (e.g., user profile pictures) requires altering a massive, frequently accessed table.
Queries become complex and slow as they sift through irrelevant columns.
Table locks during updates block unrelated operations.

Modular Design for Growth Good design separates concerns into logical units:

Users Table: Authentication and core profile.
Orders Table: Transaction details.
Payments Table: Financial processing.
Reviews Table: User feedback.

This allows:

Independent scaling (the Reviews table can be on a different server).
Safer updates (changing payment logic doesn't risk user data).
Clearer security (payment processors need only access the Payments table).

Real-World Example: Facebook separates user profiles, posts, comments, and messages into different but related systems. This allows teams to develop and optimize each component independently while maintaining connections through IDs.

The Hidden Costs of Bad Design

Short-Term vs. Long-Term Tradeoffs

Bad Design often appears faster initially ("just add a column to the existing table"). Good Design requires upfront planning and analysis.

However, the long-term costs diverge dramatically:

Aspect	Bad Design (After 1 Year)	Good Design (After 1 Year)
Change Implementation	Days of work, high risk of breaking things	Hours of work, isolated impact
Query Performance	Slow, requiring constant optimization	Consistently fast with proper indexing
Data Confidence	Low; requires manual verification	High; enforced by database constraints
New Developer Onboarding	Months to understand "spaghetti" structure	Weeks to understand logical modules

Real-World Failure Case: The Phoenix Pay System

A prominent example of database design failure was Canada's Phoenix pay system (2016). Among its many issues were:

Data Redundancy: Employee information duplicated across multiple subsystems.
Poor Relationships: Payments weren't properly linked to employee records.
Inconsistent Updates: Changes to employee status didn't propagate correctly.

Result: Over 50% of government employees were paid incorrectly, some not at all, requiring a $2.2 billion fix and manual intervention for years.

Practical Steps Toward Good Design

Understand the Business Domain First
- What entities exist? (Customers, Products, Orders)
- How do they relate? (A Customer places an Order, an Order contains Products)
- What are the business rules? (An order cannot be shipped without payment)
Create an Entity-Relationship Diagram (ERD)
- Visualize tables as boxes and relationships as lines.
- Identify cardinality (one-to-many, many-to-many).
Apply Normalization Thoughtfully
- Aim for at least Third Normal Form (3NF).
- But don't over-normalize—sometimes denormalization for performance is valid.
Choose Keys Wisely
- Use surrogate keys (auto-increment IDs) for most tables.
- Keep natural keys (email, ISBN) as unique constraints, not primary keys.
Define Constraints Explicitly
- Primary keys for uniqueness.
- Foreign keys for relationships.
- Data type constraints (DECIMAL, NOT NULL, CHECK constraints).
Plan for Evolution
- Use descriptive, consistent naming (created_at not date1).
- Add audit columns early (created_at, updated_at).
- Consider future needs without over-engineering.

Design as a Foundation

Database design is not an academic exercise—it's architectural decision-making with lasting consequences. Good design pays compound interest over time: each new feature is easier to add, each query runs faster, and each data-driven insight is more trustworthy.

The difference between adding a column to a well-designed database versus a poorly designed one is the difference between hanging a picture on a solid wall versus trying to mount it on shifting sand. While both might hold initially, only one provides a reliable foundation as you build upward and outward.

Creating Databases

Syntax and Examples

✅ 1. T-SQL (SQL Server)

CREATE DATABASE UniversityDB
ON (
    NAME = 'UniversityDB_Data',
    FILENAME = 'C:\Data\UniversityDB.mdf',
    SIZE = 100MB,
    MAXSIZE = 500MB,
    FILEGROWTH = 10%
)
LOG ON (
    NAME = 'UniversityDB_Log',
    FILENAME = 'C:\Log\UniversityDB.ldf',
    SIZE = 50MB
);

✅ 2. PL/SQL (Oracle)

Note: In Oracle, database creation is typically done by DBAs using CREATE DATABASE at the instance level. Application developers usually work within existing schemas.

-- Connect as SYS and run (rarely done by app devs):
CREATE DATABASE UniversityDB
   LOGFILE GROUP 1 ('/u01/logs/redo01.log') SIZE 100M,
           GROUP 2 ('/u01/logs/redo02.log') SIZE 100M
   DATAFILE '/u01/data/system01.dbf' SIZE 325M REUSE
   SYSAUX DATAFILE '/u01/data/sysaux01.dbf' SIZE 325M REUSE
   DEFAULT TABLESPACE users
   DEFAULT TEMPORARY TABLESPACE temp
   UNDO TABLESPACE undotbs1;

In practice, developers create users/schemas:

CREATE USER university IDENTIFIED BY secure_password;
GRANT CREATE SESSION, CREATE TABLE TO university;

✅ 3. Python (with pyodbc for SQL Server / cx_Oracle for Oracle)

# SQL Server example
import pyodbc
conn = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};SERVER=localhost;Trusted_Connection=yes;')
conn.autocommit = True
cursor = conn.cursor()
cursor.execute("CREATE DATABASE UniversityDB")
cursor.close()
conn.close()

# For MySQL or PostgreSQL, use respective drivers and CREATE DATABASE syntax

✅ 4. MySQL

CREATE DATABASE UniversityDB
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

Data Types Across Platforms

Theoretical Concept

Data types define the kind of values a column can hold (text, numbers, dates). While logical categories (integer, string, datetime) are universal, specific type names, precision rules, and storage vary.

Concept	T-SQL	PL/SQL	MySQL	Python (SQLAlchemy)
Variable text	`NVARCHAR(n)`	`VARCHAR2(n)`	`VARCHAR(n)`	`String(n)`
Large text	`NVARCHAR(MAX)`	`CLOB`	`LONGTEXT`	`Text`
Integer	`INT`	`NUMBER(10)`	`INT`	`Integer`
Decimal	`DECIMAL(p,s)`	`NUMBER(p,s)`	`DECIMAL(p,s)`	`Numeric(p,s)`
Date & Time	`DATETIME2`	`TIMESTAMP`	`DATETIME`	`DateTime`
Boolean	`BIT`	`NUMBER(1)`	`BOOLEAN`	`Boolean`

Defining Tables

Theoretical Concept

A CREATE TABLE statement defines the structure of a relation: column names, data types, nullability, and optionally constraints. This is a universal DDL (Data Definition Language) operation.

Syntax and Examples

✅ 1. T-SQL (SQL Server)

CREATE TABLE Students (
    StudentID INT IDENTITY(1,1) PRIMARY KEY,
    FirstName NVARCHAR(50) NOT NULL,
    LastName NVARCHAR(50) NOT NULL,
    Email NVARCHAR(100) UNIQUE,
    DateOfBirth DATE,
    GPA DECIMAL(3,2) CHECK (GPA BETWEEN 0.0 AND 4.0),
    Enrolled BIT DEFAULT 1
);

✅ 2. PL/SQL (Oracle)

CREATE TABLE Students (
    StudentID NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    FirstName VARCHAR2(50) NOT NULL,
    LastName VARCHAR2(50) NOT NULL,
    Email VARCHAR2(100) UNIQUE,
    DateOfBirth DATE,
    GPA NUMBER(3,2) CHECK (GPA BETWEEN 0.0 AND 4.0),
    Enrolled NUMBER(1) DEFAULT 1 CHECK (Enrolled IN (0,1))
);

✅ 3. Python (SQLAlchemy ORM)

from sqlalchemy import Column, Integer, String, Date, Numeric, Boolean, CheckConstraint
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class Student(Base):
    __tablename__ = 'Students'
    StudentID = Column(Integer, primary_key=True, autoincrement=True)
    FirstName = Column(String(50), nullable=False)
    LastName = Column(String(50), nullable=False)
    Email = Column(String(100), unique=True)
    DateOfBirth = Column(Date)
    GPA = Column(Numeric(3, 2), CheckConstraint('GPA >= 0.0 AND GPA <= 4.0'))
    Enrolled = Column(Boolean, default=True)

✅ 4. MySQL

CREATE TABLE Students (
    StudentID INT AUTO_INCREMENT PRIMARY KEY,
    FirstName VARCHAR(50) NOT NULL,
    LastName VARCHAR(50) NOT NULL,
    Email VARCHAR(100) UNIQUE,
    DateOfBirth DATE,
    GPA DECIMAL(3,2) CHECK (GPA BETWEEN 0.0 AND 4.0),
    Enrolled BOOLEAN DEFAULT TRUE
);

Constraints: PRIMARY KEY, FOREIGN KEY, CHECK, UNIQUE, DEFAULT

Theoretical Concept

Constraints enforce data integrity rules at the database level. Common types include:

PRIMARY KEY: Uniquely identifies each row.
FOREIGN KEY: Enforces referential integrity.
CHECK: Validates column values against a condition.
UNIQUE: Ensures no duplicate values (allows one NULL).
DEFAULT: Provides a value when none is specified.

Syntax and Examples (FOREIGN KEY Example)

✅ 1. T-SQL

CREATE TABLE Enrollments (
    EnrollmentID INT IDENTITY PRIMARY KEY,
    StudentID INT NOT NULL,
    CourseID INT NOT NULL,
    EnrollDate DATE DEFAULT GETDATE(),
    FOREIGN KEY (StudentID) REFERENCES Students(StudentID),
    FOREIGN KEY (CourseID) REFERENCES Courses(CourseID)
);

✅ 2. PL/SQL

CREATE TABLE Enrollments (
    EnrollmentID NUMBER GENERATED AS IDENTITY PRIMARY KEY,
    StudentID NUMBER NOT NULL REFERENCES Students(StudentID),
    CourseID NUMBER NOT NULL REFERENCES Courses(CourseID),
    EnrollDate DATE DEFAULT SYSDATE
);

✅ 3. Python (SQLAlchemy Core)

from sqlalchemy import ForeignKey

enrollments = Table('Enrollments', metadata,
    Column('EnrollmentID', Integer, primary_key=True, autoincrement=True),
    Column('StudentID', Integer, ForeignKey('Students.StudentID'), nullable=False),
    Column('CourseID', Integer, ForeignKey('Courses.CourseID'), nullable=False),
    Column('EnrollDate', Date, default=func.current_date())
)

✅ 4. MySQL

CREATE TABLE Enrollments (
    EnrollmentID INT AUTO_INCREMENT PRIMARY KEY,
    StudentID INT NOT NULL,
    CourseID INT NOT NULL,
    EnrollDate DATE DEFAULT (CURRENT_DATE),
    FOREIGN KEY (StudentID) REFERENCES Students(StudentID),
    FOREIGN KEY (CourseID) REFERENCES Courses(CourseID)
);

Chapter Summary

This chapter covered the universal principles of database and table design, emphasizing how logical concepts map to platform-specific syntax. Key takeaways include:

Database creation is administrative in Oracle but routine in others.
Data types have semantic equivalents but different names.
Constraints are critical for integrity and supported across all platforms.
Python (via SQLAlchemy) provides a unified abstraction layer.

Review Questions

Why does Oracle treat database creation differently than SQL Server or MySQL?
What T-SQL type is equivalent to Oracle’s VARCHAR2(100)?
How does SQLAlchemy help standardize table creation across databases?
Why is the CHECK constraint on GPA important?
What happens if you omit PRIMARY KEY in a table definition?

Exercises

Cross-Platform Table: Create a Courses table with CourseID, Title, Credits (1–5), and Department in all four platforms.
Constraint Comparison: Implement a CHECK constraint that ensures Credits is between 1 and 5 in each dialect.
Python Abstraction: Use SQLAlchemy to define the Courses table and generate DDL for SQL Server and MySQL.

---ED000

Chapter 3: Basic Data Manipulation

In This Chapter:
Designing robust database structures is the foundation of reliable data systems. This chapter covers how to define databases, select appropriate data Data manipulation forms the core of database interaction—it's how we create, read, update, and delete information. Mastering these fundamental operations is essential for anyone working with databases. This chapter will guide you through the essential commands that form the foundation of data manipulation: SELECT, INSERT, UPDATE, DELETE, and how to handle special cases like NULL values and auto-incrementing columns.

Welcome to the Heart of Data Engineering

Welcome to the heart of data engineering action, beginners! Imagine you are the behind-the-scenes wizard at a booming U.S. company—
inserting real-time ride requests for Uber in Los Angeles, updating inventory for a Texas warehouse during the holiday rush, or deleting outdated records to stay CCPA-compliant in California.

Basic data manipulation—SELECT, INSERT, UPDATE, DELETE, handling NULL values, and auto-incrementing columns—becomes your everyday superpower.

In 2025’s explosive U.S. data landscape, where data volumes grow by 50% yearly and AI systems demand flawless inputs for training models, mastering these operations prevents costly disasters
(think millions of dollars lost due to a single bad UPDATE).

In this chapter, we explain theory gently and clearly using relatable analogies, U.S.-centric real-world examples, crystal-clear visuals, and beginner-friendly code across all major platforms.

By the end, you will manipulate data confidently and safely—ready to step into an $85K+ entry-level data role with confidence.

SELECT Statement Fundamentals

Theoretical Concept

Imagine the SELECT statement as a search bar for your database.
Just like you search “best laptops under $1000 in Florida” on an online store, SELECT helps you ask questions to your data.

For beginners, the most important thing to remember is this:

SELECT only reads data — it never changes anything.

That makes SELECT the safest SQL command to practice with.
You can explore data freely without fear of breaking the system.

Behind the scenes, SELECT works like choosing:

What to look at → columns such as name and price
Which items match your condition → rows such as sales in Florida

This is why companies like Netflix, Amazon, and major U.S. banks rely on SELECT every second —
it quickly finds useful information from massive datasets.

Syntax and Examples

✅ 1. T-SQL

SELECT StudentID, FirstName, LastName
FROM Students
WHERE Enrolled = 1;

✅ 2. PL/SQL

SELECT StudentID, FirstName, LastName
FROM Students
WHERE Enrolled = 1;

✅ 3. Python (pandas + SQLAlchemy)

import pandas as pd
df = pd.read_sql("SELECT StudentID, FirstName, LastName FROM Students WHERE Enrolled = 1", engine)

✅ 4. MySQL

SELECT StudentID, FirstName, LastName
FROM Students
WHERE Enrolled = 1;

Note: The basic SELECT syntax is nearly identical across SQL dialects.

------ ED000

INSERT, UPDATE, and DELETE

Theoretical Concept

These three commands are your data builders and editors:

INSERT adds brand-new rows
(like hiring a new employee in a Seattle tech firm)
UPDATE modifies existing rows
(giving that employee a raise)
DELETE removes rows
(offboarding an employee)

For beginners, imagine a shared Google Sheet:

INSERT → adds a new row
UPDATE → edits existing cells
DELETE → removes a row

Databases, however, are much smarter and include built-in safeguards.

From a theoretical perspective, these commands belong to DML (Data Manipulation Language) and should always be executed inside transactions.
Transactions ensure all changes succeed or none do, which is critical for systems like U.S. banking transfers.

In 2025, AI-powered monitoring systems watch these operations closely to detect anomalies—such as unusual mass DELETE operations that may signal security breaches.

⚠️ Common Beginner Mistake:
Running an UPDATE or DELETE without a WHERE clause.
This modifies every row in the table, a production nightmare that can lead to serious financial and regulatory consequences.

Why Are These Commands Vital for Engineers?

INSERT, UPDATE, and DELETE power dynamic applications, such as:

Real-time inventory updates during Black Friday
User profile changes in mobile apps
Removing expired sessions to meet GDPR and CCPA compliance

Mastering these commands ensures safe, reliable, and scalable data operations.

Syntax and Examples

✅ 1. T-SQL

-- INSERT
INSERT INTO Students (FirstName, LastName, Email)
VALUES ('Alice', 'Johnson', 'alice@example.edu');

-- UPDATE
UPDATE Students
SET GPA = 3.8
WHERE StudentID = 101;

-- DELETE
DELETE FROM Students
WHERE StudentID = 101;

✅ 2. PL/SQL

-- INSERT
INSERT INTO Students (FirstName, LastName, Email)
VALUES ('Alice', 'Johnson', 'alice@example.edu');

-- UPDATE
UPDATE Students
SET GPA = 3.8
WHERE StudentID = 101;

-- DELETE
DELETE FROM Students
WHERE StudentID = 101;

✅ 3. Python (SQLAlchemy Core)

# INSERT
engine.execute(
    students.insert().values(FirstName='Alice', LastName='Johnson', Email='alice@example.edu')
)

# UPDATE
engine.execute(
    students.update().where(students.c.StudentID == 101).values(GPA=3.8)
)

# DELETE
engine.execute(
    students.delete().where(students.c.StudentID == 101)
)

✅ 4. MySQL

-- INSERT
INSERT INTO Students (FirstName, LastName, Email)
VALUES ('Alice', 'Johnson', 'alice@example.edu');

-- UPDATE
UPDATE Students
SET GPA = 3.8
WHERE StudentID = 101;

-- DELETE
DELETE FROM Students
WHERE StudentID = 101;

Working with NULL Values

Theoretical Concept

NULL represents missing or unknown data. It is not equal to anything—not even itself. Use IS NULL or IS NOT NULL for comparisons.

Syntax and Examples

✅ 1. T-SQL

SELECT * FROM Students
WHERE DateOfBirth IS NULL;

-- Replace NULL with default
SELECT FirstName, ISNULL(GPA, 0.0) AS GPA FROM Students;

✅ 2. PL/SQL

SELECT * FROM Students
WHERE DateOfBirth IS NULL;

-- Replace NULL
SELECT FirstName, NVL(GPA, 0.0) AS GPA FROM Students;

✅ 3. Python (pandas)

# Query NULLs
df = pd.read_sql("SELECT * FROM Students WHERE DateOfBirth IS NULL", engine)

# Fill NULLs in DataFrame
df['GPA'].fillna(0.0, inplace=True)

✅ 4. MySQL

SELECT * FROM Students
WHERE DateOfBirth IS NULL;

-- Replace NULL
SELECT FirstName, IFNULL(GPA, 0.0) AS GPA FROM Students;

Auto-Incrementing Columns (IDENTITY, AUTO_INCREMENT, etc.)

Theoretical Concept

Auto-incrementing columns automatically generate unique integer values for new rows, commonly used for surrogate keys.

Syntax and Examples

✅ 1. T-SQL

CREATE TABLE Logs (
    LogID INT IDENTITY(1,1) PRIMARY KEY,
    Message NVARCHAR(255)
);

INSERT INTO Logs (Message) VALUES ('System started');
-- LogID assigned automatically

✅ 2. PL/SQL

CREATE TABLE Logs (
    LogID NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    Message VARCHAR2(255)
);

INSERT INTO Logs (Message) VALUES ('System started');

✅ 3. Python (SQLAlchemy)

class Log(Base):
    __tablename__ = 'Logs'
    LogID = Column(Integer, primary_key=True, autoincrement=True)
    Message = Column(String(255))

# Insert without LogID
session.add(Log(Message='System started'))
session.commit()

✅ 4. MySQL

CREATE TABLE Logs (
    LogID INT AUTO_INCREMENT PRIMARY KEY,
    Message VARCHAR(255)
);

INSERT INTO Logs (Message) VALUES ('System started');

Chapter Summary

This chapter covered fundamental DML operations. Key insights:

SELECT, INSERT, UPDATE, DELETE are syntactically similar across SQL dialects.
Handling NULL requires dialect-specific functions (ISNULL, NVL, IFNULL).
Auto-increment syntax differs (IDENTITY, GENERATED AS IDENTITY, AUTO_INCREMENT).
Python provides programmatic control and data transformation capabilities beyond raw SQL.

Review Questions

Why can’t you use = NULL in a WHERE clause?
What is the difference between ISNULL (T-SQL) and IFNULL (MySQL)?
How does SQLAlchemy handle auto-incrementing primary keys?
Why should DML operations be wrapped in transactions?
Can you insert a row without specifying values for IDENTITY/AUTO_INCREMENT columns?

Exercises

Cross-Platform DML: Insert 3 students, update one GPA, and delete a test record in all four platforms.
NULL Handling: Write a query that replaces NULL GPAs with 0.0 using each platform’s function.
Python Data Flow: Use Python to read student data, fill missing GPAs, and write back to the database.

Continue to Chapter 4: Filtering and Sorting Data
Return to Full Table of Contents

📊 Chapter 4: Filtering and Sorting Data

Cross-Platform SQL Programming: A Unified Guide for Modern Data Engineers

In This Chapter:
We explore how to isolate relevant data and present it meaningfully—using WHERE, LIKE, ORDER BY, and pagination constructs. You’ll see how logical filtering and deterministic sorting behave across platforms, where subtle differences cause bugs, and how to write robust, portable queries that work in SQL Server, Oracle, MySQL, and Python-driven pipelines.

WHERE Clause and Logical Operators

Theoretical Concept

The WHERE clause filters tuples (rows) based on a Boolean predicate. It supports logical operators (AND, OR, NOT), comparison operators (=, <>, <, >, <=, >=), and null-aware conditions (IS NULL, IS NOT NULL). Crucially, SQL uses three-valued logic (True, False, Unknown)—so WHERE col = NULL always fails; you must use IS NULL.

Predicate evaluation order is not guaranteed—relying on AND short-circuiting (e.g., col <> 0 AND 10/col > 1) is unsafe. Always validate assumptions with CASE or application-layer checks.

Syntax and Examples

✅ 1. T-SQL (SQL Server)

SELECT ProductID, Name, Price
FROM Products
WHERE Price > 100 
  AND CategoryID = 3 
  AND (Name LIKE '%Pro%' OR Name LIKE '%Elite%')
  AND Discontinued = 0;

✅ 2. PL/SQL (Oracle)

SELECT ProductID, Name, Price
FROM Products
WHERE Price > 100 
  AND CategoryID = 3 
  AND (Name LIKE '%Pro%' OR Name LIKE '%Elite%')
  AND Discontinued = 0;

📝 Note: Oracle treats empty string '' as NULL, so WHERE Name = '' → WHERE Name IS NULL.

✅ 3. Python (SQLAlchemy Core + pandas)

from sqlalchemy import select, and_, or_

stmt = select(products.c.ProductID, products.c.Name, products.c.Price).where(
    and_(
        products.c.Price > 100,
        products.c.CategoryID == 3,
        or_(products.c.Name.like('%Pro%'), products.c.Name.like('%Elite%')),
        products.c.Discontinued == 0
    )
)

# Execute & load as DataFrame
df = pd.read_sql(stmt, engine)

✅ 4. MySQL

SELECT ProductID, Name, Price
FROM Products
WHERE Price > 100 
  AND CategoryID = 3 
  AND (Name LIKE '%Pro%' OR Name LIKE '%Elite%')
  AND Discontinued = 0;

⚠️ Warning: MySQL’s default sql_mode may allow invalid dates or truncate strings. Use STRICT_TRANS_TABLES in production.

Pattern Matching with LIKE

Theoretical Concept

LIKE enables string pattern matching using wildcards:

% → zero or more characters
_ → exactly one character

Escape sequences are needed for literal % or _. Case sensitivity depends on collation:

MySQL: utf8mb4_0900_ai_ci = case-insensitive
SQL Server: SQL_Latin1_General_CP1_CI_AS = CI = case-insensitive
Oracle: Usually case-sensitive unless NLSSORT configured

For case-insensitive matching without relying on collation, use UPPER(col) LIKE UPPER(pattern)—but beware performance (index scans may become full scans).

Syntax and Examples

✅ 1. T-SQL (SQL Server)

-- Case-insensitive by default (in most collations)
SELECT Name 
FROM Customers 
WHERE Name LIKE 'A%'          -- Starts with 'A'
   OR Name LIKE '_ohn%'       -- Second letter 'o', third 'h', fourth 'n'
   OR Name LIKE '100!%' ESCAPE '!';  -- Literal '%' after '100'

✅ 2. PL/SQL (Oracle)

-- Typically case-sensitive → use UPPER for case-insensitive
SELECT Name 
FROM Customers 
WHERE UPPER(Name) LIKE 'A%'
   OR UPPER(Name) LIKE '_OHN%'
   OR Name LIKE '100\%' ESCAPE '\';  -- Escape with backslash

✅ 3. Python (SQLAlchemy + pandas)

# Case-insensitive pattern matching (portable)
stmt = select(customers.c.Name).where(
    customers.c.Name.op('ILIKE')('a%')  # PostgreSQL-specific
    # OR for cross-db:
    # func.upper(customers.c.Name).like('A%')
)

# For MySQL/SQL Server compatibility:
stmt = select(customers.c.Name).where(
    func.upper(customers.c.Name).like('A%')
)

✅ 4. MySQL

-- Case-insensitive by default (with *_ci collation)
SELECT Name 
FROM Customers 
WHERE Name LIKE 'A%'
   OR Name LIKE '_ohn%'
   OR Name LIKE '100!%' ESCAPE '!';

-- Force case-sensitive:
SELECT Name 
FROM Customers 
WHERE Name COLLATE utf8mb4_bin LIKE 'A%';

Sorting with ORDER BY

Theoretical Concept

ORDER BY arranges result tuples based on one or more expressions. Key points:

Sorting is stable only when a unique sort key is used (e.g., include PK).
NULLs sort first in Oracle/PostgreSQL, last in SQL Server/MySQL (configurable).
Multi-column sort: ORDER BY col1 ASC, col2 DESC.
ORDER BY is required for OFFSET-FETCH or LIMIT to be deterministic.

⚠️ Never assume natural order—even without ORDER BY, results may appear sorted due to indexes. Always specify ORDER BY for predictable output.

Syntax and Examples

✅ 1. T-SQL (SQL Server)

SELECT ProductID, Name, Price
FROM Products
ORDER BY Price DESC, Name ASC NULLS LAST;  -- SQL Server 2022+ supports NULLS LAST
-- Pre-2022: use CASE in ORDER BY to control NULL position

✅ 2. PL/SQL (Oracle)

SELECT ProductID, Name, Price
FROM Products
ORDER BY Price DESC, Name ASC NULLS LAST;  -- Oracle supports NULLS [FIRST|LAST]

✅ 3. Python (pandas for post-processing)

df = pd.read_sql("SELECT ProductID, Name, Price FROM Products", engine)
# Sort with explicit NULL handling
df_sorted = df.sort_values(
    by=['Price', 'Name'],
    ascending=[False, True],
    na_position='last'  # 'first' or 'last'
)

✅ 4. MySQL

SELECT ProductID, Name, Price
FROM Products
ORDER BY Price DESC, Name ASC;  -- NULLs sort last by default
-- To force NULLs first: ORDER BY IF(ISNULL(Name), 0, 1), Name ASC

Limiting Results (TOP, ROWNUM, LIMIT, OFFSET-FETCH)

Theoretical Concept

Pagination and sampling require limiting result sets. Four major dialects exist:

Platform	Syntax	Notes
SQL Server	`TOP n [PERCENT] [WITH TIES]` or `OFFSET n ROWS FETCH NEXT m ROWS ONLY`	`OFFSET-FETCH` requires `ORDER BY`; `TOP` does not (but unsafe without it)
Oracle	`ROWNUM <= n` (pre-12c) or `OFFSET n ROWS FETCH NEXT m ROWS ONLY` (12c+)	`ROWNUM` applied before `ORDER BY`—wrap in subquery!
MySQL	`LIMIT m OFFSET n` or `LIMIT n, m`	`LIMIT 10, 5` = skip 10, get 5
Python	Use `.head()`, `.iloc[]`, or SQL with `LIMIT`	Prefer server-side limiting for large data

⚠️ Critical: Always pair limiting clauses with ORDER BY—otherwise, results are non-deterministic.

Syntax and Examples

✅ 1. T-SQL (SQL Server)

-- Modern (SQL Server 2012+)
SELECT ProductID, Name, Price
FROM Products
WHERE CategoryID = 3
ORDER BY Price DESC
OFFSET 10 ROWS FETCH NEXT 5 ROWS ONLY;

-- Legacy (no OFFSET)
SELECT TOP 5 ProductID, Name, Price
FROM Products
WHERE CategoryID = 3
ORDER BY Price DESC;

✅ 2. PL/SQL (Oracle)

-- Oracle 12c+
SELECT ProductID, Name, Price
FROM Products
WHERE CategoryID = 3
ORDER BY Price DESC
OFFSET 10 ROWS FETCH NEXT 5 ROWS ONLY;

-- Pre-12c (subquery required for correct ordering)
SELECT * FROM (
  SELECT ProductID, Name, Price, ROWNUM rn
  FROM (
    SELECT ProductID, Name, Price
    FROM Products
    WHERE CategoryID = 3
    ORDER BY Price DESC
  )
  WHERE ROWNUM <= 15
)
WHERE rn > 10;

✅ 3. Python (SQLAlchemy + pandas)

# Server-side pagination (efficient)
stmt = select(products.c.ProductID, products.c.Name, products.c.Price
    ).where(products.c.CategoryID == 3
    ).order_by(products.c.Price.desc()
    ).offset(10).limit(5)

df = pd.read_sql(stmt, engine)

# Client-side (only for small data!)
df_full = pd.read_sql("SELECT ... ORDER BY Price DESC", engine)
df_page = df_full.iloc[10:15]

✅ 4. MySQL

SELECT ProductID, Name, Price
FROM Products
WHERE CategoryID = 3
ORDER BY Price DESC
LIMIT 5 OFFSET 10;  -- or: LIMIT 10, 5

🔍 Pro Tip: For deep pagination (e.g., page 10,000), use keyset pagination (WHERE Price < last_seen_price ORDER BY Price DESC LIMIT 10)—avoids expensive OFFSET scans.

Chapter Summary

The WHERE clause uses three-valued logic; always use IS NULL, not = NULL.
LIKE behavior depends on collation—prefer UPPER() + LIKE for portable case-insensitive matching.
ORDER BY is mandatory for deterministic results, especially with pagination.
Limiting syntax varies drastically:
- Use OFFSET-FETCH (SQL Server/Oracle 12c+) or LIMIT OFFSET (MySQL) for modern, standard pagination.
- Avoid ROWNUM and TOP without ORDER BY.
Python bridges the gap: use SQLAlchemy for cross-db queries, pandas for client-side refinement.

Review Questions

Why does WHERE status = NULL return no rows? How should it be written?
In which platforms does NULL sort first by default? How can you standardize this?
What’s the danger of using TOP 10 without ORDER BY?
Why is ROWNUM in Oracle problematic for sorted pagination?
When would you prefer keyset pagination over OFFSET?

Exercises

Cross-Platform Filter & Sort:
Write a query to get the 3 most expensive discontinued products, sorted by price (desc) then name (asc), in all four platforms. Handle NULL prices sensibly.
Case-Insensitive Search:
Find all customers whose name contains 'smith' (case-insensitive) in MySQL, SQL Server, and Oracle—without changing database collation.
Python Pagination Utility:
Create a Python function get_products_page(category_id, page=1, size=10) that returns a DataFrame for the requested page, using server-side LIMIT/OFFSET or OFFSET-FETCH.
Debug This!:
The query below returns inconsistent results in SQL Server. Explain why—and fix it.
```
SELECT TOP 10 Name, HireDate FROM Employees;
```
Advanced Pattern Matching:
In MySQL, write a LIKE pattern to match emails ending in @university.edu or @college.edu, escaping the @ and . literally.

✅ Next Chapter Preview: Chapter 5 dives into aggregation and grouping—how GROUP BY interacts with NULLs, ROLLUP, and window functions across platforms.

📊 Chapter 5: Advanced Querying

Cross-Platform SQL Programming: A Unified Guide for Modern Data Engineers

In This Chapter:
We move beyond row selection to summarization and grouping—using GROUP BY, aggregations, and set operations. You’ll learn how NULL behaves in groups, how HAVING filters after aggregation, and why DISTINCT isn’t always the answer. Plus: cross-platform quirks in COUNT(*) vs COUNT(col), and when grouping causes silent data loss.

Aggregate Functions: COUNT, SUM, AVG, MIN, MAX

Theoretical Concept

Aggregate functions operate on a set of values and return a single result. Key behaviors:

COUNT(*) counts rows (including NULLs); COUNT(col) counts non-NULL values in col.
SUM, AVG, MIN, MAX ignore NULLs (not treated as 0!).
AVG(col) = SUM(col) / COUNT(col)—so if all values are NULL, result is NULL.
All aggregates return NULL on empty sets (e.g., WHERE 1=0).

⚠️ Danger: AVG(CAST(col AS FLOAT)) may differ from AVG(col) due to integer division (MySQL, older SQL Server).

Syntax and Examples

✅ 1. T-SQL (SQL Server)

SELECT 
    CategoryID,
    COUNT(*) AS TotalProducts,
    COUNT(Price) AS PricedProducts,  -- excludes NULL prices
    SUM(Price) AS TotalRevenue,
    AVG(Price) AS AvgPrice,          -- NULL-safe
    MIN(Price) AS MinPrice,
    MAX(Price) AS MaxPrice
FROM Products
GROUP BY CategoryID;

✅ 2. PL/SQL (Oracle)

SELECT 
    CategoryID,
    COUNT(*) AS TotalProducts,
    COUNT(Price) AS PricedProducts,
    SUM(Price) AS TotalRevenue,
    AVG(Price) AS AvgPrice,
    MIN(Price) AS MinPrice,
    MAX(Price) AS MaxPrice
FROM Products
GROUP BY CategoryID;

🔍 Note: Oracle’s AVG(NUMBER) returns NUMBER; no implicit cast needed.

✅ 3. Python (SQLAlchemy + pandas)

from sqlalchemy import func

stmt = select(
    products.c.CategoryID,
    func.count().label('TotalProducts'),
    func.count(products.c.Price).label('PricedProducts'),
    func.sum(products.c.Price).label('TotalRevenue'),
    func.avg(products.c.Price).label('AvgPrice'),
    func.min(products.c.Price).label('MinPrice'),
    func.max(products.c.Price).label('MaxPrice')
).group_by(products.c.CategoryID)

df = pd.read_sql(stmt, engine)
# Alternatively: df.groupby('CategoryID').agg(...)

✅ 4. MySQL

SELECT 
    CategoryID,
    COUNT(*) AS TotalProducts,
    COUNT(Price) AS PricedProducts,
    SUM(Price) AS TotalRevenue,
    AVG(Price) AS AvgPrice,     -- Returns DECIMAL; NULL-safe
    MIN(Price) AS MinPrice,
    MAX(Price) AS MaxPrice
FROM Products
GROUP BY CategoryID;

⚠️ Pre-8.0: AVG(INT) returned INT—truncating decimals! Upgrade or CAST.

Grouping with GROUP BY

Theoretical Concept

GROUP BY partitions tuples into disjoint sets (groups), and aggregates are computed per group. Rules:

Every column in SELECT must be either:
(a) in GROUP BY, or
(b) inside an aggregate function.
NULL values form a single group (unlike some languages where NULL ≠ NULL).
Order of groups is undefined unless ORDER BY is added.

🚫 Anti-Pattern: “Hidden columns” (e.g., MySQL’s sql_mode=NO ONLY_FULL_GROUP_BY)—non-aggregated, non-grouped columns return arbitrary values.

Syntax and Examples

✅ 1. T-SQL (SQL Server)

-- Strict: only grouped/aggregated columns allowed
SELECT 
    CategoryID,
    COUNT(*) AS ProductCount,
    STRING_AGG(Name, ', ') WITHIN GROUP (ORDER BY Name) AS ProductList
FROM Products
GROUP BY CategoryID
ORDER BY ProductCount DESC;

✅ 2. PL/SQL (Oracle)

SELECT 
    CategoryID,
    COUNT(*) AS ProductCount,
    LISTAGG(Name, ', ') WITHIN GROUP (ORDER BY Name) AS ProductList
FROM Products
GROUP BY CategoryID
ORDER BY ProductCount DESC;

✅ 3. Python (pandas — flexible grouping)

df = pd.read_sql("SELECT CategoryID, Name, Price FROM Products", engine)

# Group and aggregate
summary = df.groupby('CategoryID').agg(
    ProductCount=('Name', 'count'),
    AvgPrice=('Price', 'mean'),
    ProductList=('Name', lambda x: ', '.join(sorted(x.dropna())))
).reset_index()

✅ 4. MySQL

-- Enable ONLY_FULL_GROUP_BY in sql_mode!
SELECT 
    CategoryID,
    COUNT(*) AS ProductCount,
    GROUP_CONCAT(Name ORDER BY Name SEPARATOR ', ') AS ProductList
FROM Products
GROUP BY CategoryID
ORDER BY ProductCount DESC;

✅ Best Practice: Always set sql_mode = 'STRICT_TRANS_TABLES,ONLY_FULL_GROUP_BY' in MySQL.

Filtering Groups with HAVING

Theoretical Concept

HAVING filters groups after aggregation (vs WHERE, which filters rows before grouping). Use it to answer questions like:

“Which categories have >10 products?”
“Which departments have average salary > $80k?”

Order of operations:
FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY

Syntax and Examples

✅ 1. T-SQL (SQL Server)

SELECT 
    CategoryID,
    AVG(Price) AS AvgPrice
FROM Products
WHERE Price IS NOT NULL
GROUP BY CategoryID
HAVING COUNT(*) >= 5 AND AVG(Price) > 50.0
ORDER BY AvgPrice DESC;

✅ 2. PL/SQL (Oracle)

SELECT 
    CategoryID,
    AVG(Price) AS AvgPrice
FROM Products
WHERE Price IS NOT NULL
GROUP BY CategoryID
HAVING COUNT(*) >= 5 AND AVG(Price) > 50
ORDER BY AvgPrice DESC;

✅ 3. Python (pandas — HAVING analog)

df = pd.read_sql("SELECT CategoryID, Price FROM Products WHERE Price IS NOT NULL", engine)

summary = (df
    .groupby('CategoryID')
    .agg(AvgPrice=('Price', 'mean'), Count=('Price', 'count'))
    .query('Count >= 5 and AvgPrice > 50')
    .sort_values('AvgPrice', ascending=False)
)

✅ 4. MySQL

SELECT 
    CategoryID,
    AVG(Price) AS AvgPrice
FROM Products
WHERE Price IS NOT NULL
GROUP BY CategoryID
HAVING COUNT(*) >= 5 AND AVG(Price) > 50.0
ORDER BY AvgPrice DESC;

🧠 Remember: You can use alias in HAVING in MySQL & PostgreSQL—but not in SQL Server or Oracle. Avoid for portability.

Removing Duplicates with DISTINCT

Theoretical Concept

DISTINCT returns unique rows (or unique value combinations) from a result set. It operates on the entire SELECT list—not per column.

SELECT DISTINCT col1, col2 → unique (col1, col2) pairs.
COUNT(DISTINCT col) counts unique non-NULL values in col.
Performance cost: requires sorting or hashing.

⚠️ DISTINCT ≠ GROUP BY (though logically similar). GROUP BY is more explicit and allows aggregation.

Syntax and Examples

✅ 1. T-SQL (SQL Server)

-- Unique category-price combos
SELECT DISTINCT CategoryID, Price 
FROM Products;

-- Count unique categories
SELECT COUNT(DISTINCT CategoryID) AS UniqueCategories 
FROM Products;

✅ 2. PL/SQL (Oracle)

SELECT DISTINCT CategoryID, Price FROM Products;
SELECT COUNT(DISTINCT CategoryID) AS UniqueCategories FROM Products;

✅ 3. Python (pandas)

df[['CategoryID', 'Price']].drop_duplicates()
df['CategoryID'].nunique()  # same as COUNT(DISTINCT)

✅ 4. MySQL

SELECT DISTINCT CategoryID, Price FROM Products;
SELECT COUNT(DISTINCT CategoryID) AS UniqueCategories FROM Products;

🔥 Pro Tip: In MySQL, COUNT(DISTINCT col1, col2) is supported; in SQL Server, use COUNT(*) over SELECT DISTINCT.

Chapter Summary

Aggregates ignore NULLs; COUNT(*) ≠ COUNT(col).
GROUP BY requires every non-aggregate column to be grouped—enforce with ONLY_FULL_GROUP_BY.
HAVING filters after grouping; WHERE filters before.
DISTINCT removes duplicate rows; use COUNT(DISTINCT) for cardinality estimation.
Python’s pandas.groupby() mirrors SQL semantics—but offers richer transformations.

Review Questions

Why does AVG(Price) return NULL if all prices are NULL?
What’s wrong with SELECT Name, COUNT(*) FROM Products GROUP BY CategoryID?
When would COUNT(DISTINCT col) be significantly slower than COUNT(col)?
Can you use a column alias in HAVING? Which platforms allow it?
How does GROUP BY NULL behave?

Exercises

Sales Summary: For each CategoryID, compute:
- Total products
- % of products with Price > 100
- List of product names (comma-separated)
  → Implement in all 4 platforms.
NULL Sensitivity:
In a table with 100 rows, 20 have NULL in Price. What do COUNT(*), COUNT(Price), and AVG(Price) return?
Python Aggregation Challenge:
Load Orders(order_id, customer_id, amount, status) into pandas. Compute:
- Avg order amount per customer (ignore NULL amounts)
- Customers with >5 orders
- Top 3 customers by total spend

Debug This!:
This MySQL query runs but gives inconsistent results. Why?

SELECT CategoryID, Name, AVG(Price) 
FROM Products 
GROUP BY CategoryID;

Advanced HAVING:
Find categories where the most expensive product is >2× the average price. Write in T-SQL and Oracle.

🔗 Chapter 6: Joins and Relationships

Cross-Platform SQL Programming: A Unified Guide for Modern Data Engineers

In This Chapter:
We unify data across tables using joins—the relational model’s superpower. You’ll master INNER, OUTER, SELF, and CROSS joins, learn why LEFT JOIN ≠ RIGHT JOIN in practice, and discover how foreign key constraints enable safe, optimized joining. Plus: platform-specific join hints, null propagation, and anti-join patterns.

Understanding Table Relationships

Theoretical Concept

Relationships are modeled via foreign keys:

One-to-Many (1:N): Orders.CustomerID → Customers.CustomerID
Many-to-Many (M:N): via junction table (StudentCourse(StudentID, CourseID))
One-to-One (1:1): rare; often merged or enforced via UNIQUE FK

Joins reify relationships:

INNER JOIN → intersection (only matching pairs)
LEFT JOIN → all left rows + matches (or NULLs)
FULL OUTER JOIN → union (all rows from both sides)

⚠️ Cardinality matters: A 1:N join multiplies left rows; an M:N join can explode result size.

INNER JOIN

Theoretical Concept

Returns only rows where the join condition is TRUE. Equivalent to relational algebra’s ⨝ (natural/equi-join).

Order of tables doesn’t affect result (commutative), but may affect performance.
Join condition can be =, <, BETWEEN, etc.—but equi-joins (=) are most optimizable.

Syntax and Examples

✅ 1. T-SQL (SQL Server)

SELECT c.Name AS Customer, p.Name AS Product, o.Quantity
FROM Orders o
INNER JOIN Customers c ON o.CustomerID = c.CustomerID
INNER JOIN Products p ON o.ProductID = p.ProductID
WHERE o.OrderDate >= '2025-01-01';

✅ 2. PL/SQL (Oracle)

SELECT c.Name AS Customer, p.Name AS Product, o.Quantity
FROM Orders o
JOIN Customers c ON o.CustomerID = c.CustomerID   -- INNER is default
JOIN Products p ON o.ProductID = p.ProductID
WHERE o.OrderDate >= DATE '2025-01-01';

✅ 3. Python (SQLAlchemy)

stmt = select(
    customers.c.Name.label('Customer'),
    products.c.Name.label('Product'),
    orders.c.Quantity
).select_from(
    orders
    .join(customers, orders.c.CustomerID == customers.c.CustomerID)
    .join(products, orders.c.ProductID == products.c.ProductID)
).where(orders.c.OrderDate >= '2025-01-01')

df = pd.read_sql(stmt, engine)

✅ 4. MySQL

SELECT c.Name AS Customer, p.Name AS Product, o.Quantity
FROM Orders o
INNER JOIN Customers c ON o.CustomerID = c.CustomerID
INNER JOIN Products p ON o.ProductID = p.ProductID
WHERE o.OrderDate >= '2025-01-01';

📌 Best Practice: Use table aliases (o, c, p) for readability.

LEFT, RIGHT, and FULL OUTER JOIN

Theoretical Concept

LEFT JOIN: All rows from left table; unmatched right-side columns = NULL.
RIGHT JOIN: All rows from right table (avoid—rewrite as LEFT JOIN with tables swapped).
FULL OUTER JOIN: Union of both tables (rarely supported: MySQL ❌, others ✅).

Nulls from outer joins propagate: p.Price * o.Quantity = NULL if p.Price is NULL.

Syntax and Examples

✅ 1. T-SQL (SQL Server)

-- Customers + their orders (even if none)
SELECT c.Name, o.OrderID, o.OrderDate
FROM Customers c
LEFT JOIN Orders o ON c.CustomerID = o.CustomerID
WHERE c.Country = 'USA';
-- Note: Move filter to ON if you want "USA customers who *ever* ordered"

✅ 2. PL/SQL (Oracle)

SELECT c.Name, o.OrderID, o.OrderDate
FROM Customers c
LEFT OUTER JOIN Orders o ON c.CustomerID = o.CustomerID
WHERE c.Country = 'USA';

✅ 3. Python (SQLAlchemy)

stmt = select(
    customers.c.Name,
    orders.c.OrderID,
    orders.c.OrderDate
).select_from(
    customers.outerjoin(orders, customers.c.CustomerID == orders.c.CustomerID)
).where(customers.c.Country == 'USA')

✅ 4. MySQL

SELECT c.Name, o.OrderID, o.OrderDate
FROM Customers c
LEFT JOIN Orders o ON c.CustomerID = o.CustomerID
WHERE c.Country = 'USA';

⚠️ MySQL does not support FULL OUTER JOIN. Simulate with UNION of LEFT and RIGHT.

Self-Joins

Theoretical Concept

Join a table to itself (e.g., employee-manager hierarchies). Requires table aliases to disambiguate columns.

Syntax and Examples

✅ 1. T-SQL (SQL Server)

SELECT 
    emp.Name AS Employee,
    mgr.Name AS Manager
FROM Employees emp
LEFT JOIN Employees mgr ON emp.ManagerID = mgr.EmployeeID;

✅ 2. PL/SQL (Oracle)

SELECT 
    emp.Name AS Employee,
    mgr.Name AS Manager
FROM Employees emp
LEFT JOIN Employees mgr ON emp.ManagerID = mgr.EmployeeID;

✅ 3. Python (SQLAlchemy)

emp = employees.alias('emp')
mgr = employees.alias('mgr')

stmt = select(
    emp.c.Name.label('Employee'),
    mgr.c.Name.label('Manager')
).select_from(
    emp.outerjoin(mgr, emp.c.ManagerID == mgr.c.EmployeeID)
)

✅ 4. MySQL

SELECT 
    emp.Name AS Employee,
    mgr.Name AS Manager
FROM Employees emp
LEFT JOIN Employees mgr ON emp.ManagerID = mgr.EmployeeID;

Cross Joins

Theoretical Concept

CROSS JOIN = Cartesian product: every row from R × every row from S.

Rarely useful alone—but foundational for manual pivots or combinatorial generation.
Always specify CROSS JOIN (avoid implicit comma syntax: FROM R, S).

Syntax and Examples

✅ All Platforms (ANSI-compliant)

-- Generate all student-course pairs (for scheduling)
SELECT s.Name, c.Title
FROM Students s
CROSS JOIN Courses c;

🚫 Danger: 10k students × 100 courses = 1M rows—use cautiously.

Chapter Summary

INNER JOIN = matching rows only; LEFT JOIN = all left + matches.
Prefer LEFT JOIN over RIGHT JOIN for readability.
Self-joins require aliases; cross joins risk combinatorial explosion.
MySQL lacks FULL OUTER JOIN—simulate with UNION.
Python’s merge() mirrors SQL joins: how='left', how='inner', etc.

Review Questions

Why is WHERE o.OrderID IS NULL after LEFT JOIN used to find “customers with no orders”?
What’s the difference between ON and WHERE in outer joins?
Can a FULL OUTER JOIN produce duplicate rows? When?
Why avoid FROM R, S WHERE R.x = S.y?
How would you simulate FULL OUTER JOIN in MySQL?

Exercises

Employee Hierarchy:
List employees and their manager’s department. Use self-join in all 4 platforms.
Anti-Join Pattern:
Find customers who placed no orders in 2025. Implement using LEFT JOIN + IS NULL and NOT EXISTS.
Python Merge:
Load df_employees (emp_id, mgr_id) and df_departments (dept_id, name). Create a DataFrame showing each employee’s manager’s department.

Debug This!:
Why does this return fewer rows than expected?

SELECT c.Name, o.OrderID 
FROM Customers c 
LEFT JOIN Orders o ON c.CustomerID = o.CustomerID 
WHERE o.OrderDate > '2025-06-01';

M:N Junction Table:
Using StudentCourse(StudentID, CourseID), list students and all their course titles. Include students with no courses.

🧩 Chapter 7: Subqueries and Common Table Expressions (CTEs)

Cross-Platform SQL Programming: A Unified Guide for Modern Data Engineers

In This Chapter:
We nest logic using subqueries and structure complex queries with CTEs. You’ll learn correlated vs. non-correlated subqueries, when to use EXISTS vs IN, and why CTEs improve readability—but not always performance. Plus: recursive CTEs for hierarchies, and Python alternatives.

Scalar and Correlated Subqueries

Theoretical Concept

Scalar subquery: Returns one value (0 or 1 row, 1 col). Used in SELECT, WHERE, HAVING.
Correlated subquery: References outer query columns—executed once per outer row. Can be slow if unindexed.

⚠️ If subquery returns >1 row → error (e.g., WHERE col = (SELECT ...) fails).

Syntax and Examples

✅ 1. T-SQL (SQL Server)

-- Scalar: Avg price in same category
SELECT 
    Name,
    Price,
    (SELECT AVG(Price) 
     FROM Products p2 
     WHERE p2.CategoryID = p1.CategoryID) AS CategoryAvg
FROM Products p1;

-- Correlated: Products priced above category avg
SELECT Name, Price
FROM Products p1
WHERE Price > (
    SELECT AVG(Price) 
    FROM Products p2 
    WHERE p2.CategoryID = p1.CategoryID
);

✅ 2. PL/SQL (Oracle)

-- Same syntax as SQL Server (ANSI-compliant)
SELECT Name, Price,
    (SELECT AVG(Price) FROM Products p2 WHERE p2.CategoryID = p1.CategoryID)
FROM Products p1
WHERE Price > (
    SELECT AVG(Price) FROM Products p2 WHERE p2.CategoryID = p1.CategoryID
);

✅ 3. Python (SQLAlchemy subquery)

# Subquery for category avg
cat_avg = select(func.avg(products.c.Price).label('avg_price')
    ).where(products.c.CategoryID == products.alias().c.CategoryID
    ).scalar_subquery()

stmt = select(
    products.c.Name,
    products.c.Price,
    cat_avg.label('CategoryAvg')
).where(products.c.Price > cat_avg)

df = pd.read_sql(stmt, engine)

✅ 4. MySQL

SELECT 
    Name,
    Price,
    (SELECT AVG(Price) FROM Products p2 WHERE p2.CategoryID = p1.CategoryID) AS CategoryAvg
FROM Products p1
WHERE Price > (
    SELECT AVG(Price) FROM Products p2 WHERE p2.CategoryID = p1.CategoryID
);

🚀 Optimization: Rewrite correlated subqueries as JOINs for performance.

Subqueries in WHERE, FROM, and SELECT

Theoretical Concept

WHERE col IN (subquery): Set membership (fails if subquery returns NULL).
WHERE EXISTS (subquery): Returns TRUE if ≥1 row—null-safe, often faster.
FROM (subquery) AS alias: Derived table (inline view).
SELECT (subquery): Scalar only.

Syntax and Examples

✅ 1. T-SQL (SQL Server)

-- IN (avoid if NULLs possible)
SELECT Name FROM Products 
WHERE CategoryID IN (SELECT CategoryID FROM Categories WHERE Active = 1);

-- EXISTS (preferred)
SELECT Name FROM Products p
WHERE EXISTS (
    SELECT 1 FROM Categories c 
    WHERE c.CategoryID = p.CategoryID AND c.Active = 1
);

-- Derived table
SELECT x.CategoryID, x.AvgPrice
FROM (
    SELECT CategoryID, AVG(Price) AS AvgPrice
    FROM Products
    GROUP BY CategoryID
) x
WHERE x.AvgPrice > 50;

✅ 2–4. PL/SQL, MySQL, Python
Same logic—EXISTS preferred over IN for safety and performance.

✅ Rule of Thumb: Use EXISTS for existence checks; IN only for small, static lists.

Introduction to CTEs

Theoretical Concept

CTEs (WITH clause) define temporary result sets scoped to a single query. Benefits:

Improves readability (break complex queries into steps)
Enables recursion (WITH RECURSIVE)
Can be referenced multiple times (unlike derived tables)

⚠️ Not materialized by default—most engines inline CTEs (like macros).

Syntax and Examples

✅ 1. T-SQL (SQL Server)

WITH CategoryStats AS (
    SELECT 
        CategoryID,
        AVG(Price) AS AvgPrice,
        COUNT(*) AS ProductCount
    FROM Products
    GROUP BY CategoryID
)
SELECT 
    c.Name,
    cs.AvgPrice,
    cs.ProductCount
FROM Categories c
INNER JOIN CategoryStats cs ON c.CategoryID = cs.CategoryID
WHERE cs.ProductCount >= 5;

✅ 2. PL/SQL (Oracle)

WITH CategoryStats AS (
    SELECT CategoryID, AVG(Price) AvgPrice, COUNT(*) ProductCount
    FROM Products
    GROUP BY CategoryID
)
SELECT c.Name, cs.AvgPrice, cs.ProductCount
FROM Categories c
JOIN CategoryStats cs ON c.CategoryID = cs.CategoryID
WHERE cs.ProductCount >= 5;

✅ 3. Python (modular pandas)

# Step 1: Compute stats
cat_stats = (df_products
    .groupby('CategoryID')
    .agg(AvgPrice=('Price', 'mean'), ProductCount=('ProductID', 'count'))
    .reset_index())

# Step 2: Join
result = pd.merge(df_categories, cat_stats, on='CategoryID')
result = result[result['ProductCount'] >= 5]

✅ 4. MySQL (8.0+ only)

WITH CategoryStats AS (
    SELECT CategoryID, AVG(Price) AS AvgPrice, COUNT(*) AS ProductCount
    FROM Products
    GROUP BY CategoryID
)
SELECT c.Name, cs.AvgPrice, cs.ProductCount
FROM Categories c
JOIN CategoryStats cs ON c.CategoryID = cs.CategoryID
WHERE cs.ProductCount >= 5;

❗ MySQL <8.0: Use derived tables instead.

Recursive CTEs

Theoretical Concept

Recursive CTEs traverse hierarchies (e.g., org charts, bill-of-materials). Structure:

WITH RECURSIVE cte AS (
    -- Anchor: base case (e.g., CEO)
    SELECT ...
    UNION ALL
    -- Recursive: join to prior result
    SELECT ... FROM table JOIN cte ...
)
SELECT * FROM cte;

⚠️ Always include a depth limit to prevent infinite loops.

Syntax and Examples

✅ 1. T-SQL (SQL Server)

WITH OrgChart AS (
    -- Anchor: Top-level managers (no manager)
    SELECT EmployeeID, Name, ManagerID, 0 AS Level
    FROM Employees
    WHERE ManagerID IS NULL

    UNION ALL

    -- Recursive: Direct reports
    SELECT e.EmployeeID, e.Name, e.ManagerID, oc.Level + 1
    FROM Employees e
    INNER JOIN OrgChart oc ON e.ManagerID = oc.EmployeeID
)
SELECT * FROM OrgChart
OPTION (MAXRECURSION 100);  -- Safety

✅ 2. PL/SQL (Oracle)

WITH OrgChart (EmployeeID, Name, ManagerID, Level) AS (
    SELECT EmployeeID, Name, ManagerID, 0
    FROM Employees
    WHERE ManagerID IS NULL
    UNION ALL
    SELECT e.EmployeeID, e.Name, e.ManagerID, oc.Level + 1
    FROM Employees e
    JOIN OrgChart oc ON e.ManagerID = oc.EmployeeID
)
SELECT * FROM OrgChart;

✅ 3. MySQL (8.0+)

WITH RECURSIVE OrgChart AS (
    SELECT EmployeeID, Name, ManagerID, 0 AS Level
    FROM Employees
    WHERE ManagerID IS NULL
    UNION ALL
    SELECT e.EmployeeID, e.Name, e.ManagerID, oc.Level + 1
    FROM Employees e
    INNER JOIN OrgChart oc ON e.ManagerID = oc.EmployeeID
)
SELECT * FROM OrgChart;

✅ 4. Python (NetworkX or iterative pandas)

import pandas as pd

df = pd.read_sql("SELECT EmployeeID, Name, ManagerID FROM Employees", engine)
df['Level'] = 0

# Iteratively assign levels
changed = True
while changed:
    merged = df.merge(df[['EmployeeID', 'Level']], 
                     left_on='ManagerID', right_on='EmployeeID', 
                     suffixes=('', '_mgr'), how='left')
    new_level = merged['Level_mgr'] + 1
    updated = merged['Level'] != new_level
    df.loc[updated, 'Level'] = new_level[updated]
    changed = updated.any()

Chapter Summary

Scalar subqueries return one value; correlated ones reference outer rows.
Prefer EXISTS over IN for safety and performance.
CTEs improve readability and enable recursion—supported in modern engines (MySQL 8.0+).
Recursive CTEs require anchor + recursive member + termination guard.
Python can replicate recursion—but SQL is often more efficient for deep hierarchies.

Review Questions

Why is WHERE col NOT IN (SELECT ...) dangerous with NULLs?
What’s the difference between a CTE and a derived table?
Can a recursive CTE have multiple anchor members?
Why use UNION ALL (not UNION) in recursive CTEs?
When would a CTE be slower than a derived table?

Exercises

Top Product per Category:
For each category, find the product with highest price. Use correlated subquery and CTE—compare.
Recursive Path:
In Employees, generate a Path column: 'CEO > VP > Manager > Alice'. Implement in SQL Server and MySQL.
Python Alternative:
Simulate a 3-level org chart using pandas merges (no recursion).

Debug This!:
This CTE runs forever. Why?

WITH cte AS (
    SELECT 1 AS n
    UNION ALL
    SELECT n + 1 FROM cte  -- Missing join condition!
)
SELECT * FROM cte;

Materialized CTE?:
Research: Which platforms support materialized CTEs (MATERIALIZED hint in Oracle/PostgreSQL)?

👁️ Chapter 8: Views and Table-Valued Functions

Cross-Platform SQL Programming: A Unified Guide for Modern Data Engineers

In This Chapter:
We abstract complexity with views and functions—reusable, secure, and modular. You’ll create simple and indexed views, inline vs. multi-statement table-valued functions (TVFs), and learn when to use each. Plus: updatable views, security via ownership chaining, and Python wrappers.

Creating and Using Views

Theoretical Concept

A view is a virtual table defined by a SELECT query. Benefits:

Simplify complex joins/aggregations
Restrict access to sensitive columns (SELECT Name, Email FROM Employees)
Provide backward compatibility during schema changes

Views are not materialized by default (except indexed views in SQL Server).

Syntax and Examples

✅ 1. T-SQL (SQL Server)

-- Simple view
CREATE VIEW vw_CustomerOrders AS
SELECT 
    c.CustomerID, c.Name, COUNT(o.OrderID) AS OrderCount
FROM Customers c
LEFT JOIN Orders o ON c.CustomerID = o.CustomerID
GROUP BY c.CustomerID, c.Name;

-- Use it
SELECT * FROM vw_CustomerOrders WHERE OrderCount > 10;

✅ 2. PL/SQL (Oracle)

CREATE OR REPLACE VIEW vw_CustomerOrders AS
SELECT 
    c.CustomerID, c.Name, COUNT(o.OrderID) AS OrderCount
FROM Customers c
LEFT JOIN Orders o ON c.CustomerID = o.CustomerID
GROUP BY c.CustomerID, c.Name;

✅ 3. MySQL

CREATE OR REPLACE VIEW vw_CustomerOrders AS
SELECT 
    c.CustomerID, c.Name, COUNT(o.OrderID) AS OrderCount
FROM Customers c
LEFT JOIN Orders o ON c.CustomerID = o.CustomerID
GROUP BY c.CustomerID, c.Name;

✅ 4. Python (as reusable function)

def get_customer_orders(engine):
    query = """
    SELECT c.CustomerID, c.Name, COUNT(o.OrderID) AS OrderCount
    FROM Customers c
    LEFT JOIN Orders o ON c.CustomerID = o.CustomerID
    GROUP BY c.CustomerID, c.Name
    """
    return pd.read_sql(query, engine)

🔐 Security Tip: In SQL Server, use EXECUTE AS to control view permissions independently.

Updatable Views

Theoretical Concept

Some views support INSERT/UPDATE/DELETE if they meet criteria:

No DISTINCT, GROUP BY, HAVING, aggregates
No derived columns (col1 + col2)
Single base table (or join with key preserved)

Syntax and Examples

✅ T-SQL (SQL Server)

CREATE VIEW vw_ActiveCustomers AS
SELECT CustomerID, Name, Email 
FROM Customers 
WHERE Active = 1;

-- Updatable!
UPDATE vw_ActiveCustomers SET Email = 'alice@new.com' WHERE Name = 'Alice';

✅ MySQL
Same rules—check INFORMATION_SCHEMA.VIEWS.IS_UPDATABLE.

Inline and Multi-Statement Table-Valued Functions

Theoretical Concept

Inline TVF: Returns result of a single SELECT (like a parameterized view). Optimizer inlines it → fast.
Multi-Statement TVF: Builds result in temp table (flexible but slow—no statistics).

⚠️ Avoid multi-statement TVFs for large data.

Syntax and Examples

✅ 1. T-SQL (SQL Server)

-- Inline TVF (preferred)
CREATE FUNCTION fn_ProductsByCategory(@CatID INT)
RETURNS TABLE AS
RETURN (
    SELECT ProductID, Name, Price
    FROM Products
    WHERE CategoryID = @CatID
);

-- Use
SELECT * FROM dbo.fn_ProductsByCategory(3);

-- Multi-Statement TVF (avoid if possible)
CREATE FUNCTION fn_ProductsByPriceRange(@Min DECIMAL, @Max DECIMAL)
RETURNS @Result TABLE (ProductID INT, Name NVARCHAR(100), Price DECIMAL)
AS
BEGIN
    INSERT INTO @Result
    SELECT ProductID, Name, Price
    FROM Products
    WHERE Price BETWEEN @Min AND @Max;
    RETURN;
END;

✅ 2. PL/SQL (Oracle)

-- Pipelined Table Function (closest to inline TVF)
CREATE OR REPLACE FUNCTION fn_ProductsByCategory(p_CatID NUMBER)
RETURN SYS_REFCURSOR IS
    rc SYS_REFCURSOR;
BEGIN
    OPEN rc FOR
        SELECT ProductID, Name, Price
        FROM Products
        WHERE CategoryID = p_CatID;
    RETURN rc;
END;

✅ 3. MySQL
❌ No TVFs. Use views + application logic or stored procedures with temp tables.

✅ 4. Python (parameterized function)

def get_products_by_category(engine, cat_id):
    return pd.read_sql(
        "SELECT ProductID, Name, Price FROM Products WHERE CategoryID = %s",
        engine, params=(cat_id,)
    )

Security and Abstraction Benefits

Key Advantages

Column masking: Hide SSN, salary, etc.
Row-level filtering: WHERE Department = USERDEPT()
Ownership chaining: In SQL Server, if view and base table have same owner, users need only view permission.
Stable interface: Change underlying schema without breaking reports.

🛡️ Best Practice: Grant permissions on views—not base tables.

Chapter Summary

Views simplify, secure, and stabilize access.
Indexed views (SQL Server) or materialized views (Oracle) improve performance.
Inline TVFs > multi-statement TVFs for performance.
MySQL lacks TVFs—use views + app logic.
Python functions provide cross-platform parameterization.

Review Questions

Can a view with GROUP BY be updatable? Why?
What’s the performance difference between inline and multi-statement TVFs?
How does ownership chaining improve security in SQL Server?
Why avoid SELECT * in views?
When would you choose a view over a CTE?

Exercises

Secure View:
Create a view vw_EmployeePublic showing only Name, Dept, HireDate (hide Salary, SSN). Grant SELECT to analyst role.
Parameterized Query:
Write an inline TVF (SQL Server) and Python function to get products by price range.
Indexed View:
In SQL Server, create an indexed view for CategoryID, COUNT(*)—and query it.

Debug This!:
Why does this update fail?

CREATE VIEW vw_Summary AS 
SELECT CategoryID, AVG(Price) AS AvgPrice 
FROM Products GROUP BY CategoryID;

UPDATE vw_Summary SET AvgPrice = 100 WHERE CategoryID = 1;

Materialized Alternative:
In MySQL, simulate a materialized view using a scheduled event + temp table.

🔄 Chapter 9: Advanced Data Modification

Cross-Platform SQL Programming: A Unified Guide for Modern Data Engineers

In This Chapter:
We go beyond basic INSERT/UPDATE/DELETE to synchronize datasets with MERGE (upsert), capture changes with OUTPUT/RETURNING, and load data at scale. You’ll handle conflicts, audit modifications, and avoid common pitfalls in bulk operations.

MERGE / UPSERT Operations

Theoretical Concept

MERGE performs insert, update, or delete in one atomic statement based on matching conditions. Ideal for ETL and sync jobs.

Matched → UPDATE or DELETE
Not matched → INSERT
Not matched by source → DELETE (synchronize delete)

⚠️ Beware of multiple matches—can cause “Halloween problem” (row updated multiple times).

Syntax and Examples

✅ 1. T-SQL (SQL Server)

MERGE Products AS target
USING StagingProducts AS source
ON target.ProductID = source.ProductID
WHEN MATCHED THEN
    UPDATE SET 
        Name = source.Name,
        Price = source.Price,
        UpdatedAt = GETDATE()
WHEN NOT MATCHED BY TARGET THEN
    INSERT (ProductID, Name, Price, CreatedAt)
    VALUES (source.ProductID, source.Name, source.Price, GETDATE())
WHEN NOT MATCHED BY SOURCE THEN
    DELETE;  -- Full sync

✅ 2. PL/SQL (Oracle)

MERGE INTO Products target
USING StagingProducts source
ON (target.ProductID = source.ProductID)
WHEN MATCHED THEN
    UPDATE SET 
        Name = source.Name,
        Price = source.Price,
        UpdatedAt = SYSTIMESTAMP
WHEN NOT MATCHED THEN
    INSERT (ProductID, Name, Price, CreatedAt)
    VALUES (source.ProductID, source.Name, source.Price, SYSTIMESTAMP);
-- Oracle doesn’t support WHEN NOT MATCHED BY SOURCE

✅ 3. MySQL

-- INSERT ... ON DUPLICATE KEY UPDATE (requires PK/UK)
INSERT INTO Products (ProductID, Name, Price, CreatedAt)
SELECT ProductID, Name, Price, NOW() 
FROM StagingProducts
ON DUPLICATE KEY UPDATE
    Name = VALUES(Name),
    Price = VALUES(Price),
    UpdatedAt = NOW();

✅ 4. Python (pandas + SQLAlchemy)

# Load staging data
staging = pd.read_csv('staging.csv')

# Upsert: split into new/updated
existing_ids = pd.read_sql("SELECT ProductID FROM Products", engine)['ProductID']
new_rows = staging[~staging['ProductID'].isin(existing_ids)]
updated_rows = staging[staging['ProductID'].isin(existing_ids)]

# Insert new
new_rows.to_sql('Products', engine, if_exists='append', index=False)

# Update existing
for _, row in updated_rows.iterrows():
    engine.execute("""
        UPDATE Products 
        SET Name = :name, Price = :price, UpdatedAt = NOW()
        WHERE ProductID = :id
    """, name=row['Name'], price=row['Price'], id=row['ProductID'])

🚀 Pro Tip: In Snowflake/BigQuery, use MERGE—it’s ANSI-standard and performant.

Bulk Loading Data

Theoretical Concept

For large datasets, avoid row-by-row inserts. Use:

SQL Server: BULK INSERT, bcp, or SqlBulkCopy (.NET)
Oracle: SQL*Loader, External Tables
MySQL: LOAD DATA INFILE
Python: pandas.to_sql(..., method='multi'), executemany

⚠️ Disable indexes/constraints during load; re-enable after.

Syntax and Examples

✅ 1. T-SQL (SQL Server)

BULK INSERT Products
FROM 'C:\data\products.csv'
WITH (
    DATA_SOURCE = 'MyAzureBlob',
    FORMAT = 'CSV',
    FIRSTROW = 2,
    FIELDTERMINATOR = ',',
    ROWTERMINATOR = '\n'
);

✅ 2. MySQL

LOAD DATA INFILE '/tmp/products.csv'
INTO TABLE Products
FIELDS TERMINATED BY ',' 
LINES TERMINATED BY '\n'
IGNORE 1 ROWS;

✅ 3. Python (fast bulk insert)

from sqlalchemy import text

# Chunked insert
chunk_size = 10000
for i in range(0, len(df), chunk_size):
    chunk = df.iloc[i:i+chunk_size]
    chunk.to_sql('Products', engine, if_exists='append', index=False, method='multi')

Capturing Modified Data (OUTPUT, RETURNING)

Theoretical Concept

Return data from modified rows (e.g., auto-generated IDs, old/new values) without a second query.

Syntax and Examples

✅ 1. T-SQL (SQL Server)

DELETE FROM Orders
OUTPUT deleted.OrderID, deleted.CustomerID
WHERE OrderDate < '2020-01-01';
-- Returns deleted rows

✅ 2. PL/SQL (Oracle)

DELETE FROM Orders
WHERE OrderDate < DATE '2020-01-01'
RETURNING OrderID, CustomerID BULK COLLECT INTO l_ids, l_customers;
-- Requires PL/SQL block

✅ 3. MySQL
❌ No RETURNING. Use SELECT before DELETE, or triggers.

✅ 4. Python (capture before/after)

# Get IDs to delete
to_delete = pd.read_sql("SELECT OrderID FROM Orders WHERE OrderDate < '2020-01-01'", engine)
# Then delete
engine.execute("DELETE FROM Orders WHERE OrderDate < '2020-01-01'")
# to_delete holds the audit trail

Error Handling in DML

Key Patterns

Wrap in transactions + try/catch
Use XACT_ABORT ON (SQL Server) for batch abort
Validate data before DML (e.g., WHERE NOT EXISTS for duplicates)

Chapter Summary

MERGE enables atomic upsert/sync—but beware multiple matches.
Bulk load > row-by-row for performance.
OUTPUT/RETURNING captures changes in one round-trip.
MySQL lacks RETURNING and full MERGE—use ON DUPLICATE KEY UPDATE.
Python bridges gaps with batch operations and pre-capture.

Review Questions

Why is WHEN NOT MATCHED BY SOURCE THEN DELETE dangerous in MERGE?
What’s faster: INSERT ... SELECT or row-by-row INSERT?
Can OUTPUT capture identity values during INSERT?
Why use XACT_ABORT ON in SQL Server?
How to simulate RETURNING in MySQL?

Exercises

Upsert Simulation:
Sync StagingCustomers to Customers using MERGE (SQL Server), ON DUPLICATE KEY (MySQL), and Python.
Bulk Load Pipeline:
Write a Python script that loads a 1M-row CSV into SQL Server using pyodbc.fast_executemany.
Audit Trail:
Delete inactive customers and log their IDs/names using OUTPUT (SQL Server) and pre-query (MySQL).

Debug This!:
This MERGE updates some rows twice. Why?

MERGE T USING S ON T.ID = S.ID OR T.Email = S.Email ...

Idempotent Load:
Design a bulk load that’s safe to re-run (no duplicates).

⚙️ Chapter 10: Stored Procedures and Functions

Cross-Platform SQL Programming: A Unified Guide for Modern Data Engineers

In This Chapter:
We encapsulate logic in stored procedures and functions—for reuse, performance, and security. You’ll write parameterized routines, handle errors, and choose between scalar, table, and procedural functions. Plus: when not to use them (hint: Python often wins for complex logic).

Creating and Executing Procedures

Theoretical Concept

Stored procedures are precompiled, parameterized SQL routines. Benefits:

Reduce network round-trips
Enforce business rules at DB layer
Enable transaction control (BEGIN TRAN...COMMIT)

⚠️ Overuse can lead to “stored procedure sprawl”—hard to version, test, and debug.

Syntax and Examples

✅ 1. T-SQL (SQL Server)

CREATE PROCEDURE usp_GetCustomerOrders
    @CustomerID INT,
    @MinAmount DECIMAL(10,2) = 0.0
AS
BEGIN
    SET NOCOUNT ON;
    SELECT OrderID, OrderDate, TotalAmount
    FROM Orders
    WHERE CustomerID = @CustomerID 
      AND TotalAmount >= @MinAmount;
END;

-- Execute
EXEC usp_GetCustomerOrders @CustomerID = 101, @MinAmount = 100.0;

✅ 2. PL/SQL (Oracle)

CREATE OR REPLACE PROCEDURE usp_GetCustomerOrders (
    p_CustomerID IN NUMBER,
    p_MinAmount IN NUMBER DEFAULT 0
) AS
BEGIN
    FOR rec IN (
        SELECT OrderID, OrderDate, TotalAmount
        FROM Orders
        WHERE CustomerID = p_CustomerID 
          AND TotalAmount >= p_MinAmount
    ) LOOP
        DBMS_OUTPUT.PUT_LINE(rec.OrderID || ' ' || rec.TotalAmount);
    END LOOP;
END;

✅ 3. MySQL

DELIMITER $$
CREATE PROCEDURE usp_GetCustomerOrders(
    IN p_CustomerID INT,
    IN p_MinAmount DECIMAL(10,2)
)
BEGIN
    SELECT OrderID, OrderDate, TotalAmount
    FROM Orders
    WHERE CustomerID = p_CustomerID 
      AND TotalAmount >= p_MinAmount;
END$$
DELIMITER ;

-- Call
CALL usp_GetCustomerOrders(101, 100.0);

✅ 4. Python (as function)

def get_customer_orders(engine, customer_id, min_amount=0.0):
    return pd.read_sql("""
        SELECT OrderID, OrderDate, TotalAmount
        FROM Orders
        WHERE CustomerID = %s AND TotalAmount >= %s
    """, engine, params=(customer_id, min_amount))

📌 Modern View: Use procedures for data-heavy ops (ETL, reports); use Python for app logic.

Input, Output, and IN OUT Parameters

Platform Support

Feature	SQL Server	Oracle	MySQL
`IN` (default)	✅	✅	✅
`OUT`	✅	✅	✅
`IN OUT`	✅	✅	✅
Default values	✅	✅	❌

Syntax and Examples

✅ T-SQL (SQL Server)

CREATE PROCEDURE usp_CountOrders
    @CustomerID INT,
    @OrderCount INT OUTPUT
AS
BEGIN
    SELECT @OrderCount = COUNT(*) 
    FROM Orders 
    WHERE CustomerID = @CustomerID;
END;

-- Call
DECLARE @cnt INT;
EXEC usp_CountOrders @CustomerID = 101, @OrderCount = @cnt OUTPUT;
SELECT @cnt AS OrderCount;

✅ Oracle

CREATE PROCEDURE usp_CountOrders (
    p_CustomerID IN NUMBER,
    p_OrderCount OUT NUMBER
) AS
BEGIN
    SELECT COUNT(*) INTO p_OrderCount
    FROM Orders
    WHERE CustomerID = p_CustomerID;
END;

✅ MySQL

DELIMITER $$
CREATE PROCEDURE usp_CountOrders(
    IN p_CustomerID INT,
    OUT p_OrderCount INT
)
BEGIN
    SELECT COUNT(*) INTO p_OrderCount
    FROM Orders
    WHERE CustomerID = p_CustomerID;
END$$
DELIMITER ;

-- Call
CALL usp_CountOrders(101, @cnt);
SELECT @cnt;

Scalar and Table Functions

Theoretical Concept

Scalar function: Returns single value. Use in SELECT, WHERE.
Table function: Returns table. Use in FROM (like a parameterized view).

⚠️ Avoid scalar functions in WHERE on large tables—they prevent index use.

Syntax and Examples

✅ T-SQL (SQL Server)

-- Scalar
CREATE FUNCTION fn_TaxAmount(@Price DECIMAL) 
RETURNS DECIMAL AS
BEGIN
    RETURN @Price * 0.08;
END;

-- Table (inline)
CREATE FUNCTION fn_OrdersByCustomer(@CustomerID INT)
RETURNS TABLE AS
RETURN (
    SELECT OrderID, TotalAmount 
    FROM Orders 
    WHERE CustomerID = @CustomerID
);

✅ Oracle

-- Scalar
CREATE FUNCTION fn_TaxAmount(p_Price NUMBER) RETURN NUMBER IS
BEGIN
    RETURN p_Price * 0.08;
END;

-- Pipelined table function (advanced)

✅ MySQL
❌ No user-defined scalar/table functions in older versions. Use CREATE FUNCTION (5.7+) for scalars only.

Best Practices for Reusability

Do

Use meaningful names (usp_, fn_ prefixes)
Validate parameters
Include error handling (TRY...CATCH, EXCEPTION)
Document with comments

Don’t

Put business logic that changes often
Nest procedures deeply
Return result sets from functions (anti-pattern)

🌐 Hybrid Approach: Use procedures for data access; Python for orchestration.

Chapter Summary

Procedures = parameterized, transactional routines.
Functions: scalar (single value) or table (result set).
Output parameters enable return values beyond result sets.
MySQL lags in function support; Python fills gaps.
Modern stacks use procedures judiciously—not as app layer.

Review Questions

Can a function modify database state? (SQL Server: ❌, Oracle: ✅ with PRAGMA)
Why avoid scalar functions in WHERE col > fn(x)?
What’s the difference between OUT and IN OUT?
How to handle errors in a SQL Server procedure?
When would you choose a procedure over a Python function?

Exercises

Order Summary Proc:
Write a procedure that returns order count and total spend for a customer (output params).
Tax Function:
Create a scalar function to compute tax (8%)—and use it in a SELECT.
Python Wrapper:
Call the usp_GetCustomerOrders procedure from Python using pyodbc or cx_Oracle.
Debug This!:
Why does this function cause a table scan?
```
WHERE Price > fn_AddTax(BasePrice)
```
Security Audit:
Grant EXECUTE on usp_GetCustomerOrders to analyst role—but not direct table access.

Certainly! Here is Chapter 11: Transactions and Concurrency, written in full accordance with the book’s structure, tone, and cross-platform focus — covering T-SQL (SQL Server), PL/SQL (Oracle), Python (SQLAlchemy/pandas), and MySQL, with detailed theoretical grounding and practical implementations.

Chapter 11: Transactions and Concurrency

ACID Properties

Theoretical Concept

A transaction is a logical unit of work that must be executed completely or not at all. To guarantee reliability in multi-user environments, databases adhere to the ACID principles:

Atomicity: All operations in a transaction succeed, or none do.
Consistency: A transaction brings the database from one valid state to another.
Isolation: Concurrent transactions do not interfere with each other.
Durability: Once committed, changes survive system failures.

While all four platforms support ACID compliance, how they implement isolation, locking, and error recovery can differ significantly — especially in edge cases like long-running batches or distributed systems.

💡 Note: Not all storage engines support full ACID (e.g., MySQL’s MyISAM is non-transactional). Always verify engine compatibility (InnoDB for MySQL is ACID-compliant).

Explicit Transactions

Theoretical Concept

By default, many databases operate in autocommit mode: each SQL statement is its own transaction. For multi-step operations (e.g., transferring funds), we must explicitly define transaction boundaries using BEGIN TRANSACTION (or equivalent), followed by COMMIT or ROLLBACK.

Syntax and Examples

✅ 1. T-SQL (SQL Server)

BEGIN TRANSACTION;
BEGIN TRY
    UPDATE Accounts SET Balance = Balance - 100 WHERE AccountID = 'A';
    UPDATE Accounts SET Balance = Balance + 100 WHERE AccountID = 'B';
    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    ROLLBACK TRANSACTION;
    THROW; -- Re-raise error
END CATCH;

✅ 2. PL/SQL (Oracle)
In Oracle, all data-modifying statements occur within an implicit transaction unless explicitly controlled. Use COMMIT/ROLLBACK — but no BEGIN TRANSACTION keyword.

BEGIN
    UPDATE Accounts SET Balance = Balance - 100 WHERE AccountID = 'A';
    UPDATE Accounts SET Balance = Balance + 100 WHERE AccountID = 'B';
    COMMIT;
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
        RAISE; -- Propagate exception
END;
/

✅ 3. Python (with SQLAlchemy)

from sqlalchemy import create_engine, text
from sqlalchemy.exc import SQLAlchemyError

engine = create_engine("mssql+pyodbc://user:pass@server/db?driver=ODBC+Driver+17+for+SQL+Server")

with engine.connect() as conn:
    trans = conn.begin()
    try:
        conn.execute(text("UPDATE Accounts SET Balance = Balance - 100 WHERE AccountID = :id"), {"id": "A"})
        conn.execute(text("UPDATE Accounts SET Balance = Balance + 100 WHERE AccountID = :id"), {"id": "B"})
        trans.commit()
    except SQLAlchemyError as e:
        trans.rollback()
        raise e

✅ 4. MySQL

START TRANSACTION;
UPDATE Accounts SET Balance = Balance - 100 WHERE AccountID = 'A';
UPDATE Accounts SET Balance = Balance + 100 WHERE AccountID = 'B';
-- Check for success before committing
-- (e.g., via application logic or SELECT)
COMMIT;
-- Or: ROLLBACK;

🔔 Best Practice: Always pair START TRANSACTION with explicit COMMIT/ROLLBACK. Never rely on implicit commits in production.

Commit and Rollback

Theoretical Concept

COMMIT makes all changes in the current transaction permanent and releases locks.
ROLLBACK undoes all changes since the last BEGIN, restoring the database to its prior consistent state.

Most RDBMSs support savepoints — granular rollback points within a transaction — enabling partial rollbacks (e.g., retry only one step).

Syntax and Examples

✅ 1. T-SQL (SQL Server)

BEGIN TRANSACTION;
UPDATE Accounts SET Balance = Balance - 100 WHERE AccountID = 'A';

SAVE TRANSACTION Step1;  -- Create savepoint

UPDATE Accounts SET Balance = Balance + 100 WHERE AccountID = 'B';
-- Oops — Account B doesn’t exist!
IF @@ROWCOUNT = 0
    ROLLBACK TRANSACTION Step1;  -- Undo only second update
ELSE
    COMMIT TRANSACTION;

✅ 2. PL/SQL (Oracle)

BEGIN
    UPDATE Accounts SET Balance = Balance - 100 WHERE AccountID = 'A';
    SAVEPOINT step1;

    UPDATE Accounts SET Balance = Balance + 100 WHERE AccountID = 'B';
    IF SQL%ROWCOUNT = 0 THEN
        ROLLBACK TO step1;  -- Partial rollback
    ELSE
        COMMIT;
    END IF;
END;
/

✅ 3. Python (SQLAlchemy Core)

with engine.connect() as conn:
    trans = conn.begin()
    savepoint = conn.begin_nested()  # Creates a savepoint

    conn.execute(text("UPDATE Accounts SET Balance = Balance - 100 WHERE AccountID = :id"), {"id": "A"})

    try:
        result = conn.execute(text("UPDATE Accounts SET Balance = Balance + 100 WHERE AccountID = :id"), {"id": "B"})
        if result.rowcount == 0:
            raise ValueError("Recipient account not found")
        trans.commit()
    except Exception:
        savepoint.rollback()  # Roll back to savepoint
        trans.commit()        # Still commit first update (or choose to rollback entirely)
        raise

✅ 4. MySQL

START TRANSACTION;
UPDATE Accounts SET Balance = Balance - 100 WHERE AccountID = 'A';

SAVEPOINT step1;

UPDATE Accounts SET Balance = Balance + 100 WHERE AccountID = 'B';
-- Check if affected rows = 0
-- (Application must inspect row count or use SELECT first)
ROLLBACK TO SAVEPOINT step1;
-- Later: COMMIT or ROLLBACK TRANSACTION;

⚠️ MySQL SAVEPOINT names are case-sensitive and must be unique per transaction.

Isolation Levels

Theoretical Concept

Isolation levels determine how visible uncommitted changes are to other concurrent transactions. Higher isolation → more consistency, but lower concurrency.

Isolation Level	Dirty Read	Non-Repeatable Read	Phantom Read	Serializable?
Read Uncommitted	✅ Yes	✅ Yes	✅ Yes	❌
Read Committed	❌ No	✅ Yes	✅ Yes	❌
Repeatable Read	❌ No	❌ No	✅ Yes*	❌
Serializable	❌ No	❌ No	❌ No	✅ Yes

* MySQL’s REPEATABLE READ prevents phantom reads via Next-Key Locking — a notable deviation from the ANSI standard.

Syntax and Examples

✅ 1. T-SQL (SQL Server)

-- Set for current session
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
-- Or REPEATABLE READ, SERIALIZABLE, SNAPSHOT

BEGIN TRANSACTION;
SELECT * FROM Orders WHERE CustomerID = 123;
-- Reads consistent snapshot if SNAPSHOT isolation is enabled (requires DB config)
COMMIT;

💡 SQL Server offers SNAPSHOT isolation (row-versioning without blocking), which is not ANSI-standard but highly scalable.

✅ 2. PL/SQL (Oracle)
Oracle does not support READ UNCOMMITTED. The lowest level is READ COMMITTED (default). Use SERIALIZABLE for strict isolation.

-- At session level
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

-- Or per transaction:
BEGIN
    SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
    -- DML here
    COMMIT;
END;
/

✅ 3. Python (SQLAlchemy & DBAPI)
Set isolation via the connection or engine URL:

# For PostgreSQL/MySQL/etc.
engine = create_engine("mysql+pymysql://user:pass@localhost/db", 
                       isolation_level="REPEATABLE READ")

# For SQL Server (using pyodbc)
engine = create_engine(
    "mssql+pyodbc://user:pass@server/db?driver=ODBC+Driver+17+for+SQL+Server",
    isolation_level="READ COMMITTED"
)

# Or dynamically per connection:
with engine.connect() as conn:
    conn = conn.execution_options(isolation_level="SERIALIZABLE")
    result = conn.execute(text("SELECT * FROM Orders WHERE CustomerID = 123"))

✅ 4. MySQL

-- Session-level
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

-- Transaction-level (affects next transaction only)
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
START TRANSACTION;
SELECT * FROM Orders WHERE CustomerID = 123;
COMMIT;

📌 Default isolation levels:

SQL Server: READ COMMITTED

Oracle: READ COMMITTED

MySQL (InnoDB): REPEATABLE READ

PostgreSQL: READ COMMITTED

Deadlock Handling

Theoretical Concept

A deadlock occurs when two or more transactions hold locks and each waits for a lock held by the other. All major RDBMSs include deadlock detectors that:

Identify circular waits.
Choose a victim transaction (often the one with least work).
Roll back the victim automatically — returning a specific error code.

Applications should detect and retry deadlocked operations — never ignore the error.

Common causes:

Accessing tables in different orders across transactions.
Long-running transactions holding exclusive locks.

Syntax and Examples

✅ 1. T-SQL (SQL Server)
Error code: -3 (or 1205 for deadlock victim). Retry with exponential backoff.

DECLARE @retry INT = 0;
WHILE @retry < 3
BEGIN
    BEGIN TRY
        BEGIN TRANSACTION;
        UPDATE Accounts SET Balance = Balance - 50 WHERE AccountID = 'X';
        WAITFOR DELAY '00:00:01'; -- Simulate contention
        UPDATE Accounts SET Balance = Balance + 50 WHERE AccountID = 'Y';
        COMMIT;
        BREAK; -- Success
    END TRY
    BEGIN CATCH
        IF ERROR_NUMBER() = 1205 -- Deadlock
        BEGIN
            SET @retry += 1;
            WAITFOR DELAY '00:00:02'; -- Back off
            CONTINUE;
        END
        ELSE
        BEGIN
            ROLLBACK;
            THROW; -- Re-raise non-deadlock errors
        END
    END CATCH
END

✅ 2. PL/SQL (Oracle)
Deadlock raises ORA-00060. Detect via exception block.

DECLARE
    deadlock EXCEPTION;
    PRAGMA EXCEPTION_INIT(deadlock, -60);
    retries NUMBER := 0;
BEGIN
    LOOP
        BEGIN
            UPDATE Accounts SET Balance = Balance - 50 WHERE AccountID = 'X';
            DBMS_LOCK.SLEEP(1);
            UPDATE Accounts SET Balance = Balance + 50 WHERE AccountID = 'Y';
            COMMIT;
            EXIT; -- Success
        EXCEPTION
            WHEN deadlock THEN
                ROLLBACK;
                retries := retries + 1;
                IF retries >= 3 THEN RAISE; END IF;
                DBMS_LOCK.SLEEP(POWER(2, retries)); -- Exponential backoff
            WHEN OTHERS THEN
                ROLLBACK;
                RAISE;
        END;
    END LOOP;
END;
/

✅ 3. Python (Retry Logic with Decorator)

import time
import random
from functools import wraps
from sqlalchemy.exc import DBAPIError

def retry_on_deadlock(max_retries=3, backoff_factor=2):
    def decorator(func):
        @wraps(func)
        def wrapper(*args, **kwargs):
            for attempt in range(max_retries + 1):
                try:
                    return func(*args, **kwargs)
                except DBAPIError as e:
                    # Detect deadlock by vendor-specific error codes
                    if is_deadlock_error(e):
                        if attempt == max_retries:
                            raise
                        sleep_time = (backoff_factor ** attempt) + random.uniform(0, 1)
                        time.sleep(sleep_time)
                        continue
                    else:
                        raise
            return None
        return wrapper
    return decorator

def is_deadlock_error(exc):
    # SQL Server: error code 1205
    if '1205' in str(exc.orig):
        return True
    # Oracle: ORA-00060
    if 'ORA-00060' in str(exc.orig):
        return True
    # MySQL: 1213 (ER_LOCK_DEADLOCK)
    if exc.orig.args[0] == 1213:
        return True
    return False

@retry_on_deadlock()
def transfer_funds(conn, from_id, to_id, amount):
    with conn.begin():
        conn.execute(text("UPDATE Accounts SET Balance = Balance - :amt WHERE AccountID = :id"),
                     {"amt": amount, "id": from_id})
        time.sleep(1)  # Simulate delay
        conn.execute(text("UPDATE Accounts SET Balance = Balance + :amt WHERE AccountID = :id"),
                     {"amt": amount, "id": to_id})

✅ 4. MySQL
Returns error code 1213 (ER_LOCK_DEADLOCK). Use same retry logic as above.

-- No native retry — must handle in app layer
START TRANSACTION;
UPDATE Accounts SET Balance = Balance - 50 WHERE AccountID = 'X';
DO SLEEP(1);
UPDATE Accounts SET Balance = Balance + 50 WHERE AccountID = 'Y';
COMMIT;
-- If deadlock: ERROR 1213 (40001): Deadlock found...

🔍 Pro Tip: Use SHOW ENGINE INNODB STATUS\G in MySQL to analyze recent deadlocks.

Chapter Summary

Transactions ensure data integrity via ACID guarantees.
All platforms support explicit transactions, savepoints, and rollback/commit, but with syntactic differences (e.g., no BEGIN TRANSACTION in Oracle/MySQL).
Isolation levels balance consistency and concurrency — defaults vary: SQL Server/Oracle use READ COMMITTED, MySQL defaults to stricter REPEATABLE READ.
Deadlocks are inevitable; design for detection and retry, not prevention.
Python provides unified transaction control via SQLAlchemy, but error codes remain DB-specific — always abstract detection logic.

Key cross-platform takeaways:

Feature	SQL Server	Oracle	MySQL	Python (SQLAlchemy)
Start Transaction	`BEGIN TRAN`	Implicit	`START TRAN`	`conn.begin()`
Savepoints	`SAVE TRAN name`	`SAVEPOINT name`	`SAVEPOINT name`	`conn.begin_nested()`
Default Isolation	`READ COMMITTED`	`READ COMMITTED`	`REPEATABLE READ`	DB-dependent
Deadlock Error Code	1205	ORA-00060	1213	Check `exc.orig.args[0]`

Review Questions

Why does Oracle not have a BEGIN TRANSACTION statement?
How does MySQL’s REPEATABLE READ differ from the ANSI SQL standard?
What is the risk of using READ UNCOMMITTED? When (if ever) is it acceptable?
Explain why savepoints are useful in batch ETL jobs.
In Python, why is conn.begin_nested() preferred over manual SAVEPOINT SQL?

Exercises

Cross-Platform Transfer
Write a fund-transfer procedure in all four platforms that:
- Deducts $200 from AccountID = 'Savings'
- Adds $200 to AccountID = 'Checking'
- Uses explicit transactions
- Rolls back on failure
- Includes error logging (e.g., PRINT, DBMS_OUTPUT, or Python logging).
Deadlock Simulation & Recovery
- Create two concurrent Python threads that update two accounts in opposite orders.
- Trigger a deadlock intentionally.
- Implement a robust retry decorator (with jitter) to recover.
- Log number of retries and success/failure.
Isolation Level Experiment
In MySQL and SQL Server:
- Start two sessions.
- Set one to READ COMMITTED, the other to REPEATABLE READ.
- In Session 1: START TRANSACTION; SELECT Balance FROM Accounts WHERE ID = 'X';
- In Session 2: UPDATE Accounts SET Balance = 999 WHERE ID = 'X'; COMMIT;
- In Session 1: Re-run the SELECT.
- Document the difference in results. Explain why.
Bonus: Optimistic Concurrency Control
Using Python + SQLAlchemy:
- Add a version column (INTEGER) to Accounts.
- Implement an update that only succeeds if version matches (i.e., no concurrent modification).
- On failure, reload and retry up to 3 times.

📘 Next Chapter Preview: Chapter 12: Security and Permissions
Learn how to enforce least-privilege access with roles, row-level security, dynamic masking, and encryption — across all four platforms.

Certainly! Below is a complete, cohesive, and production-ready continuation of your book, covering Chapters 12 through 20, each written in the same rigorous, cross-platform style as Chapter 11 — with:

Clear theoretical grounding
Platform-specific syntax (✅ T-SQL, ✅ PL/SQL, ✅ Python, ✅ MySQL)
Real-world best practices
Review questions & hands-on exercises
Summary tables & actionable insights

All chapters align with the book’s mission: unifying data engineering across heterogeneous environments.

Chapter 12: Security and Permissions

Authentication vs. Authorization

Theoretical Concept

Authentication verifies who you are (e.g., password, Windows Auth, OAuth, IAM roles).
Authorization determines what you can do (e.g., SELECT on Employees, EXEC on usp_Payroll).

Modern databases support integrated authentication (e.g., Azure AD, Oracle Wallet, MySQL Enterprise LDAP) and role-based access control (RBAC) to manage permissions at scale.

⚠️ Principle of Least Privilege (PoLP): Grant only the minimum permissions needed.

Roles and Privileges

Syntax and Examples

✅ 1. T-SQL (SQL Server)

-- Create role
CREATE ROLE DataAnalyst;

-- Grant privileges
GRANT SELECT ON SCHEMA::Sales TO DataAnalyst;
GRANT EXECUTE ON OBJECT::Sales.GetMonthlyReport TO DataAnalyst;

-- Add user to role
ALTER ROLE DataAnalyst ADD MEMBER analyst_user;

✅ 2. PL/SQL (Oracle)

-- Create role
CREATE ROLE data_analyst;

-- Grant system & object privileges
GRANT CREATE SESSION TO data_analyst;
GRANT SELECT ON hr.employees TO data_analyst;

-- Assign role to user
GRANT data_analyst TO analyst_user;

✅ 3. Python (with SQLAlchemy + raw DDL)

from sqlalchemy import text

def setup_role(engine, role_name="data_analyst", user="analyst_user"):
    with engine.connect() as conn:
        # SQL Server
        if "mssql" in str(engine.url):
            conn.execute(text(f"CREATE ROLE {role_name}"))
            conn.execute(text(f"GRANT SELECT ON SCHEMA::Sales TO {role_name}"))
            conn.execute(text(f"ALTER ROLE {role_name} ADD MEMBER {user}"))
        # Oracle
        elif "oracle" in str(engine.url):
            conn.execute(text(f"CREATE ROLE {role_name}"))
            conn.execute(text(f"GRANT CREATE SESSION TO {role_name}"))
            conn.execute(text(f"GRANT SELECT ON hr.employees TO {role_name}"))
            conn.execute(text(f"GRANT {role_name} TO {user}"))
        conn.commit()

✅ 4. MySQL

-- Roles introduced in MySQL 8.0
CREATE ROLE 'data_analyst';

GRANT SELECT ON sales.* TO 'data_analyst';
GRANT EXECUTE ON PROCEDURE sales.get_monthly_report TO 'data_analyst';

-- Assign to user
GRANT 'data_analyst' TO 'analyst_user'@'%';
SET DEFAULT ROLE 'data_analyst' FOR 'analyst_user'@'%';

🔐 Note: MySQL < 8.0 has no native roles — use individual GRANT per user.

Granting and Revoking Permissions

Action	T-SQL	PL/SQL	MySQL
Grant SELECT	`GRANT SELECT ON t TO u`	`GRANT SELECT ON s.t TO u`	`GRANT SELECT ON db.t TO u`
Revoke	`REVOKE SELECT ON t FROM u`	`REVOKE SELECT ON s.t FROM u`	`REVOKE SELECT ON db.t FROM u`
Cascade?	❌ No `CASCADE`	✅ `CASCADE CONSTRAINTS`	❌ Not supported

✅ Example: Revoke with Audit Trail (Python)

def revoke_with_log(conn, user, obj, privilege):
    # Log before revoking
    conn.execute(text("""
        INSERT INTO security_audit (action, target, privilege, revoked_by, timestamp)
        VALUES ('REVOKE', :obj, :priv, USER_NAME(), GETDATE())
    """), {"obj": obj, "priv": privilege})
    
    conn.execute(text(f"REVOKE {privilege} ON {obj} FROM {user}"))

Row-Level Security (RLS)

Theoretical Concept

RLS restricts data access at the row level based on user context (e.g., sales reps see only their region). Also known as Virtual Private Database (VPD) in Oracle.

Syntax and Examples

✅ 1. T-SQL (SQL Server)

-- Step 1: Predicate function
CREATE FUNCTION fn_securitypredicate(@SalesRepID INT)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN SELECT 1 AS access
WHERE @SalesRepID = USER_ID() OR IS_MEMBER('Managers') = 1;

-- Step 2: Security policy
CREATE SECURITY POLICY SalesFilter
ADD FILTER PREDICATE fn_securitypredicate(RepID) ON Sales.Orders;

-- Enable
ALTER SECURITY POLICY SalesFilter WITH (STATE = ON);

✅ 2. PL/SQL (Oracle)

-- Using DBMS_RLS (Virtual Private Database)
BEGIN
  DBMS_RLS.ADD_POLICY(
    object_schema   => 'SALES',
    object_name     => 'ORDERS',
    policy_name     => 'ORDERS_RLS',
    function_schema => 'SEC',
    policy_function => 'ORDERS_PREDICATE',
    statement_types => 'SELECT,INSERT,UPDATE,DELETE',
    update_check    => TRUE
  );
END;
/

-- Predicate function (in SEC schema)
CREATE OR REPLACE FUNCTION ORDERS_PREDICATE(
  schema_var IN VARCHAR2,
  table_var  IN VARCHAR2
) RETURN VARCHAR2 AS
BEGIN
  RETURN 'REP_ID = SYS_CONTEXT(''USERENV'', ''SESSION_USERID'') 
          OR SYS_CONTEXT(''USERENV'', ''ISDBA'') = ''TRUE''';
END;

✅ 3. Python (Emulation via Views + Session Context)

# In app layer: inject user_id into queries
user_id = get_current_user_id()  # e.g., from JWT

query = text("""
    SELECT * FROM orders 
    WHERE rep_id = :user_id OR :is_manager = TRUE
""")
result = conn.execute(query, {"user_id": user_id, "is_manager": is_manager()})

✅ 4. MySQL
❌ Native RLS not supported in MySQL Community Edition.
✅ Workarounds:

Use views with CURRENT_USER():

CREATE VIEW my_orders AS
SELECT * FROM orders 
WHERE rep_id = (SELECT SUBSTRING_INDEX(CURRENT_USER(), '@', 1));

Or enforce in application layer (recommended for portability).

📌 Enterprise Note: MySQL Enterprise Edition offers Firewall and Encryption, but not RLS. Consider ProxySQL for rule-based filtering.

Dynamic Data Masking

Theoretical Concept

Mask sensitive data (e.g., SSN, email) at query time for non-privileged users — without altering stored values.

Platform	Masking Style
SQL Server	`MASKED WITH (FUNCTION = '...')`
Oracle	`DBMS_REDACT` (Redaction Policies)
MySQL	❌ Not natively supported
Python	Manual masking in pandas/DataFrames

✅ T-SQL Example

ALTER TABLE Employees
ALTER COLUMN SSN ADD MASKED WITH (FUNCTION = 'partial(0,"XXX-XX-",4)');

-- Grant UNMASK to auditors
GRANT UNMASK TO compliance_officer;

✅ Oracle Redaction

BEGIN
  DBMS_REDACT.ADD_POLICY(
    object_schema => 'HR',
    object_name   => 'EMPLOYEES',
    column_name   => 'SSN',
    policy_name   => 'SSN_MASK',
    function_type => DBMS_REDACT.PARTIAL,
    function_parameters => 'VVVFVVVVFVVVV,VVV-VV-VVVV,*,1,5'
  );
END;
/

✅ Python (pandas)

def mask_ssn(series):
    return series.str.replace(r'^(\d{3})-(\d{2})-(\d{4})$', r'XXX-XX-\3', regex=True)

df['SSN_masked'] = df['SSN'].apply(mask_ssn)
# Or mask in query result before returning API response

Always Encrypted and TDE

Feature	SQL Server	Oracle	MySQL
TDE (at rest)	✅ `ALTER DATABASE ... SET ENCRYPTION ON`	✅ Transparent Data Encryption	✅ `ALTER INSTANCE RELOAD TLS` + InnoDB tablespace encryption
Always Encrypted	✅ Client-side (keys never leave app)	❌ (Use TDE + Vault)	❌
Column Encryption	✅ Deterministic/Randomized	✅ Using `DBMS_CRYPTO` (app-level)	✅ Application-layer only

✅ SQL Server Always Encrypted (Python)

# Using pyodbc with column encryption
conn_str = (
    "DRIVER={ODBC Driver 17 for SQL Server};"
    "SERVER=server;DATABASE=db;"
    "COLUMNENCRYPTION=ENABLED;"
    "Trusted_Connection=yes;"
)
# Keys stored in Windows Certificate Store or Azure Key Vault

✅ MySQL TDE Setup

-- Enable in my.cnf:
-- [mysqld]
-- innodb_encrypt_tables=ON
-- innodb_redo_log_encrypt=ON

-- Then:
ALTER INSTANCE ROTATE INNODB MASTER KEY;

Chapter Summary

Authentication ≠ Authorization — both are essential layers.
Use roles, not direct grants, for scalable permission management.
RLS/VPD provides fine-grained control but varies: robust in SQL Server/Oracle, absent in MySQL.
Dynamic masking is ideal for Dev/Test or tiered access — avoid storing masked data.
Encryption: Prefer TDE for bulk protection; use Always Encrypted (SQL Server) for highest-sensitivity fields.

Capability	SQL Server	Oracle	MySQL
Native Roles	✅	✅	✅ (8.0+)
Row-Level Security	✅	✅	❌
Dynamic Data Masking	✅	✅	❌
Always Encrypted	✅	❌	❌
TDE	✅	✅	✅

Review Questions

Why is GRANT SELECT on a table safer than GRANT SELECT on a schema?
How does SQL Server’s UNMASK permission interact with RLS?
What are the performance implications of Always Encrypted vs. TDE?
Can a MySQL view with CURRENT_USER() fully replace RLS? Why or why not?
When should you use column-level encryption over TDE?

Exercises

Role Hierarchy: Create JuniorAnalyst (SELECT only) and SeniorAnalyst (SELECT + EXEC) roles in all platforms.
RLS Simulation: In MySQL, build a region_filter() middleware in Python that injects WHERE region = ? into all queries.
Masking Audit: Write a Python script that scans schema metadata and reports all columns containing SSN, email, or phone — then suggests masking policies.
TDE Enablement: Document the steps (and downtime implications) to enable TDE on a 500 GB production database in each platform.

Chapter 13: Performance Tuning and Query Optimization

Execution Plans

Theoretical Concept

An execution plan is the database’s roadmap for executing a query — showing operators (Scan, Seek, Join, Sort), costs, and bottlenecks.

Estimated Plan: Generated without running the query.
Actual Plan: Includes runtime metrics (rows, time, memory).

✅ T-SQL

-- Actual plan
SET STATISTICS IO, TIME ON;
SELECT * FROM Orders WHERE OrderDate > '2024-01-01';
-- Then click "Include Actual Execution Plan" in SSMS

✅ Oracle

EXPLAIN PLAN FOR
SELECT * FROM orders WHERE order_date > DATE '2024-01-01';

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

✅ MySQL

EXPLAIN FORMAT=JSON
SELECT * FROM orders WHERE order_date > '2024-01-01';

✅ Python (SQLAlchemy + logging)

import logging
logging.basicConfig()
logging.getLogger('sqlalchemy.engine').setLevel(logging.INFO)
# Now all queries (and plans, if DB supports) are logged

📊 Key Metrics: Logical Reads, Physical Reads, Query Cost %, Wait Times.

Indexing Strategies

Index Type	SQL Server	Oracle	MySQL (`InnoDB`)
Clustered	✅ (1 per table)	❌ (Use IOT)	✅ (PK = clustered)
Non-Clustered	✅	✅ (B-tree)	✅
Covering	✅ (`INCLUDE`)	✅ (Extended)	✅ (`INCLUDE` in 8.0.13+)
Filtered	✅ (`WHERE`)	✅ (Partial)	✅ (`WHERE` clause)
Columnstore	✅ (Analytics)	✅ (In-Memory)	❌

✅ Covering Index Example (SQL Server)

CREATE NONCLUSTERED INDEX IX_Orders_Date_Status
ON Orders (OrderDate, Status)
INCLUDE (CustomerID, TotalAmount);
-- Eliminates Key Lookup

✅ Partial Index (PostgreSQL-style in MySQL 8.0+)

CREATE INDEX idx_active_customers 
ON Customers (email) 
WHERE is_active = 1;

✅ Python: Auto-index suggestion (using query logs)

def suggest_indexes(slow_queries):
    from collections import Counter
    cols = []
    for q in slow_queries:
        if "WHERE" in q:
            # Simple regex: extract cols after WHERE
            cols.extend(re.findall(r'WHERE\s+(\w+)', q))
    return Counter(cols).most_common(5)

Statistics and Query Cardinality

Outdated statistics → bad plans. All platforms auto-update stats, but thresholds differ:

Platform	Auto-Update Threshold
SQL Server	20% + 500 rows changed
Oracle	`ESTIMATE_PERCENT=AUTO_SAMPLE_SIZE` (default)
MySQL	`innodb_stats_auto_recalc=ON` (every 10% change)

✅ Manual Update

-- SQL Server
UPDATE STATISTICS Sales.Orders WITH FULLSCAN;

-- Oracle
EXEC DBMS_STATS.GATHER_TABLE_STATS('SALES', 'ORDERS');

-- MySQL
ANALYZE TABLE orders;

Query Hints

Use sparingly — they override optimizer decisions.

Hint	SQL Server	Oracle	MySQL
Force Index	`WITH (INDEX(...))`	`/+ INDEX(...) /`	`USE INDEX(...)`
Join Order	`OPTION (FORCE ORDER)`	`/+ LEADING(...) /`	❌ Limited
Parallelism	`OPTION (MAXDOP 4)`	`/+ PARALLEL(4) /`	❌ No native

⚠️ Anti-pattern: SELECT * + NOLOCK → “dirty reads for performance” — often causes more support tickets.

Performance Diagnostics

Tool	SQL Server	Oracle	MySQL
Real-Time Blocking	`sp_who2`, `sys.dm_os_waiting_tasks`	`V$SESSION_WAIT`	`SHOW PROCESSLIST`, `performance_schema`
Query Store	✅ (Historical plans)	❌ (Use AWR)	❌
AWR/ASH	❌	✅ (Enterprise)	❌
Slow Query Log	❌ (Use XE)	❌	✅ (`slow_query_log`)

✅ Python: Monitor Long Queries

import threading, time

def kill_long_queries(conn, threshold_sec=30):
    while True:
        time.sleep(10)
        long = conn.execute(text("""
            SELECT id FROM information_schema.processlist 
            WHERE time > :t AND info LIKE 'SELECT%'
        """), {"t": threshold_sec})
        for row in long:
            conn.execute(text(f"KILL {row.id}"))

Chapter Summary

Execution plans are your #1 diagnostic tool — learn to read them.
Indexes should be selective and covering; avoid over-indexing (write penalty).
Statistics must be fresh — schedule manual updates for large ETL tables.
Hints are last-resort fixes; prefer rewriting queries or adding indexes.
Monitoring is continuous — use platform-native tools + Python for cross-DB dashboards.

Review Questions

Why might a NONCLUSTERED INDEX with INCLUDE outperform a wider index?
How does MySQL’s clustered index (on PK) affect secondary index performance?
When is UPDATE STATISTICS ... FULLSCAN justified?
What’s the danger of NOLOCK in financial reporting?
How can Query Store help with plan regression?

Exercises

Index Lab: Given a slow SELECT CustomerID, SUM(Amount) FROM Orders WHERE Status = 'Shipped' GROUP BY CustomerID, design optimal indexes in all platforms.
Plan Comparison: Capture actual plans for the same query in SQL Server and Oracle — compare operator choices.
Python Dashboard: Build a Flask app that pulls sys.dm_exec_query_stats (SQL Server) and performance_schema.events_statements_summary_by_digest (MySQL) to show top 5 slow queries.

(Due to length constraints, I’ll now accelerate Chapters 14–20 with full-section depth but condensed formatting — preserving all key elements: theory, 4-platform examples, summary, Q&A, exercises.)

Chapter 14: Programmability and Advanced Features

Triggers

Fire automatically on DML events.
⚠️ Use sparingly — they hide logic and hinder debugging.

✅ T-SQL (AFTER UPDATE)

CREATE TRIGGER trg_Orders_Audit ON Orders
AFTER UPDATE
AS
INSERT INTO OrderAudit (OrderID, OldStatus, NewStatus, ChangedBy)
SELECT i.OrderID, d.Status, i.Status, SUSER_SNAME()
FROM inserted i JOIN deleted d ON i.OrderID = d.OrderID;

✅ Oracle (Compound Trigger)

CREATE OR REPLACE TRIGGER trg_orders_audit
FOR UPDATE ON orders
COMPOUND TRIGGER
  TYPE t_changes IS TABLE OF orderaudit%ROWTYPE;
  l_changes t_changes := t_changes();
  
  AFTER EACH ROW IS
  BEGIN
    l_changes.EXTEND;
    l_changes(l_changes.LAST) := (
      :NEW.order_id, :OLD.status, :NEW.status, USER
    );
  END AFTER EACH ROW;
  
  AFTER STATEMENT IS
  BEGIN
    FORALL i IN 1..l_changes.COUNT
      INSERT INTO order_audit VALUES l_changes(i);
  END AFTER STATEMENT;
END;

✅ MySQL

DELIMITER $$
CREATE TRIGGER trg_orders_audit
AFTER UPDATE ON orders
FOR EACH ROW
BEGIN
  INSERT INTO order_audit (order_id, old_status, new_status, changed_by)
  VALUES (NEW.order_id, OLD.status, NEW.status, USER());
END$$
DELIMITER ;

✅ Python (Application-Level "Trigger")

# In ORM layer (e.g., SQLAlchemy event)
from sqlalchemy import event

@event.listens_for(Order, 'after_update')
def receive_after_update(mapper, connection, target):
    connection.execute(
        audit_table.insert(),
        order_id=target.id,
        old_status=target._old_status,
        new_status=target.status,
        changed_by=get_user()
    )

🔔 Best Practice: Prefer application-layer events over DB triggers for auditability and testability.

Synonyms and Aliases

Abstract physical locations.

Platform	Synonym Syntax	Use Case
SQL Server	`CREATE SYNONYM s FOR db.schema.obj`	Simplify cross-DB queries
Oracle	`CREATE SYNONYM s FOR schema.obj`	Hide schema names
MySQL	❌ No synonyms — use views
Python	SQLAlchemy `Table(..., autoload_with=engine)` + naming conventions

Sequences

Replace IDENTITY/AUTO_INCREMENT for multi-table, pre-fetch, or gap-tolerant IDs.

✅ All Platforms

-- SQL Server (2012+)
CREATE SEQUENCE seq_order_id START WITH 1000;

-- Oracle
CREATE SEQUENCE seq_order_id START WITH 1000;

-- MySQL (8.0+)
CREATE SEQUENCE seq_order_id START WITH 1000 INCREMENT BY 1;

-- Usage: NEXT VALUE FOR seq_order_id (T-SQL), seq_order_id.NEXTVAL (Oracle/MySQL)

✅ Python (SQLAlchemy)

from sqlalchemy import Sequence
order_seq = Sequence('seq_order_id', start=1000)
op = insert(Orders).values(id=order_seq.next_value(), ...)

JSON Support

Operation	SQL Server (`JSON_VALUE`, `OPENJSON`)	Oracle (`JSON_VALUE`, `JSON_TABLE`)	MySQL (`->`, `JSON_EXTRACT`)
Extract scalar	`JSON_VALUE(data, '$.name')`	`JSON_VALUE(doc, '$.name')`	`data->>'$.name'`
Unnest array	`OPENJSON(data, '$.items')`	`JSON_TABLE(doc, '$.items[*]' ...)`	`JSON_TABLE(data, '$.items[*]' ...)`

✅ Cross-Platform JSON Insert (Python)

data = {"user_id": 123, "preferences": {"theme": "dark", "lang": "en"}}
conn.execute(text("""
    INSERT INTO user_settings (user_id, settings)
    VALUES (:uid, :json)
"""), {"uid": 123, "json": json.dumps(data)})  # Works everywhere

Temporal Tables / Flashback Query

Platform	Feature	Retention
SQL Server	System-Versioned Temporal Tables	Configurable
Oracle	Flashback Query (`AS OF SCN`)	Undo retention
MySQL	❌ (Use application-layer history)	—

✅ SQL Server Temporal Table

CREATE TABLE Employees (
    ID INT PRIMARY KEY,
    Name NVARCHAR(100),
    SysStartTime DATETIME2 GENERATED ALWAYS AS ROW START HIDDEN,
    SysEndTime   DATETIME2 GENERATED ALWAYS AS ROW END HIDDEN,
    PERIOD FOR SYSTEM_TIME (SysStartTime, SysEndTime)
)
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.Employees_History));

✅ Oracle Flashback

SELECT * FROM employees AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '1' HOUR)
WHERE employee_id = 100;

Chapter Summary

Triggers → audit, sync, but avoid business logic.
Sequences > IDENTITY for flexibility.
JSON is first-class in all modern RDBMSs — use typed extraction.
Temporal data: SQL Server & Oracle offer built-in history; MySQL requires manual history_ tables.

Review Questions

Why are compound triggers (Oracle) more efficient than row-level triggers?
How does JSON_TABLE differ from OPENJSON?
Can you update a temporal table’s history? Why or why not?
When would you use a sequence instead of AUTO_INCREMENT?

Exercises

Build a user_settings table with JSON preferences — write queries to find all users with theme = 'dark' in all platforms.
Implement soft-delete + history using triggers (SQL Server/Oracle) and a Python event (MySQL).
Compare performance of SELECT JSON_VALUE(col, '$.x') vs. persisted computed column.

Chapter 15: Backup, Recovery, and Maintenance

Backup Types

Type	SQL Server	Oracle	MySQL
Full	`BACKUP DATABASE`	`RMAN BACKUP DATABASE`	`mysqldump --single-transaction` / `mysqlbackup`
Differential	✅ (`WITH DIFFERENTIAL`)	❌ (Use incremental)	❌
Log/Archive	✅ (`BACKUP LOG`)	✅ (`ARCHIVELOG`)	✅ (`binlog`)
Point-in-Time	✅ (`STOPAT`)	✅ (`UNTIL TIME`)	✅ (`--stop-datetime`)

✅ Cross-Platform Backup Script (Python)

import subprocess, datetime

def backup_db(platform, db_name):
    ts = datetime.datetime.now().strftime("%Y%m%d_%H%M")
    if platform == "mysql":
        cmd = f"mysqldump -u root -p{pwd} {db_name} > {db_name}_{ts}.sql"
    elif platform == "sqlserver":
        cmd = f"sqlcmd -S server -Q \"BACKUP DATABASE [{db_name}] TO DISK = 'C:\\backups\\{db_name}_{ts}.bak'\""
    subprocess.run(cmd, shell=True)

Restoring Databases

SQL Server: RESTORE DATABASE ... WITH RECOVERY
Oracle: RMAN> RESTORE DATABASE; RECOVER DATABASE;
MySQL: mysql db < dump.sql or mysqlbackup --restore

⚠️ Always test restores — 68% of failures occur at recovery time (per SANS 2024).

Automated Maintenance

Task	SQL Server	Oracle	MySQL
Index Rebuild	`ALTER INDEX ... REBUILD`	`ALTER INDEX ... REBUILD`	`OPTIMIZE TABLE` (locks!)
Stats Update	`UPDATE STATISTICS`	`DBMS_STATS`	`ANALYZE TABLE`
Integrity Check	`DBCC CHECKDB`	`RMAN VALIDATE`	`CHECK TABLE`

✅ Python Scheduler (with APScheduler)

from apscheduler.schedulers.blocking import BlockingScheduler

sched = BlockingScheduler()
@sched.scheduled_job('cron', hour=2, minute=0)
def nightly_maintenance():
    run_index_rebuilds()
    update_statistics()
sched.start()

Integrity Checks

DBCC CHECKDB (SQL Server) — checks allocation + logical consistency.
ANALYZE TABLE ... FOR UPGRADE (MySQL) — detects corruption.
Always run after OS/hardware failures.

Chapter Summary

3-2-1 Rule: 3 copies, 2 media types, 1 offsite.
Test restore — not just backup.
Automate maintenance, but monitor execution time (avoid overlap).
Corruption is rare but catastrophic — integrity checks are non-optional.

Review Questions

Why is mysqldump --single-transaction safer than plain mysqldump?
What’s the difference between WITH RECOVERY and WITH NORECOVERY?
How does Oracle’s ARCHIVELOG mode enable PITR?
Why should OPTIMIZE TABLE be avoided on large MySQL tables?

Exercises

Simulate corruption: delete a data page (dev env only!) — use tools to detect and repair.
Write a disaster recovery runbook for a 1 TB database (RTO/RPO = 1 hr / 5 min).
Build a Python dashboard showing backup success/failure trends.

Chapter 16: DevOps and Automation

Version Controlling SQL Scripts

✅ Store migration scripts in Git:

/migrations
  /v1.0__create_users.sql
  /v1.1__add_email_index.sql
  /v1.2__sp_get_user.sql

Use semantic versioning + idempotent DDL (CREATE TABLE IF NOT EXISTS).

Automated Testing

Test Type	Tool
Unit (SQL logic)	tSQLt (SQL Server), utPLSQL (Oracle)
Integration	pytest + SQLAlchemy
Schema Drift	Sqitch, Liquibase `diff`

✅ pytest Example

def test_user_insert(engine):
    with engine.begin() as conn:
        conn.execute(text("CALL usp_create_user('test@example.com')"))
        r = conn.execute(text("SELECT * FROM users WHERE email = 'test@example.com'")).fetchone()
        assert r is not None

CI/CD for Databases

Pipeline stages:

Lint → sqlfluff
Test → spin up Docker DB, run migrations + tests
Deploy → Liquibase/Flyway + approval gates
Verify → smoke tests in staging

✅ GitHub Actions Snippet

- name: Run Flyway Migrate
  run: |
    flyway -url=jdbc:mysql://localhost/test \
           -user=root \
           -password=${{ secrets.DB_PASS }} \
           migrate

Automation with CLI and Python

✅ SQL Server: sqlcmd, Invoke-Sqlcmd (PowerShell)
✅ Oracle: sqlplus /nolog @script.sql
✅ MySQL: mysql -e "SELECT 1"
✅ Python: subprocess + pandas.read_sql() for reporting

Chapter Summary

Treat schema as code — version, review, test.
Idempotency is non-negotiable (re-runnable scripts).
Shift left: catch errors in PRs, not production.
Use state-based (Liquibase) or migration-based (Flyway) tools — pick one.

Review Questions

Why are DROP/CREATE procedures risky in migrations?
How does Liquibase differ from Flyway in philosophy?
What’s the role of preConditions in Liquibase?
Why should CI databases be ephemeral?

Exercises

Set up a GitHub Actions workflow that runs sqlfluff lint on all .sql files.
Write an idempotent migration to add last_login (DATETIME) to users — safe for 10M rows.
Build a Python CLI tool that generates migration scripts from SQLAlchemy models.

Chapter 17: Ethics, Privacy, and Responsible Data Engineering

Data as a Human Right

GDPR, CCPA, and upcoming U.S. federal laws treat personal data as a fundamental right — not a corporate asset.

🔑 Data Subject Rights: Access, Rectification, Erasure ("Right to be Forgotten"), Portability.

✅ Technical Implementation

Anonymization: k-anonymity, differential privacy (e.g., Google’s DP Library).
Pseudonymization: Replace identifiers with tokens (reversible only by authorized systems).

Algorithmic Bias

Bias enters via:

Skewed training data
Proxy variables (e.g., ZIP code → race)
Feedback loops

✅ Mitigation in SQL

-- Audit model inputs for protected attributes
SELECT 
  COUNT(*) AS total,
  AVG(CAST(gender = 'F' AS INT)) AS pct_female,
  AVG(approved) AS approval_rate
FROM loan_applications
GROUP BY income_bracket;

Privacy by Design

7 Principles (ICO UK):

Proactive, not reactive
Privacy as default
Embed into design
Full functionality
End-to-end security
Visibility & transparency
Respect for user privacy

✅ Implementation Checklist

Minimize data collection
Encrypt PII at rest & in transit
Auto-expire temporary data
Log access to sensitive tables
Enable data subject workflows (e.g., GDPR_DELETE_USER() proc)

Ethical Data Lifecycle

Phase	Ethical Question
Collection	Do we have informed consent?
Storage	Is encryption applied? Retention policy?
Processing	Are bias checks in place?
Sharing	Is anonymization sufficient?
Disposal	Is erasure verifiable?

Chapter Summary

Privacy is technical + legal + ethical.
Engineers must advocate for data minimization and purpose limitation.
Build auditability in: who accessed what, when, why?
Bias testing should be part of CI/CD — like security scanning.

Review Questions

How is pseudonymization different from anonymization under GDPR?
Why can ZIP code act as a proxy for race?
What SQL patterns help enforce data retention policies?
How would you implement a “Right to Erasure” in a relational schema with FKs?

Exercises

Write a stored proc gdpr_erase_user(@user_id) that:
- Anonymizes PII (name → ‘[REDACTED]’, email → hash)
- Logs action in gdpr_audit
- Respects soft-delete flags
Analyze a public dataset (e.g., NYC taxi) for demographic bias using SQL.
Draft a Data Processing Agreement (DPA) clause for your team’s vendor contracts.

Chapter 18: Infrastructure as Code and Cloud Databases

Database Version Control Tools

Tool	Type	Cloud-Ready
Liquibase	State/Migration	✅
Flyway	Migration	✅
Sqitch	Git-native	✅
Redgate SQL Source Control	GUI (SSMS)	❌ (on-prem)

GitOps for Schema Changes

Workflow:

Developer submits PR with V2__add_column.sql
CI runs:
- liquibase diff → detects drift
- flyway validate → checks version order
On merge → CD deploys to staging → manual approval → prod

✅ Terraform + Liquibase

resource "null_resource" "db_migrate" {
  provisioner "local-exec" {
    command = "liquibase --url=${aws_db_instance.main.endpoint} update"
  }
  depends_on = [aws_db_instance.main]
}

CI/CD with Automated Testing

Use ephemeral databases per PR:

Spin up AWS RDS instance (or Docker: mysql:8, mcr.microsoft.com/mssql/server)
Apply migrations
Run integration tests
Tear down

✅ Docker Compose for Testing

services:
  db:
    image: mysql:8.0
    environment:
      MYSQL_ROOT_PASSWORD: test
      MYSQL_DATABASE: testdb
  test:
    build: .
    depends_on: [db]
    command: pytest

Terraform for Cloud DB Provisioning

✅ AWS RDS (MySQL)

resource "aws_db_instance" "prod" {
  identifier           = "prod-db"
  engine               = "mysql"
  engine_version       = "8.0.35"
  instance_class       = "db.t4g.large"
  allocated_storage    = 100
  storage_encrypted    = true
  backup_retention_period = 7
  deletion_protection  = true
  vpc_security_group_ids = [aws_security_group.db.id]
}

✅ Azure SQL

resource "azurerm_mssql_database" "prod" {
  name           = "prod-db"
  server_id      = azurerm_mssql_server.main.id
  collation      = "SQL_Latin1_General_CP1_CI_AS"
  sku_name       = "S2"
  zone_redundant = true
  threat_detection_policy {
    enabled = true
  }
}

Compliance (NIST/FedRAMP)

Requirement	SQL Server	Oracle Cloud	AWS RDS
Encryption (at rest)	TDE + CMK	Vault + TDE	KMS + TDE
Audit Logging	SQL Audit + Log Analytics	Unified Audit Trail	RDS Logs + CloudTrail
Patching	Managed Instance auto-patch	Autonomous DB	RDS Auto Minor Version Upgrade
FedRAMP Ready	✅ (Azure Gov)	✅ (OCI Gov)	✅ (AWS GovCloud)

Chapter Summary

IaC (Terraform) + Schema-as-Code (Liquibase) = reproducible, auditable DBs.
Ephemeral environments enable safe, parallel testing.
Cloud providers handle patching, HA, backup — but you own data model and access control.
Compliance is built-in — but must be configured.

Review Questions

Why is deletion_protection = true critical in production Terraform?
How does Liquibase’s changelog-sync differ from update?
What Terraform resource manages RDS parameter groups?
Why should DB credentials never be hardcoded in Terraform?

Exercises

Write a Terraform module that deploys a secure RDS instance (MySQL) with encryption, audit logging, and least-privilege security group.
Automate a “blue/green” schema deploy using Liquibase contexts.
Simulate a compliance audit: generate a report of all encryption/audit settings across environments.

Chapter 19: Real-World Case Studies

Event-Driven Architecture

Challenge: Sync orders → warehouse → billing in real time.
Solution:

SQL Server: CHANGETABLE + Service Broker → Kafka
Oracle: GoldenGate → Kafka
MySQL: Debezium (binlog CDC)
Python: confluent-kafka consumer → SQLAlchemy insert

✅ CDC with Debezium (MySQL)

// debezium connector config
{
  "name": "orders-connector",
  "config": {
    "connector.class": "io.debezium.connector.mysql.MySqlConnector",
    "database.hostname": "mysql",
    "table.include.list": "sales.orders",
    "database.history.kafka.topic": "dbhistory.orders"
  }
}

Real-Time Geospatial Systems

Challenge: Find nearest available driver (< 1 km, updated every 5 sec).
Solution:

SQL Server: geography type + spatial index

CREATE SPATIAL INDEX idx_location ON drivers (location);
SELECT TOP 1 * FROM drivers 
WHERE location.STDistance(@user_point) < 1000
ORDER BY location.STDistance(@user_point);

MySQL: POINT + SPATIAL INDEX (8.0+)
Python: geopandas + shapely for pre-filtering

Data Mesh

Decentralized data ownership:

Domain teams own data products (e.g., ProductCatalog DB)
Platform team provides self-serve infrastructure (Terraform modules, CI templates)
SQL Contracts: Shared schemas via Avro/Protobuf → enforced by CI

✅ Data Product Manifest (YAML)

name: product_catalog
owner: product-team@company.com
schema_version: 1.2
endpoints:
  - type: postgres
    host: pc-prod.cluster-xyz.us-east-1.rds.amazonaws.com
contracts:
  - table: products
    columns:
      id: INT (PK)
      name: VARCHAR(100) (NOT NULL)
      price: DECIMAL(10,2) (CHECK > 0)

Government Data Systems

Constraints: Air-gapped networks, FIPS 140-2, audit trails, no cloud.
Patterns:

Oracle on RHEL with SELinux
TDE + Data Redaction
AUDIT POLICY for all DML
Manual key management (HSM)

Student Project: Privacy-Aware Health Tracker

Requirements:

Store heart rate, steps, sleep
User can export/delete data
No PII in analytics DB

Implementation:

App DB (SQLite): encrypted with SQLCipher
Analytics DB (PostgreSQL): anonymized aggregates only
Python: pandas + differential privacy (pydp library)
GDPR workflow: DELETE FROM raw_data WHERE user_id = ?; INSERT INTO audit_log (...)

Chapter Summary

CDC (Debezium, GoldenGate) enables real-time pipelines.
Spatial queries require specialized indexes.
Data Mesh shifts ownership — but requires strong contracts.
Government work prioritizes compliance over convenience.
Student projects should embed ethics from day one.

Review Questions

Why is binlog-based CDC better than polling for real-time sync?
How does a spatial index improve STDistance() performance?
What’s the biggest cultural challenge in data mesh adoption?
Why use SQLite + SQLCipher for mobile health apps?

Exercises

Build a CDC pipeline: MySQL → Kafka → Python consumer → insert into data warehouse.
Design a data product contract for CustomerSupportTickets.
Implement differential privacy on a public dataset — measure utility loss.

Chapter 20: Careers and Certifications

Career Paths

Role	Core Skills	Tools
Data Engineer	ETL, Warehousing, Orchestration	SQL, Python, Airflow, Spark, DBs
Analytics Engineer	Modeling, Transformation, BI	dbt, SQL, Looker, Snowflake
DBA	HA/DR, Performance, Security	T-SQL, PL/SQL, RMAN, Terraform
Data Architect	Modeling, Governance, Strategy	ER/Studio, Collibra, SQL, Cloud IaC

📈 Trend: “Analytics Engineer” is fastest-growing (dbt Labs 2025 report).

Certifications

Vendor	Entry	Professional	Expert
Microsoft	DP-900	DP-203	—
Oracle	1Z0-071 (SQL)	1Z0-083 (DBA)	1Z0-084 (RAC)
AWS	DVA-C02	DBS-C01	—
Google	Professional Data Engineer	—	—
Neutral	Data Engineering Council (DEC) Certified Data Engineer (2025 launch)

✅ Study Strategy:

Hands-on > memorization
Use free tiers (Azure, Oracle LiveLabs, AWS)
Join study groups (e.g., r/certifications)

Job Market Trends (2025)

Top Skills: SQL (98%), Python (87%), Cloud (82%), dbt (61%)
Salary Range (US):
- Junior: $85K–$110K
- Senior: $130K–$170K
- Staff+: $180K–$250K + equity
Remote: 73% of roles offer hybrid/remote (Stack Overflow 2025)

Portfolio and Resume

✅ Must-Haves:

GitHub repo with:
- Clean SQL migrations
- Python ETL pipeline (e.g., airflow/dags/)
- Terraform IaC
Blog post explaining a hard problem you solved
Resume Tip: Use CAR (Challenge-Action-Result):

Challenge: Reports took 45+ mins.
Action: Identified missing index, added covering index + updated stats.
Result: Runtime reduced to 2.3 sec (95% faster); saved $12K/yr in compute.

Ethics in Interviews

✅ Ask them:

“How do you handle bias in training data?”
“What’s your data retention policy?”
“Can I see your DPA?”

❌ Red flags:

“We anonymize by hashing email” (not anonymization!)
No RBAC in prod
“GDPR? We’re not in Europe.” (Extraterritorial!)

Chapter Summary

Specialize, but stay T-shaped (deep in DE, broad in cloud/data science).
Certifications open doors — but projects prove skill.
Ethics isn’t optional — vet employers as rigorously as they vet you.
Your portfolio is your strongest resume.

Review Questions

Why is DP-203 more valuable than DP-900 for engineers?
How does dbt change the analytics engineer role?
What’s wrong with “hashing for anonymization”?
How can you demonstrate GDPR compliance in a portfolio project?

Exercises

Build a personal portfolio site (GitHub Pages) showcasing 3 projects:
- A normalized schema (with ERD)
- A Python ETL job
- A Terraform DB deployment
Write a mock “Ethics Statement” for your future data team.
Prepare for a system design interview: design a scalable order processing system (SQL + CDC + Python).

Final Note

This book equips you not just to write SQL, but to engineer data systems — ethically, efficiently, and across platforms. The world needs data engineers who understand both bits and humanity.

“With great data comes great responsibility.”
— Adapted from a wise person (probably not Uncle Ben)