DML & DDL Syntax Core Fundamentals

Structured Query Language (SQL) is the foundational declarative programming language governing relational database management systems (RDBMS). Conceived around Edgar F. Codd's mathematical relational model at IBM in the 1970s, SQL abstracts the physical mechanics of secondary storage, disk page layout, and index traversal behind mathematical set theory and first-order predicate calculus.

This guide provides an exhaustive, university-grade examination of SQL syntax and engine architecture. We examine the relational algebra foundations of SQL, Data Definition Language (DDL) constraints, Data Manipulation Language (DML) mutation lifecycles, the strict logical execution order of SQL queries, transaction control, storage data types (including arbitrary-precision decimals and JSONB), and the physical mechanics of Write-Ahead Logging (WAL).

1. The Relational Model & Relational Algebra Foundations#

SQL is not an arbitrary collection of keywords; it is a concrete declarative syntax derived from Relational Algebra and Tuple Relational Calculus.

       Relational Algebra to SQL Conceptual Mapping

       Relational Algebra Operation             SQL Implementation
       =============================================================================
       Selection:  sigma_phi(R)        <=====>  WHERE phi
       Projection: pi_{A, B}(R)        <=====>  SELECT A, B
       Cartesian:  R x S               <=====>  FROM R CROSS JOIN S
       Join:       R bowtie_theta S    <=====>  FROM R JOIN S ON theta
       Union:      R union S           <=====>  SELECT ... UNION SELECT ...
       Difference: R \ S               <=====>  EXCEPT / MINUS
       Intersection: R intersect S     <=====>  INTERSECT

The Six Primitive Relational Algebra Operators#

A relational algebra query operates on one or more relations (tables) and evaluates to a new relation. Codd defined six fundamental primitive operators from which all relational transformations derive:

1. Selection (σ)

Filters tuples (rows) that satisfy a boolean predicate formula φ:

σ_{φ}(R) = \{ t | t ∈ R and φ(t) is True \}In SQL: The WHERE clause.

σ_{salary > 75000}(Employees) <===> SELECT FROM Employees WHERE salary > 75000;2. Projection (π)*

Extracts specified attributes (columns) A_1, ..., A_k, discarding unmentioned columns and eliminating duplicate rows:

π_{A_1, ..., A_k}(R) = \{ t[A_1, ..., A_k] | t ∈ R \}In SQL: The SELECT attribute list with DISTINCT.

π_{first_name, salary}(Employees) <===> SELECT DISTINCT first_name, salary FROM Employees;3. Cartesian Product (×)

Combines every tuple of relation R with every tuple of relation S:

R × S = \{ r ∪ s | r ∈ R and s ∈ S \}If |R| = M and |S| = N, the Cartesian product contains exactly M × N tuples.

In SQL: FROM R CROSS JOIN S or FROM R, S.

4. Set Union (∪)

Combines all tuples from two union-compatible relations (identical schema arity and compatible data types):

R ∪ S = \{ t | t ∈ R or t ∈ S \}In SQL: UNION (which deduplicates tuples by default) vs UNION ALL (which preserves duplicates).

5. Set Difference (\)

Yields tuples present in relation R that do not appear in relation S:

R \ S = \{ t | t ∈ R and t ∉ S \}In SQL: EXCEPT (PostgreSQL, SQLite) or MINUS (Oracle).

6. Rename (ρ)

Renames a relation or its attributes:

ρ_{S(B_1, ..., B_n)}(R)In SQL: Table and column aliasing (FROM Employees AS E, SELECT salary AS compensation).

2. Data Definition Language (DDL) Architecture & Schema Engineering#

