CREATE TABLE Students (
    StudentID INT PRIMARY KEY AUTO_INCREMENT,
    StudentFirstName VARCHAR(50),
    StudentLastName VARCHAR(50),
    DateofBirth DATE,
    Address VARCHAR(100),
    StudentEmail VARCHAR(100),
    StudentPhone VARCHAR(20),
    GradeLevel INT
);
CREATE TABLE Teachers (
    TeacherID INT PRIMARY KEY AUTO_INCREMENT,
    TeacherFirstName VARCHAR(50),
    TeacherLastName VARCHAR(50),
    TeacherEmail VARCHAR(100),
    TeacherPhone VARCHAR(20),
    Specialization VARCHAR(100)
);
CREATE TABLE Courses (
    CourseID INT PRIMARY KEY AUTO_INCREMENT,
    CourseName VARCHAR(100),
    CourseDescription TEXT,
    TeacherID INT,
    StartDate DATE,
    EndDate DATE,
    FOREIGN KEY (TeacherID) REFERENCES Teachers(TeacherID)
);
CREATE TABLE Enrollments (
    EnrollmentID INT PRIMARY KEY AUTO_INCREMENT,
    StudentID INT,
    CourseID INT,
    EnrollmentDate DATE,
    Status ENUM('Enrolled', 'Completed', 'Dropped'),
    Grade DECIMAL(5, 2),
    FOREIGN KEY (StudentID) REFERENCES Students(StudentID),
    FOREIGN KEY (CourseID) REFERENCES Courses(CourseID)
);
CREATE TABLE Assignments (
    AssignmentID INT PRIMARY KEY AUTO_INCREMENT,
    CourseID INT,
    AssignmentTitle VARCHAR(100),
    DueDate DATE,
    MaxScore DECIMAL(5, 2),
    FOREIGN KEY (CourseID) REFERENCES Courses(CourseID)
);
CREATE TABLE Scores (
    ScoreID INT PRIMARY KEY AUTO_INCREMENT,
    AssignmentID INT,
    StudentID INT,
    Score DECIMAL(5,2),
    DateSubmitted DATE,
    Comments TEXT,
    FOREIGN KEY (StudentID) REFERENCES Students(StudentID),
    FOREIGN KEY (AssignmentID) REFERENCES Assignments(AssignmentID)
);
CREATE TABLE Materials (
    MaterialID INT PRIMARY KEY AUTO_INCREMENT,
    CourseID INT,
    MaterialName VARCHAR(100),
    FileLink VARCHAR(255),
    UploadDate DATE,
    MaterialDescription TEXT,
    FOREIGN KEY (CourseID) REFERENCES Courses(CourseID)
);
CREATE TABLE Messages (
    MessageID INT PRIMARY KEY AUTO_INCREMENT,
    SenderID INT,
    ReceiverID INT,
    MessageContent TEXT,
    TimeStamp TIMESTAMP
);
CREATE TABLE Exams (
    ExamID INT PRIMARY KEY AUTO_INCREMENT,
    CourseID INT,
    ExamTitle VARCHAR(100),
    Date DATE,
    Duration INT,
    ExamMaxScore DECIMAL(5,2),
    FOREIGN KEY (CourseID) REFERENCES Courses(CourseID)
);
CREATE TABLE ExamScores (
    ExamScoreID INT PRIMARY KEY AUTO_INCREMENT,
    ExamID INT,
    StudentID INT,
    ExamScore DECIMAL(5,2),
    DateTaken DATE,
    Comments TEXT,
    FOREIGN KEY (ExamID) REFERENCES Exams(ExamID),
    FOREIGN KEY (StudentID) REFERENCES Students(StudentID)
);
INSERT INTO Students (StudentFirstName, StudentLastName, DateofBirth, Address, StudentEmail, StudentPhone, GradeLevel) VALUES
    ('John', 'Doe', '2005-03-15', '123 Main St', 'john.doe@gmail.com', '555-123-4567', 10),
    ('Jane', 'Smith', '2006-07-20', '456 Elm St', 'jane.smith@gmail.com', '555-987-6543', 9),
    ('Robert', 'John', '2004-01-10', '789 Oak St', 'robert.john@gmail.com', '555-456-7890', 11),
    ('Sarah', 'Brown', '2007-05-29', '101 Maple St', 'sarah.brown@gmail.com', '555-321-6540', 8),
    ('Michael', 'Wil', '2003-09-03', '234 Cedar St', 'michael.wil@gmail.com', '555-654-9871', 10);
INSERT INTO Teachers (TeacherFirstName, TeacherLastName, TeacherEmail, TeacherPhone, Specialization) VALUES
    ('Alice', 'Johnson', 'alice.johnson@gmail.com', '555-111-2222', 'Mathematics'),
    ('David', 'Smith', 'david.smith@gmail.com', '555-222-3333', 'Science'),
    ('Emily', 'Wilson', 'emily.wilson@gmail.com', '555-333-4444', 'Literature'),
    ('Frank', 'Brown', 'frank.brown@gmail.com', '555-444-55555', 'History'),
    ('Grace', 'Davis', 'grace.davis@gmail.com', '555-555-66666', 'Computer Science');
INSERT INTO Courses (CourseName, CourseDescription, TeacherID, StartDate, EndDate) VALUES
    ('Basic Mathematics', 'Introduction math course', 1, '2023-09-01', '2024-05-30'),
    ('Basic of Science', 'Introduction Science Course', 2, '2023-09-05', '2024-06-01'),
    ('English Literature', 'Exploring classic literature', 3, '2023-08-28', '2024-06-05'),
    ('World History - I', 'Introduction to world history', 4, '2023-08-30', '2024-06-10'),
    ('Computer Programming', 'Introduction to programming', 5, '2023-09-03', '2024-06-15');
