The SQL Language (Part-4)

Aggregate Functions:

PostgreSQL Supports aggregate functions, It computes single result from multiple input rows.
Aggregates to compute:

• Count
• Sum
• avg(average)
• max(maximum)
• min(minimum) over the set of rows.

It is important to understand the interaction between aggregates and SQL's WHERE and HAVING clauses.
The fundamental difference between WHERE and HAVING is this: WHERE selects input rows before groups
and aggregates are computed (thus, it controls which rows go into the aggregate computation), whereas
HAVING selects group rows after groups and aggregates are computed. Thus, the WHERE clause must not
contain aggregate functions; it makes no sense to try to use an aggregate to determine which rows will
be inputs to the aggregates. On the other hand, the HAVING clause always contains aggregate functions.
(Strictly speaking, you are allowed to write a HAVING clause that doesn't use aggregates, but it's seldom
useful. The same condition could be used more efficiently at the WHERE stage.)

Updates

You can update existing rows using the UPDATE command. Suppose you discover the temperature readings are all off by 2 degrees after November 28. You can correct the data as follows:
UPDATE weather
SET temp_hi = temp_hi - 2, temp_lo = temp_lo - 2
WHERE date > '1994-11-28';

Deletions

Rows can be removed from the table using Delete command.
If you no longer want any query to execute you can use Delete command:
DELETE FROM weather WHERE city = 'Hayward';