-- Create Students Table
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 Instructors Table
CREATE TABLE Instructors (
    InstructorID INT PRIMARY KEY,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    Email VARCHAR(100),
    Phone VARCHAR(20)
);

-- Create Courses Table
CREATE TABLE Courses (
    CourseID INT PRIMARY KEY,
    CourseName VARCHAR(100),
    Description TEXT,
    StartDate DATE,
    EndDate DATE,
    Fee DECIMAL(10, 2),
    InstructorID INT,
    FOREIGN KEY (InstructorID) REFERENCES Instructors(InstructorID)
);

-- Create Registration Table
CREATE TABLE Registrations (
    RegistrationID INT PRIMARY KEY,
    StudentID INT,
    CourseID INT,
    RegistrationDate DATE,
    FOREIGN KEY (StudentID) REFERENCES Students(StudentID),
    FOREIGN KEY (StudentID) REFERENCES Courses(CourseID)
);

-- Insert data for Students table
INSERT INTO Students (StudentID, FirstName, LastName, DateOfBirth, GradeLevel, Address, Email, Phone)
VALUES
    
(1, 'John', 'Doe', '2006-03-15', 7, '123 Main St', 'john.doe@gmail.com', '555-123-4567'),
(2, 'Jane', 'Smith', '2006-05-20', 8, '456 Elm St', 'jane.smith@gmail.com', '555-987-6543'),
(3, 'David', 'Musk', '2006-02-10', 9, '789 Oak St', 'david.musk@gmail.com', '555-456-7890'),
(4, 'Emily', 'Jo', '2006-07-03', 7, '101 Pine St', 'emily.jo@gmail.com', '555-321-6540'),
(5, 'Shriyan', 'Kallem', '2006-09-18', 8, '202 Maple St', 'michael.brown@gmail.com', '555-654-9871');

SELECT * FROM Students;

-- Insert data for Instructors table
INSERT INTO Instructors(InstructorID, FirstName, LastName, Email, Phone)
VALUES
    
(1, 'Farha', 'Shaikh', 'farha.bright@gmail.com', '9892982803'),
(2, 'Prasad', 'Murali', 'prasad.murali@gmail.com', '57365863648'),
(3, 'Shivangi', 'Shaikh', 'shivangi.tg@gmail.com', '09438508437'),
(4, 'Georgia', 'Greasil', 'georgia.greasil@gmail.com', '68363402847'),
(5, 'Gemma', 'Oldham', 'gemma.oldham@gmail.com', '5490735936');

SELECT * FROM Instructors;

-- Insert data for Courses table
INSERT INTO Courses(CourseID, CourseName, Description, StartDate, EndDate, Fee, InstructorID)
VALUES

(1, 'Mathematics Boot Camp', 'Intensive Mathematics crash course', '2026-11-15', '2026-11-29', 50.00, 5),
(2, 'Science Explorer', 'Exploration of scientific topics', '2026-11-01', '2026-11-15', 56.00, 2),
(3, 'Coding Fundamentals', 'Introduction to programming and coding', '2026-11-20', '2023-12-4', 65.00, 3),
(4, 'Creative Writing', 'Developing creative writing skills', '2026-11-10', '2026-11-24', 62.00, 4),
(5, 'Trecking Up Mountains', 'Being safe and developing stamina and strength in going in the wild.', '2026-11-19', '2026-12-3', 100.00, 1);

SELECT * FROM Courses;
    
-- Insert data for Registrations table
INSERT INTO Registrations(RegistrationID, StudentID, CourseID, RegistrationDate)
VALUES
    
(1, 1, 1, '2023-01-10'),
(2, 2, 2, '2023-02-05'),
(3, 3, 3, '2023-02-18'),
(4, 4, 4, '2023-03-05'),
(5, 5, 5, '2023-03-20');

SELECT * FROM Registrations;


-- List all courses with their respective instructors:
SELECT c.CourseName, i.FirstName AS InstructorFirstName, i.LastName AS InstructorLastName
FROM Courses AS c JOIN Instructors AS i ON c.InstructorID = i.InstructorID;

-- List all students registrations:
SELECT r.RegistrationId, s.FirstName AS StudentFirstName, s.LastName AS StudentLastName, c.CourseName
FROM Registrations AS r
JOIN Students AS s ON r.StudentID = s.StudentID
JOIN Courses AS c ON r.CourseID = c.CourseID;

-- Find the total number of students registered for each course:
SELECT c.CourseName, COUNT(r.StudentID) AS NumStudentsRegistered
FROM Courses AS c 
LEFT JOIN Registrations AS r ON c.CourseID = r.CourseID
GROUP BY c.CourseName;

-- List students who have registered for a specific course (e.g., "Coding Fundamentals"):
SELECT s.FirstName, s.LastName
From Students AS s 
JOIN Registrations AS r ON  s.StudentID = r.StudentID
JOIN Courses AS c ON r.CourseID = c.CourseID
WHERE c.CourseName = 'Coding Fundumentals';

-- Find courses taught by a specific instructor
SELECT c.CourseName
FROM Courses AS c 
WHERE c.InstructorID = 1;

-- Calculate the total fees collected for each course:
SELECT c.CourseName, COUNT(r.StudentID) AS NumStudentRegistered, SUM(c.Fee) AS TotalFeesCollected From Courses AS c 
LEFT JOIN Registrations AS r ON c.CourseID = r.CourseID GROUP BY c.CourseName;

-- Find the course with the highest number of students registrations
SELECT c.CourseName, COUNT(r.StudentID) AS NUmStudentRegistered FROM Courses AS c 
LEFT JOIN Registrations AS r ON c.CourseID = r.CourseID
GROUP BY c.CourseName 
Order BY NumStudentRegistered DESC LIMIT 1;