Question 1

SUM of a column

Accepted Answer

SUM adds all non-NULL values in the column. 3 + 7 + 2 = 12. Like COUNT, SUM ignores NULL values. If a row had NULL quantity, it would be skipped rather than treated as 0.

Question 2

AVG of a column

Accepted Answer

AVG sums all non-NULL values and divides by the count of non-NULL values. (80 + 90 + 70) / 3 = 240 / 3 = 80. AVG also ignores NULLs - a NULL row does not count toward either the sum or the divisor.

Question 3

Aggregate with WHERE

Accepted Answer

WHERE filters rows before the aggregate function runs. Only Alice's two orders (100 and 300) are included. SUM(100 + 300) = 400. Bob's order is excluded by the WHERE clause.

Question 4

COUNT(*) counts all rows

Accepted Answer

COUNT(*) counts every row in the table regardless of NULL values. All 4 rows exist, so the result is 4. This is different from COUNT(column) - COUNT(amount) would return 3 because it ignores the NULL in Carol's row.

Question 5

MIN and MAX

Accepted Answer

MIN returns the smallest non-NULL value and MAX returns the largest. MIN(price) = 10.00 (Alpha), MAX(price) = 40.00 (Beta). Both functions work on text columns too - MIN returns the alphabetically first value and MAX the last.

Question 6

COUNT(column) ignores NULLs

Accepted Answer

COUNT(column) counts only non-NULL values in that column. Alice has a NULL manager_id, so she is excluded. Bob and Carol both have manager_id = 1, giving a count of 2. Use COUNT(*) when you want to count all rows including those with NULLs.

Question 7

COUNT with WHERE

Accepted Answer

WHERE applies both conditions before COUNT(*) sees any rows. Out of Alice's three orders, only two have status 'shipped' (rows 1 and 3). Row 4 is Alice's pending order and is excluded. COUNT(*) then counts the 2 remaining rows.

Question 8

MIN on a text column

Accepted Answer

MIN and MAX work on text by applying the column's collation. For plain ASCII names, that means alphabetical order. Alice comes first alphabetically, Zara comes last, so MIN(name) = 'Alice' and MAX(name) = 'Zara'. This is useful for finding the first or last value alphabetically without sorting and limiting.