Solution:Step 1: Use LEFT JOIN to include all coursesLEFT JOIN ensures courses without enrollments appear.Step 2: Use COUNT on enrollment student_idCOUNT counts non-NULL student_id, so zero if none enrolled.Final Answer:SELECT c.title, COUNT(e.student_id) AS student_count FROM Courses c LEFT JOIN En

[Solved] Given tables:Students(id, name)Enrollments(student_id, course_id)Courses(id, title)Write a query to list all courses and the count of students enrolled, showing 0 if none enrolled. | SQL

SQL - LEFT and RIGHT JOIN

Given tables:
Students(id, name)
Enrollments(student_id, course_id)
Courses(id, title)
Write a query to list all courses and the count of students enrolled, showing 0 if none enrolled.

ASELECT c.title, COUNT(e.student_id) AS student_count FROM Courses c LEFT JOIN Enrollments e ON c.id = e.course_id GROUP BY c.title;

BSELECT c.title, COUNT(e.student_id) AS student_count FROM Courses c JOIN Enrollments e ON c.id = e.course_id GROUP BY c.title;

CSELECT c.title, COUNT(*) AS student_count FROM Courses c LEFT JOIN Enrollments e ON c.id = e.course_id WHERE e.student_id IS NOT NULL GROUP BY c.title;

DSELECT c.title, COUNT(e.student_id) AS student_count FROM Courses c LEFT JOIN Enrollments e ON c.id = e.course_id HAVING student_count > 0;

Step-by-Step Solution

Solution:

Step 1: Use LEFT JOIN to include all courses
LEFT JOIN ensures courses without enrollments appear.
Step 2: Use COUNT on enrollment student_id
COUNT counts non-NULL student_id, so zero if none enrolled.
Final Answer:
SELECT c.title, COUNT(e.student_id) AS student_count FROM Courses c LEFT JOIN Enrollments e ON c.id = e.course_id GROUP BY c.title; -> Option A
Quick Check:
LEFT JOIN + COUNT counts enrollments, zero if none [OK]

Quick Trick: LEFT JOIN + COUNT(non-null column) counts related rows, zero if none [OK]

Common Mistakes:

MISTAKES

Using INNER JOIN excludes courses with no students
Filtering out NULLs in WHERE clause
Using COUNT(*) counts all rows including NULLs

Master "LEFT and RIGHT JOIN" in SQL

9 interactive learning modes - each teaches the same concept differently

Learn Why Deep Visual Try Challenge Project Recall Time

More SQL Quizzes

Given tables:

Step 1: Use LEFT JOIN to include all courses

Step 2: Use COUNT on enrollment student_id

Final Answer:

Quick Check:

Want More Practice?