-1

I am working with SQL+ and am having trouble with querying multiple tables, while comparing count values.

For example,

I want to know the Student Names and ID # that have taken more than 5 exams in high school. Student Names && ID #s are stored in the STUDENT table, while the amount of exams as well as student ID#s are stored in the EXAM table.

What would be the best way to query for "students names and id#s who have taken more than 5 exams"?

I am working in ORACLE and SQLPLUS.

Thanks

userJoe
  • 73
  • 2
  • 12
  • Please **[EDIT]** your question and add the definition of the tables (as `create table` statements), some sample data and the expected output based on that data. [**Formatted text**](http://stackoverflow.com/help/formatting) please, [**no screen shots**](http://meta.stackoverflow.com/questions/285551/why-may-i-not-upload-images-of-code-on-so-when-asking-a-question/285557#285557). **[edit]** your question do not post code or additional information in comments. –  Apr 24 '17 at 15:04

1 Answers1

0

Use COUNT(*) on the exams table grouped by STUDENT_ID to find how many exams each student has taken. The HAVING clause applies a filter to the aggregate so we get only the students who have taken more than five exams.

This provides a sub-query which we can use to filter the main student table.

select s.name, s.student_id
from student s
where s.id in 
   ( select e.student_id 
     from exam e
     group by e.student_id having count(*) > 5 )

I wrote all that, then I re-read your question and maybe it's as simple as:

select s.name, s.student_id
from student s
     join exam e
     on e.student_id = s.student_id
where e.no_of_exams > 5

So difficult to tell. when the question is poorly framed.

APC
  • 144,005
  • 19
  • 170
  • 281