CREATE TABLE schedules (
id BIGSERIAL PRIMARY KEY,
subject_id BIGINT FOREIGN KEY REFERENCES subjects(id),
semester VARCHAR(5) -- YYYYS -- year and semester
);
CREATE TABLE enrollments (
id BIGSERIAL PRIMARY KEY,
student_id BIGINT FOREIGN KEY REFERENCES students(id),
schedule_id BIGINT FOREIGN KEY REFERENCES schedules(id),
grade VARCHAR(2) -- letter
);
If the university rule for transcript is get the highest score, the query is quite simple, first you'll need to make sure that grade is ordered in certain way on MAX agregate (for example: A+ > A > A- > B+ > ...) , then you'll need to do this query:
SELECT s.subject_id
, MAX(e.grade)
FROM enrollments e
JOIN schedules s
ON e.schedule_id = s.id
WHERE e.student_id = ?
GROUP BY 1
If the university rules is get the latest score, first of all, you'll need to make sure that the semester is ordered (for example: 2015A < 2015B < 2015C < 2015A < ...), then you'll need to find the latest semester of each subject, for example:
SELECT s.subject_id
, MAX(s.semester)
FROM enrollments e
JOIN schedules s
ON e.schedule_id = s.id
WHERE e.student_id = ?
GROUP BY 1
SELECT s.subject_id
, e.grade
FROM enrollments e
JOIN schedules s
ON e.schedule_id = s.id
WHERE e.student_id = ?
AND (s.subject_id, s.semester) IN
( SELECT s.subject_id
, MAX(s.semester)
FROM enrollments e
JOIN schedules s
ON e.schedule_id = s.id
WHERE e.student_id = ?
GROUP BY 1
)
GROUP BY 1
There are another alternative for this, you can use aggregate with OVER and PARTITION BY syntax, then compare the result with the aggregate to remove the previous scores, for example:
SELECT r.subject_id
, r.grade
FROM (
SELECT s.subject_id
, e.grade
, s.semester
, MAX(s.semester) OVER (PARTITION BY s.subject_id) max_sem
FROM enrollments e
JOIN schedules s
ON e.schedule_id = s.id
WHERE e.student_id = ?) r
WHERE r.semester_id = r.max_sem
That's it, that's the way to get the transcript. Those queries can be modified to fetch the Cumulative GPA. just add one more join with subjects table to get the units/credits, then just calculate the SUM(subjects.unit * enrollments.num_grade) / SUM(subjects.unit)
You can also calculate the Cumulative GPA per semester by adding the semester for the partition/group by part. If you wonder, why do I write this post? because I have googled and not found any posts that write about this.
