Classes With at Least 5 Students

Classes With at Least 5 Students

What is this problem about?

This is a standard SQL problem where you are given a Courses table with columns student and class. You need to find all the classes that have at least five students enrolled.

Why is this asked in interviews?

Microsoft and Google use this "Easy" database question to test your understanding of basic SQL filtering after aggregation. It evaluations your ability to use the GROUP BY clause combined with the HAVING clause. This is a day-to-day task for anyone working with relational databases.

Algorithmic pattern used

The pattern is SQL Aggregation with Post-filtering.

1. You group the rows by the class column.
2. You count the number of students in each group using COUNT().
3. You use the HAVING clause to filter out groups where the count is less than 5.

Example explanation

Table Courses:

student	class
A	Math
B	Math
C	Math
D	Math
E	Math
F	Science

1. Group by class: Math has 5 students, Science has 1.
2. Filter: Only Math satisfies count >= 5. Result: Math.

Common mistakes candidates make

The most common mistake is trying to use a WHERE clause for the count (e.g., WHERE COUNT(student) >= 5). In SQL, WHERE filters rows before they are grouped, while HAVING filters the results after they are grouped. Another mistake is forgetting that COUNT(*) or COUNT(student) is needed within the HAVING clause.

Interview preparation tip

Remember the order of operations in SQL: FROM -> WHERE -> GROUP BY -> HAVING -> SELECT -> ORDER BY. Knowing that HAVING is the correct place for aggregate filters is a fundamental SQL concept.