Oracle SQL Is there a way to find intersections of data sets stored in same table when number of data sets are dynamic

Question

Imagine the following data set. (This is not the exact same problem I am working on, but this is just a simple example that demonstrate the requirement)

Student ID,  Course ID
S1           C1
S1           C2
S1           C3
S2           C1
S2           C3
S3           C1
S3           C2

In above data set each student is registered under number of courses. I want to find out which students are taking all the causes in this table.

So if I use INTERSECT keyword it will look like this.

SELECT student_id FROM <table> where course_id = 'C1'
INTERSECT
SELECT student_id FROM <table> where course_id = 'C2'
INTERSECT
SELECT student_id FROM <table> where course_id = 'C3'

Obviously this work only if the list of courses are fixed.

Is there a way to do this with the list of courses are dynamic and using SQL only.

I could write a PLSQL function to do this. But then there will be a cursor executed for each unique course id and intersection will be done by PLSQL code.

I am looking to see if it is possible to offload as much of it as possible to SQL engine (may be using analytical functions) as the data set involved can be large.

Answer 1

Oracle allows you to use COUNT(DISTINCT) as an analytic function. So, you could do:

SELECT DISTINCT student_id 
    FROM   (SELECT student_id, 
                  COUNT(DISTINCT course_id) 
                  OVER ( PARTITION BY student_id  )  AS course_per_student, 
                  COUNT(DISTINCT course_id) OVER ()  AS available_courses 
        FROM   t) 
WHERE  course_per_student = available_courses 

DEMO