SQL Aggregate Functions and Grouping 2

Question 1

Which aggregate function returns the total number of rows, including those with NULL values in the counted column?

Accepted Answer

COUNT(*)

Answer

COUNT(*) counts every row regardless of NULLs, while COUNT(column) skips NULLs.

Question 2

What does AVG(salary) ignore when computing the average?

Accepted Answer

NULL values

Answer

AVG ignores NULL rows entirely, dividing the sum by the count of non-NULL values.

Question 3

In a query with GROUP BY department, which column can appear in SELECT without being aggregated?

Accepted Answer

department

Answer

Only the grouping column(s) may appear ungrouped and unaggregated in the SELECT list.

Question 4

Which clause filters groups based on an aggregate condition like SUM(amount) > 1000?

Accepted Answer

HAVING

Answer

HAVING applies conditions to grouped results after aggregation, unlike WHERE.

Question 5

What is the result of COUNT(DISTINCT city) on a column with values 'NY','NY','LA',NULL?

Accepted Answer

2

Answer

DISTINCT counts unique non-NULL values, so 'NY' and 'LA' give 2.

SQL Practice Test