Display courses with at least 10 students

Question

I have the following tables:

Students (id, name, surname, study_year, department_id)

Courses(id, name)

Course_Signup(id, student_id, course_id, year)

I want to display the courses to which at least 10 students have signed up for, only using subqueries (no group-by, join or set operations). This could be easily implemented using data aggregation and join:

SELECT c.name, COUNT(csn.course_id)
FROM Course_Signup csn
JOIN Courses c
    ON csn.course_id = c.id
GROUP BY c.name
HAVING COUNT(csn.course_id) >= 10

But how would I do this only using subqueries? Is there any other way, other than COUNT, to get the number of courses? Thank you, in advance!

Answer 1

You can use a correlated sub-query to retrieve the name:

SELECT (SELECT c.name FROM Courses c WHERE csn.course_id = c.id) AS name,
       COUNT(*)
FROM   Course_Signup csn
GROUP BY
       course_id
HAVING COUNT(*) >= 10

Note: you should also GROUP BY the primary key the uniquely identifies the course as there may be two courses with the same name.

If you also don't want to use GROUP BY then:

SELECT name
FROM   Courses c
WHERE  10 <= ( SELECT COUNT(*)
               FROM   Course_Signup csn
               WHERE  csn.course_id = c.id )

or, to also get the number of sign-ups:

SELECT *
FROM   (
  SELECT name,
         ( SELECT COUNT(*)
           FROM   Course_Signup csn
           WHERE  csn.course_id = c.id ) AS num_signups
  FROM   Courses c
)
WHERE  num_signups >= 10;