5455

CREATE TABLE Customers (
    Customer_ID INT PRIMARY KEY,
    Name VARCHAR(100),
    Email VARCHAR(100) UNIQUE,
    Address VARCHAR(255),
    Phone VARCHAR(15),
    Date_Joined DATE
);

CREATE TABLE Categories (
    Category_ID INT PRIMARY KEY,
    Category_Name VARCHAR(100) NOT NULL
);

CREATE TABLE Products (
    Product_ID INT PRIMARY KEY,
    Product_Name VARCHAR(100) NOT NULL,
    Category_ID INT,
    Price DECIMAL(10, 2) NOT NULL CHECK (Price >= 0),
    Stock_Quantity INT NOT NULL CHECK (Stock_Quantity >= 0),
    FOREIGN KEY (Category_ID) REFERENCES Categories(Category_ID) ON DELETE CASCADE ON UPDATE CASCADE
);


CREATE TABLE Orders (
    Order_ID INT PRIMARY KEY,
    Customer_ID INT,
    Order_Date DATE NOT NULL,
    Total_Amount DECIMAL(10, 2) NOT NULL CHECK (Total_Amount >= 0),
    Shipping_Address VARCHAR(255),
    FOREIGN KEY (Customer_ID) REFERENCES Customers(Customer_ID) ON DELETE CASCADE ON UPDATE CASCADE
);


CREATE TABLE OrderDetails (
    OrderDetail_ID INT PRIMARY KEY,
    Order_ID INT,
    Product_ID INT,
    Quantity INT NOT NULL CHECK (Quantity > 0),
    Unit_Price DECIMAL(10, 2) NOT NULL CHECK (Unit_Price >= 0),
    FOREIGN KEY (Order_ID) REFERENCES Orders(Order_ID) ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY (Product_ID) REFERENCES Products(Product_ID) ON DELETE CASCADE ON UPDATE CASCADE
);


CREATE TABLE Payments (
    Payment_ID INT PRIMARY KEY,
    Order_ID INT,
    Payment_Method VARCHAR(50) NOT NULL,
    Payment_Amount DECIMAL(10, 2) NOT NULL CHECK (Payment_Amount >= 0),
    Payment_Date DATE NOT NULL,
    FOREIGN KEY (Order_ID) REFERENCES Orders(Order_ID) ON DELETE CASCADE ON UPDATE CASCADE
);

INSERT INTO Categories (Category_ID, Category_Name)
VALUES 
(1, 'Electronics'),
(2, 'Home Appliances');


INSERT INTO Customers (Customer_ID, Name, Email, Address, Phone, Date_Joined)
VALUES 
(1, 'John Doe', 'john.doe@example.com', '123 Main St, City', '123-456-7890', '2025-01-06'),
(2, 'Jane Smith', 'jane.smith@example.com', '456 Elm St, City', '987-654-3210', '2025-01-07');


INSERT INTO Products (Product_ID, Product_Name, Category_ID, Price, Stock_Quantity)
VALUES 
(1, 'Smartphone', 1, 699.99, 50),
(2, 'Laptop', 1, 1200.00, 30),
(3, 'Washing Machine', 2, 499.99, 20),
(4, 'Microwave Oven', 2, 300.00, 15);

INSERT INTO Orders (Order_ID, Customer_ID, Order_Date, Total_Amount, Shipping_Address)
VALUES 
(1, 1, '2025-01-06', 699.99, '123 Main St, City'),
(2, 2, '2025-01-07', 1300.00, '456 Elm St, City');

INSERT INTO OrderDetails (OrderDetail_ID, Order_ID, Product_ID, Quantity, Unit_Price)
VALUES 
(1, 1, 1, 1, 699.99),
(2, 2, 2, 1, 1200.00);


INSERT INTO Payments (Payment_ID, Order_ID, Payment_Method, Payment_Amount, Payment_Date)
VALUES 
(1, 1,'Credit Card',699.99,'2025-01-06'),
(2, 2,'PayPal',1300.00,'2025-01-07');



UPDATE Products
SET Price = Price * 0.9
WHERE Category_ID IN (SELECT Category_ID FROM Categories WHERE Category_Name = 'Electronics');


UPDATE Products
SET Price = Price * 0.85
WHERE Price > 500;

UPDATE Orders
SET Total_Amount = Total_Amount - 50
WHERE Total_Amount > 1000;



SELECT TOP 5 
    c.Name AS Customer_Name,
    SUM(o.Total_Amount) AS Total_Amount_Spent,
    COUNT(o.Order_ID) AS Number_of_Orders,
    (SELECT TOP 1 od.Product_ID 
     FROM OrderDetails od 
     JOIN Orders o2 ON od.Order_ID = o2.Order_ID
     WHERE o2.Customer_ID = c.Customer_ID
     GROUP BY od.Product_ID 
     ORDER BY COUNT(od.Product_ID) DESC) AS Most_Purchased_Product
FROM 
    Customers c
JOIN 
    Orders o ON c.Customer_ID = o.Customer_ID
WHERE 
    o.Order_Date >= DATEADD(MONTH, -6, GETDATE())
GROUP BY 
    c.Customer_ID,c.Name
ORDER BY 
    Total_Amount_Spent DESC;




GO

CREATE VIEW CustomerOrderSummary AS
SELECT
    c.Customer_ID,
    c.Name AS Customer_Name,
    o.Order_ID,
    o.Order_Date,
    p.Product_ID,
    p.Product_Name,
    od.Quantity,
    od.Unit_Price,
    pay.Payment_ID,
    pay.Payment_Method,
    pay.Payment_Amount,
    pay.Payment_Date
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 p ON od.Product_ID = p.Product_ID
LEFT JOIN
    Payments pay ON o.Order_ID = pay.Order_ID;