Question

g (15 points) 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:

SQL Query

//////////////////////////////////////////////////////////////////////////////////////////////////

select student_name from Student

INNER JOIN Class ON Student.id = Class.student_id

LEFT JOIN Instructor ON Class.instructor_id = Instructor.id

WHERE Instructor.school <> 'Computer Science'

Explanation:

First thing is to select the student_name from Student table.

Next we need to connect with the Class table to get all Students currently taking classes.

Next we need to connect the Class table with the Instructor table, to get all of the Instructors currently teaching class(s).

Finally, we place the Where condition for the acquired instructors to not be from the Computer Science department.