[Zope-DB] looping through

[Matthew T. Kromer]

on 8/31/01 7:41 AM, Russell Hires at rhires@earthlink.net wrote:

> Hello everyone,
> 
> I hope I'm in the right place. I'm working on a gradebook program for
> teachers, and at this point I want a teacher to be able to add an assignment.
> I've got a basic table that has the student name, id, assignment, and grade,
> with a row for each assignment/grade for each student. I'm at the point now
> where the teacher adds a new assignment and I want <something> to go through
> the table, and create new rows for each student, but with a null value for
> the grade which will be entered later. How do I do that? I'm thinking of just
> going through the table with a SELECT DISTINCT student-id clause (that way I
> only create the rows once for each student assignment), but that seems like
> two steps, one for the SELECT DISTINCT, but then adding new rows based on
> that query is baffling me.
> 
> Thanks for the help!


Hi Russell,

*Generally,* I'd say you do something like this with a relation (hence,
releational database) which is a three-table set, e.g.

CREATE TABLE STUDENTS (
    NAME VARCHAR2(64),
    ID  NUMBER PRIMARY KEY,
    ...
)

CREATE TABLE ASSIGNMENTS (
    NAME VARCHAR2(64),
    ID  NUMBER PRIMARY KEY,
    DUE DATE
    ...
)

CREATE TABLE GRADE (
    STUDENTID NUMBER REFERENCES STUDENTS(ID),
    ASSIGNMENTID NUMBER REFERENCES ASSIGNMENTS(ID),
    SCORE NUMBER,
    COMPLETEDON DATE
    ...
)

and by joining the three tables, you can get your grades.  Thus, implicitly
with insertion into the assignments table you create a new assignment which
no-one has completed yet.

So, you can do something like

    select students.name student, assignments.name assignment, grade.score
grade 
    from students, assignments, grade
    where 
        assignments.name = 'My choice asssignment',
        and grade.assignmentid = assignment.id,
        and grade.studentid = students.id

(that query puts a lot of faith into the query optimizer, but presumably
your tables are pretty small)

By playing around with DISTINCT etc you can cause the NULL entries of a join
to appear as results (I'd have to go look up the exact syntax).  I usually
lean heavily on the query plan explanation function of the database (Oracle
for me) to tune queries for speed.