SQL Subqueries and Joins Quiz

Which of the following queries best finds students who are taking the same course as 'Alice' using a subquery?

• SELECT StudentName FROM Students WHERE CourseID IN (SELECT CourseID FROM Students WHERE StudentName = 'Alice');

• SELECT StudentName FROM Students s JOIN Courses c ON s.CourseID = c.CourseID WHERE c.CourseName = 'Alice';

• SELECT StudentName FROM Students WHERE StudentName = 'Alice';

• SELECT StudentName FROM Students WHERE CourseID = ALL (SELECT CourseID FROM Courses WHERE CourseName = 'Alice');

You are tasked to find instructors not assigned to any course. Which type of SQL approach is most appropriate?

• Inner Join

• LEFT JOIN with WHERE CourseID IS NOT NULL

• Subquery using NOT IN

• Correlated Subquery inside JOIN

Why might a subquery be preferred over a join in Task 1 (finding students enrolled in 'Database Systems')?

• It simplifies the query by hiding the relationship in a nested form

• It uses fewer keywords, making the SQL script shorter to write and read

• Because it clearly separates the filtering condition from the main query

• Because JOINs do not work with multiple tables

Which query correctly lists students taking courses with no assigned instructor using a JOIN?

• SELECT StudentName FROM Students JOIN Courses ON Students.CourseID = Courses.CourseID WHERE Courses.InstructorID IS NULL;

• SELECT StudentName FROM Students JOIN Instructors ON Students.CourseID = Instructors.InstructorID;

• SELECT StudentName FROM Students WHERE InstructorID IS NULL;

• SELECT StudentName FROM Students JOIN Courses ON Courses.InstructorID IS NOT NULL;

Which JOIN ensures that all students are listed even if their course has no instructor assigned?

• INNER JOIN between Students and Instructors

• LEFT JOIN between Students → Courses → Instructors

• RIGHT JOIN between Instructors and Courses

• CROSS JOIN between Students and Courses

Which of the following best explains when to prefer an INNER JOIN over a subquery?

• When filtering using a single-column comparison

• When you need to filter rows that have no related values

• When combining data from multiple tables for result display

• When avoiding Cartesian products

Which query correctly returns instructors who are not assigned to any course using a LEFT OUTER JOIN?

• SELECT i.InstructorName FROM Instructors i LEFT JOIN Courses c ON i.InstructorID = c.InstructorID WHERE c.CourseID IS NULL;

• SELECT i.InstructorName FROM Instructors i LEFT JOIN Courses c ON i.InstructorID = c.InstructorID WHERE i.InstructorID IS NULL;

• SELECT i.InstructorName FROM Instructors i LEFT JOIN Courses c ON c.InstructorID = i.InstructorID WHERE c.InstructorID IS NOT NULL;

• SELECT i.InstructorName FROM Instructors i JOIN Courses c ON i.InstructorID = c.InstructorID WHERE c.CourseID IS NULL;

Which approach is more flexible for adding complex filtering conditions on aggregated data across related tables?

• Subqueries in SELECT clause

• Joins with GROUP BY

• Joins with LIKE CLAUSE

• WHERE clause only

Which query correctly displays each student's name along with their course name and instructor name, even if no instructor is assigned?

• SELECT s.StudentName, c.CourseName, i.InstructorName FROM Students s JOIN Courses c ON s.CourseID = c.CourseID JOIN Instructors i ON c.InstructorID = i.InstructorID;

• SELECT s.StudentName, c.CourseName, i.InstructorName FROM Students s JOIN Courses c ON s.CourseID = c.CourseID LEFT JOIN Instructors i ON c.InstructorID = i.InstructorID;

• SELECT s.StudentName, c.CourseName, i.InstructorName FROM Students s LEFT JOIN Courses c ON s.CourseID = c.CourseID LEFT JOIN Instructors i ON c.InstructorID = i.InstructorID;

• SELECT s.StudentName, c.CourseName, i.InstructorName FROM Students s LEFT JOIN Instructors i ON s.CourseID = i.InstructorID JOIN Courses c ON s.CourseID = c.CourseID;

What is the difference between single-row and multiple-row subqueries?

• One returns only one column, the other returns many

• One uses joins, the other doesn't

• One returns one value, the other returns more than one

• One must be inside SELECT, the other in WHERE