SQLPlus Sub-Query Difficulty

The schema is as follows:

STUDENT(*snum: integer, sname: string, major: string, slevel: string, age: integer) 
CLASS(*name: string, meets_at: date, room: string, fid: integer) 
ENROLLED(*snum:integer, *cnam开发者_JAVA技巧e: string ) 
FACULTY(*fid: integer, fname: string, deptid: integer) 
DEPARTMENT (*deptid: integer,dname: string, Location:string)

(The fields with '*' are primary keys.)

I want to find the names of faculty members for whom the combined enrollment of the courses that they teach is less than 6 and more than 2, and print the combined enrollment.

I have the following query written, which correctly finds the faculty member, but I can't figure out how to print the combined enrollment.

SELECT fname FROM (faculty NATURAL JOIN class) JOIN enrolled ON(name = cname) GROUP BY fid, fname HAVING COUNT(*) BETWEEN 2 and 6;

I feel like I have to make this a nested sub-query, but I'm not sure how to total the enrollment counts.

Any help is most appreciated.

You can include aggregates in your select clause:

SELECT
   fname,
   COUNT(*) AS EnrollmentCount
FROM (faculty NATURAL JOIN class)
JOIN enrolled
 ON(name = cname)
GROUP BY fid, fname
HAVING COUNT(*) BETWEEN 2 and 6;

By the way, the between operator is inclusive: this says Count() >= 2 and Count() <= 6 , not quite what you specified in your question. Also, I'm not a fan of the natural join: I would inner join and explicity specify what criteria should be used.

Think about picking up a SQL book. Skimming through the chapters will give you a good idea of what's possible, then you can return for details when you run across a problem.