/* List students, courses, instructors & their grade for the course */
/* filter for only students who have completed their courses */

select student_id, s.lname, s.fname, course_id, description, i.lname, avg(grade), sum(percent), sum(value)  
from grade join student    s using (student_id)
           join course     c using (course_id)
           join instructor i using(instructor_id) 
group by student_id, s.lname, s.fname, course_id, description, i.lname
having sum(percent) = 1
order by student_id;


/* List students, courses, instructors & their grade for the course */
/* filter for only students who have not completed their courses */

select student_id, s.lname, s.fname, course_id, description, i.lname, avg(grade), sum(percent), sum(value)  
from grade join student    s using (student_id)
           join course     c using (course_id)
           join instructor i using(instructor_id) 
group by student_id, s.lname, s.fname, course_id, description, i.lname
having sum(percent) < 1
order by student_id;