The SQL query does not work as required

I have this database of college. I have to Retrieve student-ids of students who have taken a class with an instructor named “John”.

SO far

SELECT student_id
FROM enrolls
JOIN teaches ON enrolls.number=teaches.number
WHERE Instructor.name=(
    SELECT name
    FROM Instructor
    WHERE name='john'
);

but this is not working.

create table Course (
  number int,
  title varchar(255),
  credits int,
  syllabus varchar(255),
  PRIMARY KEY (number)
  );

INSERT INTO Course VALUES (620,'Algorithm',3,'XYZ');
INSERT INTO Course VALUES (232,'Java',2,'ABC');
INSERT INTO Course VALUES (420,'Cpp',2,'PQRS');
INSERT INTO Course VALUES (720,'Big Data',3,'NVGY');
INSERT INTO Course VALUES (120,'Intelligent System',4,'KJHU');
INSERT INTO Course VALUES (220,'Operating System',3,'GED');
INSERT INTO Course VALUES (480,'Graphics',4,'RSFN');
INSERT INTO Course VALUES (520,'Distributed Networks',3,'NHU');
INSERT INTO Course VALUES (820,'Data Mining',3,'TYU');
INSERT INTO Course VALUES (700,'Cryptography',1,'MNO');

create table Student (
  student_id int,
  name varchar(255),
  department varchar(255),
  PRIMARY KEY (student_id)
  );

INSERT INTO Student VALUES (2345,'Mike','Computer Science');
INSERT INTO Student VALUES (346,'Rob','Computer Science');
INSERT INTO Student VALUES (789,'Nick','Game Design');
INSERT INTO Student VALUES (675,'Sara','Computer Science');
INSERT INTO Student VALUES (123,'Raj','Mechanical');
INSERT INTO Student VALUES (5331,'Jack','Biotech');
INSERT INTO Student VALUES (1023,'Michelle','Chemical');
INSERT INTO Student VALUES (9800,'Jie','Industrial');
INSERT INTO Student VALUES (7834,'Dan','Petroleum');
INSERT INTO Student VALUES (4567,'Patrick','Computer Science');

create table Instructor (
  instructor_id int,
  name varchar(255),
  department varchar(255),
  title varchar(255),
  PRIMARY KEY (instructor_id)
  );

INSERT INTO Instructor VALUES (12,'John','Computer Science','Java');
INSERT INTO Instructor VALUES (23,'Bischof','Mechanical','Drawing');
INSERT INTO Instructor VALUES (56,'Kwon','Biotech','Biology');
INSERT INTO Instructor VALUES (78,'Deever','Security','Cryptography');
INSERT INTO Instructor VALUES (45,'Jin','Computer Science','Cpp');
INSERT INTO Instructor VALUES (67,'Bailey','Petroleum','Metalurgy');
INSERT INTO Instructor VALUES (90,'Richard','Industrial','Manufacture');
INSERT INTO Instructor VALUES (11,'Joe','Chemical','Chemistry');
INSERT INTO Instructor VALUES (24,'Roger','Game Design','Cpp');
INSERT INTO Instructor VALUES (55,'Zack','Industria','Design');

create table CourseOffering (
  number int,
  year int,
  semester int,
  section_number int,
  classroom int,
  PRIMARY KEY (number,year,semester,section_number),
  FOREIGN KEY (number) REFERENCES Course (number)
  );

INSERT INTO CourseOffering VALUES (620,2012,2,102,3540);
INSERT INTO CourseOffering VALUES (232,2011,1,103,3455);
INSERT INTO CourseOffering VALUES (420,2013,3,101,2650);
INSERT INTO CourseOffering VALUES (720,2013,3,100,3455);
INSERT INTO CourseOffering VALUES (120,2014,1,102,3000);
INSERT INTO CourseOffering VALUES (220,2008,2,104,1450);
INSERT INTO CourseOffering VALUES (480,2009,4,106,1400);
INSERT INTO CourseOffering VALUES (520,2010,3,102,3500);
INSERT INTO CourseOffering VALUES (820,2008,3,100,2700);
INSERT INTO CourseOffering VALUES (700,2012,1,103,2540);

create table CourseOffering_Timing (
  number int,
  year int,
  semester int,
  section_number int,
  timing int,
  PRIMARY KEY (number,year,semester,section_number,timing),
  FOREIGN KEY (number) REFERENCES Course (number)
  );

