Re: SQL query may not be enough, what else should I try?

From: <joe_celko_at_my-deja.com>
Date: Tue, 21 Mar 2000 03:25:43 GMT
Message-ID: <8b6q3a$f2u$1@nnrp1.deja.com>

>> Table A holds all the student names, Table B has holds the
associations between Table A and Table C. Table C contains all classes offered.

I want to learn which students (undergrads) are not currently enrolled in a particular group of classes (e.g. Humanities), and have not accrued at least 18 total units - over the entire time so far spent here. The second part I've got, but the first part of that has me stifled for a good solution. <<

>> Table A holds all the student names, Table B has holds the
associations between Table A and Table C. Table C contains all classes offered.

It sure would be nice if you had included some DDL, so we have some idea where you are keeping the units earned, the current enrollments, etc. Here is my wild guesses:

CREATE TABLE Classes
(class_id CHAR(7) NOT NULL,
department CHAR(20) NOT NULL,
...);

CREATE TABLE Students
(student_id INTEGER NOT NULL PRIMARY KEY, student name CHAR(30) NOT NULL,
...);

CREATE TABLE Enrollments
(student_id INTEGER NOT NULL,
class_id CHAR(7) NOT NULL,
units_earned INTEGER, -- null means enrolled now, no grade yet PRIMARY KEY (student_id, class_id));

Now try something like this:

SELECT S1.student_name
FROM Students AS S1, Enrollments AS E1 WHERE S1.student_id = E1.student_id
AND NOT EXISTS -- no current enrollment in list of courses

        (SELECT *
           FROM Enrollments AS E2
          WHERE S1.student_id = E2.student_id
            AND E2.units_earned IS NULL -- current enrollment
            AND class_id IN (<list of courses>))

GROUP BY S1.student_name
HAVING SUM(E1units_earned) < 18;

You might want to replace <list of courses> with a subquery to a table that you can insert into and delete from to build the list.

--CELKO-- Sent via Deja.com http://www.deja.com/
Before you buy. Received on Mon Mar 20 2000 - 21:25:43 CST