How to count the number of times an attribute appears in an inner query?

Question

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:

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?

Answer 1

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

Answer 2

Something like this.

The below lists everything: which student took which course taught by which professor.

select
  s.student_id, c.course_id, p.professor_id
from
  students s
  join takes_course tc on (s.student_id = tc.student_id)
  join course c on (c.course_id = tc.course_id)
  join professor p on (p.professor_id = c.professor_id)
;

But we do not care about courses. The below lists which student is taught by which professor(s). (student_id, course_id) is unique, and 1 course is taught by 1 professor, so the same (student_id, professor_id) values appear multiple times, if the student attended multiple courses taught by the same professor.

select
  s.student_id, p.professor_id
from
  students s
  join takes_course tc on (s.student_id = tc.student_id)
  join course c on (c.course_id = tc.course_id)
  join professor p on (p.professor_id = c.professor_id)
;

We do not care about professor_id and we need to list students taught by more than 1 course.

select
  s.student_id
from
  students s
  join takes_course tc on (s.student_id = tc.student_id)
  join course c on (c.course_id = tc.course_id)
  join professor p on (p.professor_id = c.professor_id)
group by
  s.student_id, p.professor_id
having
  count(*) > 1
;

This may list the same student multiple times because a student can take 2 courses from a professor and 3 courses from another professor, so list 1 student only once:

select
  distinct s.student_id
from
  students s
  join takes_course tc on (s.student_id = tc.student_id)
  join course c on (c.course_id = tc.course_id)
  join professor p on (p.professor_id = c.professor_id)
group by
  s.student_id, p.professor_id
having
  count(*) > 1
;