2

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?

2

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

  • The final answer was similar to what I needed with the nested queries. – CapturedTree Oct 18 '17 at 2:28
3

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
;
  • (upvoted) The only thing I'd say is: we need the students' names - not the IDs! – stefan Oct 17 '17 at 18:16
  • 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? – CapturedTree Oct 18 '17 at 2:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.