Aggregate Functions

Lowest GPA of students applying to CS

There are two places the WHERE clause could go:

SELECT min(gpa)
FROM Student INNER JOIN Apply USING (sID)
WHERE major = 'CS';

SELECT min(gpa) FILTER (WHERE major = 'CS')
FROM Student INNER JOIN Apply USING (sID);

If we wanted the overall min to compare to the CS min:

SELECT min(gpa) AS overall_min, min(gpa) FILTER (WHERE major = 'CS') AS cs_min
FROM Student INNER JOIN Apply USING (sID);

Number of students applying to Cornell

There’s a trap here in that some students made several applications. Here using DISTINCT inside the aggregate function helps:

SELECT count(DISTINCT sID)
FROM Apply INNER JOIN college USING (cName);

Amount by which average GPA of students applying to CS exceeds average of students not applying to CS

WITH not_cs AS (
SELECT sID FROM Student
EXCEPT
SELECT sID FROM Apply WHERE major = 'CS'
)
SELECT avg(gpa) FILTER (WHERE sID IN (SELECT DISTINCT sID FROM Apply WHERE major = 'CS'))
  - avg(gpa) FILTER (WHERE sID IN (SELECT sID FROM not_cs))
FROM Student;