DROP TABLE student;

CREATE TABLE student(ID VARCHAR(50) PRIMARY KEY, NAME VARCHAR(50));

INSERT INTO student VALUES('','bobo');

INSERT INTO student VALUES('','sisi');

INSERT INTO student VALUES('','gugu');

INSERT INTO student VALUES('','mimi');

DROP TABLE room;

CREATE TABLE room(room_id VARCHAR(50) PRIMARY KEY, student_id VARCHAR(50),room_type VARCHAR(50));

TRUNCATE TABLE room;

INSERT INTO room VALUES('r1','','musicRoom');

INSERT INTO room VALUES('r2','','musicRoom');

INSERT INTO room VALUES('r3','','musicRoom');

INSERT INTO room VALUES('r4','','englishRoom');

INSERT INTO room VALUES('r5','','englishRoom');

create INDEX r_student_id on room (STUDENT_ID);

create INDEX room_type on room (ROOM_TYPE);

SELECT * FROM room;

DROP TABLE teacher;

CREATE TABLE teacher(teacher_id VARCHAR(50) PRIMARY KEY, student_id VARCHAR(50),teacher_type VARCHAR(50));

TRUNCATE TABLE teacher;

INSERT INTO teacher VALUES('m1','','musicTeacher');

INSERT INTO teacher VALUES('m2','','musicTeacher');

INSERT INTO teacher VALUES('m3','','englishTeacher');

INSERT INTO teacher VALUES('m4','','englishTeacher');

INSERT INTO teacher VALUES('m5','','englishTeacher');

create index t_student_id on teacher (STUDENT_ID);

create index teacher_type on teacher (TEACHER_TYPE);

SELECT * FROM student s LEFT OUTER JOIN teacher t ON s.id = t.student_id LEFT OUTER JOIN room r ON s.id = r.student_id WHERE t.teacher_type = 'musicTeacher' AND r.room_type = 'musicRoom';

SELECT * FROM (SELECT* FROM student s LEFT OUTER JOIN teacher t ON s.id = t.student_id WHERE t.teacher_type = 'musicTeacher' ) t1 LEFT OUTER JOIN room r ON t1.id = r.student_id WHERE r.room_type = 'musicRoom';