SQL Count Number Of Classmates For a Student Across All Courses

I have the following tables:

• Student: Student_ID (PK), Student_Name
• Course: Course_ID (PK), Course_Name
• Enrollment: Student_ID (FK), Course_ID (FK)

I need 2 queries:

1. A query that computes for each student id in the Student table the total number of different (Unique) classmates that this student has across all courses. If the student is not enrolled in any courses 0 should be returned.

   For example if Student_ID 123 is enrolled in 3 courses and he has 10 different classmates in each of those courses, I should get the following result:

   Student_ID        Num_of_classmates
   -----------------------------------
   123               30
   

2. A SQL query that returns all students with all of their classmates. classmate of a student is another student who is enrolled in at least one same class.

Answer

It is unclear which of these you want:

• Total students in all the classes (which would include a given student).
• Total distinct students, since some classmates might be in multiple classes.
• Total distinct students not including the given student (“I am not my own classmate”).

In any case, the idea is basically two joins and aggregation:

select s.student_id,
       count(ec.student_id) as total_classmates,
       count(distinct s.student_id) as distinct_classmates,
       count(distinct case when ec.student_id <> s.student_id then ec.student_id end) as distinct_other_classmates
from student s left join
     enrollment e
     on e.student_id = s.student_id left join
     enrollment ec
     on ec.class_id = e.class_id;
group by s.student_id;