Oracle – How to Count Attribute Occurrences in an Inner Query

group byjoin;oracleoracle-11g

I have this query that I am trying to write out:

Which students (names) have taken more than one course from the same professor?

I have a students, course, and professor table. This is the ER diagram:

enter image description here

I have 4 tables:

  • Students (student_id is the PK)
  • Courses (course_id is the PK and professor_id is the FK to the Professor Table)
  • Takes_courses (student_id and course_id is the PK)
  • Professor (professor_id is the PK)

My plan:

Select student_name
From Student
Where student_id > Some (Select *
                         From Courses Natural Join Takes_Courses
                         Group By professor_id)

I am not able to run this query above. How am I supposed to count the number of student_id appears in the inner query?

Best Answer

Testbed:

create table students (student_id integer, student_name varchar(100));

insert into students(student_id,student_name)
select 1, 'Alice' from dual
union all
select 2, 'Bob' from dual
union all
select 3, 'Charlie' from dual;

create table courses (course_id integer, professor_id integer);

insert into courses
select 9+level, mod(level,3)+101 from dual connect by level<=10;

create table takes_courses (student_id integer, course_id integer);

insert into takes_courses
select student_id, course_id
from students cross join courses
where course_id<10+student_id*3;

The query below:

  1. Finds all unique student/professor/course combinations (so we don't get results for students who take the same course twice — not sure that is possible from the question).

  2. Groups the student/professor pairings and filters out those that only match a single course.

  3. Uses the resulting student IDs to look up the student names.

select student_name
from students
where student_id in ( select student_id
                      from( select distinct student_id, professor_id, course_id
                            from takes_courses join courses using(course_id) )
                      group by student_id, professor_id
                      having count(*)>1 );

| STUDENT_NAME |
| :----------- |
| Bob          |
| Charlie      |

dbfiddle here

Related Question