CREATE FUNCTION f_GradeToNum (a_Grade varchar(1))
RETURNS INTEGER
BEGIN
CASE a_Grade
WHEN 'A' THEN RETURN 5;
WHEN 'B' THEN RETURN 4;
WHEN 'C' THEN RETURN 3;
WHEN 'D' THEN RETURN 2;
WHEN 'E' THEN RETURN 1;
ELSE RETURN 0;
END;
END;

CREATE FUNCTION f_AvgGrade (Id INTEGER)
RETURNS DOUBLE
BEGIN
DECLARE AverageGrade DOUBLE;
SELECT AVG(f_GradeToNum(FinalGrade)) INTO AverageGrade
FROM Registration WHERE PersonId = Id;
RETURN AverageGrade;
END;

CREATE VIEW v_top20Students (FirstName, LastName, AverageGrade) AS
SELECT TOP 20 FirstName, LastName, f_AvgGrade(Id) as AverageGrade
FROM Person ORDER BY AverageGrade DESC;