Problems understanding the count function

[Teufel]

I would appreciate any help anyone could give me on this, I am pretty new to SQL and finding it a bit of a steep learning curve. My problem is I have to find a list of teachers who have at least 3 degree students in their class. Here is my shot at it that hasn't been very successful:

select l.title, l.name,u.unit_code
from u,l
where l.STAFF_NO in (
select STAFF_NO
from u
where UNIT_CODE in (
select count (UNIT_CODE)
from e
where STU_NO > 3));

That part so far doesn't work, I wanted to some how connect the stu_no with this query:

select STU_NO
from s
where course='degree';

I don't know how to link this part either. I have been scratching my head over this for days.

 

[PruSQLer]

I'm having a little trouble figuring how your tables link up. What is table e - do you simply need that to join to Table s? Can you link "u" or "l" directly to "s"? I think one problem you're having with SQL is you're using subquerys too much and not going with Joins. Joins are much easier to use. So, without being sure of your structure, here's my stab at a solution:

Select l.title, l.name, u.unit_code, count(*)
From u, l, e, s
Where l.staff_no = u.staff_no
and u.unit_code = e.unit_code
and e.stu_no = s.stu_no
and course = 'degree'
Group by l.title, l.name, u.unit_code
Having count(*) > 2

If that's not it, let me know.