-- Create a customer Table
CREATE TABLE Customer (
    CustomerID INT AUTO_INCREMENT,
    FirstName VARCHAR(20) NOT NULL,
    LastName VARCHAR(20) NOT NULL,

    -- Set CustomerID as a PRIMARY key
    PRIMARY KEY(CustomerID)
);


-- Create a Order Table
CREATE TABLE Orders (

    OrderID INT AUTO_INCREMENT,
    OrderDate DATE NOT NULL,
    CustomerID INT NOT NULL,

    -- Set OrderID as a PRIMARY KEY
    PRIMARY KEY(OrderID),

    -- Create a foreign Key relationship to Customer
    FOREIGN KEY (CustomerID) REFERENCES Customer(CustomerID)
    
);

    -- Insert data into Customer table
INSERT INTO Customer ( FirstName, LastName)
VALUES 
('John', 'Smith'),
('Sarah', 'Jones'),
('Mike', 'Brown');

SELECT * FROM Customer;

-- Insert data into Order table
INSERT INTO Orders (OrderDate, CustomerID)
VALUES
('2026-10-01', 1),
('2026-10-02', 1),
('2026-10-03', 2);

SELECT * FROM Orders;


-- Update a Customer Last name
UPDATE Customer
SET LastName = 'Johnson'
WHERE CustomerID = 1;

SELECT * FROM Customer;

-- Delete the customer with customerID

DELETE FROM Customer
WHERE CustomerID = 3;

SELECT * FROM Customer;