Oracle SQL: Finding common subjects students take in each class

Question

Below is a table of DDL and DML students

CREATE TABLE STUDENTS
(
  CLASS NUMBER,
  STUDENT_ROLLNO  NUMBER,
  SUBJECT VARCHAR2(50)
);

INSERT INTO STUDENTS(CLASS, STUDENT_ROLLNO, SUBJECT) VALUES(1, 1,'ENGLISH');
INSERT INTO STUDENTS(CLASS, STUDENT_ROLLNO, SUBJECT) VALUES(1, 1,  'MATHS');
INSERT INTO STUDENTS(CLASS, STUDENT_ROLLNO, SUBJECT) VALUES(1, 1,  'SCIENCE');
INSERT INTO STUDENTS(CLASS, STUDENT_ROLLNO, SUBJECT) VALUES(1, 1,  'ART');
INSERT INTO STUDENTS(CLASS, STUDENT_ROLLNO, SUBJECT) VALUES(1, 2,  'ENGLISH');
INSERT INTO STUDENTS(CLASS, STUDENT_ROLLNO, SUBJECT) VALUES(1, 2,  'MATHS');
INSERT INTO STUDENTS(CLASS, STUDENT_ROLLNO, SUBJECT) VALUES(1, 2,  'FRENCH');
INSERT INTO STUDENTS(CLASS, STUDENT_ROLLNO, SUBJECT) VALUES(1, 2,  'POETRY');
INSERT INTO STUDENTS(CLASS, STUDENT_ROLLNO, SUBJECT) VALUES(1, 3,  'ENGLISH');
INSERT INTO STUDENTS(CLASS, STUDENT_ROLLNO, SUBJECT) VALUES(1, 3,  'MATHS');

Now I need to find a common subject selected by each student in this class. In this case, the result will be ENGLISH and MATHS.

I need to write a query in order to get the required result, since theirs is more class and the result should be like in a class.

Thank.

Answer 1

try it

 WITH SUB1 AS (
SELECT  
CLASS,SUBJECT,COUNT(*) NMB  FROM
STUDENTS
GROUP BY CLASS,SUBJECT
),SUB2 AS(
SELECT CLASS, MAX(NMB)MAX_NMB FROM
(SELECT  
CLASS,COUNT(*) NMB  FROM
STUDENTS
GROUP BY CLASS,SUBJECT
)
GROUP BY CLASS
)
SELECT SUB1.CLASS,SUB1.SUBJECT
FROM SUB1
INNER JOIN SUB2
ON SUB1.NMB=SUB2.MAX_NMB  AND SUB1.CLASS=SUB2.CLASS

Answer 2

Without any suicides:

SELECT class,
       subject
FROM   (
  SELECT s.*,
         COUNT( DISTINCT student_rollno ) OVER ( PARTITION BY class ) AS num_students
  FROM   students s
)
GROUP BY class, subject
HAVING   COUNT( DISTINCT student_rollno ) = MAX( num_students );

Output

CLASS SUBJECT
----- -------
    1 ENGLISH
    1 MATHS