Data Definition Language (DDL) statements construct, modify, and drop relational schema objects (tables, schemas, views, constraints, indexes). DDL modifications manipulate the database's internal Data Dictionary (System Catalogs).

       Relational Integrity Constraint Architecture

       +-------------------------------------------------------------+
       |                         Orders Table                        |
       |  order_id (PRIMARY KEY, Clustered Index, NOT NULL, UNIQUE)  |
       |  customer_id (FOREIGN KEY -> Customers.customer_id)        |
       |  order_date (DATE, NOT NULL, DEFAULT CURRENT_DATE)          |
       |  total_amount (DECIMAL(12, 2), CHECK (total_amount >= 0))   |
       +------------------------------+------------------------------+
                                      |
                                      | (Referential Integrity Check)
                                      v
       +-------------------------------------------------------------+
       |                        Customers Table                      |
       |  customer_id (PRIMARY KEY, BIGINT)                          |
       |  email (VARCHAR(255), UNIQUE, NOT NULL)                     |
       +-------------------------------------------------------------+

Relational Integrity Constraints#

DDL enforces relational invariants at the database engine level, guaranteeing data integrity regardless of upstream application bugs:

1. Entity Integrity (`PRIMARY KEY`):

- Enforces uniqueness and non-nullability across one or more columns.

- Automatically constructs a unique index (in MySQL InnoDB and Microsoft SQL Server, this is the physical Clustered Index, dictating the physical on-disk B+ tree sorting order of data rows).

1. Referential Integrity (`FOREIGN KEY ... REFERENCES`):

- Establishes a formal parent-child dependency between tables.

- Enforces cascading action rules upon parent modification:

- ON DELETE RESTRICT / NO ACTION: Aborts the transaction if child records exist.

- ON DELETE CASCADE: Automatically deletes all child rows when the parent row is deleted.

- ON DELETE SET NULL: Updates child foreign key columns to NULL (requires child column to be nullable).

1. Domain Integrity (`CHECK` & Data Types):

- Restricts permissible attribute values using boolean predicates:

CHECK (unit_price > 0.00 AND discount_pct BETWEEN 0.0 AND 1.0)

1. Column Uniqueness (`UNIQUE`):

- Prevents duplicate values while permitting NULL values (under standard SQL-92, multiple rows may contain NULL because NULL = NULL evaluates to Unknown / False).

Physical Mechanics of Schema Alterations (`ALTER TABLE`)#

Modifying a table schema on a database holding hundreds of millions of rows presents critical locking hazards:

• Metadata Locks (Exclusive DDL Lock): An ALTER TABLE statement requests an exclusive Access Exclusive Lock on the table. While waiting for long-running SELECT queries to finish, the DDL request queues behind them, blocking all subsequent reads and writes, resulting in application connection pool exhaustion!
• Table Rewrites vs Metadata-Only Changes:

- Metadata-Only: Adding a nullable column with no default, or adding a column with a constant default in modern PostgreSQL 11+ (stored in system catalogs without rewriting data pages).

- Full Table Rewrite: Changing a data type from INT to BIGINT, or changing column nullability without proof. The engine copies every row into a new table file on disk, generating massive disk I/O and holding exclusive write locks.

3. Data Manipulation Language (DML) Mutation Lifecycles#

Data Manipulation Language (DML) statements query, insert, update, and delete rows.

       DML Write Execution & Write-Ahead Logging (WAL) Lifecycle

       Client Application
              |
              | 1. INSERT / UPDATE / DELETE Statement
              v
       SQL Execution Engine (Query Planner & Transaction Manager)
              |
              | 2. Appends sequential delta record to WAL Buffer
              v
       Write-Ahead Log (WAL File on Disk) <=== [ fsync() guarantees durability! ]
              |
              | 3. Modifies data page in Shared Buffer Pool (Memory)
              v
       Shared Buffer Pool (Page marked DIRTY)
              |
              | 4. Asynchronous Background Writer flushes dirty pages
              v
       Data File (Table Heap on Disk)

1. Atomic Upsert Operations (`ON CONFLICT` / `MERGE`)#

In distributed web applications, checking if a record exists and subsequently inserting or updating (SELECT then INSERT/UPDATE) suffers from a critical Time-of-Check to Time-of-Use (TOCTOU) race condition. Two concurrent requests can execute the check simultaneously, causing duplicate key violations.

Modern SQL provides atomic Upsert syntax:

