E-Commerce Order Managment System.txt

                 --------E-Commerce Order Managment System---------

------------------------------------------------------------------
TABLES & RELATIONSHIPS
------------------------------------------------------------------

(1)
CREATE TABLE products(
    product_id INT PRIMARY KEY,
    name VARCHAR(50),
    category_id INT,
    price DECIMAL(10,2),
    stock_quantity INT,
    added_date DATE,
    FOREIGN KEY(category_id)REFERENCES Categories(category_id)
);

(2)
CREATE TABLE Categories(
    category_id INT PRIMARY KEY,
    category_name VARCHAR(50)
);

(3)
CREATE TABLE Customers(
    customer_id INT PRIMARY KEY,
    name VARCHAR(50),
    email VARCHAR(50),
    phone_number VARCHAR(12),
    adress VARCHAR(100),
    registration_date DATE
);

(4)
CREATE TABLE Orders(
    order_id INT PRIMARY KEY,
    customer_id INT,
    order_date DATE, 
    total_amount DECIMAL(10,2),
    status VARCHAR(50),
    FOREIGN KEY (customer_id)REFERENCES customers(customer_id)
);

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

 (6)   
CREATE TABLE payments(
    payment_id INT PRIMARY KEY,
    order_id INT,
    payment_date DATE,
    payment_method VARCHAR(50),
    payment_status VARCHAR(50),
    FOREIGN KEY(order_id) REFERENCES orders(order_id)
);

(7)
CREATE TABLE shipping(
    shipping_id INT PRIMARY KEY,
    order_id INT,
    shipping_date DATE,
    delivery_date VARCHAR(50),
    shipping_status VARCHAR(50),
    FOREIGN KEY(order_id)REFERENCES orders(order_id)
);
    

-------------------------------------------------------
INSERTING VALUES:-

(1)-----

INSERT INTO Products (product_id, name, category_id, price, stock_quantity, added_date) 
VALUES
(101, 'Laptop', 1, 999.99, 150, '2024-01-15'),
(102, 'T-Shirt (Medium)', 2, 19.99, 500, '2024-02-01'),
(103, 'The Hitchhiker''s Guide', 3, 12.50, 75, '2024-03-10'),
(104, 'Coffee Maker', 4, 45.00, 90, '2024-04-22'),
(105, 'Smartphone', 1, 699.00, 300, '2024-05-05'),
(106, 'Apple', 5, 0.99, 2000, '2024-06-12'),
(107, 'Jeans (Size 32x32)', 2, 35.50, 120, '2024-07-01'),
(108,'squishy ball',6,30.10,20,'2024-07-02');


(2)----

INSERT INTO categories(category_id,category_name)
VALUES
(1, 'Electronics'),
(2, 'Apparel'),
(3, 'Books'),
(4, 'Home Goods'),
(5, 'Groceries'),
(6,'Toys');

(3)----

INSERT INTO Customers (customer_id, name, email, phone_number, adress, registration_date)
VALUES
(1, 'John Doe', 'john.doe@example.com', '555-0101', '123 Main St, Anytown, USA', '2025-01-15'),
(2, 'Jane Smith', 'jane.smith@example.com', '555-0202', '456 Oak Ave, Somewhere, USA', '2025-02-20'),
(3, 'Peter Jones', 'p.jones@example.com', '555-0303', '789 Pine Ln, Nowhere, USA', '2025-03-10'),
(4, 'Mary Williams', 'mary.w@example.com', '555-0404', '101 Maple Rd, Yourtown, USA', '2025-04-05'),
(5, 'David Brown', 'davidb@example.com', '555-0505', '222 Birch Blvd, Newtown, USA', '2025-05-12'),
(6, 'Susan Davis', 'susan_d@example.com', '555-0606', '333 Cedar Ct, Oldtown, USA', '2025-06-18'),
(7, 'Michael Miller', 'mike.m@example.com', '555-0707', '444 Spruce St, Mytown, USA', '2025-07-25'),
(8, 'Laura Wilson', 'laura.w@example.com', '555-0808', '555 Fir Pl, Histown, USA', '2025-08-30');


(4)-----

INSERT INTO Orders (order_id, customer_id, order_date, total_amount, status)
VALUES
(101, 1, '2025-01-15', 150.75, 'Shipped'),
(102, 2, '2024-02-01', 89.99, 'Processing'),
(103, 3, '2024-02-10', 450.00, 'Cancelled'),
(104, 1, '2024-03-05', 215.50, 'Shipped'),
(105, 7, '2023-03-12', 32.40, 'Cancelled'),
(106, 3, '2022-04-01', 75.00, 'Delivered'),
(107, 5, '2025-04-20', 600.25, 'Shipped'),
(108, 6, '2025-05-01', 12.99, 'Processing');

(5)-----

INSERT INTO order_items (order_item_id, order_id, product_id, quantity, subtotal) 
VALUES
(111, 101, 101, 22, 59.98),
(222, 101, 102, 11, 25.50),
(333, 102, 103, 34, 14.97),
(444, 103, 105, 18, 29.99),
(555, 103, 104, 51, 49.75),
(666, 104, 102, 29, 51.00),
(777, 106, 106, 11, 4.99),
(888, 108, 104, 10, 99.50);