INSERT INTO CourseOffering_Timing VALUES (620,2012,2,102,9);
INSERT INTO CourseOffering_Timing VALUES (232,2011,1,103,3);
INSERT INTO CourseOffering_Timing VALUES (420,2013,3,101,2);
INSERT INTO CourseOffering_Timing VALUES (720,2013,3,100,5);
INSERT INTO CourseOffering_Timing VALUES (120,2014,1,102,4);
INSERT INTO CourseOffering_Timing VALUES (220,2008,2,104,10);
INSERT INTO CourseOffering_Timing VALUES (480,2009,4,106,12);
INSERT INTO CourseOffering_Timing VALUES (520,2010,3,102,4);
INSERT INTO CourseOffering_Timing VALUES (820,2008,3,100,2);
INSERT INTO CourseOffering_Timing VALUES (700,2012,1,103,3);

create table PreRequisite (
  number int,
  prerequisite_number int,
  PRIMARY KEY (number,prerequisite_number),
  FOREIGN KEY (number) REFERENCES Course(number)
  );

INSERT INTO PreRequisite VALUES (620,480);
INSERT INTO PreRequisite VALUES (232,120);
INSERT INTO PreRequisite VALUES (420,320);
INSERT INTO PreRequisite VALUES (720,580);
INSERT INTO PreRequisite VALUES (120,110);
INSERT INTO PreRequisite VALUES (220,130);
INSERT INTO PreRequisite VALUES (480,380);
INSERT INTO PreRequisite VALUES (520,360);
INSERT INTO PreRequisite VALUES (820,700);
INSERT INTO PreRequisite VALUES (700,610);

create table enrolls (
  student_id int,
  number int,
  year int,
  semester int,
  section_number int,
  grade varchar(10),
  PRIMARY KEY (student_id,number,year,semester,section_number),
  FOREIGN KEY (number,year,semester,section_number) REFERENCES CourseOffering(number,year,semester,section_number),
  FOREIGN KEY (student_id) REFERENCES Student (student_id)
  );

INSERT INTO enrolls VALUES (2345,620,2012,2,102,'A');
INSERT INTO enrolls VALUES (346,232,2011,1,103,'B');
INSERT INTO enrolls VALUES (789,420,2013,3,101,'A');
INSERT INTO enrolls VALUES (675,720,2013,3,100,'C');
INSERT INTO enrolls VALUES (123,120,2014,1,102,'B');
INSERT INTO enrolls VALUES (5331,220,2008,2,104,'A');
INSERT INTO enrolls VALUES (1023,480,2009,4,106,'B');
INSERT INTO enrolls VALUES (9800,520,2010,3,102,'A');
INSERT INTO enrolls VALUES (7834,820,2008,3,100,'C');
INSERT INTO enrolls VALUES (4567,700,2012,1,103,'F');

create table teaches (
  instructor_id int,
  number int,
  year int,
  semester int,
  section_number int,
  PRIMARY KEY (instructor_id, number, year, semester, section_number),
  FOREIGN KEY (instructor_id) REFERENCES Instructor(instructor_id),
  FOREIGN KEY ( number, year, semester, section_number) REFERENCES CourseOffering( number, year, semester, section_number)
  );

INSERT INTO teaches VALUES (12,620,2012,2,102);
INSERT INTO teaches VALUES (23,232,2011,1,103);
INSERT INTO teaches VALUES (56,420,2013,3,101);
INSERT INTO teaches VALUES (78,720,2013,3,100);
INSERT INTO teaches VALUES (45,120,2014,1,102);
INSERT INTO teaches VALUES (67,220,2008,2,104);
INSERT INTO teaches VALUES (90,480,2009,4,106);
INSERT INTO teaches VALUES (11,520,2010,3,102);
INSERT INTO teaches VALUES (24,820,2008,3,100);
INSERT INTO teaches VALUES (55,700,2012,1,103);

---

Your WHERE clause needs to refer to one of the tables you have selected or joined, which are enrolls and teaches. So you would have to do something like WHERE teaches.instructor_id=(SELECT instructor_id FROM Instructor WHERE name='john');

PS: You might also want to throw a DISTINCT in your top level select.