CODEWITHSUNDEEP
sqlsyntax
Heisenberg
Heisenberg

Reputation: 8806

Not understanding this SQL query

I'm following the Stanford's Coursera database course, and there's this query I do not understand.

The instructor says that it returns colleges paired with the highest GPA of their applicants.

select distinct College.cName, state, GPA
from College, Apply, Student
where College.cName = Apply.cName
  and Apply.sID = Student.sID
  and GPA >= all
          (select GPA from Student, Apply
           where Student.sID = Apply.sID
             and Apply.cName = College.cName);

I don't understand the GPA >= all part. Doesn't that mean we are looking for rows with GPA that is >= ALL applicants, instead of just applicants to a particular college? In other words, I think the query is returning colleges that have applicants with GPA highest everywhere.

Upvotes: 0

Views: 100

Answers (3)

MinhD
MinhD

Reputation: 1810

Let's say:

GPA >= all is the same with = MAX (GPA): It will select only the college that have GPA greater or equal to all others college GPA.

Below query is the same, but easier to understand:

select distinct College.cName, state, GPA
from College, Apply, Student
where College.cName = Apply.cName
  and Apply.sID = Student.sID
  and GPA =
          (select MAX(GPA) from Student, Apply
           where Student.sID = Apply.sID
             and Apply.cName = College.cName);

Upvotes: 0

Kevin Roche
Kevin Roche

Reputation: 377

No, because the select statement following the "GPA >= ALL" statement is correlated to the outer clause - they share the same table correlation names "College" "Apply" "Student". That means that you'll effectively get a a look up against the "College" currently being referenced by the WHERE clause - i.e. for College_A we look up all students within College_A, for College_B we look up all students within College_B etc.

Upvotes: 0

Smandoli
Smandoli

Reputation: 7019

College.cName = Apply.cName

This provides the limit to a particular college. Try removing it.

Upvotes: 2

Related Questions