CREATE TABLE Products( 
    ProductID MEDIUMINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, 
    ProductName VARCHAR(150) NOT NULL, 
    UnitPrice DECIMAL(5,2),
    StockQuantity MEDIUMINT UNSIGNED
    );

CREATE TABLE Customers( 
    CustomerID MEDIUMINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, 
    CustomerName VARCHAR(150) NOT NULL, 
    EmailAddress VARCHAR(200) UNIQUE, 
    PhoneNumber VARCHAR(15) NULL, 
    Region VARCHAR(50), 
    LoyaltyYears TINYINT UNSIGNED CHECK(LoyaltyYears BETWEEN 0 AND 50)
    );

CREATE TABLE Orders ( 
    OrderID MEDIUMINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, 
    CustomerID MEDIUMINT UNSIGNED, 
    OrderDate DATETIME, 
    TotalAmount DECIMAL(6,2), 
    FOREIGN KEY(CustomerID) REFERENCES Customers(CustomerID) ON DELETE CASCADE  
    );


CREATE TABLE OrderDetails ( 
    OrderID MEDIUMINT UNSIGNED, 
    ProductID MEDIUMINT UNSIGNED, 
    Quantity TINYINT UNSIGNED, 
    LineTotal DECIMAL(7,2), 
    FOREIGN KEY (OrderID) REFERENCES Orders(OrderID) ON DELETE CASCADE, 
    FOREIGN KEY (ProductID) REFERENCES Products(ProductID)
    ); 


INSERT INTO Customers(CustomerID, CustomerName, EmailAddress, PhoneNumber, Region, LoyaltyYears) VALUES 
    (1, 'Alice Johnson','alice.johnson@gmail.com', '555-1234', 'North', 15), 
    (2, 'Bob Smith', 'bob.smith@gmail.com', '555-5678', 'South', 5), 
    (3, 'Carol Davis', 'carol.davis@gmail.com', '555-8765', 'East',15), 
    (4, 'David Brown', 'david.brown@gmail.com', '555-4321', 'West', 2);


INSERT INTO Products(ProductID, ProductName, UnitPrice, StockQuantity) VALUES 
    (101, 'Laptop', 799.99, 50), 
    (102, 'Smartphone', 499.99, 100), 
    (103, 'Headphones', 89.99, 200), 
    (104, 'Keyboard', 49.99, 150),
    (105, 'Mouse', 29.99, 300); 


INSERT INTO Orders(CustomerID, OrderDate, TotalAmount)VALUES 
(1, '2025-10-01 14:30:00', 249.99), 
(2, '2025-10-05 09:15:00', 89.50), 
(3, '2025-10-10 16:45:00', 499.00), 
(1, '2025-10-15 11:00:00', 129.99); 



INSERT INTO OrderDetails(OrderID, ProductID, Quantity, LineTotal) VALUES 
(1, 101, 2, 79.98), 
(1, 102, 1, 170.01), 
(2, 103, 1, 89.50),
(3, 101, 5, 399.95), 
(3, 104, 1, 99.05), 
(4, 102, 2, 129.99); 

-- List all orders with customer names and order dates
SELECT Customers.CustomerName, Orders.OrderID, Orders.OrderDate 
FROM Customers 
LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID; 

-- Show each product name and total quantity sold 
SELECT ProductName, SUM(StockQuantity) AS TotalQuantity 
FROM Products 
GROUP BY ProductName; 

-- Display all customers and the number of orders they placed. Include customers with zero orders 
SELECT Customers.CustomerName , COUNT(Orders.OrderID) AS OrderCount
FROM Customers 
LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID 
GROUP BY CustomerName;