• PostgreSQL / SQLite (`ON CONFLICT`):

```sql

INSERT INTO user_preferences (user_id, theme, notification_enabled, updated_at)

VALUES (1042, 'dark', true, CURRENT_TIMESTAMP)

ON CONFLICT (user_id)

DO UPDATE SET

theme = EXCLUDED.theme,

notification_enabled = EXCLUDED.notification_enabled,

updated_at = EXCLUDED.updated_at;

```

The pseudo-table EXCLUDED represents the candidate row that conflicted with the existing unique index. The update occurs atomically within the internal index latch.

• MySQL (`ON DUPLICATE KEY UPDATE`):

```sql

INSERT INTO user_preferences (user_id, theme, updated_at)

VALUES (1042, 'dark', NOW())

ON DUPLICATE KEY UPDATE

theme = VALUES(theme),

updated_at = NOW();

```

2. Multi-Row Bulk Operations and `RETURNING` Clauses#

Executing individual INSERT statements inside a loop incurs network round-trip overhead and multiple transaction commits. Multi-row insertion batches thousands of tuples in a single network frame:

INSERT INTO audit_log (event_id, user_id, action, ip_address)
VALUES 
    (gen_random_uuid(), 101, 'LOGIN_SUCCESS', '198.51.100.12'),
    (gen_random_uuid(), 102, 'PASSWORD_RESET', '198.51.100.45'),
    (gen_random_uuid(), 103, 'PAYMENT_SUBMIT', '198.51.100.89')
RETURNING event_id, created_at;

The RETURNING clause projects the generated default values (such as serial IDs, UUIDs, or timestamp defaults) back to the caller immediately, eliminating secondary lookup queries.

4. The Logical Query Execution Order#

A pervasive misconception among software developers is reading a SQL statement from top to bottom, beginning with SELECT. In relational database engines, the Logical Execution Order differs fundamentally from the written syntactic order.

       The 8-Stage Logical Query Execution Pipeline

       Syntactic Order (How Code is Written)       Logical Execution Order (How Engine Processes)
       ==========================================================================================
       1. SELECT [DISTINCT]                        1. FROM & JOIN
       2. FROM                                     2. WHERE
       3. JOIN ... ON                              3. GROUP BY
       4. WHERE                                    4. HAVING
       5. GROUP BY                                 5. SELECT
       6. HAVING                                   6. DISTINCT
       7. ORDER BY                                 7. ORDER BY
       8. LIMIT / OFFSET                           8. LIMIT / OFFSET

Step-by-Step Engine Evaluation Mechanics#

1. `FROM` & `JOIN`:

The engine identifies the source tables. If joins are present, it evaluates join predicates (ON), constructing the virtual Cartesian working table.

1. `WHERE`:

Row-level filter predicates are evaluated across the working table. Rows evaluating to False or Unknown (NULL) are eliminated.

Crucial Rule: Column aliases created in SELECT cannot be evaluated in WHERE, because SELECT has not yet executed!

1. `GROUP BY`:

The remaining rows are partitioned into buckets based on distinct values of the specified grouping attributes.

1. `HAVING`:

Evaluates aggregate predicates across the grouped buckets. Buckets that fail the condition are dropped.

1. `SELECT`:

The projection step. Mathematical expressions, scalar functions, and window functions are evaluated. Column aliases are established.

1. `DISTINCT`:

Identical projected tuples are deduplicated via hash table or sorting.

1. `ORDER BY`:

The final projected relation is sorted based on specified attributes.

Note: Because SELECT executed prior to ORDER BY, column aliases are valid inside ORDER BY!

1. `LIMIT` / `OFFSET`:

Truncates the output to the specified row count.

5. Storage Architecture, Data Types & Write-Ahead Logging (WAL)#

The choice of storage data types dictates memory alignment, index packing density, and arithmetic precision.

