HW:$1\mathrm{.}14.$ Only the Brave: Finding the GPA Gladiators for Scholarships

Consider the following relations:

Students Enrollments Courses

Name Type

NetId VARCHAR$(10)$

FirstName VARCHAR$(255)$

LastName VARCHAR$(255)$

Department VARCHAR$(100)$

Name Type

NetId VARCHAR$(10)$

CRN INT

Credits INT

Score REAL

Name Type

CRN INT

Title VARCHAR$(255)$

Department VARCHAR$(100)$

Instructor VARCHAR$(255)$

We want to nominate excellent students for a scholarship, but there is a very limited number of students we can nominate. Therefore, we only want the best of the best students. Students' scores and GPAs obviously matter, but we don't want students who have only been taking easy classes. To find students that fit this criteria, we want students who have taken at least $2$ "difficult" courses, and that student's average score across all their classes taken must be at or above the average score of students who took at least $4$ "difficult" courses. For a course to be considered "difficult," the difference between the highest and lowest score in the course must be at least $50($inclusive$).$

Write an SQL query that returns, for every student that should be nominated for the scholarship, the student's NetId, LastName and the average Score $($as AverageScore$)$ of this student $($taken across all courses that the student is enrolled in$).$ While calculating the average scores, weight the scores by the credits of the courses. That is$,$ AverageScore $=\backslash$Sigma $($Score $*$ Credits$)/\backslash$Sigma $($Credits$).$ Finally, ROUND AverageScore to the nearest $2$ digits using a call to ROUND$(???,2).$

Return the results in the decreasing order of AverageScore and increasing order of NetId.