(6)-----

INSERT INTO payments (payment_id, order_id, payment_date, payment_method, payment_status) 
VALUES
(1, 101, '2024-01-15', 'Credit Card', 'Paid'),
(2, 102, '2025-01-16', 'PayPal', 'Paid'),
(3, 103, '2024-03-17', 'Credit Card', 'Pending'),
(4, 104, '2023-04-18', 'Debit Card', 'Paid'),
(5, 105, '2024-01-19', 'PayPal', 'Paid'),
(6, 106, '2025-07-20', 'Credit Card', 'Paid'),
(7, 107, '2025-03-21', 'UPI', 'Failed'),
(8, 108, '2024-01-22', 'Credit Card', 'Paid');

(7)-----

INSERT INTO shipping (shipping_id, order_id, shipping_date, delivery_date, shipping_status) 
VALUES
(1, 101, '2025-01-15', '2025-01-20', 'Delivered'),
(2, 102, '2025-01-16', '2025-01-21', 'In Transit'),
(3, 103, '2025-01-17', NULL, 'Pending'),
(4, 104, '2025-01-18', '2025-01-22', 'Delivered'),
(5, 105, '2025-01-19', '2025-01-24', 'Dispatched'),
(6, 106, '2025-01-20', '2025-01-25', 'Delivered'),
(7, 107, '2025-01-21', NULL, 'Pending'),
(8, 108, '2025-01-22', '2025-01-27', 'Delivered');

-------------------------------------------------------------------------------------------

TASKS & FUNCTIONALITIES

---------------------------------------------------------------------------------------------

---------------------------------------------
QUESTION :- 1 (implement CRUD operation)
---------------------------------------------

-----(1)

INSERT INTO products(product_id,name,category_id,price,stock_quantity,added_date)
VALUES
(109,'cargo pants',2,76.09,23,'2024-09-09');


INSERT INTO customers(customer_id,name,email,phone_number,adress,registration_date)
VALUES
(9,'robert Mathews','robert.m@example.com','444-1010','231 Satr street,Delhi','2025-01-25');


INSERT INTO Orders (order_id, customer_id, order_date, total_amount, status)
VALUES
(999,7,'2025-06-01',760.11,'Shipped')

-----(2)

UPDATE products 
SET stock_quantity = 155
WHERE product_id = 101;

-----(3)

DELETE FROM orders
WHERE status = 'Cancelled'
AND order_date < CURDATE()-INTERVAL 30 DAY;

------------------------------------------------------------------
QUESTION :- 2 (Use SQL clauses(WHERE, HAVING,LIMIT))
------------------------------------------------------------------

-----(1)

SELECT *
FROM orders
WHERE order_date >= CURDATE()-INTERVAL 6 MONTH;

-----(2)

SELECT *
FROM products
ORDER BY price DESC
LIMIT 5;

-----(3)

SELECT customer_id, COUNT(order_id) AS total_order
FROM orders
GROUP BY customer_id
HAVING COUNT(order_id)>3;

------------------------------------------------------------------
QUESTION :- 3 (apply SQL operators (AND,OR,NOT))
------------------------------------------------------------------

----(1)

SELECT o.order_id,
       o.customer_id,
       o.status,
       p.payment_status
FROM orders o
INNER JOIN payments p
ON o.order_id = p.order_id
WHERE o.status = 'Pending'
AND p.payment_status = 'Paid';

-----(2)

SELECT *
FROM products
WHERE NOT stock_quantity = 0;

------(3)

SELECT DISTINCT c.customer_id, c.name, c.registration_date
FROM customers c
LEFT JOIN orders o 
ON c.customer_id = o.customer_id
WHERE c.registration_date > '2022-12-31'
OR o.total_amount > 10000;

------------------------------------------------------------------
QUESTION :- 4 (sorting & grouping data(ORDER BY, GROUP BY))
------------------------------------------------------------------

-----(1)

SELECT * FROM products 
ORDER BY price DESC;

------(2)

SELECT customer_id, COUNT(order_id) AS total_orders
FROM orders
GROUP BY customer_id;

------(3)

SELECT c.category_name, SUM(oi.subtotal) AS total_revenue
FROM categories c
JOIN products p ON c.category_id = p.category_id
JOIN order_items oi ON p.product_id = oi.product_id
GROUP BY c.category_name;
 
------------------------------------------------------------------
QUESTION :- 5 (use aggregate functions (SUM,AVG,MAX,MIN,COUNT) )
------------------------------------------------------------------

-----(1)

SELECT SUM(total_amount) AS total_revenue 
FROM Orders;

-----(2)

SELECT p.name, SUM(o.quantity) AS total_quantity_sold
FROM order_items o
JOIN products p ON o.product_id = p.product_id
GROUP BY p.name
ORDER BY total_quantity_sold DESC
LIMIT 1;

------(3)

SELECT AVG(total_amount) AS average_order_value 
FROM Orders;

