GROUP BY and HAVING

• So far, we’ve applied aggregate operators to all (qualifying) tuples. Sometimes, we want to apply them to each of several groups of tuples.

• Consider: Find the age of the youngest sailor for each rating level.

  • In general, we don’t know how many rating levels exist, and what the rating values for these levels are!
  • Suppose we know that rating values go from 1 to 10; we can write 10 queries that look like this (!):

SELECT MIN (S.age)

FROM Sailors S

WHERE S.rating = i

For i = 1, 2, ... , 10:

Previous slide

Next slide

Back to first slide

View graphic version