Untitled

USE oel;

-- Create the Customers table
CREATE TABLE Customers (
    Customer_ID INT PRIMARY KEY,
    Name VARCHAR(255),
    Email VARCHAR(255) UNIQUE,
    Address TEXT,
    Phone VARCHAR(15),
    Date_Joined DATE
);

-- Create the Categories table
CREATE TABLE Categories (
    Category_ID INT PRIMARY KEY,
    Category_Name VARCHAR(255) UNIQUE
);

-- Create the Products table
CREATE TABLE Products (
    Product_ID INT PRIMARY KEY,
    Product_Name VARCHAR(255),
    Category_ID INT,
    Price DECIMAL(10, 2),
    Stock_Quantity INT,
    FOREIGN KEY (Category_ID) REFERENCES Categories(Category_ID)
        ON UPDATE CASCADE ON DELETE SET NULL
);

-- Create the Orders table
CREATE TABLE Orders (
    Order_ID INT PRIMARY KEY,
    Customer_ID INT,
    Order_Date DATE,
    Total_Amount DECIMAL(10, 2),
    Shipping_Address TEXT,
    FOREIGN KEY (Customer_ID) REFERENCES Customers(Customer_ID)
        ON UPDATE CASCADE ON DELETE CASCADE
);

-- Create the OrderDetails table
CREATE TABLE OrderDetails (
    OrderDetail_ID INT PRIMARY KEY,
    Order_ID INT,
    Product_ID INT,
    Quantity INT,
    Unit_Price DECIMAL(10, 2),
    FOREIGN KEY (Order_ID) REFERENCES Orders(Order_ID)
        ON UPDATE CASCADE ON DELETE CASCADE,
    FOREIGN KEY (Product_ID) REFERENCES Products(Product_ID)
        ON UPDATE CASCADE ON DELETE SET NULL
);

-- Create the Payments table
CREATE TABLE Payments (
    Payment_ID INT PRIMARY KEY,
    Order_ID INT,
    Payment_Method VARCHAR(50),
    Payment_Amount DECIMAL(10, 2),
    Payment_Date DATE,
    FOREIGN KEY (Order_ID) REFERENCES Orders(Order_ID)
        ON UPDATE CASCADE ON DELETE CASCADE
);

GO

-- Inserting Categories data
INSERT INTO Categories (Category_ID, Category_Name) VALUES
(1, 'Electronics'),
(2, 'Clothing'),
(3, 'Furniture');

-- Inserting Products data
INSERT INTO Products (Product_ID, Product_Name, Category_ID, Price, Stock_Quantity) VALUES
(1, 'Laptop', 1, 800.00, 50),
(2, 'Smartphone', 1, 500.00, 100),
(3, 'Shirt', 2, 30.00, 200),
(4, 'Sofa', 3, 700.00, 30),
(5, 'Headphones', 1, 150.00, 150);

-- Inserting Customers data
INSERT INTO Customers (Customer_ID, Name, Email, Address, Phone, Date_Joined) VALUES
(1, 'Zohaib', 'zohaib@example.com', '1234 kotli St, Lahore', '123-456-7890', '2023-01-15'),
(2, 'Sharjeel', 'sharjeel@example.com', '5678 burj St, Lahore', '987-654-3210', '2022-12-20'),
(3, 'Bilal', 'bilal@example.com', '9876 sargodha St, Lahore', '555-333-1111', '2023-02-10');

-- Inserting Orders data
INSERT INTO Orders (Order_ID, Customer_ID, Order_Date, Total_Amount, Shipping_Address) VALUES
(1, 1, '2024-12-15', 1100.00, '1234 kotli St, Lahore'),
(2, 2, '2024-12-18', 200.00, '5678 burj St, Lahore'),
(3, 3, '2024-12-20', 850.00, '9876 sargodha St, Lahore');

-- Inserting OrderDetails data
INSERT INTO OrderDetails (OrderDetail_ID, Order_ID, Product_ID, Quantity, Unit_Price) VALUES
(1, 1, 1, 1, 800.00),
(2, 1, 5, 2, 150.00),
(3, 2, 3, 4, 30.00),
(4, 3, 4, 1, 700.00);

-- Inserting Payments data
INSERT INTO Payments (Payment_ID, Order_ID, Payment_Method, Payment_Amount, Payment_Date) VALUES
(1, 1, 'Credit Card', 1100.00, '2024-12-15'),
(2, 2, 'PayPal', 200.00, '2024-12-18'),
(3, 3, 'Credit Card', 850.00, '2024-12-20');

GO

-- Apply a 10% discount to Electronics category products
UPDATE Products
SET Price = Price * 0.9
WHERE Category_ID = (SELECT Category_ID FROM Categories WHERE Category_Name = 'Electronics');

-- Apply a 15% discount to products priced above $500
UPDATE Products
SET Price = Price * 0.85
WHERE Price > 500;

-- Apply a flat $50 discount on orders over $1000
UPDATE Orders
SET Total_Amount = Total_Amount - 50
WHERE Total_Amount > 1000;

GO

-- Generate the report of top 5 customers who spent the most in the last 6 months
SELECT TOP 5
    C.Name AS Customer_Name,
    SUM(OD.Quantity * OD.Unit_Price) AS Total_Amount_Spent,
    COUNT(O.Order_ID) AS Number_Of_Orders,
    (SELECT TOP 1 Product_Name FROM Products WHERE Product_ID = OD.Product_ID) AS Most_Purchased_Product
FROM Customers C
JOIN Orders O ON C.Customer_ID = O.Customer_ID
JOIN OrderDetails OD ON O.Order_ID = OD.Order_ID
WHERE O.Order_Date > DATEADD(MONTH, -6, GETDATE()) -- Last 6 months
GROUP BY C.Customer_ID
ORDER BY Total_Amount_Spent DESC;

GO

-- Create a view to combine Customer, Order, Product, and Payment details
CREATE VIEW Customer_Order_Payment_View AS
SELECT 
    C.Customer_ID,
    C.Name AS Customer_Name,
    O.Order_ID,
    O.Order_Date,
    O.Total_Amount,
    O.Shipping_Address,
    P.Payment_Method,
    P.Payment_Amount,
    P.Payment_Date,
    OD.Quantity,
    OD.Unit_Price,
    PR.Product_Name
FROM Customers C
JOIN Orders O ON C.Customer_ID = O.Customer_ID
JOIN OrderDetails OD ON O.Order_ID = OD.Order_ID
JOIN Products PR ON OD.Product_ID = PR.Product_ID
JOIN Payments P ON O.Order_ID = P.Order_ID;

GO

-- Retrieve data from the view
SELECT * FROM Customer_Order_Payment_View;
GO