Hey everyone! I’m working on a database problem and could use some help. I’ve got three tables: Learners, Classes, and Class_Enrollment. I’m trying to find a way to list all the classes that have at least 10 students signed up.
I know how to do this with joins and GROUP BY, but I’m wondering if there’s a way to do it using only subqueries. Here’s what I’ve got so far:
SELECT name
FROM Classes
WHERE id IN (
SELECT course_id
FROM Class_Enrollment
-- Not sure what to do here
)
Is there a trick to count the number of students without using COUNT? Maybe something with EXISTS or ALL? I’m really stumped on this one. Any ideas would be super helpful! Thanks in advance for your suggestions!
Hey DancingButterfly! 
Interesting question you’ve got there! I love a good database puzzle. Have you considered using the ALL keyword for this? It might be a bit unconventional, but it could work something like this:
SELECT name
FROM Classes c
WHERE 10 <= ALL (
SELECT COUNT(*)
FROM Class_Enrollment e
WHERE e.course_id = c.id
)
This basically says ‘give me all classes where 10 is less than or equal to ALL the counts of students in that class’. It’s a bit mind-bending at first, but it does the trick without explicit joins!
What do you think about this approach? Have you tried anything similar? I’m curious to hear if you’ve explored other subquery techniques too. Maybe there’s an even cooler way to solve this that we haven’t thought of yet! 
hey dancingbutterfly, cool problem! here’s another way to tackle it:
SELECT name
FROM Classes
WHERE (
SELECT COUNT(DISTINCT learner_id)
FROM Class_Enrollment
WHERE course_id = Classes.id
) >= 10
this uses a correlated subquery to count distinct learners per class. it’s simple and avoids joins too. lemme know if it works for ya!
I’ve encountered a similar challenge before, and I can share an approach that might work for you. Instead of using COUNT, you can leverage the power of subqueries with HAVING. Here’s a solution that avoids explicit joins:
SELECT name
FROM Classes
WHERE id IN (
SELECT course_id
FROM Class_Enrollment
GROUP BY course_id
HAVING SUM(1) >= 10
)
This query uses SUM(1) as a clever way to count rows without the COUNT function. It essentially adds 1 for each row in a group, effectively counting them. The HAVING clause then filters for groups with at least 10 students.
This method maintains the subquery structure you’re aiming for while achieving the desired result. It’s a bit unconventional, but it gets the job done without resorting to joins or the COUNT function. Hope this helps with your database problem!