Re: ENTRY LEVEL QUESTION...HELP...

From: <Lance.Humpert_at_mail.tju.edu>
Date: 1996/08/13
Message-ID: <177E2E56A.ASVLH_at_TJUVM.TJU.EDU>#1/1

vikram.karmarkar_at_internetMCI.com writes:  

> I have a students table with corresponding assignments they've submitted.
> Each student is supposed to turn in 5 assignments(1 thru' 5 "assn_num"s)
> I have to create a report showing a list of "assn_num" missing
> for all the students who have not turned in all five assignments.
>
> Any help/hint/suggestion will be greatly appreciated!
>
> The data looks like this :
 

 [snip]  

Your best bet would be to define a table containing the assignments for each class. The data looks like this:  

   CLASS ASSN_NUM
-------- ---------

     300          1
     300          2
     300          3
      :           :
      :           :
     400          4
     400          5
 

If your first table is called 'students' and the one above is called 'assignments', you should be able to perform the following query:  

  select students.student, assignments.class, assignments.assn_num     from students X, assignments
   where students.class = assignments.class

     and not exists
        (select * from students, assignments
          where students.class = assignments.class
            and students.assn_num = assignments.assn_num
            and students.student = X.student)

   order by students.student, assignments.class;  

The subquery identifies all assignments that have been completed for any student in the students table. By prefixing this subquery with a 'not exists' clause and using an alias 'X' to refer back to proper student, you should be able to list non-existent grades. Of course, this logic fails to report students who have not turned in any assignments, as would have been my case. ;)  

Add a 'break on student skip 1;' before your query, and you should be generating one fine-looking report.  

• Lance (Remember, even oracle can not report on data that isn't there.)

Received on Tue Aug 13 1996 - 00:00:00 CEST