CREATE TABLE Students (
    student_id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    grade INT NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL
);

CREATE TABLE Clubs (
    club_id INT PRIMARY KEY AUTO_INCREMENT,
    club_name VARCHAR(100) UNIQUE NOT NULL,
    description TEXT,
    advisor VARCHAR(100) NOT NULL
);

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

CREATE TABLE Events (
    event_id INT PRIMARY KEY AUTO_INCREMENT,
    student_id INT,
    club_id INT,
    event_name VARCHAR(100) NOT NULL,
    event_date DATE NOT NULL,
    FOREIGN KEY (club_id) REFERENCES Clubs(club_id),
    FOREIGN KEY (student_id) REFERENCES Students(student_id)
);

CREATE TABLE Attendance (
    attendance_id INT PRIMARY KEY AUTO_INCREMENT,
    event_id INT,
    student_id INT,
    status ENUM('Present','Absent') NOT NULL,
    FOREIGN KEY (event_id) REFERENCES Events(event_id),
    FOREIGN KEY (student_id) REFERENCES Students(student_id)
);

INSERT Students (student_id, first_name, last_name,	grade, email) VALUES	
    (1, 'John', 'Doe', 8, 'john.doe@example.com'),
    (2,	'Emma', 'Smith', 9, 'emma.smith@example.com'),
    (3,	'Liam', 'Johnson', 7, 'liam.johnson@example.com'),
    (4, 'Sophia','Brown', 10, 'sophia.brown@example.com'),
    (5,	'Noah', 'Williams', 8, 'noah.williams@example.com'),
    (6,	'Olivia','Miller', 9, 'olivia.miller@example.com'),
    (7,	'William', 'Davis', 10, 'william.davis@example.com'),
    (8,	'Ava','Garcia', 7, 'ava.garcia@example.com'),
    (9,	'James','Martinez', 8, 'james.martinez@example.com'),
    (10,'Isabella',	'Hernandez', 9, 'isabella.hernandez@example.com');

INSERT Clubs (club_id,club_name,description,advisor) VALUES
    (1,'Robotics Club','Students learn and build robots.','Mr. Anderson'),
    (2,'Drama Club', 'Acting, stage performances, and theater arts.','Ms. Johnson'),
    (3,'Science Club','Experiments, research, and science fairs.','Dr. Carter'),
    (4,'Chess Club','Improving chess strategies and competitions.','Mr. Lee'),
    (5,'Music Club','Learning and performing musical instruments.','Mrs. White');

INSERT Memberships (membership_id, student_id, club_id, join_date) VALUES
    (1,	1, 1,'2024-02-01'),
    (2,	1, 4,'2024-01-15'),
    (3,	2, 2,'2024-03-05'),
    (4,	3, 3,'2024-02-20'),
    (5,	4, 5,'2024-04-10'),
    (6,	5, 1,'2024-02-25'),
    (7,	6, 3,'2024-03-12'),
    (8,	7, 1,'2024-04-05'),
    (9,	8, 2,'2024-02-28'),
    (10,9, 4,'2024-05-10'),
    (11,10,	5,'2024-06-01');

INSERT Events (event_id,club_id,event_name,event_date) VALUES
    (1,1,'Robotics Competition','2024-06-10'),
    (2,1,'Robotics Workshop','2024-07-15'),
    (3,2,'Annual Drama Performance','2024-05-15'),
    (4,2,'Drama Rehearsal','2024-06-20'),
    (5,3,'Science Fair','2024-07-20'),
    (6,3,'Science Quiz','2024-08-10'),
    (7,4,'Chess Tournament','2024-08-05'),
    (8,4,'Chess Strategy Session','2024-09-05'),
    (9,5,'School Music Concert','2024-09-10'),
    (10,5,'Music Talent Show','2024-10-12'),
    (11,1,'Tech Talk on AI','2024-11-05');

INSERT Attendance (attendance_id,	event_id,	student_id,	status) VALUES
    (1,	1,	1,	'Present'),
    (2,	1,	5,	'Absent'),
    (3,	2,	1,	'Present'),
    (4,	2,	7,	'Present'),
    (5,	2,	5,	'Absent'),
    (6,	3,	2,	'Present'),
    (7,	4,	2,	'Present'),
    (8,	4,	8,	'Present'),
    (9,	5,	3,	'Present'),
    (10,	5,	6,	'Present'),
    (11,	6,	3,	'Absent'),
    (12,	6,	6,	'Present'),
    (13,	7,	1,	'Present'),
    (14,	8,	1,	'Present'),
    (15,	8,	9,	'Present'),
    (16,	9,	4,	'Present'),
    (17,	10,	4,	'Present'),
    (18,	10,	10,	'Present'),
    (19,	11,	1,	'Present'),
    (20,	11,	5,	'Absent'),
    (21,	11,	7,	'Present'),
    (22,	11,	3,	'Absent');

