Use SQL query to check if an entry contains a list of values ​​and compare

Question

I have two MySql tables with the following schema

Courses(CNO:CHAR(10),CName:VARCHAR(50), DeptId:CHAR(3)): Each course has a unique ID called CNO
Register(RNO:CHAR(10), CNO:CHAR(10), Year:INTEGER, Sem:INTEGER, Grade:CHAR)

The current task is to find all tuples that satisfy (r1, r2, d), in which the student with student ID r1 has completed all the DeptIds completed by the student with student ID r2 as ID's department's courses.

My method is to first find all the courses completed in the department by the student with student number r2, and then similarly find all the courses completed in the department by the student with student number r1, and compare whether they are the same. But I don't know if this is correct or how to compare the two results.

This is the code I use to check all courses completed by the student with middle school number r2 in a specific department:

Here is the link to the code and data DB Fiddle

Answer 1

This is my method

step 1: Create a table with two columns, student1 and student2, containing unique combinations of students

For example:

RNO1 RNO2
1    2
1    3
2    3

Step 2: Create a table adding a unique DeptId

for each different DeptId from the previous table

For example:

RNO1 RNO2 DeptId
1    2    IME
1    2    CHE
1    2    ECO
1    3    IME
1    3    CHE
2    3    ECO

Step 3: Finally, only the tuples in which RNO1 and RNO2 have taken the same course in each DeptId are retained.

The following is an example where rno1_rno2_deptid refers to the table above

SELECT
   *
FROM
   rno1_rno2_deptid as d
WHERE
   NOT EXISTS((SELECT DISTINCT CNO FROM Register AS r WHERE r.RNO = d.RNO1 AND r.DeptId = d.DeptId) 
   MINUS 
   (SELECT DISTINCT CNO FROM Register AS r WHERE r.RNO = d.RNO2 AND r.DeptId = d.DeptId))

The above only selects the case where RNO1 and RNO2 have the same CNO in the same series.