Classes More Than 5 Students

easy database sql

Problem

Courses(student, class). Return class names with at least 5 distinct students.

Inputc=[('A','Math'),('B','Math'),('C','Math'),('D','Math'),('E','Math'),('F','Bio')]

Output['Math']

Math has 5 distinct students; Bio has only 1.

rows (compact)

SELECT class
FROM Courses
GROUP BY class
HAVING COUNT(DISTINCT student) >= 5;

Explanation

We want class names that have at least five distinct students. The whole problem comes down to counting students per class and then keeping only the busy ones — a textbook GROUP BY plus HAVING.

GROUP BY class gathers all the rows for each class into one bucket. Then COUNT(DISTINCT student) counts how many different students are in that bucket. The DISTINCT matters: if the same student is listed twice for a class, they should still count only once.

HAVING COUNT(DISTINCT student) >= 5 is the filter. We use HAVING rather than WHERE because the condition is about an aggregate computed per group, which WHERE cannot see.

Example: Math has students A, B, C, D, E — five distinct, so it passes. Bio has only F — one student, so it is dropped. The result is just ['Math'].

Time: O(n) Space: O(n)