Q.Name the five aggregate functions in SQL and state what each returns. Explain the difference between COUNT(*) and COUNT(column).

Aggregate functions summarise a whole column into one value; this asks you to list them and to distinguish the two forms of COUNT.

Aggregate (group) functions take a whole column of values and return a single summary value. The five standard ones are:

• COUNT() — the number of rows.
• SUM(column) — the total of the values in a numeric column.
• AVG(column) — the average (mean) of the values in a numeric column.
• MAX(column) — the largest value in the column.
• MIN(column) — the smallest value in the column.

Difference between COUNT(*) and COUNT(column): COUNT(*) counts all the rows in the result, including rows that contain NULLs. COUNT(column) counts only the rows where that particular column is not NULL. For our Employee table, COUNT(*) gives 6 (all employees), but COUNT(Salary) gives 5, because one employee's salary is NULL and is therefore not counted. …

Do not assume AVG or SUM treats a NULL as 0 — they simply skip NULLs, so an average is over the count of non-NULL values only. This i …