CREATE TABLE Customer ( 
    CustomerID INT AUTO_INCREMENT PRIMARY KEY, 
    FirstName VARCHAR(30), 
    LastName VARCHAR(30),
    Email VARCHAR(50) UNIQUE, 
    JoinDate DATE
    );

CREATE TABLE `Order`( 
    OrderID INT AUTO_INCREMENT PRIMARY KEY, 
    OrderDate DATE, 
    Amount DECIMAL(5,2), 
    CustomerID INT, 
    FOREIGN KEY (CustomerID) REFERENCES Customer(CustomerID)
    );

INSERT INTO Customer (FirstName, LastName, Email, JoinDate) VALUES 
('Alice', 'Johnson','alice.johnson@gmail.com', '2023-01-15'), 
('Bob', 'Smith', 'bob.smith@gmail.com', '2023-03-22'), 
('Carol','Nguyen', 'carol.nguyen@gmail.com', '2024-06-10'), 
('David','Lee', 'david.lee@gmail.com', '2025-02-05'), 
('Eva', 'Martinez','eva.martinez@gmail.com', '2025-09-01'); 



INSERT INTO `Order` (OrderDate, Amount, CustomerID) VALUES
('2025-01-10', 120.50, 1),
('2025-01-12', 75.00, 2),
('2025-01-13', 200.00, 3),
('2025-01-14', 50.25, 4),
('2025-01-15', 99.99, 5),
('2025-01-16', 35.00, 1),
('2025-01-17', 150.00, 2),
('2025-01-18', 80.00, 3),
('2025-01-19', 60.00, 4),
('2025-01-20', 110.00, 5);

-- Show each order with customer's full name
SELECT 
    o.OrderID, 
    o.OrderDate,
    o.Amount, 
    CONCAT(c.FirstName, ' ', c.LastName) AS CustomerName 
FROM
    `Order` o 
JOIN 
    Customer c ON o.CustomerID = c.CustomerID; 

-- List Customers who have placed at least one order
SELECT DISTINCT
    c.CustomerID, 
    c.FirstName, 
    c.LastName
FROM 
    Customer c
JOIN 
    `Order` o ON c.CustomerID = o.CustomerID; 

-- Show customers who haven't placed any orders
SELECT 
    c.CustomerID,
    c.FirstName,
    c.LastName
FROM 
    Customer c
LEFT JOIN 
    `Order` o ON c.CustomerID = o.CustomerID
WHERE 
    o.OrderID IS NULL;


-- Retrieve orders placed after January 1st, 2025
SELECT * 
FROM `Order`
WHERE ORDERDATE > '2025-01-01';  


-- List all customers who joined in 2024 
SELECT * 
FROM Customer 
WHERE Year(JoinDate) = 2024; 

-- Show all orders with an amount greater then $100 

SELECT * 
FROM `Order` 
WHERE Amount > 100.00;

-- Find the total number of orders per customer 
SELECT CustomerID, COUNT(*) AS TotalOrders  
FROM `Order`
GROUP BY CustomerID;