ecommerceSQL

-- Create the database
CREATE DATABASE ecommerce;
USE ecommerce;

-- Create tables
CREATE TABLE customers (
    customer_id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    email VARCHAR(100) UNIQUE,
    registration_date DATE
);

CREATE TABLE products (
    product_id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100),
    description TEXT,
    price DECIMAL(10, 2),
    stock_quantity INT
);

CREATE TABLE orders (
    order_id INT PRIMARY KEY AUTO_INCREMENT,
    customer_id INT,
    order_date DATETIME,
    total_amount DECIMAL(10, 2),
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

CREATE TABLE order_items (
    order_item_id INT PRIMARY KEY AUTO_INCREMENT,
    order_id INT,
    product_id INT,
    quantity INT,
    price DECIMAL(10, 2),
    FOREIGN KEY (order_id) REFERENCES orders(order_id),
    FOREIGN KEY (product_id) REFERENCES products(product_id)
);

-- Insert sample data
INSERT INTO customers (first_name, last_name, email, registration_date) VALUES
('John', 'Doe', 'john.doe@email.com', '2023-01-15'),
('Jane', 'Smith', 'jane.smith@email.com', '2023-02-20'),
('Mike', 'Johnson', 'mike.johnson@email.com', '2023-03-10');

INSERT INTO products (name, description, price, stock_quantity) VALUES
('Laptop', 'High-performance laptop', 999.99, 50),
('Smartphone', 'Latest model smartphone', 699.99, 100),
('Headphones', 'Noise-cancelling headphones', 199.99, 200);

INSERT INTO orders (customer_id, order_date, total_amount) VALUES
(1, '2023-04-01 10:30:00', 1199.98),
(2, '2023-04-02 14:45:00', 699.99),
(3, '2023-04-03 09:15:00', 399.98);

INSERT INTO order_items (order_id, product_id, quantity, price) VALUES
(1, 1, 1, 999.99),
(1, 3, 1, 199.99),
(2, 2, 1, 699.99),
(3, 3, 2, 199.99);

-- Sample queries

-- 1. Get total revenue
SELECT SUM(total_amount) AS total_revenue FROM orders;

-- 2. Get top 3 bestselling products
SELECT p.name, SUM(oi.quantity) AS total_sold
FROM products p
JOIN order_items oi ON p.product_id = oi.product_id
GROUP BY p.product_id
ORDER BY total_sold DESC
LIMIT 3;

-- 3. Get customers who have made more than one order
SELECT c.customer_id, c.first_name, c.last_name, COUNT(o.order_id) AS order_count
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id
HAVING order_count > 1;

-- 4. Get average order value
SELECT AVG(total_amount) AS average_order_value FROM orders;

-- 5. Get product inventory status
SELECT name, 
    CASE 
        WHEN stock_quantity > 100 THEN 'High'
        WHEN stock_quantity BETWEEN 50 AND 100 THEN 'Medium'
        ELSE 'Low'
    END AS inventory_status
FROM products;