☁️ Master AWS Redshift Data Warehousing

-- Create customers table
CREATE TABLE customers (
    customer_id INTEGER PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    email VARCHAR(100),
    city VARCHAR(50),
    state VARCHAR(20),
    signup_date DATE
);

-- Create products table  
CREATE TABLE products (
    product_id INTEGER PRIMARY KEY,
    book_title VARCHAR(200),
    author VARCHAR(100),
    genre VARCHAR(50),
    price DECIMAL(8,2),
    publication_year INTEGER
);

-- Create orders table
CREATE TABLE orders (
    order_id INTEGER PRIMARY KEY,
    customer_id INTEGER,
    order_date DATE,
    total_amount DECIMAL(10,2),
    status VARCHAR(20)
);

-- Create order_items table (what was bought in each order)
CREATE TABLE order_items (
    order_item_id INTEGER PRIMARY KEY,
    order_id INTEGER,
    product_id INTEGER,
    quantity INTEGER,
    price_per_item DECIMAL(8,2)
);

-- Insert sample customers
INSERT INTO customers VALUES 
(1, 'John', 'Smith', 'john@email.com', 'New York', 'NY', '2023-01-15'),
(2, 'Sarah', 'Johnson', 'sarah@email.com', 'Los Angeles', 'CA', '2023-02-20'),
(3, 'Mike', 'Brown', 'mike@email.com', 'Chicago', 'IL', '2023-03-10'),
(4, 'Emma', 'Davis', 'emma@email.com', 'Houston', 'TX', '2023-04-05');

-- Insert sample products
INSERT INTO products VALUES
(1, 'The Great Gatsby', 'F. Scott Fitzgerald', 'Classic', 12.99, 1925),
(2, 'To Kill a Mockingbird', 'Harper Lee', 'Classic', 13.99, 1960),
(3, 'Harry Potter Book 1', 'J.K. Rowling', 'Fantasy', 15.99, 1997),
(4, 'The Martian', 'Andy Weir', 'Sci-Fi', 14.99, 2011);

-- Insert sample orders  
INSERT INTO orders VALUES
(1, 1, '2024-01-20', 28.98, 'completed'),
(2, 2, '2024-01-22', 15.99, 'completed'),
(3, 1, '2024-02-01', 14.99, 'completed'),
(4, 3, '2024-02-15', 42.97, 'completed');