Extract E-Commerce Order Data from SQL

E-Commerce Data Extraction

Real-world SQL dumps often contain multiple related tables. This example shows how to extract order data from a typical e-commerce database schema with products and orders.

Example SQL

CREATE TABLE products (
  id INTEGER PRIMARY KEY,
  sku VARCHAR(20) NOT NULL,
  name VARCHAR(200) NOT NULL,
  price DECIMAL(10,2) NOT NULL,
  weight_kg FLOAT
);

INSERT INTO products (id, sku, name, price, weight_kg) VALUES
  (1, 'ELEC-001', 'Wireless Bluetooth Headphones', 79.99, 0.25),
  (2, 'ELEC-002', 'USB-C Charging Cable (2m)', 12.99, 0.05),
  (3, 'HOME-001', 'Stainless Steel Water Bottle', 24.95, 0.35),
  (4, 'ELEC-003', 'Portable Power Bank 10000mAh', 39.99, 0.22);

CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  order_number VARCHAR(20) NOT NULL,
  customer_email VARCHAR(255) NOT NULL,
  product_id INTEGER REFERENCES products(id),
  quantity INTEGER NOT NULL DEFAULT 1,
  total_amount DECIMAL(10,2) NOT NULL,
  order_date TIMESTAMP NOT NULL,
  status VARCHAR(20) DEFAULT 'pending'
);

INSERT INTO orders VALUES
  (1, 'ORD-2024-001', 'alice@example.com', 1, 2, 159.98, '2024-01-15 10:30:00', 'shipped'),
  (2, 'ORD-2024-002', 'bob@example.com', 2, 5, 64.95, '2024-01-15 14:22:00', 'delivered'),
  (3, 'ORD-2024-003', 'alice@example.com', 3, 1, 24.95, '2024-01-16 09:05:00', 'pending'),
  (4, 'ORD-2024-004', 'charlie@example.com', 4, 1, 39.99, '2024-01-16 16:45:00', 'shipped');

Key Extraction Points

Since this SQL contains two tables, the tool extracts data from the INSERT statements. The first INSERT populates the products CSV, and the second populates orders. The info bar shows which tables were detected.

Handling Related Tables

The tool extracts data from each INSERT statement independently. It does not perform JOINs or resolve foreign key references. If you need to combine related data:

1. Extract each table to a separate CSV (process one at a time)
2. Use a spreadsheet VLOOKUP or a tool like pandas to join the data
3. Or write a SQL JOIN query first and convert the joined result