Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- 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;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement