Classes More Than 5 Students - Leetcode Solution

The "Classes More Than 5 Students" problem on leetcode asks us to find the names of all classes that have more than 5 students enrolled.

We are given a table "courses" that has two columns: "course_id" and "student_id". The "course_id" column represents the id of the course and the "student_id" column represents the id of the student who is enrolled in that course.

To solve this problem, we need to first count the number of students enrolled in each course. We can do this using the GROUP BY clause in SQL.

Then, we can use the HAVING clause to filter out the courses that have less than or equal to 5 students enrolled.

Finally, we can join the "courses" table with the "students" table using the "student_id" column to get the names of the courses.

Here's the SQL query to solve this problem:

SELECT c.course_id, COUNT(c.student_id) AS num_students, s.name
FROM courses c
JOIN students s ON c.student_id = s.student_id
GROUP BY c.course_id
HAVING num_students > 5;

This query first selects the "course_id" column from the "courses" table. It also counts the number of students enrolled in each course using the COUNT function and aliases it as "num_students". It then selects the "name" column from the "students" table using the JOIN clause to join the two tables on the "student_id" column.

The GROUP BY clause groups the results by the "course_id" column, and the HAVING clause filters out the courses that have less than or equal to 5 students enrolled.

The final output will be a table with the course_id, number of students, and the name of the course.

1