CREATE TABLE Student( 
    StudentID INT AUTO_INCREMENT PRIMARY KEY, 
    NAME VARCHAR(100) UNIQUE, 
    GradeLevel INT, 
    Email VARCHAR(100) UNIQUE, 
    EnrollmentDate DATE, 
    IsActive BOOLEAN DEFAULT TRUE
);

CREATE TABLE ExamScore( 
    ScoreID INT AUTO_INCREMENT PRIMARY KEY, 
    StudentID INT, 
    Subject VARCHAR(50),
    Score DECIMAL(5,2), 
    ExamDate DATE, 
    Remarks VARCHAR(255), 
    FOREIGN KEY (StudentID) REFERENCES Student(StudentID), 
    UNIQUE (StudentID, Subject, ExamDate)
    );

INSERT INTO Student(Name, GradeLevel, Email, EnrollmentDate) VALUES 
('Alice Johnson', 10, 'alice.johnson@gmail.com', '2023-08-15'), 
('Brian Smith', 11, 'brian.smith@gmail.com', '2023-08-20'), 
('Carmen Lee', 11, 'carmen.lee@gmail.com', '2024-01-22'), 
('David Kim', 12, 'david.kim@gmail.com', '2023-12-22'); 

INSERT INTO ExamScore(StudentID, Subject, Score, ExamDate , Remarks) VALUES 
(1, 'Math', 88.50, '2023-09-10', 'Strong Performance'), 
(1, 'Science', 91.00, '2023-09-12', 'Excellent Understanding'), 
(2, 'Math', 76.25, '2023-09-10', 'Needs Improvement'), 
(2, 'Science', 82.75, '2023-09-12','Good Effort'), 
(3, 'Math', 94.00, '2023-09-12', 'Outstanding'), 
(3, 'Science', 89.50, '2023-09-12', 'Very Good'), 
(4, 'Math', 67.00 , '2023-09-10','Struggled with concepts'), 
(4, 'Science', 73.25, '2023-09-12', 'Average Performance'); 


SELECT * 
FROM ExamScore; 

SELECT AVG(Score) AS OverallAverage 
FROM ExamScore; 

SELECT Subject, AVG(Score) AS SubjectAverage
FROM ExamScore
GROUP BY Subject;

SELECT StudentID, AVG(Score) AS AverageScore 
FROM ExamScore 
GROUP BY StudentID; 

SELECT Student.Name, ExamScore.Subject, ExamScore.Score
FROM ExamScore
JOIN Student ON ExamScore.StudentID = Student.StudentID;


ALTER TABLE ExamScore 
ADD CONSTRAINT chk_score_range CHECK(Score BETWEEN 0 AND 94);