4.6

Show how to define the view student_grades(ID,GPA) giving the grade-point average of each student, based on the query in Exercise 3.2; recall that we use a relation grade_points(grade,points) to get the numeric points associated with a letter grade. Make sure your view definition correctly handles the case of null values for the grade attribute of the takes relation.

---

We should not add credits for courses with a null grade; further, to correctly handle the case where a student has not completed any course, we should make sure we don’t divide by zero, and should instead return a null value.

We break the query into a subquery that finds sum of credits and sum of credit-grade-points, taking null grades into account. The outer query divides the above to get the average, taking care of divide by zero.

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_points
        FROM (takes NATURAL JOIN course) NATURAL LEFT OUTER JOIN grade_points
        GROUP BY ID)
        
        UNION
        
        SELECT ID,null,null
        FROM student
        WHERE ID NOT IN (SELECT ID FROM takes))
    

The view defined above takes care of null grades by considering the credit points to be 0 and not adding the corresponding credits in credit_sum.

The query above ensures that a student who has not taken any course with non-null credits, and has credit_sum = 0 gets a GPA of null. This avoids the division by zero, which would otherwise have resulted.

In systems that do not support decode, an alternative is the case construct. Using case, the solution would be written as follows:

CREATE VIEW student_grades(ID, GPA) AS 
    SELECT 
        ID, 
        credit_points / (
            CASE 
                WHEN credit_sum = 0 THEN NULL
                ELSE credit_sum
            END
        )
    FROM(
        (
            SELECT 
                ID,
                SUM ( 
                    CASE 
                        WHEN grade IS NULL THEN 0 
                        ELSE credits 
                    END
                ) AS credit_sum,
                SUM (
                    CASE 
                        WHEN grade IS NULL THEN 0 
                        ELSE credits * points 
                    END
                ) AS credit_points
            FROM (takes NATURAL JOIN course) NATURAL LEFT OUTER JOIN grade_points
            GROUP BY ID
        ) 
        
        UNION
        
        SELECT ID,null,null
        FROM student
        WHERE ID NOT IN (SELECT ID FROM takes)
    ) AS ss
    

An alternative way of writing the above query would be to use student natural left outer join gpa, in order to consider students who have not taken any course.