CREATE TABLE Students (
    StudentID INT PRIMARY KEY,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    DateOfBirth DATE,
    GradeLevel INT,
    Email VARCHAR(100)
);

CREATE TABLE Teachers (
    TeacherID INT PRIMARY KEY,
    Name VARCHAR(50),
    Email VARCHAR(100)
);

CREATE TABLE Subjects (
    SubjectID INT PRIMARY KEY,
    SubjectName VARCHAR(50),
    TeacherID INT,
    FOREIGN KEY (TeacherID) REFERENCES Teachers(TeacherID)
);

CREATE TABLE Grades (
    GradeID INT,
    StudentID INT,
    SubjectID INT,
    Grade CHAR(2),
    Semester VARCHAR(50),
    FOREIGN KEY (StudentID) REFERENCES Students(StudentID),
    FOREIGN KEY (SubjectID) REFERENCES Subjects(SubjectID)
);

CREATE TABLE Attendance (
    AttendanceID INT PRIMARY KEY,
    StudentID INT,
    Date DATE,
    Status VARCHAR(10),
    FOREIGN KEY (StudentID) REFERENCES Students(StudentID)
);

INSERT INTO Students (StudentID, FirstName, LastName, DateOfBirth, GradeLevel, Email) VALUES
    (1, 'Alice', 'Smith', '2008-05-10', 10, 'alice.smith@example.com'),
    (2, 'Bob', 'Johnson', '2009-11-20', 9, 'bob.johnson@example.com'),
    (3, 'Charlie', 'Williams', '2007-02-15', 11, 'charlie.williams@example.com'),
    (4, 'Diana', 'Brown', '2008-08-05', 10, 'diana.brown@example.com'),
    (5, 'Ethan', 'Jones', '2009-03-12', 9, 'ethan.jones@example.com'),
    (6, 'Fiona', 'Garcia', '2008-12-25', 10, 'fiona.garcia@example.com'),
    (7, 'George', 'Martinez', '2007-07-30', 11, 'george.martinez@example.com'),
    (8, 'Hannah', 'Rodriguez', '2009-01-14', 9, 'hannah.rodriguez@example.com'),
    (9, 'Ian', 'Lee', '2008-06-20', 10, 'ian.lee@example.com'),
    (10, 'Jack', 'Hernandez', '2007-09-10', 11, 'jack.hernandez@example.com');

INSERT INTO Teachers (TeacherID, Name, Email) VALUES
    (1,  'Ms. Garcia', 'garcia@example.com'),
    (2, 'Mr. Smith', 'smith@example.com'),
    (3, 'Ms. Brown', 'brown@example.com'),
    (4, 'Mr. Johnson', 'johnson@example.com'),
    (5, 'Ms. Davis', 'davis@example.com');

INSERT INTO Subjects (SubjectID, SubjectName, TeacherID) VALUES
    (1, 'Mathematics', 1),
    (2, 'Science', 2),
    (3, 'History', 3),
    (4, 'English', 4),
    (5, 'Art', 5);

INSERT INTO Grades (GradeID,StudentID,SubjectID,Grade,Semester) VALUES
    (1,1,1,'A','Fall 2024'),
    (2,1,2,'B','Fall 2024'),
    (3,1,3,'A','Fall 2024'),
    (4,1,4,'C','Fall 2024'),
    (5,1,5,'B','Fall 2024'),
    (6,2,1,'B','Fall 2024'),
    (7,2,2,'A','Fall 2024'),
    (8,2,3,'C','Fall 2024'),
    (9,2,4,'B','Fall 2024'),
    (10,2,5,'A','Fall 2024'),
    (11,3,1,'A','Fall 2024'),
    (12,3,2,'C','Fall 2024'),
    (13,3,3,'B','Fall 2024'),
    (14,3,4,'A','Fall 2024'),
    (15,3,5,'C','Fall 2024'),
    (16,4,1,'B','Fall 2024'),
    (17,4,2,'A','Fall 2024'),
    (18,4,3,'B','Fall 2024'),
    (19,4,4,'C','Fall 2024'),
    (20,4,5,'A','Fall 2024'),
    (21,5,1,'A','Fall 2024'),
    (22,5,2,'B','Fall 2024'),
    (23,5,3,'C','Fall 2024'),
    (24,5,4,'A','Fall 2024'),
    (25,5,5,'B','Fall 2024'),
    (26,6,1,'C','Fall 2024'),
    (27,6,2,'A','Fall 2024'),
    (28,6,3,'B','Fall 2024'),
    (29,6,4,'C','Fall 2024'),
    (30,6,5,'A','Fall 2024'),
    (31,7,1,'B','Fall 2024'),
    (32,7,2,'C','Fall 2024'),
    (33,7,3,'A','Fall 2024'),
    (34,7,4,'B','Fall 2024'),
    (35,7,5,'C','Fall 2024'),
    (36,8,1,'A','Fall 2024'),
    (37,8,2,'B','Fall 2024'),
    (38,8,3,'A','Fall 2024'),
    (39,8,4,'B','Fall 2024'),
    (40,8,5,'A','Fall 2024'),
    (41,9,1,'B','Fall 2024'),
    (42,9,2,'A','Fall 2024'),
    (43,9,3,'C','Fall 2024'),
    (44,9,4,'B','Fall 2024'),
    (45,9,5,'A','Fall 2024'),
    (46,10,1,'A','Fall 2024'),
    (47,10,2,'B','Fall 2024'),
    (48,10,3,'A','Fall 2024'),
    (49,10,4,'C','Fall 2024'),
    (50,10,5,'B','Fall 2024');

