Principles and design of database management System Note 1

1. A problem was identified in the beginner's database.

"Find out what the average sailor's age is the lowest among all ranks"

Error wording:

SELECT s.rating

From Sailor S

WHERE AVG (s.age) = (SELECT MIN (AVG (s2.age))

From Sailor S2

GROUP by S2.rating)

In the past has been the aggregation function can not have the aggregation function provisions troubled, in fact, even if the legal, elected and useless min, because it is selected in the group rather than in the grouping set selected.

Correct wording: (produce a temporary table)

SELECT Temp.rating,temp.avgage

From (SELECT s.rating,avg (s.age) as Avgage

From Sailor S

GROUP by s.rating) as Temp

WHERE temp.avgage= (SELECT MIN (temp.avgage) from Temp)

Select a temp table with rating and average age avgage, then select min (avgage) with nesting

Another method to be tested:

SELECT Temp.rating,temp.avgage

From (SELECT s.rating,avg (s.age) as Avgage

From Sailor S

GROUP by s.rating) as Temp

WHERE temp.avgage= (SELECT MIN (temp.avgage) from Temp)

Principles and design of database management System Note 1