3.2

Suppose you are given a relation grade_points(grade, points) that provides a conversion from letter grades in the takes relation to numeric scores; for example, an “A” grade could be specified to correspond to 4 points, an “A-” to 3.7 points, a “B+” to 3.3 points, a “B” to 3 points and so on. The grade points earned by a student for a course offering (section) is defined as the number of credits for the course multiplied by the numeric points for the grade that the student received.

Given the preceding relation and our university schema, write each of the following queries in SQL. You may assume for simplicity that no takes tuple has the null value for grade.
a. Find the total grade points earned by the student with ID ‘12345’, across all courses taken by the student.
b. Find the grade point average (GPA) for the above student, that is, the total grade points divided by the total credits for the associated courses.
c. Find the ID and the grade-point average of each student.
d. Now reconsider your answers to the earlier parts of this exercise under the assumption that some grades might be null. Explain whether your solutions still work and, if not, provide versions that handle nulls properly.

Find the total grade points earned by the student with ID ‘12345’, across all courses taken by the student.

SELECT SUM(c.credits * g.points)
FROM takes AS t 
    INNER JOIN course AS c ON t.course_id = c.course_id
    INNER JOIN grade_points AS g ON t.grade = g.grade
WHERE t.ID = '12345'

In the above query, a student who has not taken any course would not have any tuples, whereas we would expect to get 0 as the answer. One way of fixing this problem is to use the outer join operation, which we study later in Chapter 4. Another way to ensure that we get 0 as the answer is via the following query:

(
SELECT SUM(c.credits * g.points)
FROM takes AS t 
    INNER JOIN course AS c ON t.course_id = c.course_id
    INNER JOIN grade_points AS g ON t.grade = g.grade
WHERE t.ID = '12345'
)
UNION 
(
SELECT 0
FROM student 
WHERE ID = '12345' AND NOT EXISTS (SELECT * FROM takes WHERE ID = '12345')
)

Find the grade point average (GPA) for the above student, that is, the total grade-points divided by the total credits for the associated courses.

SELECT SUM(c.credits * g.points) / SUM(credits) AS GPA
FROM takes AS t 
    INNER JOIN course AS c ON t.course_id = c.course_id
    INNER JOIN grade_points AS g ON t.grade = g.grade
WHERE t.ID = '12345'

As before, a student who has not taken any course would not appearn in the above result; we can ensure that such a stuent appears in the result by using the modified query from the previous part of this question. However, an additional issue in this case is that the sum of credits would also be 0, resulting in a divide-by-zero condition. In fact, the only meaningful way of defining the GPA in this case is to define it as null. We can ensure that such a student appears in the result with a null GPA by adding the following union clause to the above query.

UNION 
(
    SELECT null AS GPA 
    FROM student 
    WHERE ID = '12345' AND 
        NOT EXISTS (SELECT * FROM takes WHERE ID='12345')
)

Find the ID and the grade-point average of each student.

SELECT t.ID, SUM(c.credits * g.points) / SUM(credits) AS GPA
FROM takes AS t 
    INNER JOIN course AS c ON t.course_id = c.course_id
    INNER JOIN grade_points AS g ON t.grade = g.grade
GROUP BY t.ID

Again, to handle students who have not taken any course, we would have to add the following union clause:

UNION 
(
    SELECT ID, null AS GPA 
    FROM student 
    WHERE NOT EXISTS (SELECT * FROM takes WHERE takes.ID = student.ID)
)

Now reconsider your answers to the earlier parts of this exercise under the assumption that some grades might be null. Explain whether your solutions still work and, if not, provide versions that handle nulls properly.

The queries listed above all include a test of equality on grade between grade_points and takes. Thus, for any takes tuple with a null grade, that student’s course would be eliminated from the rest of the computation of the result. As a result, the credits of such courses would be eliminated also, and thus the queries would return the correct answer even if some grades are null.