INSERT INTO Attendance (AttendanceID,StudentID,Date,Status) VALUES
    (1,1,'2024-12-01','Present'),
    (2,1,'2024-12-02','Absent'),
    (3,1,"2024-12-03",'Present'),
    (4,1,"2024-12-04",'Present'),
    (5,1,'2024-12-05','Absent'),
    (6,2,'2024-12-01','Present'),
    (7,2,'2024-12-02','Present'),
    (8,2,'2024-12-03','Absent'),
    (9,2,'2024-12-04','Present'),
    (10,2,'2024-12-05','Present'),
    (11,3,'2024-12-01','Absent'),
    (12,3,'2024-12-02','Present'),
    (13,3,'2024-12-03','Present'),
    (14,3,'2024-12-04','Absent'),
    (15,3,'2024-12-05','Present'),
    (16,4,'2024-12-01','Present'),
    (17,4,'2024-12-02','Absent'),
    (18,4,'2024-12-03','Present'),
    (19,4,'2024-12-04','Present'),
    (20,4,'2024-12-05','Absent'),
    (21,5,'2024-12-01','Present'),
    (22,5,'2024-12-02','Present'),
    (23,5,'2024-12-03','Absent'),
    (24,5,'2024-12-04','Present'),
    (25,5,'2024-12-05','Present');

-- SELECT Students.StudentID, Students.FirstName, Students.LastName, Students.DateOfBirth, Students.GradeLevel, Students.Email
-- FROM Students
-- WHERE Students.GradeLevel = 10

-- SELECT Subjects.SubjectID, Subjects.SubjectName, Teachers.Name
-- FROM Subjects
-- JOIN Teachers ON Teachers.TeacherID=Subjects.TeacherID
-- WHERE Teachers.Name = 'Ms. Garcia'

-- SELECT Students.FirstName, Students.LastName, Grades.Grade
-- FROM Students
-- JOIN Grades ON Grades.StudentID=Students.StudentID
-- JOIN Subjects ON Subjects.SubjectID=Grades.SubjectID
-- WHERE Subjects.SubjectName='Mathematics'

-- SELECT Students.FirstName, Students.LastName, COUNT(Attendance.Status) AS AbsenceCount
-- FROM Students
-- JOIN Attendance ON Attendance.StudentID=Students.StudentID
-- WHERE Attendance.Status='Absent'
-- GROUP BY Students.FirstName, Students.LastName

-- SELECT Subjects.SubjectName, COUNT(Grades.SubjectID) AS StudentCount
-- FROM Subjects
-- JOIN Grades ON Grades.SubjectID=Subjects.SubjectID
-- GROUP BY Subjects.SubjectName

-- SELECT FirstName, LastName
-- FROM Students
-- JOIN Grades ON Grades.StudentID=Students.StudentID
-- JOIN Subjects ON Subjects.SubjectID=Grades.SubjectID
-- WHERE Grades.Grade='A' AND Subjects.SubjectName='Science'

-- SELECT Students.FirstName, Students.LastName, Attendance.Status
-- FROM Students
-- JOIN Attendance ON Attendance.StudentID=Students.StudentID
-- WHERE Attendance.Date='2024-12-01'

-- SELECT Subjects.SubjectName, Students.FirstName, Students.LastName, Grades.Grade
-- FROM Subjects
-- JOIN Grades ON Grades.SubjectID=Subjects.SubjectID
-- JOIN Students ON Students.StudentID=Grades.StudentID
-- WHERE Grades.Grade='A'
-- ORDER BY Subjects.SubjectName ASC

-- SELECT Teachers.Name, COUNT(Grades.Grade) AS AScore
-- FROM Teachers
-- JOIN Subjects ON Subjects.TeacherID=Teachers.TeacherID
-- JOIN Grades ON Grades.SubjectID=Subjects.SubjectID
-- WHERE Grades.Grade='A'
-- GROUP BY Teachers.Name

CREATE VIEW GradeNumber AS SELECT Grades.GradeID, IF(Grade='A',4,IF(Grade='B',3, IF(Grade='C',2, IF(Grade='D',1,0)))) AS Score 
FROM Grades; 
-- SELECT *FROM GradeNumber;
SELECT Subjects.SubjectName, AVG(GradeNumber.Score) AS AverageGrade
FROM Subjects
JOIN Grades ON Subjects.SubjectID=Grades.SubjectID
JOIN GradeNumber ON Grades.GradeID=GradeNumber.GradeID
GROUP BY Subjects.SubjectName