SQL Aggregate Functions and Grouping 4

Question 1

What does GROUP BY GROUPING SETS((a),(b)) produce?

Accepted Answer

Separate aggregations grouped by a and by b

Answer

GROUPING SETS computes multiple independent groupings in one query.

Question 2

Which clause can use a column alias defined in SELECT in many databases?

Accepted Answer

HAVING

Answer

Some databases allow SELECT aliases in HAVING since it runs after SELECT logically in those engines.

Question 3

What is returned by AVG over the values 2, 4, NULL, 6?

Accepted Answer

4

Answer

AVG ignores NULL, so (2+4+6)/3 = 4.

Question 4

Which is true about ORDER BY in a grouped query?

Accepted Answer

It sorts the final grouped result

Answer

ORDER BY sorts the output after grouping and aggregation are complete.

Question 5

What does the MIN function return for a group of dates?

Accepted Answer

The earliest date

Answer

MIN returns the smallest value, which for dates is the earliest one.

SQL Practice Test