Given tables: Students(id, name) Enrollments(student_id, course_id) Courses(course_id, course_name) Write a query to find all students and the courses they are enrolled in, including students with no enrollments.
ASELECT Students.name, Courses.course_name FROM Students LEFT JOIN Enrollments ON Students.id = Enrollments.student_id LEFT JOIN Courses ON Enrollments.course_id = Courses.course_id;
BSELECT Students.name, Courses.course_name FROM Students INNER JOIN Enrollments ON Students.id = Enrollments.student_id INNER JOIN Courses ON Enrollments.course_id = Courses.course_id;
CSELECT Students.name, Courses.course_name FROM Courses RIGHT JOIN Enrollments ON Courses.course_id = Enrollments.course_id LEFT JOIN Students ON Enrollments.student_id = Students.id;
DSELECT Students.name, Courses.course_name FROM Students JOIN Enrollments JOIN Courses WHERE Students.id = Enrollments.student_id AND Enrollments.course_id = Courses.course_id;
Step-by-Step Solution
Solution:
Step 1: Identify the need to include all students
We want all students listed, even those without enrollments, so we use LEFT JOIN starting from Students.
Step 2: Use two LEFT JOINs to connect tables
First LEFT JOIN Enrollments to Students, then LEFT JOIN Courses to Enrollments to get course names or NULL if none.
Final Answer:
SELECT Students.name, Courses.course_name FROM Students LEFT JOIN Enrollments ON Students.id = Enrollments.student_id LEFT JOIN Courses ON Enrollments.course_id = Courses.course_id; -> Option A
Quick Check:
Two LEFT JOINs keep all students and their courses [OK]
Quick Trick:Chain LEFT JOINs to include all from first table [OK]
Common Mistakes:
MISTAKES
Using INNER JOIN excludes students without courses
Omitting ON clauses for JOINs
Master "INNER JOIN" in SQL
9 interactive learning modes - each teaches the same concept differently
