SQL-Queries.txt

-- Create Customer table
CREATE TABLE customers (
    customer_id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255),
    username VARCHAR(255),
    password VARCHAR(255),
    email VARCHAR(255),
    address VARCHAR(255),
    is_deleted BOOLEAN
);

-- Create Product table
CREATE TABLE products (
    product_id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255),
    category VARCHAR(255),
    brand VARCHAR(255),
    price DOUBLE,
    stock_quantity INT,
    low_stock_alert_threshold INT,
    is_deleted BOOLEAN
);

-- Create SalesOrder table
CREATE TABLE sales_orders (
    order_id INT PRIMARY KEY AUTO_INCREMENT
);

-- Create OrderItem table
CREATE TABLE order_items (
    order_item_id INT PRIMARY KEY AUTO_INCREMENT,
    order_id INT,
    product_id INT,
    quantity INT,
    FOREIGN KEY (order_id) REFERENCES sales_orders(order_id),
    FOREIGN KEY (product_id) REFERENCES products(product_id)
);

-- Insert initial data into Product table
INSERT INTO products (name, category, brand, price, stock_quantity, low_stock_alert_threshold, is_deleted)
VALUES 
    ('Laptop', 'Electronics', 'Dell', 799.99, 30, 5, false),
    ('Coffee Maker', 'Home & Kitchen', 'Keurig', 89.99, 50, 10, false),
    ('Running Shoes', 'Footwear', 'Nike', 119.99, 40, 5, false),
    ('Headphones', 'Electronics', 'Bose', 249.99, 60, 7, false),
    ('Backpack', 'Travel', 'North Face', 99.99, 35, 5, false),
    ('Gaming Console', 'Electronics', 'Sony', 299.99, 25, 5, false),
    ('Smartphone', 'Electronics', 'Samsung', 699.99, 45, 5, false);

-- Sample Customer registration
INSERT INTO customers (name, username, password, email, address, is_deleted)
VALUES ('John Doe', 'john_doe', 'password123', 'john.doe@example.com', '123 Main St', false);

-- Sample SalesOrder creation (assuming order_id is generated automatically)
INSERT INTO sales_orders DEFAULT VALUES;

-- Sample OrderItem creation
INSERT INTO order_items (order_id, product_id, quantity)
VALUES (1, 1, 2);  -- Assuming the product_id refers to a laptop in this case

-- Query to retrieve all products
SELECT * FROM products;

-- Query to retrieve all customers
SELECT * FROM customers;

-- Query to retrieve all orders with associated items and products
SELECT 
    so.order_id,
    oi.order_item_id,
    oi.quantity,
    p.name AS product_name
FROM sales_orders so
JOIN order_items oi ON so.order_id = oi.order_id
JOIN products p ON oi.product_id = p.product_id;