SQL query to check if some entry contains list of values and compare them

Question

I have two MySql tables with following schema

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

Now the task is to find all tuples such that (r1,r2,d) such that student with roll no. r1 has done all courses of department with DeptId ID that student with roll no. r2 has done.

My approach was to first find all the courses of that department done by student r2 and the similary find all courses done by student r1 in that department and compare them if they are identical, But I don't know if it would be correct and also how to compare the tow results Here is my code to check all courses done by student r2 in particular department

Here is the link to code and data DB Fiddle

Answer 1

Here is my approach

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

ex.

RNO1 RNO2
1    2
1    3
2    3

Step 2: Create a table that also adds a unique DeptId to the previous table for each distinct DeptId ofr RNO1 and RNO2

ex.

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

Step 3: Finally, keep only tuples where RNO1 and RNO2 have both taken the same courses for each DeptId.

Here is an exmaple Where rno1_rno2_deptid refers to the above table

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 cases where RNO1 and RNO2 have the same CNO for the same department.