INSERT INTO Enrollments (StudentID, CourseID, EnrollmentDate, Status, Grade) VALUES
    (1, 1, '2023-09-02', 'Enrolled', 5.1),
    (2, 2, '2023-09-06', 'Enrolled', 5.0),
    (3, 3, '2023-08-29', 'Enrolled', 5.2),
    (4, 4, '2023-08-31', 'Enrolled', 5.1),
    (5, 5, '2023-09-04', 'Enrolled', 5.4);
INSERT INTO Assignments (CourseID, AssignmentTitle, DueDate, MaxScore) VALUES
    (1, 'Math Quis 1', '2023-10-15', 100),
    (2, 'Lab Report 1', '2023-10-20', 100),
    (3, 'Essay on Classic Literature', '2023-10-25', 100),
    (4, 'History Research Project', '2023-10-30', 100),
    (5, 'Programming Assignment 1', '2023-11-05', 100);
INSERT INTO Scores (AssignmentID, StudentID, Score, DateSubmitted, Comments) VALUES
    (1, 1, 90, '2023-10-15', 'Well done'),
    (2, 2, 95, '2023-10-20', 'Excellent'),
    (3, 3, 88, '2023-10-25', 'Great Essay'),
    (4, 4, 92, '2023-10-30', 'Impresive Research!'),
    (5, 5, 87, '2023-11-05', 'Good Start!');
INSERT INTO Materials (CourseID, MaterialName, FileLink, UploadDate, MaterialDescription) VALUES
    (1, 'Math Lecture Slides', 'math_lecture.pdf', '2023-09-05', 'Course materials for mathematic'),
    (2, 'Science Lab Manual', 'science_lab.pdf', '2023-09-10', 'Lab resources for science basic'),
    (3, 'Literature Reading List', 'literature_lost.pdf', '2023-09-01', 'Required reading for literature course'),
    (4, 'History Timeline', 'history_timeline.pdf', '2023-09-15', 'Hstorical events and dates'),
    (5, 'Programming Exercises', 'programming_exercise.pdf', '2023-09-20', 'Practice coding exercise');
INSERT INTO Messages (SenderID, ReceiverID, MessageContent, TimeStamp) VALUES
    (1, 3, 'Hi, I have a question about the math assignment.', '2023-10-15 14:30:00'),
    (2, 4, 'Could you provide more guidance on the history project?', '2023-10-16 09:45:00'),
    (3, 1, 'I enjoyed the literature lecture today', '2023-10-17 16:15:00'),
    (4, 2, 'The science lab was interesting. thanks', '2023-10-18 12:00:00'),
    (5, 1, 'I need help with the programming assignment', '2023-10-18 18:20:00');    
INSERT INTO Exams (CourseID, ExamTitle, Date, Duration, ExamMaxScore) VALUES
    (1, 'Math Misterm', '2023-11-10', 120, 200),
    (2, 'Science Exam', '2023-11-15', 180, 300),
    (3, 'Literature Quiz', '2023-11-20', 60, 100),
    (4, 'History Exam', '2023-11-25', 120, 200),
    (5, 'Code Challenge', '2023-11-30', 90, 150);
INSERT INTO ExamScores (ExamID, StudentID, ExamScore, DateTaken, Comments) VALUES
    (1, 1, 180, '2023-11-10', 'Impresive Performance on the math midterm'),
    (2, 2, 270, '2023-11-15', 'Outstanding result on the science final exam'),
    (3, 3, 55, '2023-11-20', 'You can do better on the literature quiz'),
    (4, 4, 115, '2023-11-25', 'Good effort on the history final exam'),
    (5, 5, 80, '2023-11-30', 'Well done on the programming challange');
-- SELECT Courses.CourseID, Courses.CourseName, Courses.CourseDescription, Courses.TeacherID, Courses.StartDate, Courses.EndDate
-- FROM Courses
-- JOIN Enrollments ON Courses.CourseID=Enrollments.CourseID
-- JOIN Students ON Enrollments.StudentID=Students.StudentID
-- WHERE Students.StudentID=1
-- SELECT AVG(Scores.Score) AS AverageScore
-- FROM Scores
-- JOIN Students ON Scores.StudentID=Students.StudentID
-- WHERE Students.StudentFirstName='Jane' AND Students.StudentLastName='Smith';
-- SELECT AVG(YEAR(CURRENT_DATE())-YEAR(Students.DateofBirth)) AS Age
-- FROM Students
-- SELECT Scores.Score AS HighestScore 
-- FROM Scores
-- WHERE Scores.Score ORDER BY Scores.Score DESC LIMIT 1
-- SELECT Courses.CourseID, MAX(Materials.UploadDate) AS LatestMaterialUploded
-- FROM Materials
-- JOIN Courses ON Materials.CourseID=Courses.CourseID
-- GROUP BY Courses.CourseID
-- SELECT Scores.AssignmentID, Scores.Score
-- FROM Scores
-- WHERE Scores.Score <90
-- SELECT Teachers.TeacherFirstName, Teachers.TeacherLastName, COUNT(*) AS CourseCount
-- FROM Teachers
-- JOIN Courses ON Courses.TeacherID=Teachers.TeacherID
-- GROUP BY Teachers.TeacherID 
-- ORDER BY CourseCount, Teachers.TeacherFirstName ASC LIMIT 1;
SELECT Students.GradeLevel, COUNT(*) AS TotalStudents
FROM Students
GROUP BY Students.GradeLevel