Chapter 11, Problem 1PE

Explanation of Solution

Query

 create view student_grades (ID,GPA) as

     select ID, credit_points / decode (credit_sum, 0, NULL, credit_sum)

     from ((select ID, sum(decode(grade,NULL,0,credits)) as credit_sum,

         sum(decode(grade,NULL,0,credits*points)) as credit_sum

         from(takes natural join course) natural left outer join grade_points

         group by ID)

     union

 select ID, NULL

 from student

 where ID not in (select ID from takes))

Explanation

• The credits for course with a null grade should not be added to handle the case where a student has not completed any course.
• The query can be broken down into a sub query that finds sum of credits and sum of credit grade points.
• Here view takes care of NULL grades by considering the credit points to be 0.