CREATE TABLE Instructors(
    InstructorID INT PRIMARY KEY,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    Email VARCHAR(100),
    Phone VARCHAR(20)
);
INSERT INTO Instructors VALUES
    (1, "Sahra", "Adams", "sahra.adams@email.com", "555-111-2222"),
    (2, "Robert", "Jo", "robert.jo@email.com", "555-222-3333"),
    (3, "Laura", "Garcia", "laura.garcia@email.com", "555-333-1111");
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)
);
INSERT INTO Students VALUES
    (1, "John", "Do", "2006-03-15", 7, "123 Main Street", "john.jo@email.com", "555-123-4567"),
    (2, "Jane", "Smith", "2006-05-20", 8, "456 Elm Street", "jane.smith@email.com", "555-987-6543"),
    (3, "David", "Musk", "2006-02-10", 9, "79 Oak Street", "david.musk@email.com", "555-456-7890");
CREATE TABLE Course(
    CourseID INT PRIMARY KEY,
    CourseName VARCHAR(50),
    Description TEXT,
    StartDate DATE,
    EndDate DATE,
    Fee DECIMAL(10, 2),
    InstructorID INT,
    FOREIGN KEY (InstructorID) REFERENCES Instructors (InstructorID)
);
INSERT INTO Course VALUES
    (102, "Mathematics Bootcamp", "Intensive Mathematics Crash Course", "2027-01-15", "2027-02-15", 200.00, 1),
    (103, "Science Explorer", "Exploration of Science Topics", "2027-03-01", "2027-04-01", 150.00, 2),
    (104, "Coding Fundamentals", "Introduction to Programming and Coding", "2027-02-20", "2027-03-20", 180.00, 3);
CREATE TABLE Registration(
    RegistrationID INT PRIMARY KEY,
    StudentID INT,
    CourseID INT,
    RegistrationDate DATE,
    FOREIGN KEY (StudentID) REFERENCES Students (StudentID),
    FOREIGN KEY (CourseID) REFERENCES Course (CourseID)
);
INSERT INTO Registration VALUES
    (1, 1, 102, "2026-10-10"),
    (2, 2, 103, "2026-10-15"),
    (3, 3, 104, "2026-10-18"),
    (4, 1, 103, "2026-10-10"),
    (5, 2, 104, "2026-10-10"),
    (6, 3, 102, "2026-10-10");
-- SELECT FirstName, LastName, CourseName, Description FROM Instructors JOIN Course ON Instructors.InstructorID = Course.InstructorID;
-- SELECT MIN(Fee) FROM Course;
-- SELECT CourseName, Fee FROM Course WHERE Fee = (SELECT MIN(Fee) FROM Course);
-- SELECT FirstName, LastName, CourseName, Fee FROM Students JOIN Registration ON Students.StudentID = Registration.StudentID JOIN Course ON Registration.CourseID = Course.CourseID;
SELECT FirstName, LastName, SUM(Fee) FROM Students JOIN Registration 
    ON Students.StudentID = Registration.StudentID JOIN Course 
    ON Registration.CourseID = Course.CourseID
    GROUP BY Students.StudentID;