for SQL*PLUS :
BREAKPOINT ON student_id;
SELECT student_id,assignment_no,DECODE(completed_sw,'Y','completed','N','not
completed','*') FROM assignment
ORDER BY student_id
Alex
Pornpira Vachareeyanukul пишет в сообщении ...
>Hi there,
>I need some help with the select statement. I've tried everything and stil
>haven't come up with the answer.
>
>I have a table called ASSIGNMENT with
>student_id,
>class_subject,
>assignment_no,
>completed_sw => value can be Y or N
>
>Each student can have 0 or more assignments for each class subect. I want
>to find out for each student if they completed all their class assignments
>or not.
>
>For example
>Student A has
>assignment 1- not completed,
>assignment 2- not completed,
>assignment 3- completed
>for class subject=history
>
>Student A also has
>assignment 1- completed,
>assignment 2- completed
>for class subject=math
>
>I want the result to be like
>student class subject completed
>------ ------------ -----
>A history N
>A math Y
>
>I've come upt with the select statement that finds all students who have
>not completed their assignments.
>
>Select distinct student_id, class_subject, 'N'
> from assignment a
> where 'N' in
> (select distinct p.completed_sw
> from assignment p
> where a.student_id = p.student_id
> and a.class_subject = p.class_subject
> )
>
>I still need to find out which students have completed all the
>assignments.
>
>Any tips or help would be appreciated.
>
>Thank you,
>Mimi
Received on Wed Nov 24 1999 - 01:36:04 CST
