SQL Aggregate Functions and Grouping 2 — Questions and Answers
Question 1: Which aggregate function returns the total number of rows, including those with NULL values in the counted column?
- COUNT(column)
- COUNT(*) (Correct answer)
- COUNT(DISTINCT column)
- SUM(column)
Correct answer: COUNT(*)
COUNT(*) counts every row regardless of NULLs, while COUNT(column) skips NULLs.
Question 2: What does AVG(salary) ignore when computing the average?
- Zero values
- NULL values (Correct answer)
- Negative values
- Duplicate values
Correct answer: NULL values
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?
- salary
- department (Correct answer)
- hire_date
- employee_id
Correct answer: department
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?
- WHERE
- HAVING (Correct answer)
- ON
- FILTER
Correct answer: HAVING
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?
- 1
- 2 (Correct answer)
- 3
- 4
Correct answer: 2
DISTINCT counts unique non-NULL values, so 'NY' and 'LA' give 2.
Question 6: Which function returns the largest value in a numeric column?
- TOP
- MAX (Correct answer)
- GREATEST
- HIGH
Correct answer: MAX
MAX returns the highest value among the rows in each group or set.
Question 7: In the logical order of execution, when is GROUP BY processed relative to WHERE?
- Before WHERE
- After WHERE (Correct answer)
- Same time as WHERE
- After SELECT
Correct answer: After WHERE
WHERE filters rows first, then GROUP BY groups the remaining rows.
SQL Aggregate Functions and Grouping 2
Which aggregate function returns the total number of rows, including those with NULL values in the counted column?