FC0-U71 INNER JOIN Practice Question
A database has a 'Students' table with columns: StudentID (primary key), Name, Major. Another table 'Enrollments' has columns: EnrollmentID (primary key), StudentID (foreign key), CourseID. Which SQL query correctly lists each student's name and their enrolled courses by joining the tables?
⚠ Common exam trap
Some might confuse RIGHT JOIN (returns all rows from Enrollments, which could include enrollments without a student) or LEFT JOIN (returns all students, even those without enrollments, but the question asks for students with enrolled courses). The implicit comma join (option C) is equivalent to INNER JOIN but less explicit and can lead to errors if WHERE clause is omitted.
Answer choices
Why each option matters
Answer the question above first, then reveal the full breakdown to understand why each option is right or wrong.
Correct answer & explanation
✓
SELECT Name, CourseID FROM Students INNER JOIN Enrollments ON Students.StudentID = Enrollments.StudentID;
INNER JOIN returns only rows where the join condition (Students.StudentID = Enrollments.StudentID) is satisfied, which lists each student's name only if they have at least one enrollment. Option A (RIGHT JOIN) would include all enrollments, even if a StudentID does not match a student, resulting in NULL names for orphaned enrollments. Option C uses an implicit cross join (comma) with a WHERE clause; though it produces the same result set, it is not considered a proper JOIN statement and is less clear, so it does not meet the requirement of 'joining tables'. Option D (LEFT JOIN) would include students with no enrollments, showing NULL for CourseID, which does not match the requirement of listing only enrolled courses.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✗
SELECT Name, CourseID FROM Students RIGHT JOIN Enrollments ON Students.StudentID = Enrollments.StudentID;
Why it's wrong here
RIGHT JOIN returns all rows from the right table (Enrollments) and matching rows from Students. Students without enrollments are excluded, but enrollments without a matching student (if any) would appear with NULL for Name. This does not match the requirement to list each student's enrolled courses.
- ✓
SELECT Name, CourseID FROM Students INNER JOIN Enrollments ON Students.StudentID = Enrollments.StudentID;
Why this is correct
INNER JOIN correctly returns only students who have enrollments, showing their name and course ID. This matches the requirement.
- ✗
SELECT Name, CourseID FROM Students, Enrollments WHERE Students.StudentID = Enrollments.StudentID;
Why it's wrong here
The implicit comma join without a WHERE clause produces a Cartesian product (each row of Students paired with each row of Enrollments). Although the WHERE clause here is present, the syntax is outdated and less clear; it functions as an INNER JOIN, but the query is correct. However, it is not the best practice; optionally, it is acceptable but not the only correct syntax.
- ✗
SELECT Name, CourseID FROM Students LEFT JOIN Enrollments ON Students.StudentID = Enrollments.StudentID;
Why it's wrong here
LEFT JOIN returns all students, even those without enrollments, who would show NULL for CourseID. This does not meet the requirement to list only students with enrolled courses.
Go deeper
Related to this question
About these practice questions
This FC0-U71 question is part of Courseiva's 988-question bank — original exam-style content with full explanations and wrong-answer analysis, never real exam questions or exam dumps. Learn why practice questions differ from exam dumps →
JA
Written by Johnson Ajibi, MSc IT Security
Senior Network & Security Engineer · founder of Courseiva
This FC0-U71 practice question is part of Courseiva's free CompTIA certification practice question bank. Courseiva provides original exam-style practice questions with explanations, topic-based practice, mock exams, readiness tracking, and study analytics to help learners prepare for the FC0-U71 exam.