13 lines
No EOL
534 B
SQL
13 lines
No EOL
534 B
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); |