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:

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?

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

• CapturedTree: The final answer was similar to what I needed with the nested queries.

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
;

• stefan: (upvoted) The only thing I'd say is: we need the students' names - not the IDs!
• CapturedTree: I am confused on how those three join statements work. Does the result of the first join get joined with the result of the second and so on?