CREATE TABLE Students ( 
    StudentID SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    Name VARCHAR(100) NOT NULL, 
    Major VARCHAR(100) NOT NULL
    ); 

CREATE TABLE Courses ( 
    CourseID SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, 
    Title VARCHAR(100) NOT NULL UNIQUE, 
    Department VARCHAR(50) NOT NULL
    );

CREATE TABLE Enrollments( 
    EnrollmentID BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, 
    StudentID SMALLINT UNSIGNED NOT NULL, 
    CourseID SMALLINT UNSIGNED NOT NULL, 
    Semester VARCHAR(20) NOT NULL, 
    FOREIGN KEY (StudentID) REFERENCES Students(StudentID), 
    FOREIGN KEY (CourseID) REFERENCES Courses(CourseID)
    );

INSERT INTO Students (StudentID, Name, Major)VALUES 
    (101,'Alice Johnson', 'Computer Science'), 
    (102, 'Bob Smith', 'Mathematics'), 
    (103, 'Carol Lee', 'Physics'), 
    (104, 'David Kim', 'Computer Science'), 
    (105, 'Eva Brown', 'Biology'), 
    (106, 'Frank Wright', 'Computer Science'), 
    (107, 'Grace Lin', 'Mathematics'), 
    (108, 'Henry Zhao', 'Computer Science'); 

INSERT INTO Courses(CourseID, Title, Department)VALUES 
    (201, 'Database Systems', 'Computer Science'), 
    (202, 'Calculus I', 'Math'), 
    (203, 'Physics I', 'Physics'), 
    (204, 'Algorithms', 'Computer Science'), 
    (205, 'Linear Algebra', 'Math'), 
    (206, 'Biology 101', 'Biology'), 
    (207, 'Discrete Math', 'Math'); 

INSERT INTO Enrollments (EnrollmentID, StudentID, CourseID, Semester)VALUES 
    (3001, 101, 201, 'Fall 2025'), 
    (3002, 101, 202, 'Fall 2025'), 
    (3003, 102, 205, 'Fall 2025'),
    (3004, 103, 203, 'Fall 2025'),
    (3005, 104, 201, 'Fall 2025'),
    (3006, 104, 204, 'Fall 2025'),
    (3007, 105, 206, 'Fall 2025'),
    (3008, 106, 207, 'Fall 2025'),
    (3009, 106, 201, 'Fall 2025'),
    (3010, 107, 202, 'Fall 2025'),
    (3011, 108, 202, 'Fall 2025'),
    (3012, 108, 201, 'Fall 2025');


SELECT s.Name, s.Major
FROM Students s
JOIN Enrollments e ON s.StudentID = e.StudentID
JOIN Courses c ON e.CourseID = c.CourseID
WHERE c.Title = 'Database Systems'
  AND e.Semester = 'Fall 2025';