Combining results from 2 queries?

[mal2ty]

I need a query that will display the students who have enrolled in all the courses. I am working with the following tables:

STUDENTS(SID,FNAME,LNAME)
CATALOG(CNO,CTITLE)
COURSES(TERM,LINENO,CNO)
ENROLLS(SID,TERM,LINENO)

I can display the number of courses each student has enrolled in by using the following query:

SELECT STUDENTS.SID
FROM STUDENTS, ENROLLS
WHERE STUDENTS.SID = ENROLLS.SID
GROUP BY STUDENTS.LNAME
ORDER BY SUM(ENROLLS.SID) DESC;

Unfortunately I can't figure out how to display the students who have enrolled in all the courses. Can someone please offer some suggestions?

 

[expostfacto]

I'm afraid you're not very clear... you say you want the number of courses a student is enrolled in, but you only select the sid in your query! This could be written as
SELECT S.SID, (SELECT COUNT(*) FROM ENROLLS E WHERE E.SID = S.SID) AS N_COURSES
FROM STUDENTS
ORDER BY N_COURSES DESC

If you can use a parameter in your query for the TERM you are interested in, you could get a list of students in all classes in a similar fashion:
SELECT S.SID
FROM STUDENTS
WHERE (SELECT COUNT(*) FROM ENROLLS E WHERE E.SID = S.SID) = (SELECT COUNT(*) FROM COURSES C WHERE C.TERM = :TERM)

Otherwise it is more compliated and you will have to rephrase your query as "the students for whom there exist no classes in which they are not enrolled:"
SELECT S.SID
FROM STUDENTS S
WHERE NOT EXISTS (
SELECT 1 FROM COURSES C
WHERE NOT EXISTS (
SELECT 1 FROM ENROLLS E
WHERE E.SID = S.SID
AND E.TERM = C.TERM
AND E.LINENO = C.LINENO
)
)

 

[mal2ty]

Sorry for the lack of clearity. Here are two queries that are closer to what I'm truly looking for:

This query counts all the courses in the catalog:
SELECT COUNT(*)
FROM CATALOG;

This query counts the distinct number of courses each student has taken, and groups it by student id:
SELECT STUDENTS.SID, COUNT(DISTINCT CATALOG.CNO)
FROM STUDENTS, ENROLLS, COURSES, CATALOG
WHERE STUDENTS.SID = ENROLLS.SID AND
ENROLLS.LINENO = COURSES.LINENO AND
COURSES.CNO = CATALOG.CNO
GROUP BY STUDENTS.SID;

What I want is a list of the student ids, where the count of the number of courses they have taken matches the number of courses in the catalog.

I've tried combining these queries, but I'm receiving an error from Oracle. Here is the combined query as I tried it:
SELECT STUDENTS.SID, COUNT(ENROLLS.SID)
FROM STUDENTS, ENROLLS
WHERE STUDENTS.SID = ENROLLS.SID AND
(SELECT COUNT(DISTINCT CATALOG.CNO)
FROM STUDENTS, ENROLLS, COURSES, CATALOG
WHERE STUDENTS.SID = ENROLLS.SID AND
ENROLLS.LINENO = COURSES.LINENO AND
COURSES.CNO = CATALOG.CNO
GROUP BY STUDENTS.SID) IN (SELECT COUNT(*)
FROM CATALOG)
GROUP BY STUDENTS.SID;

The error I received from Oracle is:
ERROR at line 4:
ORA-01427: single-row subquery returns more than one row

Does anyone have any further suggestions for me?

 

[carp]

OK, how about:

SELECT STUDENTS.SID, COUNT(DISTINCT CATALOG.CNO)
FROM STUDENTS, ENROLLS, COURSES, CATALOG
WHERE STUDENTS.SID = ENROLLS.SID AND
ENROLLS.LINENO = COURSES.LINENO AND
COURSES.CNO = CATALOG.CNO
GROUP BY STUDENTS.SID
HAVING count(distinct catalog.cno) = (SELECT COUNT(*) FROM CATALOG)