CREATE TABLE Students(
    StudentID INT PRIMARY KEY,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    DateOfBirth DATE,
    GradeLevel INT,
    Address VARCHAR(100),
    Email VARCHAR(100),
    Phone 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,
    FirstName VARCHAR(50),
    LastName VARCHAR(100),
    Email VARCHAR(100),
    Phone 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, Firstname, LastName, DateOfBirth, GradeLevel, Address, Email, Phone)
VALUES

(1, 'Daniel', 'Macagnino', '2015-07-07', 7, 'E22 8CR', 'daniel.macagnino@gmail.com','18364184357'),
(2, 'Hiliny', 'Ashworth', '2014-11-23', 8, 'E12 32Y', 'hiliny.ashworth@gmail.com','12632237437'),
(3, 'Daniel', 'Walker', '2015-05-16', 7, '382 Forest Gate St', 'daniel.walker@gmail.com','27455246276'),
(4, 'Shriyan Reddy', 'Kallem', '2015-06-06', 7, 'E15 2NS', 'shriyanreddy369@gmail.com','62324387645'),
(5, 'Thomas', 'Ashworth', '2015-03-08', 7, '5ES 8YA', 'thomas.ashworth@gmail.com','71562487526');


INSERT INTO Courses (CourseID, CourseName, Description, StartDate, EndDate)
VALUES
    
(1, 'Basic Mathematics', 'Fundamental math concepts', '2026-01-15', '2026-03-15'),
(2, 'Coding Fundamentals', 'Introduction to Programming', '2026-02-10', '2026-04-10'),
(3, 'Art and Creativity', 'Developing artistic skills', '2026-01-25', '2026-03-25'),
(4, 'Jungle Tour', 'Exploring and learning about the wild', '2025-12-31', '2026-02-28'),
(5, 'Space Miracles', 'Learning about the mysteries of space', '2026-02-06', '2026-04-21');

INSERT INTO Enrollments(EnrollmentID, StudentID, CourseID, EnrollmentDate)
VALUES
(1, 1, 1, '2025-08-22'),
(2, 2, 2, '2026-06-04'),
(3, 3, 3, '2026-01-16'),
(4, 4, 4, '2025-08-22'),
(5, 5, 5, '2026-10-11');

INSERT INTO Teachers (TeacherID, FirstName, LastName, Email, Phone)
VALUES

(1, 'Georgia', 'Greasil', 'georgia.greasil@gmail.com', '75153218743'),
(2, 'Farha', 'Shaikh', 'farha.bright@gmail.com', '698635242427'),
(3, 'Emma', 'Oldham', 'emma.oldham@gmail.com', '16654761254'),
(4, 'Jaden', 'Fernandes', 'jaden.fernandes@gmail.com', '24674175435'),
(5, 'Sabastian', 'Oswald', 'sabastian.oswald@gmail.com', '13245575844');

    
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');


-- SQL Queries
-- 1. List all students:

SELECT * FROM Students;

-- 2. List all courses:

SELECT * FROM Courses;

-- 3.  List all enrolled students and their courses:

SELECT s.FirstName, s.LastName, c.CourseName
FROM Students AS s
JOIN Enrollments AS e ON s.StudentID = e.StudentID
JOIN Courses AS c ON e.CourseID = c.CourseID;

-- 4 Find students enrolled in a specific courses 

SELECT s.FirstName, s.LastName
FROM Students AS s 
JOIN Enrollments AS e ON s.StudentID = e.StudentID
JOIN Courses AS c ON e.CourseID = c.CourseID
WHERE c.CourseName = 'Coding Fundamentals';

-- 5. List teachers and their assigned classes:
SELECT t.FirstName, t.LastName, cl.ClassID, c.CourseName
FROM Teachers AS t
JOIN Classes AS cl ON t.TeacherID = cl.TeacherID
JOIN Courses AS c ON cl.CourseID = c.CourseID;

-- 6. Find the course schedule for a specific student
SELECT c.CourseName, cl.Schedule
FROM Students AS s
JOIN Enrollments AS e ON s.StudentID = e.StudentID
JOIN Courses AS c ON e.CourseID = c.CourseID
JOIN Classes AS cl ON c.CourseID = cl.CourseID
WHERE s.StudentID = 1;

-- 7. List all courses with the number of students enrolled in each
SELECT c.CourseName, COUNT(e.StudentID) AS NumStudentsEnrolled
FROM Courses AS c
LEFT JOIN Enrollments AS e ON c.CourseID = e.CourseID
GROUP BY c.CourseName;

-- 8. Find the total fee collected from a specific student
SELECT SUM(f.Amount) AS TotalFeesCollected
FROM Fees AS f
WHERE f.StudentID = 3;

-- 9. Calculate the total number of students enrolled in each grade level:
SELECT GradeLevel, COUNT(*) AS NumStudents
FROM Students
GROUP BY GradeLevel;

-- 10. Calculate the total fees collected from students:
SELECT SUM(Amount) AS TotalFeesCollected
FROM Fees;