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!

CodePudding user response：

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;

CodePudding user response：

You could do:

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

which only uses a subquery and has no group-by, join or set operations.

fiddle

If you wanted the actual count in the result set then you would need to repeat the subquery in the select list.

You might also need to do COUNT(DISTINCT cs.student_id) if there might be duplicates; particularly if the same student can sign up in multiple years - but then you might want to restrict to a single year anyway.