Question

g Write an SQL statement to list all of the names of students who have not taken a classwith an instructorwho is a faculty of theComputer Sciencedepartment. Constraints:-Useat least one outer join,-Use natural joins and using conditions, exclusively, as the join conditions,-Do not use any other kinds of joins (i.e no cross products etc.)

Answer 1

Answer:

SELECT StudentFullname FROM Studentdata INNER JOIN Student_Class ON Studentdata.uniqueno = Student_Class.student_id LEFT JOIN Student_Instructor ON Student_Class.instructor_id = Student_Instructor.id WHERE Student_Instructor.faculty <> 'Computer Science'

Explanation:

Assume the following table names;

Studentdata - for student table

Student_Class - for each class

Student_Instructor - for student's instructors

The code first list out StudentFullname from studentdata table.

The code is the connected to Student_Class to get a list of students in class

The code is then connected to Student_Instructor table to get a list of instructors in class

Lastly, the where then filters instructors that are not from computer science department