CREATE TABLE Students (student_id INT AUTO_INCREMENT PRIMARY KEY,
    student_name VARCHAR(100) NOT NULL,
    grade_level INT NOT NULL);

CREATE TABLE Clubs(
    club_id INT AUTO_INCREMENT PRIMARY KEY,
    club_name VARCHAR(100) NOT NULL,
    advisor_name VARCHAR(100)
);

CREATE TABLE Memberships(
    membership_id INT AUTO_INCREMENT PRIMARY KEY,
    student_id INT,
    club_id INT, 
    join_date DATE,
    FOREIGN KEY (student_id) REFERENCES Students(student_id),
    FOREIGN KEY (club_id) REFERENCES Clubs (club_id)
);

INSERT INTO Students (student_name,grade_level) VALUES
('Emma Johnson',7),
('Noah Smith',7),
('Olivia Williams',7);

INSERT INTO Clubs (club_name,advisor_name) VALUES
('Chess Club','Mr.Anderson'),
('Art Club','Ms.Taylor'),
('Science Club','Mrs.Roberts');

INSERT INTO Memberships (student_id,club_id,join_date) VALUES
(1,1,'2023-1-15'),
(2,2,'2023-01-18'),
(3,3,'2023-01-20');

SELECT *from Students;
SELECT *from Clubs;

SELECT Students.student_name, Clubs.club_name, Memberships.join_date 
FROM Memberships 
JOIN Students ON Memberships.student_id=Students.student_id
JOIN Clubs ON Memberships.club_id=Clubs.club_id;

SELECT Students.student_name
FROM Students
JOIN Memberships ON Students.student_id=Memberships.student_id
JOIN Clubs ON Memberships.club_id=Clubs.club_id
WHERE Clubs.club_name='Chess Club';