join up to 3 tables in MySQL

Question

I have tables are:

• student { student_id as primary key, firstName, lastName };
• teacher { teacher_id as primary key, firstName, lastName };
• course {course_id as primary key, courseName, credits, teacher_id (foreign key from teacher table) }
• class { class_id as primary key, roomNumber, teacher_id foreign_key from teacher, course_id foreign key from course }
• classStudent { student_id foreign key from student, class_id foreign key from class }

I want to retrieve courseName, teacher's first and last name, roomNumber for a specific student id.

My database:

CREATE TABLE `student` 
(
    `student_id` int(10) NOT NULL,
    `firstName` varchar(20) NOT NULL,
    `lastName` varchar(20) NOT NULL,
    `phone_number` int(8) NOT NULL,
    `Email` varchar(40) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `teacher` 
(
    `teacher_id` int(10) NOT NULL,
    `firstName` varchar(20) NOT NULL,
    `lastName` varchar(20) NOT NULL,
    `phone_number` int(8) NOT NULL,
    `Email` varchar(40) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `course` 
(
    `course_id` varchar(10) NOT NULL,
    `Course_Name` varchar(20) NOT NULL,
    `Description` varchar(100) NOT NULL,
    `credits` int(1) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

ALTER TABLE `class`
    ADD PRIMARY KEY (`class_id`),
    ADD KEY `classTeacher` (`teacher_id`),
    ADD KEY `classCourse` (`course_id`);

ALTER TABLE `classstudent`
    ADD PRIMARY KEY (`class_id`,`student_id`),
    ADD KEY `studentClass` (`student_id`);

-- Constraints for table `class`
--
ALTER TABLE `class`
    ADD CONSTRAINT `classCourse` 
        FOREIGN KEY (`course_id`) REFERENCES `course` (`course_id`) 
            ON UPDATE CASCADE,
    ADD CONSTRAINT `classTeacher` 
        FOREIGN KEY (`teacher_id`) REFERENCES `teacher` (`teacher_id`);

--
-- Constraints for table `classstudent`
--
ALTER TABLE `classstudent`
    ADD CONSTRAINT `classstudent_ibfk_1` 
        FOREIGN KEY (`class_id`) REFERENCES `class` (`class_id`),
    ADD CONSTRAINT `studentClass` 
        FOREIGN KEY (`student_id`) REFERENCES `student` (`student_id`);

ALTER TABLE `classstudent`
    ADD PRIMARY KEY (`class_id`,`student_id`),
    ADD KEY `studentClass` (`student_id`);

I am also new to stack-overflow so please tell me if there is any note about my question.

Answer 1

Select  course.courseName, teacher.firstName, teacher.lastName, class.roomNumber
from student
 inner join classStudent 
on student.student_id=classStudent.student_id
 inner join class
on classStudent.class_id = class.class_id
Inner join course
On class.course_id = course.course_id
Inner join teacher
On course.teacher_id = teacher.teacher_id
Where student.student_id = ?