Labsheet02

PostgreSQL7.4

Use of SQL language in PostgreSQL (continued)
Most of the contents of this Labsheet is taken from the PostgreSQL 7.4.17 Documentation. It is available at the following link: http://www.postgresql.org/docs/7.4/interactive/index.html

Aggregate Functions
Common aggregate functions are: count sum avg (average) max (maximum) min (minimum)

Note: avg, min and max ignore tuples that have a null value for the specified attribute, but count considers null values. As an example, we can find the highest low-temperature reading anywhere with Execute the following query using postgreSQL and see the results:
SELECT max(temp_lo) FROM weather;

Note: The aggregate max cannot be used in the WHERE clause
(This restriction exists because the WHERE clause determines the rows that will go into the aggregation stage; so it has to be evaluated before aggregate functions are computed.) Aggregates are also very useful in combination with GROUP BY clauses. For example, we can get the maximum low temperature observed in each city with Execute the following query using postgreSQL and see the results:
SELECT city, max(temp_lo) FROM weather GROUP BY city;

which gives us one output row per city. Each aggregate result is computed over the table rows matching that city. We can filter these grouped rows using HAVING: Execute the following query using postgreSQL and see the results:
SELECT city, max(temp_lo) FROM weather GROUP BY city HAVING max(temp_lo) < 40;

which gives us the same results for only the cities that have all temp_lo values below 40. Finally, if we only care about cities whose names begin with “S”, we might do Execute the following query using postgreSQL and see the results:
SELECT city, max(temp_lo) FROM weather WHERE city LIKE ’S%’ GROUP BY city Begum Durgahee HAVING max(temp_lo) < 40;

1

Labsheet02

PostgreSQL7.4

The