SQL Subqueries and Joins Quiz

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');

Inner Join

LEFT JOIN with WHERE CourseID IS NOT NULL

Subquery using NOT IN

Correlated Subquery inside JOIN

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

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;

INNER JOIN between Students and Instructors

LEFT JOIN between Students → Courses → Instructors

RIGHT JOIN between Instructors and Courses

CROSS JOIN between Students and Courses

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

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;

Subqueries in SELECT clause

Joins with GROUP BY

Joins with LIKE CLAUSE

WHERE clause only

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;

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