dblabs/n.sql

--Which student has the highest grade average? Advice: define and use a view that
--gives the person number and grade average for each student.

DROP VIEW IF EXISTS StudentsAverages;

CREATE VIEW StudentsAverages AS
SELECT Students.firstName, Students.pNbr, TakenCourses.grade, avg(grade) as averageGrade
FROM Students
JOIN TakenCourses ON Students.pNbr = TakenCourses.pNbr
GROUP BY Students.pNbr;

SELECT firstName, averageGrade FROM StudentsAverages
WHERE averageGrade = (SELECT max(averageGrade) FROM StudentsAverages);