CREATE TABLE Students (
    StudentID INT PRIMARY KEY,
    StudentFirstName VARCHAR(50),
    StudentLastName VARCHAR(50),
    DateOfBirth DATE,
    GradeLevel INT,
    Address VARCHAR(100),
    Email VARCHAR(100),
    Phone VARCHAR(20)
);
CREATE TABLE Instructors (
    InstructorID INT PRIMARY KEY,
    InstructorFirstName VARCHAR(50),
    InstructorLastName VARCHAR(50),
    Email VARCHAR(100),
    Phone VARCHAR(20)
);
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 TABLE Registrations (
    RegistrationID INT PRIMARY KEY,
    StudentID INT,
    CourseID INT,
    RegistrationDate DATE,
    FOREIGN KEY (StudentID) REFERENCES Students(StudentID),
    FOREIGN KEY (CourseID) REFERENCES Courses(CourseID)
);
INSERT INTO Students (StudentID, StudentFirstName, StudentLastName, DateOfBirth, GradeLevel, Address, Email, Phone) VALUES
    (1, 'John', 'Doe', '2006-03-15', 7, '123 Main St', 'jhon.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, 'Michael', 'Brown', '2006-09-18', 8, '202 Maple St', 'michael.brown@gmail.com', '555-654-9871');
INSERT INTO Instructors (InstructorID, InstructorFirstName, InstructorLastName, Email, Phone) 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');
INSERT INTO Courses (CourseID, CourseName, Description, StartDate, EndDate, Fee, InstructorID) VALUES
    (1, 'Mathematics Boot Camp', 'Intensive Mathematics Crash Course', '2023-01-15', '2023-02-15', 200.00, 1),
    (2, 'Science Explorer', 'Exploration of scientific topics', '2023-03-01', '2023-04-01', 150.00, 2),
    (3, 'Coding Fundamental', 'Introduction to programming and coding', '2023-02-20', '2023-03-20', 180.00, 3),
    (4, 'Creative Writing', 'Developing creative writing skills', '2023-04-10', '2023-5-10', 120.00, 4);
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, 3, '2023-03-20');
-- SELECT InstructorID, InstructorFirstName, InstructorLastName, Email, Phone
-- FROM Instructors
-- SELECT Courses.CourseName, Instructors.InstructorFirstName, Instructors.InstructorLastName
-- FROM Courses
-- JOIN Instructors ON Instructors.InstructorID=Courses.InstructorID;
-- SELECT RegistrationID, StudentFirstName, StudentLastName, CourseName
-- FROM Registrations
-- JOIN Students ON Registrations.StudentID=Students.StudentID
-- JOIN Courses ON Registrations.CourseID=Courses.CourseID;
-- SELECT Courses.CourseName, COUNT(Registrations.StudentID) AS Num_Registered
-- FROM Courses 
-- JOIN Registrations ON Registrations.CourseID=Courses.CourseID
-- GROUP BY Courses.CourseName;
-- SELECT Students.StudentFirstName, Students.StudentLastName 
-- FROM Students
-- JOIN Registrations ON Registrations.StudentID=Students.StudentID
-- JOIN Courses ON Registrations.CourseID=Courses.CourseID
-- WHERE Courses.CourseName='Coding Fundamental'
-- SELECT Courses.CourseName
-- FROM Courses 
-- JOIN Instructors ON Instructors.InstructorID=Courses.InstructorID
    -- WHERE Instructors.InstructorFirstName='Sarah' AND Instructors.InstructorLastName='Adams'
-- SELECT Courses.CourseName, COUNT(Registrations.StudentID) AS Num_Registered, SUM(Courses.Fee) AS TotalFeesCollected
-- FROM Courses 
    -- JOIN Registrations ON Registrations.CourseID=Courses.CourseID
-- GROUP BY Courses.CourseID, Courses.CourseName; 
SELECT Courses.CourseName, COUNT(Registrations.StudentID) AS NumStudentRegistered
FROM Courses
JOIN Registrations ON Registrations.CourseID=Courses.CourseID
GROUP BY Courses.CourseID, Courses.CourseName
    ORDER BY NumStudentRegistered DESC LIMIT 1;