Simple system to track grades and easily calculate GPA (cumulative, specific year, or specific term)

gpa.sql

CREATE TABLE classes (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    year INTEGER NOT NULL,
    term INTEGER NOT NULL,
    grade DECIMAL NOT NULL,
    credits INTEGER NOT NULL
);

CREATE OR REPLACE FUNCTION gpa() RETURNS DECIMAL
  LANGUAGE SQL
  AS $$
    SELECT SUM(grade * credits) / SUM(credits) FROM classes;
  $$;

CREATE OR REPLACE FUNCTION gpa(year INTEGER) RETURNS DECIMAL
  LANGUAGE SQL
  AS $$
    SELECT SUM(grade * credits) / SUM(credits) FROM classes WHERE year=$1;
  $$;

CREATE OR REPLACE FUNCTION gpa(year INTEGER, term INTEGER) RETURNS DECIMAL
  LANGUAGE SQL
  AS $$
    SELECT SUM(grade * credits) / SUM(credits) FROM classes WHERE year=$1 AND term=$2;
  $$;


COMMENT ON COLUMN classes.name IS 'Class name';
COMMENT ON COLUMN classes.year IS 'Academic year';
COMMENT ON COLUMN classes.term IS 'Number of term within academic year';
COMMENT ON COLUMN classes.grade IS 'Final grade in grade points, e.g. A = 4.0, B = 3.0, etc.';
COMMENT ON COLUMN classes.credits IS 'Number of credits earned from the class'; 

COMMENT ON FUNCTION gpa() IS 'Calculate cumulative GPA';
COMMENT ON FUNCTION gpa(year INTEGER) IS 'Calculate gpa for specific academic year';
COMMENT ON FUNCTION gpa(year INTEGER, term INTEGER) IS 'Calculate gpa for specific term in an academic year';