+-----------------------------------------------------------------------------------------+
|                                DATA TYPE CHARACTERISTICS MATRIX                         |
+-----------------------------------------------------------------------------------------+
|  Data Type     |  Storage Size      |  Precision Characteristics|  Appropriate Domain   |
+----------------+--------------------+---------------------------+-----------------------+
|  BIGINT        |  8 bytes (64-bit)  |  -9.22 x 10^18 to 9.22x10^18| Primary Keys, IDs   |
|  DECIMAL(P, S) |  Variable          |  Exact arbitrary precision| Currency, Accounting  |
|  DOUBLE PREC   |  8 bytes (IEEE 754)|  ~15-17 significant digits| Scientific, Spatial   |
|  VARCHAR(N)    |  1-4 bytes + text  |  Variable character string| Names, Descriptions   |
|  JSONB         |  Variable (Binary) |  Decomposed parsed JSON   | Semi-structured data  |
+-----------------------------------------------------------------------------------------+

1. Monetary Data: `DECIMAL` vs. `DOUBLE PRECISION`#

Storing financial currency in floating-point data types (FLOAT, DOUBLE PRECISION) causes catastrophic rounding errors due to binary fractional representation limitations:

• In binary floating-point, values like 0.10 cannot be represented exactly; they evaluate to 0.100000000000000005551...
• Over millions of transactions, rounding drift accumulates, creating financial audit imbalances.
• `DECIMAL(P, S)` / `NUMERIC(P, S)`:

Stores numbers in Binary-Coded Decimal (BCD) or variable-base chunks (e.g., base-10,000 in PostgreSQL).

- P is Precision: total number of significant digits.

- S is Scale: number of fractional digits to the right of the decimal point.

- DECIMAL(12, 2) accommodates amounts up to 9,999,999,999.99 with mathematically exact base-10 addition, subtraction, and multiplication.

2. Write-Ahead Logging (WAL) & ACID Durability#

Relational database durability (the D in ACID) is guaranteed by Write-Ahead Logging:

1. When a transaction issues an UPDATE or INSERT, modifying the physical table heap on disk immediately would require random disk I/O, which is slow.
2. Instead, the engine writes a compact, sequential redo record to the WAL Buffer describing the exact binary delta.
3. Upon COMMIT, the database issues a synchronous fsync() system call flushing the WAL buffer sequentially to disk.
4. The actual data pages in RAM (Shared Buffers) are marked as Dirty. They are flushed to disk asynchronously by a background checkpoint daemon.
5. If power fails, upon reboot the database reads the sequential WAL log, replaying all committed transactions into the heap pages (Crash Recovery).

6. Complete Production-Grade Database Migration & Execution Engine#

The following complete TypeScript/Node.js script implements an automated relational database migration runner and transactional execution engine using connection pooling and parameterized DML:

/**
 * Production-Grade Relational Schema Migration & DML Execution Engine
 * Built for Node.js 18+ utilizing pg (node-postgres)
 */

import { Pool, PoolClient } from 'pg';

export interface Migration {
  id: string;
  name: string;
  up: string;
  down: string;
}

export class DatabaseMigrationEngine {
  private pool: Pool;

  constructor(connectionString: string) {
    this.pool = new Pool({
      connectionString,
      max: 10,
      idleTimeoutMillis: 30000,
      connectionTimeoutMillis: 5000,
    });
  }

  // Initialize schema migrations tracking table
  public async initializeMigrationTable(): Promise<void> {
    const ddl = `
      CREATE TABLE IF NOT EXISTS schema_migrations (
        version VARCHAR(64) PRIMARY KEY,
        name VARCHAR(255) NOT NULL,
        applied_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
      );
    `;
    await this.pool.query(ddl);
  }