-- SELECT student_id, first_name, last_name, grade, email
-- FROM Students
-- WHERE grade=9

-- SELECT Students.student_id, Students.first_name, Students.last_name, Clubs.club_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='Science Club'

-- SELECT Events.event_id, Clubs.club_id, Events.event_name, Events.event_date
-- FROM Events
-- JOIN Clubs ON Events.club_id=Clubs.club_id
-- WHERE Events.event_date='2024-07-20';

-- SELECT Events.event_id, Events.event_name, Events.event_date, Clubs.club_name
-- FROM Events
-- LEFT JOIN Clubs ON Clubs.club_id=Events.club_id
-- ORDER BY Events.event_date ASC;

-- SELECT Students.student_id, Students.first_name, Students.last_name, Events.event_name, Attendance.status
-- FROM Students
-- JOIN Attendance ON Attendance.student_id=Students.student_id
-- JOIN Events ON Events.event_id=Attendance.event_id
-- WHERE Events.event_name='Science Quiz' AND Attendance.status='Present'

-- SELECT Clubs.club_name, COUNT(Memberships.student_id) AS total_members
-- FROM Clubs
-- JOIN Memberships ON Clubs.club_id=Memberships.club_id
-- GROUP BY Clubs.club_name

-- SELECT Students.student_id, Students.first_name, Students.last_name, COUNT(Memberships.student_id) AS club_joined
-- FROM Students
-- JOIN Memberships ON Students.student_id=Memberships.student_id
-- JOIN Clubs ON Clubs.club_id=Memberships.club_id
-- GROUP BY Students.student_id, Students.first_name, Students.last_name
-- HAVING club_joined > 1

-- SELECT Events.event_date, Events.event_name, Students.student_id, Students.first_name, Students.last_name, Attendance.status
-- FROM Events
-- JOIN Attendance ON Attendance.event_id=Events.event_id
-- JOIN Students ON Students.student_id=Attendance.student_id
-- ORDER BY Events.event_date

-- SELECT Clubs.club_name, COUNT(Events.club_id) AS total_events
-- FROM Clubs
-- JOIN Events ON Events.club_id=Clubs.club_id
-- GROUP BY Clubs.club_name
-- HAVING total_events >2

-- SELECT Students.student_id, Students.first_name, Students.last_name
-- FROM Students
-- LEFT JOIN Attendance ON Students.student_id=Attendance.student_id
-- WHERE Attendance.status = 'Absent' 
   
-- CREATE VIEW Present AS SELECT Students.student_id, COUNT(Attendance.status) AS present
-- FROM Students
-- LEFT JOIN Attendance ON Students.student_id=Attendance.student_id
-- WHERE Attendance.status='Present'
-- GROUP BY  Students.student_id;
-- SELECT Students.student_id, Students.first_name, Students.last_name
-- FROM Students
-- LEFT JOIN Present ON Students.student_id=Present.student_id
-- WHERE Present.present IS NULL;

-- SELECT Events.event_name, SUM(Attendance.status='Present') AS total_present
-- FROM Events
-- LEFT JOIN Attendance ON Attendance.event_id=Events.event_id
-- GROUP BY Events.event_name

-- SELECT Students.first_name, Students.last_name, Clubs.club_name, Memberships.join_date
-- FROM Students
-- JOIN Memberships ON Students.student_id=Memberships.student_id
-- JOIN Clubs ON Clubs.club_id=Memberships.club_id
-- WHERE YEAR(Memberships.join_date) = '2024'

-- SELECT Clubs.club_name, COUNT(Attendance.student_id) AS attendance_count
-- FROM Clubs
-- JOIN Events ON Clubs.club_id=Events.club_id
-- JOIN Attendance ON Attendance.event_id=Events.event_id
-- GROUP BY Clubs.club_name

-- CREATE VIEW Never AS SELECT Students.student_id, COUNT(Attendance.status) AS att_never
-- FROM Students
-- LEFT JOIN Attendance ON Students.student_id=Attendance.student_id
-- WHERE Attendance.status='Absent'
-- GROUP BY Students.student_id;
-- SELECT Students.student_id, Students.first_name, Students.last_name
-- FROM Students
-- LEFT JOIN Never ON Never.student_id=Students.student_id
-- WHERE Never.att_never IS NULL;

SELECT Students.first_name, Students.last_name, COUNT(Attendance.student_id) AS events_attended
FROM Students
JOIN Attendance ON Attendance.student_id=Students.student_id
WHERE Attendance.status='Present'
GROUP BY Students.first_name, Students.last_name