I have a table that looks like below:

CREATE TABLE table1
(
    student_id number,
    class_type_id number,
    class_time varchar,
    class_subject varchar
);

---

INSERT INTO table1 (student_id, class_type_id, class_time, class_subject)
VALUES (1, 1, '08:30', 'Arithmetic'), 
       (1, 3, '10:30', 'Science'), 
       (1, 3, '11:30', 'Advanced Science'), 
       (2, 1, '09:30', 'Arithmetic'), 
       (2, 7, '11:00', 'Cooking'), 
       (1, 5, '14:00', 'English');

I want a query that checks for and returns any students that are enrolled in duplicate class types (shown below):

I've tried the following but was wondering if there was a 'cleaner' or different way to do this?

SELECT
    student_id, count
FROM
    (SELECT 
         COUNT(*) AS count, 
         student_id, class_type_id
     FROM
         (SELECT
              student_id, class_type_id
          FROM
              table1)
     GROUP BY
         student_id) 
WHERE
    count > 1;

Also: is there any way I can cleanly verify whether STUDENT_ID <-> CLASS_TYPE_ID is a 1:1 relationship (I know that this example OBVIOUSLY is not a 1:1 relationship but was just curious...)

Thanks in advance!

CodePudding user response：

Indeed, there is a simpler way.

Sample data:

SQL> with table1 (student_id, class_type_id, class_time, class_subject) as
  2    (select 1, 1, '08:30', 'Arithmetic'       from dual union all
  3     select 1, 3, '10:30', 'Science'          from dual union all
  4     select 1, 3, '11:30', 'Advanced Science' from dual union all
  5     select 2, 1, '09:30', 'Arithmetic'       from dual union all
  6     select 2, 7, '11:00', 'Cooking'          from dual union all
  7     select 1, 5, '14:00', 'English'          from dual
  8    )

Query begins here:

  9  select student_id, class_type_id
 10  from table1
 11  group by student_id, class_type_id
 12  having count(*) > 1;

STUDENT_ID CLASS_TYPE_ID
---------- -------------
         1             3

SQL>