  // Execute a migration inside an ACID Transaction block
  public async applyMigration(migration: Migration): Promise<void> {
    const client: PoolClient = await this.pool.connect();
    try {
      await client.query('BEGIN'); // Start ACID transaction

      // 1. Check if migration already executed
      const checkRes = await client.query(
        'SELECT version FROM schema_migrations WHERE version = 1 FOR UPDATE',
        [migration.id]
      );

      if (checkRes.rowCount && checkRes.rowCount > 0) {
        console.log(`[*] Migration {migration.id} ({migration.name}) already applied. Skipping.`);
        await client.query('ROLLBACK');
        return;
      }

      console.log(`[+] Executing Migration: {migration.id} - {migration.name}`);
      // 2. Execute DDL script
      await client.query(migration.up);

      // 3. Record migration metadata
      await client.query(
        'INSERT INTO schema_migrations (version, name) VALUES (1, 2)',
        [migration.id, migration.name]
      );

      await client.query('COMMIT'); // Commit transaction atomically
      console.log(`[+] Successfully applied migration {migration.id}`);
    } catch (error) {
      await client.query('ROLLBACK'); // Abort on failure
      console.error(`[-] Migration {migration.id} failed. Transaction rolled back!`, error);
      throw error;
    } finally {
      client.release();
    }
  }

  // Parameterized Upsert demonstration
  public async upsertCustomerOrder(
    orderId: string,
    customerId: number,
    totalAmount: number
  ): Promise<any> {
    const sql = `
      INSERT INTO customer_orders (order_id, customer_id, total_amount, updated_at)
      VALUES (1, 2, 3, CURRENT_TIMESTAMP)
      ON CONFLICT (order_id)
      DO UPDATE SET
          total_amount = EXCLUDED.total_amount,
          updated_at = CURRENT_TIMESTAMP
      RETURNING order_id, customer_id, total_amount, updated_at;
    `;
    const res = await this.pool.query(sql, [orderId, customerId, totalAmount]);
    return res.rows[0];
  }

  public async close(): Promise<void> {
    await this.pool.end();
  }
}

// Module Execution Simulation
if (require.main === module) {
  const mockMigrations: Migration[] = [
    {
      id: '20260101_001',
      name: 'create_customer_orders_table',
      up: `
        CREATE TABLE IF NOT EXISTS customer_orders (
          order_id VARCHAR(64) PRIMARY KEY,
          customer_id BIGINT NOT NULL,
          total_amount DECIMAL(12, 2) NOT NULL CHECK (total_amount >= 0.0),
          created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
          updated_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
        );
        CREATE INDEX IF NOT EXISTS idx_customer_orders_customer_id ON customer_orders (customer_id);
      `,
      down: `DROP TABLE IF EXISTS customer_orders;`
    }
  ];

  console.log('[*] Migration engine compiled and validated successfully.');
}

7. Mathematical Foundations of DDL & DML Operations#

1. Relational Set Cardinality & Cartesian Product#

Let R and S be two finite relations with cardinalities:

|R| = M, |S| = NThe cardinality of their Cartesian product is:

|R × S| = M × NFor an unconditional Cross Join or missing join predicate across three tables with sizes 10^4, 10^5, 10^6:

|T_1 × T_2 × T_3| = 10^4 × 10^5 × 10^6 = 10^{15 tuples}Attempting to materialize 10^{15} rows in memory or temp disk files exhausts storage immediately.

2. Decimal Data Type Physical Byte Calculation#

In PostgreSQL and MySQL, DECIMAL(P, S) does not store digits as ASCII strings. It groups digits into discrete base-10,000 chunks (4 decimal digits per 2-byte integer).

For a column defined as DECIMAL(P, S):

• Total integer digits: I = P - S
• Fractional digits: S

The total number of required weight groups is:

Groups = ⌈ (I) / (4) ⌉ + ⌈ (S) / (4) ⌉For DECIMAL(12, 2):

• I = 12 - 2 = 10 ──> ⌈ 10 / 4 ⌉ = 3 groups
• S = 2 ──> ⌈ 2 / 4 ⌉ = 1 group
• Total groups = 3 + 1 = 4 groups.
• Each group consumes 2 bytes, plus a 4-byte header:

Storage = (4 groups × 2 bytes) + 4 byte header = 12 bytes per valueCompared to an 8-byte BIGINT, a DECIMAL(12, 2) requires 12 bytes, balancing compact packing with exact precision.

8. Common Pitfalls, Anti-Patterns & Query Traps#

