Determining the number of occurrences of a record

Question

I have two tables - student and interview - with values:

student
----------
sID  
sName  

interview
----------
sID  
date  
outcome = (OFFER, NOOFFER, HIRED)  

And I have to list the sID of any student who has never received an offer but who has had more than five interviews.

I am having trouble figuring out how to determine if a student has had more than 5 interviews. I know you can just count the records to see if the sID is listed more than 5 times, but I am not exactly sure how to format it. Any help would be greatly appreciated!

Answer 1

Use:

SELECT s.sid
  FROM STUDENT s
 WHERE EXISTS(SELECT NULL -- more than five interviews
                FROM INTERVIEW i 
               WHERE i.sid = s.sid
            GROUP BY i.sid
              HAVING COUNT(*) > 5)
   AND NOT EXISTS(SELECT NULL -- never received an offer
                    FROM INTERVIEW i
                   WHERE i.sid = s.sid
                     AND i.outcome = 'OFFER')

Answer 2

select s.sID,s.sName from
( select sID,count(0) as numInterviews 
  from interview 
  where sID not in (select sID from interview where outcome='OFFER') 
  group by sID
) as interviewCounts
join student s on s.sID=interviewCounts.sID
where interviewCount>5