------------------------------------------------------------------
QUESTION :- 6 (establish primary and foreign key relationsips)
------------------------------------------------------------------

already built in the starting 

------------------------------------------------------------------
QUESTION :- 7 (implement Joins)
------------------------------------------------------------------

------(1)

SELECT p.name AS product_name, c.category_name
FROM products p
INNER JOIN categories c ON p.category_id = c.category_id;

------(2)

SELECT o.order_id, o.order_date, c.name, c.email
FROM orders o
LEFT JOIN customers c ON o.customer_id = c.customer_id;

------(3)

SELECT o.order_id, o.status
FROM shipping s
RIGHT JOIN orders o ON s.order_id = o.order_id
WHERE s.shipping_id IS NULL;

------(4)

SELECT c.name, o.order_id
FROM Customers c
LEFT JOIN Orders o ON c.customer_id = o.customer_id
UNION
SELECT c.name, o.order_id
FROM Customers c
RIGHT JOIN Orders o ON c.customer_id = o.customer_id;

------------------------------------------------------------------
QUESTION :- 8 (use Subqueries)
------------------------------------------------------------------

------(1)

SELECT * FROM Orders
WHERE customer_id IN (
    SELECT customer_id 
    FROM Customers 
    WHERE registration_date > '2022-12-31'
);

------(2)

SELECT name FROM Customers
WHERE customer_id = (
    SELECT customer_id 
    FROM Orders 
    GROUP BY customer_id 
    ORDER BY SUM(total_amount) DESC 
    LIMIT 1
);

------(3)

SELECT name FROM products
WHERE product_id NOT IN (
    SELECT DISTINCT product_id FROM order_items
);

------------------------------------------------------------------
QUESTION :- 9 (Implement date & time functions)
------------------------------------------------------------------

-----(1)

SELECT MONTH(order_date) AS order_month, COUNT(order_id) AS total_orders
FROM Orders
GROUP BY order_month;

------(2)

SELECT order_id, DATEDIFF(delivery_date, shipping_date) AS delivery_time
FROM shipping
WHERE delivery_date IS NOT NULL;

------(3)

SELECT order_id, DATE_FORMAT(order_date, '%d-%m-%Y') AS formatted_date
FROM Orders;

------------------------------------------------------------------
QUESTION :- 10 (use string manipulation functions)
------------------------------------------------------------------

------(1)

SELECT UPPER(name) AS IN_UPPERCASE
FROM products;

-------(2)

SELECT TRIM(name) AS cleaned_customer_name
FROM Customers;

-------(3)

SELECT 
    customer_id, 
    email, 
    REPLACE(email, '', 'Not Provided') AS replace_value
FROM customers;

(SMALL CONFUSION IS THERE AND THE BELOW CODE IS TAKEN FROM GOOGLE)

SELECT 
    customer_id, 
    email, 
    COALESCE(NULLIF(email, ''), 'Not Provided') AS replace_value 
FROM customers;

------------------------------------------------------------------
QUESTION :- 11 (Implement window functions)
------------------------------------------------------------------

------(1)

SELECT customer_id, 
       SUM(total_amount) AS total_spent,
       RANK() OVER (ORDER BY SUM(total_amount) DESC) AS spending_rank
FROM Orders
GROUP BY customer_id;

------(2)

SELECT order_date, 
       total_amount,
       SUM(total_amount) OVER (ORDER BY order_date) AS cumulative_revenue
FROM Orders;

Cumulative example :-
(Jan: ₹1,000 → Cumulative: ₹1,000
Feb: ₹1,500 → Cumulative: ₹2,500)

------(3)

SELECT order_id, 
       order_date,
       COUNT(order_id) OVER (ORDER BY order_date) AS running_order_count
FROM Orders;

ese ayega ye--

Row 1: COUNT sees 1 row. Result: 1.
Row 2: COUNT sees Row 1 and Row 2. Result: 2.
Row 3: COUNT sees Rows 1, 2, and 3. Result: 3, and so on. 

------------------------------------------------------------------
QUESTION :- 12 (apply SQL CASE Expression)
------------------------------------------------------------------

-------(1)

SELECT 
    c.name, 
    SUM(o.total_amount) AS total_spent,
    CASE 
        WHEN SUM(o.total_amount) > 50000 THEN 'Gold'
        WHEN SUM(o.total_amount) BETWEEN 20000 AND 50000 THEN 'Silver'
        ELSE 'Bronze'
    END AS Loyalty_Status
FROM Customers c
LEFT JOIN Orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.name;

------(2)

SELECT
    p.name,
    SUM(oi.quantity) AS units_sold,
    CASE
        WHEN SUM(oi.quantity) > 500 THEN 'Best Seller'
        WHEN SUM(oi.quantity) BETWEEN 200 AND 500 THEN 'Popular'
        ELSE 'Regular'
    END AS Product_Category
FROM
    products p
LEFT JOIN
    order_items oi ON p.product_id = oi.product_id
GROUP BY
    p.product_id, p.name
ORDER BY
    units_sold DESC;