+-----------------------------------------------------------------------------------------+
|                                SQL ANTI-PATTERN MATRIX                                  |
+-----------------------------------------------------------------------------------------+
|  Anti-Pattern                   |  Root Cause             |  Optimal SQL Engineering    |
+---------------------------------+-------------------------+-----------------------------+
|  SELECT * in Production Code    |  Transfers unneeded data|  Explicit attribute projection|
|                                 |  breaks index-only scans|  SELECT id, email, created_at |
+---------------------------------+-------------------------+-----------------------------+
|  Using FLOAT for Currency       |  IEEE 754 binary rounding| Use DECIMAL(P, S) or store  |
|                                 |  accumulates drift      |  cents in integer BIGINT    |
+---------------------------------+-------------------------+-----------------------------+
|  Unindexed Foreign Keys         |  Full table scans on    |  Explicitly index every     |
|                                 |  CASCADE parent updates |  FOREIGN KEY child column   |
+---------------------------------+-------------------------+-----------------------------+
|  Functions on Indexed Columns   |  Sargability violation  |  Re-write predicate to isolate|
|  WHERE YEAR(created_at) = 2026 |  engine cannot use index|  WHERE created_at >= '2026...'|
+---------------------------------+-------------------------+-----------------------------+

1. The Sargability Violation (Search Argument Ability)#

An index on created_at stores sorted datetime timestamps. When a developer writes:

-- INSECURE ANTI-PATTERN: Non-Sargable Predicate
SELECT * FROM orders WHERE EXTRACT(YEAR FROM created_at) = 2026;

The database engine cannot traverse the B+ tree index, because the value being evaluated is the output of the function EXTRACT(), not the raw column value! The engine must execute a Sequential Full Table Scan, invoking the function on every single row.

• Sargable Re-write (Enables O(log N) Index Range Scan):

```sql

SELECT * FROM orders

WHERE created_at >= '2026-01-01 00:00:00'

AND created_at < '2027-01-01 00:00:00';

```

9. Checkpoint Practice Problems & Step-by-Step Solutions#

Problem 1: Relational Algebra Equivalence & Query Tree Optimization#

Scenario: A database query optimizer receives a SQL query evaluating customer purchases:

SELECT Customers.name, Orders.order_total
FROM Customers
JOIN Orders ON Customers.customer_id = Orders.customer_id
WHERE Orders.order_total > 500.00;

Assume:

• Relation Customers contains N_C = 100,000 rows.
• Relation Orders contains N_O = 1,000,000 rows.
• Exactly 5% of orders have order_total > 500.00 (Selectivity factor σ = 0.05).

Questions:

1. Formulate the unoptimized relational algebra expression that performs the Cartesian product/join first, followed by selection and projection.
2. Formulate the optimized relational algebra expression using the heuristic rule of "Pushing Selections Down" (performing filtering before the join).
3. Calculate the number of intermediate tuples processed by the join operation under the unoptimized vs optimized relational algebra plan.

Solution:

Step 1: Unoptimized Relational Algebra Expression:

The naive execution plan joins the two full tables first and filters afterward:

π_{name, order_total} ( σ_{order_total > 500.00} ( Customers ⨝_{Customers.customer_id = Orders.customer_id} Orders ) )Step 2: Optimized Relational Algebra Plan (Push Down Selection):

By relational algebra equivalence rules, selection distributes over join:

σ_φ(R ⨝ S) ≡ R ⨝ σ_φ(S)π_{name, order_total} ( Customers ⨝_{Customers.customer_id = Orders.customer_id} ( σ_{order_total > 500.00}(Orders) ) )Step 3: Calculate Intermediate Tuple Processing:

• Unoptimized Plan:

The join must evaluate all N_O = 1,000,000 orders against N_C = 100,000 customers.

Tuples entering Join = 1,000,000 ordersThe join materializes 1,000,000 combined records into an intermediate working memory buffer before the filter discards 950,000 rows!

• Optimized Plan:

The selection filter runs first on Orders:

