SQL query not functioning as required

manpreet Best Answer 3 years ago

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 (
                                                

                                                                                                        
                                                    
                                                
                                                

                                                    

                                                        
                                                            0
                                                            views 

                                                            0
                                                                likes 
                                                            
                                                            

                                                            0
                                                            shares
                                                            


    

         Facebook
        
         Twitter
        
        
         Linked In 
        
          WhatsApp