Question

Write an SQL query using the university schema to find the ID of each student who has never taken a course at the university. Do this using no subqueries and no set operations (use an outer join)

SQL question, you can right-click the picture and select open in a new tab for a better-looking experience.

Answer 1

Below is the SQL query to list all student id who have not taken any course in university.

Left join is sued here between table student and takes. This will result all student id from the student table and corresponding course_id of students. Students who have not taken any course will be shown as null course_id. Then the filter is used in WHERE clause which will result in only those student id with course_id as null.

Query-

SELECT s.ID, t.course_id
FROM student AS s
LEFT JOIN takes AS t
ON t.ID = s.ID
WHERE course_id IS NULL;

Answer 2

SELECT Student.ID

FROM Student OUTER JOIN Course ON Student.ID != Course.ID;