Filtered Orders = N_O × σ = 1,000,000 × 0.05 = 50,000 ordersThe join operation only needs to process 50,000 tuples!

The hash table or index lookups are reduced by 95%, reducing memory usage and join CPU time by a factor of 20.

Problem 2: Decimal Precision, Scale & Exact Byte Storage Calculation#

Scenario: A financial ledger defines a transaction table on PostgreSQL:

CREATE TABLE transaction_ledger (
    ledger_id BIGINT PRIMARY KEY,
    debit_amount DECIMAL(14, 4) NOT NULL,
    credit_amount DECIMAL(14, 4) NOT NULL
);

Questions:

1. What is the maximum numerical value that can be stored in debit_amount?
2. What is the smallest positive non-zero fractional value that can be represented in debit_amount?
3. Using the 4-digit base-10,000 chunk storage rule (2 bytes per 4-digit group + 4-byte header), calculate the exact byte storage required for each DECIMAL(14, 4) value.
4. Calculate the raw data storage required for 10,000,000 rows in Megabytes (excluding primary key and row header overhead).

Solution:

Step 1: Maximum Numerical Value:

Precision P = 14, Scale S = 4.

• Integer digits = P - S = 14 - 4 = 10 digits.
• Fractional digits = 4 digits.

Maximum Value = 9,999,999,999.9999Step 2: Smallest Non-Zero Fractional Value:

With scale S = 4, the smallest step is in the 4th decimal place:

Smallest Non-Zero Value = 10^{-4} = 0.0001Step 3: Calculate Byte Storage per `DECIMAL(14, 4)` Value:

• Integer digits: I = 10 ──> ⌈ 10 / 4 ⌉ = 3 groups
• Fractional digits: S = 4 ──> ⌈ 4 / 4 ⌉ = 1 group
• Total groups = 3 + 1 = 4 groups.
• Each group requires 2 bytes:

Payload = 4 × 2 = 8 bytes- Add PostgreSQL numeric header (4 bytes):

Storage per Value = 8 + 4 = 12 bytesStep 4: Total Storage for 10,000,000 Rows:

Each row stores two decimal columns (debit_amount and credit_amount):

Storage per Row = 12 bytes × 2 = 24 bytesFor 10,000,000 rows:

Total Bytes = 10,000,000 × 24 bytes = 240,000,000 bytesConvert to Megabytes:

Storage in MB = (240,000,000) / (1,048,576) ≈ 228.88 MB---

Problem 3: Logical Order of Execution Error Diagnosis & Alias Resolution#

Scenario: A data analyst writes the following query to identify high-spending customers:

SELECT 
    customer_id, 
    SUM(unit_price * quantity) AS total_spend
FROM order_items
WHERE total_spend > 1000.00
GROUP BY customer_id
ORDER BY total_spend DESC;

When executed, the database immediately halts with an error:

ERROR: column "total_spend" does not exist at line 5: WHERE total_spend > 1000.00

Questions:

1. Using the formal 8-stage Logical Query Execution order, explain why the database engine throws this error.
2. Why does the ORDER BY total_spend DESC clause in the exact same query not trigger an error?
3. Provide the correct, standard SQL re-write that achieves the analyst's desired filtering.

Solution:

Step 1: Diagnostic Analysis of the Error:

In the logical query processing pipeline, the execution order is:

1. FROM order_items
2. WHERE total_spend > 1000.00 <=== Engine is here!
3. GROUP BY customer_id
4. HAVING
5. SELECT customer_id, SUM(...) AS total_spend <=== Alias established here!
6. DISTINCT
7. ORDER BY total_spend DESC
8. LIMIT

When Stage 2 (WHERE) executes, the SELECT clause (Stage 5) has not yet been processed! The alias total_spend does not exist in the working data dictionary. Furthermore, WHERE evaluates row-level predicates before grouping occurs; an aggregate function (SUM()) cannot be evaluated before rows are bucketed!

Step 2: Why `ORDER BY` Resolves Successfully:

