MySQL doesn't seem to work correct

manpreet Best Answer 3 years ago

I have this college database where i have to Retrieve names of instructors teaching Computer Science courses, the sections (course number, section number, year, semester) they are teaching, and the total number of students in the sections

So far this is what have managed but the output is not correct.

SELECT  i.instructor_id,o.year,o.semester,o.section_number, o.number as CourseNumber,o.Total_num_student
FROM Instructor i JOIN
(SELECT t.instructor_id,t.year,t.semester,t.section_number,o1.number,o1.Total_num_student
 FROM teaches t JOIN
 (SELECT COUNT(s.student_id) as Total_num_student,e.section_number,e.number 
  FROM Student s JOIN
  enrolls e
  ON s.student_id=e.student_id
  GROUP BY section_number,e.number) as o1
 ON o1.section_number=t.section_number) as o
on i.instructor_id=o.instructor_id
WHERE department='Computer Science';

Can you see what is wrong with the output.

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 (621,'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
                                                

                                                                                                        
                                                    
                                                
                                                

                                                    

                                                        
                                                            0
                                                            views 

                                                            0
                                                                likes 
                                                            
                                                            

                                                            0
                                                            shares
                                                            


    

         Facebook
        
         Twitter
        
        
         Linked In 
        
          WhatsApp