Question 1

Star vs column in COUNT

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. '2' catches anyone who confuses COUNT(*) with COUNT(DISTINCT customer): Alice appears twice, so they think duplicates are collapsed, leaving Alice, Bob, Carol = 3... or if they also drop the NULL row they get 2. '450' catches anyone who runs SUM(amount) instead - 100 + 200 + 150 = 450 (the NULL row contributes nothing).

Question 2

What does SUM do with a clean 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. '10' is the trap for anyone who sums only the first two rows (3 + 7). '4' is the trap for anyone who confuses SUM with COUNT(*) and returns the row count instead.

Question 3

MIN and MAX in the same SELECT

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 4

Which value does AVG actually divide by?

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. '85' catches anyone who averages only two of the three rows, dropping the lowest: (80 + 90) / 2. '90' catches anyone who confuses AVG(score) with MAX(score) - the highest value in the column. '240' catches anyone who returns the raw SUM without dividing by the row count.

Question 5

Only Alice's orders this time

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 6

The missing manager trap

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

Shipped orders only - how many?

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

Does alphabetical order apply to MIN and MAX?

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.

Question 9

The repeat customer problem

Accepted Answer

COUNT(DISTINCT column) counts unique non-NULL values only. Alice appears twice but is counted once. The distinct customers are Alice, Bob, and Carol - 3 unique values. This is useful for answering 'how many unique customers placed an order?'

Question 10

An all-NULL column hits SUM

Accepted Answer

When every value in the column is NULL, SUM returns NULL - not 0. There are no non-NULL values to add. If you need 0 instead, wrap it: COALESCE(SUM(amount), 0). This behavior applies to all aggregate functions except COUNT(*), which always returns a number.

Question 11

What does AVG use as its denominator?

Accepted Answer

AVG ignores NULLs in both the numerator and denominator. It computes (80 + 90) / 2 = 85, not (80 + 90) / 4 = 42.5. This means AVG and SUM/COUNT(*) can give inconsistent pictures of the data when NULLs are present. If you want NULLs to count as 0, use AVG(COALESCE(score, 0)) or SUM(score) / COUNT(*).

Question 12

Mixing aggregate and non-aggregate columns

Accepted Answer

When you mix aggregate functions with non-aggregate columns, every non-aggregate column must appear in GROUP BY. Here COUNT(*) returns one number for the whole table, but name has one value per row - the database can't collapse multiple names into one result row without knowing how to group them. Fix this with GROUP BY name (count per name) or remove name from the SELECT list (count all rows).

Question 13

Valid fix for non-grouped column

Accepted Answer

The problem is name - it is not in GROUP BY and not wrapped in an aggregate. The cleanest fix is to remove name from the SELECT list so every remaining column is either in GROUP BY or aggregated. Dropping GROUP BY entirely (option B) still fails because MAX(salary) is an aggregate while name is not, so the same error remains. Adding HAVING name IS NOT NULL (option C) fails because name is still not grouped or aggregated - HAVING cannot reference it either. GROUP BY dept, MAX(salary) (option D) is a syntax error - you cannot GROUP BY an aggregate expression.

Aggregation