Stage 7 (ORDER BY) executes after Stage 5 (SELECT). By the time the sorting stage runs, the projection has completed, the scalar calculations are finalized, and the alias total_spend is registered in the output schema. Therefore, referencing total_spend in ORDER BY is completely valid.

Step 3: Correct SQL Re-Write:

Aggregate filters must be placed in the `HAVING` clause (Stage 4), which executes after GROUP BY:

SELECT 
    customer_id, 
    SUM(unit_price * quantity) AS total_spend
FROM order_items
GROUP BY customer_id
HAVING SUM(unit_price * quantity) > 1000.00
ORDER BY total_spend DESC;

Problem 4: Foreign Key Cascading Referential Integrity & Transaction Rollback#

Scenario: An e-commerce database contains two tables linked by a cascading foreign key:

CREATE TABLE parent_accounts (
    account_id INT PRIMARY KEY,
    account_status VARCHAR(20) NOT NULL
);

CREATE TABLE child_subscriptions (
    sub_id INT PRIMARY KEY,
    account_id INT NOT NULL,
    monthly_fee DECIMAL(10, 2) NOT NULL,
    CONSTRAINT fk_account 
        FOREIGN KEY (account_id) 
        REFERENCES parent_accounts(account_id) 
        ON DELETE CASCADE
);

The database currently holds:

• parent_accounts: Account 101, Account 102.
• child_subscriptions:

- Sub 1 (account_id = 101, monthly_fee = 50.00)

- Sub 2 (account_id = 101, monthly_fee = 75.00)

- Sub 3 (account_id = 102, monthly_fee = 20.00)

An administrator executes the following transaction:

BEGIN TRANSACTION;
DELETE FROM parent_accounts WHERE account_id = 101;
INSERT INTO child_subscriptions (sub_id, account_id, monthly_fee) VALUES (4, 999, 100.00);
COMMIT;

Note that Account 999 does not exist in parent_accounts.

Questions:

1. What happens immediately after the second statement (DELETE FROM parent_accounts WHERE account_id = 101;) executes within the transaction? How many rows remain in child_subscriptions in the uncommitted transaction state?
2. What happens when the third statement (INSERT ... VALUES (4, 999, ...)) executes?
3. What is the final state of parent_accounts and child_subscriptions after the transaction terminates?

Solution:

Step 1: Evaluate State after `DELETE`:

• When DELETE FROM parent_accounts WHERE account_id = 101 executes, the foreign key constraint ON DELETE CASCADE triggers automatically.
• The engine deletes Account 101 from parent_accounts, and cascades to delete all child subscriptions referencing account_id = 101 (Sub 1 and Sub 2).
• Within the uncommitted transaction isolation boundary, only Sub 3 remains in child_subscriptions.

Step 2: Evaluate `INSERT` with Non-Existent Foreign Key:

• The third statement attempts to insert Sub 4 with account_id = 999.
• The engine checks referential integrity against parent_accounts.
• Because account_id = 999 does not exist in parent_accounts, the constraint fk_account is violated!
• The database raises an error: ERROR: insert or update on table "child_subscriptions" violates foreign key constraint "fk_account".
• The transaction enters an aborted error state (current transaction is aborted, commands ignored until end of transaction block).

Step 3: Final State after Transaction Termination:

• Under the Atomicity principle of ACID, a transaction is an all-or-nothing unit of work.
• When an error occurs or ROLLBACK is issued (or COMMIT is attempted on an aborted transaction, which PostgreSQL translates to an automatic rollback), the entire transaction aborts.
• The engine consults the Write-Ahead Log undo/savepoint mechanisms, reversing all intermediate modifications:

- Account 101 is restored to parent_accounts.

- Sub 1 and Sub 2 are restored to child_subscriptions.

• Final Result:

- parent_accounts contains: Account 101, Account 102 (Zero rows deleted!).

- child_subscriptions contains: Sub 1, Sub 2, Sub 3 (Zero rows deleted, Sub 4 rejected!).

The database remains in its exact original state, guaranteeing absolute data integrity.