Get id with same value

I have a table as below:

studentNo  courseNo 
s001         c001  
s001         c002
s002         c001
s002         c002
s003         c001
s003         c003

May I know how can I get the studentNo who have exactly same course as student s001? Many thanks.

Answer

You want to count the number of courses for each student and then join the table to itself for different students taking the same course with the same total number of courses and then count that the total number of matched rows in the join equals total number of courses:

WITH course_count (studentno, courseno, num_courses) AS (
  SELECT t.*,
         COUNT(courseNo) OVER (PARTITION BY studentno)
  FROM   table_name t
)
SELECT c.studentno
FROM   course_count c
       INNER JOIN course_count s
       ON (   c.courseno   =  s.courseno
          AND c.studentno  != s.studentno
          AND c.num_courses =  s.num_courses )
WHERE  s.studentno = 's001'
GROUP BY c.studentno
HAVING COUNT(c.courseno) = MAX(s.num_courses);

Which, for the (expanded) sample data:

CREATE TABLE table_name (studentNo, courseNo) AS
SELECT 's001', 'c001' FROM DUAL UNION ALL 
SELECT 's001', 'c002' FROM DUAL UNION ALL
SELECT 's002', 'c001' FROM DUAL UNION ALL
SELECT 's002', 'c002' FROM DUAL UNION ALL
SELECT 's003', 'c001' FROM DUAL UNION ALL
SELECT 's003', 'c003' FROM DUAL UNION ALL
SELECT 's004', 'c002' FROM DUAL UNION ALL
SELECT 's004', 'c001' FROM DUAL UNION ALL
SELECT 's004', 'c003' FROM DUAL UNION ALL
SELECT 's005', 'c001' FROM DUAL

Outputs:

STUDENTNO
s002

db<>fiddle here