-- Create Database
CREATE DATABASE ecommerce_db;
USE ecommerce_db;
-- Customers Table
CREATE TABLE customers (
customer_id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
email VARCHAR(150) UNIQUE NOT NULL,
phone VARCHAR(15),
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- Products Table
CREATE TABLE products (
product_id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(150) NOT NULL,
category VARCHAR(100),
price DECIMAL(10,2) NOT NULL,
stock INT DEFAULT 0,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- Orders Table
CREATE TABLE orders (
order_id INT AUTO_INCREMENT PRIMARY KEY,
customer_id INT,
order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
total_amount DECIMAL(12,2),
status ENUM('Pending', 'Shipped', 'Delivered', 'Cancelled') DEFAULT 'Pending',
FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);
-- Order Items Table (Many-to-Many between Orders & Products)
CREATE TABLE order_items (
order_item_id INT AUTO_INCREMENT PRIMARY KEY,
order_id INT,
product_id INT,
quantity INT NOT NULL,
price DECIMAL(10,2) NOT NULL,
FOREIGN KEY (order_id) REFERENCES orders(order_id),
FOREIGN KEY (product_id) REFERENCES products(product_id)
);
-- Payments Table
CREATE TABLE payments (
payment_id INT AUTO_INCREMENT PRIMARY KEY,
order_id INT,
amount DECIMAL(12,2),
method ENUM('Credit Card', 'Debit Card', 'UPI', 'Net Banking', 'COD'),
status ENUM('Pending', 'Completed', 'Failed') DEFAULT 'Pending',
payment_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (order_id) REFERENCES orders(order_id)
);
-- Insert Customers
INSERT INTO customers (name, email, phone) VALUES
('Amit Sharma', 'amit@example.com', '9876543210'),
('Neha Verma', 'neha@example.com', '9988776655'),
('Rahul Singh', 'rahul@example.com', '9123456789'),
('Priya Patel', 'priya@example.com', '9876123456'),
('Kunal Mehta', 'kunal@example.com', '9988123456');
-- Insert Products
INSERT INTO products (name, category, price, stock) VALUES
('iPhone 15', 'Electronics', 75000, 10),
('Samsung Galaxy S23', 'Electronics', 65000, 15),
('Sony Headphones', 'Electronics', 4500, 30),
('Nike Shoes', 'Clothing', 3500, 20),
('Adidas T-shirt', 'Clothing', 1200, 50),
('Dell Laptop', 'Electronics', 55000, 8),
('Wooden Dining Table', 'Furniture', 15000, 5),
('Study Chair', 'Furniture', 5000, 12);
-- Insert Orders
INSERT INTO orders (customer_id, total_amount, status) VALUES
(1, 80000, 'Pending'),
(2, 3500, 'Delivered'),
(3, 1200, 'Shipped');
-- Insert Order Items
INSERT INTO order_items (order_id, product_id, quantity, price) VALUES
(1, 1, 1, 75000), -- Amit bought iPhone
(1, 3, 1, 4500), -- Amit bought Sony Headphones
(2, 4, 1, 3500), -- Neha bought Nike Shoes
(3, 5, 1, 1200); -- Rahul bought Adidas T-shirt
-- Insert Payments
INSERT INTO payments (order_id, amount, method, status) VALUES
(1, 80000, 'Credit Card', 'Pending'),
(2, 3500, 'UPI', 'Completed'),
(3, 1200, 'Debit Card', 'Completed');
