CREATE TABLE Students (
    StudentID INT PRIMARY KEY,
    StudentFirstName VARCHAR(50),
    StudentLastName VARCHAR(50),
    DateOfBirth DATE,
    GradeLevel INT,
    Address VARCHAR(100),
    StudentEmail VARCHAR(100),
    StudentPhone VARCHAR(20)
);
CREATE TABLE Courses (
    CourseID INT PRIMARY KEY,
    CourseName VARCHAR(100),
    Description TEXT,
    StartDate DATE,
    EndDate DATE
);
CREATE TABLE Enrollments(
    EnrollmentID INT PRIMARY KEY,
    StudentID INT,
    CourseID INT,
    EnrollmentDate DATE,
    FOREIGN KEY (StudentID) REFERENCES Students(StudentID),
    FOREIGN KEY (CourseID) REFERENCES Courses(CourseID)
);
CREATE TABLE Teachers(
    TeacherID INT PRIMARY KEY,
    TeacherFirstName VARCHAR(50),
    TeacherLastName VARCHAR(50),
    TeacherEmail VARCHAR(100),
    TeacherPhone VARCHAR(20)
);
CREATE TABLE Classes (
    ClassID INT PRIMARY KEY,
    TeacherID INT,
    CourseID INT,
    Schedule VARCHAR(100),
    FOREIGN KEY (TeacherID) REFERENCES Teachers(TeacherID),
    FOREIGN KEY (CourseID) REFERENCES Courses(CourseID)
);
CREATE TABLE Fees(
    FeeID INT PRIMARY KEY,
    StudentID INT,
    CourseID INT,
    Amount DECIMAL(10, 2),
    PaymentDate DATE,
    FOREIGN KEY (StudentID) REFERENCES Students(StudentID),
    FOREIGN KEY (CourseID) REFERENCES Courses(CourseID)
);
INSERT INTO Students (StudentID, StudentFirstName, StudentLastName, DateOfBirth, GradeLevel, Address, StudentEmail, StudentPhone) VALUES
    (1, 'John', 'Doe', '2006-03-15', 7, '123 Main St', 'jhon.doe@gmail.com', '555-123-4567'),
    (2, 'Jane', 'Smith', '2006-05-20', 7, '456 Elm St', 'jane.smith@gmail.com', '555-987-6543'),
    (3, 'David', 'Musk', '2006-02-10', 8, '789 Oak St', 'david.musk@gmail.com', '555-456-7890'),
    (4, 'Emily', 'Jo', '2006-07-03', 8, '101 pine St', 'emily.jo@gmail.com', '555-321-6540'),
    (5, 'Michael', 'Brown', '2006-09-18', 7, '202 Maple St', 'michael.brown@gmail.com', '555-654-9871'),
    (6, 'Olivia', 'Davis', '2008-01-25', 7, '303 Birch St', 'oliviadavis@gmail.com', '555-789-1234');
INSERT INTO Courses (CourseID, CourseName, Description, StartDate, EndDate) VALUES
    (1, 'Basic Mathematics', 'Fundamental math concepts', '2023-01-15', '2023-02-15'),
    (2, 'Coding Fundamentals', 'Introduction to Programming', '2023-02-10', '2023-04-10'),
    (3, 'Art and Creativity', 'Developing artistic skill', '2023-01-25', '2023-03-25');
INSERT INTO Enrollments (EnrollmentID, StudentID, CourseID, EnrollmentDate) VALUES
    (1, 1, 1, '2023-01-10'),
    (2, 2, 2, '2023-02-05'),
    (3, 3, 3, '2023-02-18'),
    (4, 4, 2, '2023-03-05'),
    (5, 5, 2, '2023-03-20'),
    (6, 6, 3, '2023-01-05');
INSERT INTO Teachers (TeacherID, TeacherFirstName, TeacherLastName, TeacherEmail, TeacherPhone) VALUES
    (1, 'Sarah', 'Adams', 'sarah.adams@gmail.com', '555-111-2222'),
    (2, 'Robert', 'Jo', 'robert.jo@gmail.com', '555-222-3333'),
    (3, 'Laura', 'Garcia', 'laura.garcia@gmail.com', '555-333-4444'),
    (4, 'Thomas', 'Clark', 'thomas.clark@gmail.com', '555-444-5555'),
    (5, 'Jenni', 'Moore', 'jenni.moore@gmail.com', '555-555-6666'),
    (6, 'Jack', 'Taylor', 'jacktaylor@gmail.com', '555-666-7777');
INSERT INTO Classes (ClassID, TeacherID, CourseID, Schedule) VALUES
    (1, 1, 1, 'Mon/Wed 9:00 AM - 11:30 AM'),
    (2, 2, 2, 'Tue/Thu 2:00 PM - 3:30 PM'),
    (3, 3, 3, 'Mon/Wed 3:00 PM - 4:30 PM');
INSERT INTO Fees (FeeID, StudentID, CourseID, Amount, PaymentDate) VALUES
    (1, 1, 1, 150.00, '2023-01-12'),
    (2, 2, 2, 180.00, '2023-02-08'),
    (3, 3, 3, 160.00, '2023-02-20'),
    (4, 4, 2, 175.00, '2023-03-10'),
    (5, 5, 3, 140.00, '2023-03-22'),
    (6, 6, 2, 200.00, '2023-01-18');
-- SELECT StudentID, StudentFirstName, StudentLastName, DateOfBirth, GradeLevel, Address, StudentEmail, StudentPhone
-- FROM Students
-- SELECT CourseID, CourseName, Description, StartDate, EndDate
-- FROM Courses
-- SELECT Students.StudentFirstName, Students.StudentLastName, Courses.CourseName
-- FROM Students
-- JOIN Enrollments ON Enrollments.StudentID=Students.StudentID
-- JOIN Courses ON Enrollments.CourseID=Courses.CourseID
-- SELECT StudentFirstName, StudentLastName
-- FROM Students
-- JOIN Enrollments ON Enrollments.StudentID=Students.StudentID
-- JOIN Courses ON Enrollments.CourseID=Courses.CourseID
-- WHERE Courses.CourseName='Coding Fundamentals'
-- SELECT Teachers.TeacherFirstName, Teachers.TeacherLastName, Classes.ClassID, Courses.CourseName
-- FROM Teachers
-- JOIN Classes ON Teachers.TeacherID=Classes.TeacherID
-- JOIN Courses ON Courses.CourseID=Classes.CourseID
-- SELECT Courses.CourseName, Classes.Schedule
-- FROM Courses
-- JOIN Enrollments ON Courses.CourseID=Enrollments.CourseID
-- JOIN Classes ON Classes.CourseID=Courses.CourseID
-- JOIN Students ON Students.StudentID=Enrollments.StudentID
-- WHERE Students.StudentFirstName='John' AND Students.StudentLastName='Doe'
-- SELECT Courses.CourseName, COUNT(Enrollments.StudentID) AS NumStudentEnrolled
-- FROM Courses
-- JOIN Enrollments ON Courses.CourseID=Enrollments.CourseID
-- GROUP BY Courses.CourseName
SELECT SUM(Fees.Amount) AS TotalFeesCollected
FROM Students
JOIN Fees ON Students.StudentID=Fees.StudentID
WHERE Students.StudentFirstName='David' AND Students.StudentLastName='Musk'