-- Create a table to store event information 
CREATE TABLE Events(
    event_id INT AUTO_INCREMENT PRIMARY KEY,
    event_name VARCHAR(100) NOT NULL,
    event_date DATE NOT NULL,
    location VARCHAR(100)
);



-- Create a table to store participant information
CREATE TABLE Participants(
    participant_id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE
);

-- Create a table to store event schedules
CREATE TABLE EventSchedules(
    schedule_id INT AUTO_INCREMENT PRIMARY KEY,
    event_id INT,
    participant_id INT,
    registration_date DATE NOT NULL, 
    FOREIGN KEY (event_id) REFERENCES Events(event_id),
    FOREIGN KEY(participant_id) REFERENCES
Participants(participant_id)
);

-- Insert sample events into the Event table
INSERT INTO Events(event_name,event_date,location) VALUE
('School Carnival','2023-06-10','City Park'),
('Science Fair','2023-04-20','Community Center'),
('Community Picnic','2023-06-10','City Park');

INSERT INTO Participants(first_name,last_name,email) VALUES
    ('John','Doe','john.doe@email.com'),
    ('Jane','Smith','jane.smith@email.com'),
    ('Alice','Johnson','alice.johnson@email.com');

INSERT INTO EventSchedules(event_id,participant_id,registration_date) VALUES
    (1,1,'2023-05-1'),
    (1,2,'2023-05-2'),
    (1,3,'2023-06-1'),
    (3,1,'2023-04-10'),
    (3,2,'2023-04-12');
SELECT *FROM Events;
Select *FROM Participants;
Select *from EventSchedules;

SELECT
    Events.event_name,
    Events.event_date,
    Events.location,
    Participants.first_name,
    Participants.last_name
FROM Events 
INNER JOIN EventSchedules ON Events.event_id=EventSchedules.event_id
INNER JOIN Participants ON EventSchedules.participant_id=Participants.participant_id
ORDER BY Events.event_date;