Basic Aggregate Functions - SQL

1. What does SELECT AVG(dept_avg) FROM (SELECT department_id, AVG(salary) AS dept_avg FROM employee GROUP BY department_id) AS d; return?
   Answer: It returns $70$ because the two department averages receive equal weight. When you see a subquery in the FROM clause, ask yourself: what rows does the outer query actually operate on? Here, the inner query produces one row per department, not one row per employee — and that distinction is everything. The inner query SELECT department_id, AVG(salary) FROM employee GROUP BY department_id returns exactly two rows: Department A with an average of $\frac{30+50}{2} = 40$, and Department B with an average of $100$. The outer AVG(dept_avg) then averages those two rows, giving $\frac{40+100}{2} = 70$. So A is correct — each department contributes exactly one value to the outer average, regardless of how many employees it has. B is wrong because it describes a flat average across all three salaries: $\frac{30+50+100}{3} = 60$. That would be the result if you simply wrote AVG(salary) without any grouping — the subquery structure specifically prevents this by collapsing each department first. C is wrong because SQL's AVG function doesn't return the maximum value; no such behavior exists. D is wrong because Department A's size doesn't give it extra influence in the outer query — once collapsed to a single average of $40$, it counts just once, the same as Department B. A useful rule of thumb: the outer query sees rows, not raw data. Whatever the subquery outputs becomes the dataset for the outer query. Count the rows the subquery produces, and you'll always know what the outer aggregation is working with.
2. Which output is produced by SELECT sensor_id, SUM(reading) FROM sensor_data GROUP BY sensor_id HAVING COUNT(reading) = COUNT(*);?
   Answer: Only sensor S3, with an aggregated reading total of $10$. When you see a HAVING clause comparing two aggregate functions, your job is to figure out which groups satisfy that condition before worrying about SUM values. The key concept here is how SQL handles NULL in aggregate functions. COUNT(*) counts every row in a group, including rows with NULL values. COUNT(reading), however, counts only non-NULL values in the reading column. So the condition HAVING COUNT(reading) = COUNT(*) is essentially asking: "Which groups have zero NULLs?" — meaning every row in that group has a non-NULL reading. Let's check each sensor. S1 has two NULL readings: COUNT(reading) = 0, COUNT(*) = 2 → not equal, excluded. S2 has one non-NULL ($5$) and one NULL: COUNT(reading) = 1, COUNT(*) = 2 → not equal, excluded. S3 has two non-NULL readings ($2$ and $8$): COUNT(reading) = 2, COUNT(*) = 2 → equal, included. SUM(reading) for S3 is $2 + 8 = 10$. That makes D the correct answer. Answer A is wrong because S1 fails the HAVING condition and should be excluded entirely, not returned with a NULL sum. Answer B is wrong because S2 also fails — having even one NULL reading breaks the equality. Answer C is wrong for the same reason: the HAVING clause filters out S1 and S2, so returning all three sensors misunderstands the filter entirely. Remember this pattern: COUNT(column) = COUNT(*) is a clean SQL idiom for filtering groups that contain no NULLs in that column.
3. What row is returned by SELECT SUM(amount), SUM(DISTINCT amount), COUNT(DISTINCT amount) FROM invoice;? The choices list values in select-list order.
   Answer: $(140, 100, 2)$ When working with aggregate functions in SQL, you need to track three behaviors carefully: how SUM handles duplicates, how DISTINCT filters them, and how NULL values are treated across all aggregates. Start with the raw data: $40, 40, \text{NULL}, 60$. SUM(amount) adds all non-NULL values, so $40 + 40 + 60 = 140$. NULL is silently ignored — this is standard SQL behavior. SUM(DISTINCT amount) first eliminates duplicate values, leaving the set $\{40, 60\}$, then sums: $40 + 60 = 100$. Finally, COUNT(DISTINCT amount) counts unique non-NULL values. The distinct non-NULL values are $40$ and $60$, giving a count of $2$. That produces $(140, 100, 2)$, which is B. Choice A $(140, 140, 2)$ treats SUM(DISTINCT amount) as if DISTINCT has no effect — it adds both 40s, ignoring that they're duplicates. Choice C $(100, 100, 2)$ applies DISTINCT logic to the plain SUM as well, incorrectly deduplicating the first column. Choice D $(140, 100, 3)$ gets the first two values right but counts NULL as a distinct value in COUNT(DISTINCT amount) — NULL is never counted, regardless of DISTINCT. A reliable rule to memorize: **NULL is invisible to all aggregate functions** — it's excluded from SUM, AVG, and COUNT alike. Separately, DISTINCT removes duplicate non-NULL values before aggregating. Keep these two rules distinct in your mind, and questions like this become straightforward.
4. What row is returned by SELECT COUNT(CASE WHEN status = 'paid' THEN 1 ELSE 0 END), SUM(CASE WHEN status = 'paid' THEN 1 ELSE 0 END) FROM invoice;? The choices list values in select-list order.
   Answer: $(4, 2)$ When working with aggregate functions and CASE expressions, you need to carefully separate what COUNT does from what SUM does — they behave very differently. Here's the key insight: COUNT counts rows, while SUM adds values. The CASE WHEN status = 'paid' THEN 1 ELSE 0 END expression never returns NULL — it always returns either 1 or 0. Because COUNT ignores NULL values but this expression produces no NULLs, COUNT will count every single row in the table, including the row where status IS NULL (which hits the ELSE 0 branch). With four rows total, COUNT(...) returns 4. For SUM, the expression produces 1 for each 'paid' row and 0 for everything else. The two 'paid' rows contribute 1 + 1 = 2, the 'pending' row contributes 0, and the NULL status row also contributes 0. So SUM(...) returns 2. The result is $(4, 2)$, making D correct. A $(2, 2)$ is wrong because it treats COUNT as if it only counts rows matching 'paid' — that's what COUNT(CASE WHEN status = 'paid' THEN 1 END) would do (returning NULL instead of 0 for non-matches). B $(3, 2)$ wrongly excludes the NULL-status row from COUNT, forgetting that the ELSE 0 clause handles it. C $(4, 4)$ confuses SUM with COUNT, imagining every row contributes 1. The study tip: whenever a CASE expression includes an ELSE clause that returns a non-NULL value, COUNT will always equal the total number of rows — nothing is filtered out.
5. What row is returned by SELECT AVG(hours_worked), SUM(hours_worked) / COUNT(*) FROM work_log;? The choices list the values in select-list order.
   Answer: $(8, 6)$ Whenever you see a question mixing AVG() with a manual SUM()/COUNT(*) calculation, the key concept being tested is how SQL handles NULL values differently across aggregate functions. Here's the critical rule: AVG(), SUM(), and COUNT(column_name) all ignore NULLs, but COUNT(*) counts every row, including those with NULL values. With values $8$, NULL, $6$, and $10$: - AVG(hours_worked) ignores the NULL and computes $\frac{8 + 6 + 10}{3} = 8$ - SUM(hours_worked) also ignores the NULL: $8 + 6 + 10 = 24$ - COUNT(*) counts all four rows, including the NULL row: $4$ - So SUM(hours_worked) / COUNT(*) = $24 / 4 = 6$ The result is $(8, 6)$, confirming answer B. Choice A $(8, 8)$ incorrectly assumes COUNT(*) returns 3 (as if it ignored the NULL), making both expressions equal. Choice C $(6, 8)$ swaps the two values entirely, misunderstanding which expression produces which result. Choice D $(6, 6)$ assumes both expressions skip the NULL row, meaning both would compute $24/4=6$ — but AVG() correctly excludes the NULL from its denominator, yielding 8, not 6. The study tip to remember: **COUNT(*) is the odd one out** — it never ignores NULLs. Any other aggregate with a column argument (COUNT(col), SUM(col), AVG(col)) silently skips NULLs. This distinction is a favorite exam trap.
6. What row is returned by SELECT COUNT(*) AS all_rows, COUNT(customer_id) AS known_customers, COUNT(DISTINCT customer_id) AS unique_customers FROM visits;? The choices list values in alias order.
   Answer: $(5, 3, 2)$ When working with COUNT in SQL, the key is remembering that each variant behaves differently with NULL values and duplicates — and this question tests all three at once. The table has five rows with customer_id values: NULL, $7$, $7$, NULL, $9$. Let's walk through each alias. COUNT(*) counts every row regardless of content, so it returns $5$. COUNT(customer_id) counts only non-NULL values in that column — the two NULL rows are excluded, leaving $7$, $7$, and $9$, which gives $3$. COUNT(DISTINCT customer_id) counts only unique non-NULL values — the two $7$s collapse into one, yielding $7$ and $9$, so the result is $2$. The returned row is $(5, 3, 2)$, confirming B is correct. Choice A $(5, 2, 2)$ mistakes COUNT(customer_id) for COUNT(DISTINCT customer_id) — it returns $2$ as if duplicates were already removed, but COUNT without DISTINCT counts all non-NULL values including repeats. Choice C $(3, 3, 2)$ incorrectly applies NULL-exclusion logic to COUNT(*) as well — but COUNT(*) never skips rows, even if every column is NULL. Choice D $(5, 3, 3)$ counts $9$ as a third distinct value but forgets that both $7$s are the same value and only count once under DISTINCT. A useful rule of thumb: COUNT(*) = total rows, COUNT(col) = non-NULL rows, COUNT(DISTINCT col) = unique non-NULL rows. Memorize this trio and you'll handle nearly every COUNT variation on the exam.
7. What row is returned by SELECT COUNT(*), COUNT(price), SUM(price), AVG(price) FROM product WHERE category = 'retired';? The choices list values in select-list order.
   Answer: (0, 0, NULL, NULL) When aggregate functions encounter no matching rows, they don't all behave the same way — and that's exactly what this question tests. COUNT(*) counts rows regardless of content, so with zero matching rows it returns 0. COUNT(price) counts non-NULL values of price, which is also 0 when there are no rows. So far, so good — both COUNTs return 0. Here's where it gets interesting: SUM and AVG operate on a set of values. When that set is completely empty (no rows qualify), there are no values to sum or average. SQL returns NULL for both — not 0 — because 0 would be a misleading answer. Summing nothing is undefined, not zero. This makes C correct: (0, 0, NULL, NULL). Choice A is wrong because it assumes SUM and AVG return 0 on empty sets — a common but incorrect intuition. Choice B is wrong for the same reason regarding AVG, but also gets SUM right as NULL while incorrectly leaving AVG as 0. Choice D is wrong because it assumes COUNT(*) returns NULL on empty sets — but COUNT is specifically designed to always return an integer, never NULL. Study tip: Memorize this rule: COUNT always returns a number (never NULL), while SUM, AVG, MIN, and MAX return NULL when applied to an empty set. This distinction appears frequently on SQL exams and in real debugging scenarios.
8. What row is returned by SELECT COUNT(quantity * unit_price), SUM(quantity * unit_price), AVG(quantity * unit_price) FROM order_line;? The choices list values in select-list order.
   Answer: $(2, 25, 12.5)$ When SQL aggregate functions encounter NULL values, they skip them entirely — this is the core concept being tested here. Before any aggregation happens, SQL must evaluate quantity * unit_price for each row. Any arithmetic involving NULL produces NULL, so the four computed values are $20$, NULL, NULL, and $5$. Now the aggregates operate on this derived set $(20, \text{NULL}, \text{NULL}, 5)$. COUNT, SUM, and AVG all ignore NULLs, leaving only $\{20, 5\}$ as the effective input. COUNT returns $2$ (two non-NULL products), SUM returns $25$, and AVG returns $25 \div 2 = 12.5$. That gives you $(2, 25, 12.5)$, confirming D is correct. Choice A is wrong on two counts: it reports COUNT = 4, as if NULLs were counted, and calculates AVG by dividing 25 by 4 rather than by the number of non-NULL values. Choice B gets COUNT and SUM right but makes the same averaging mistake — dividing $25$ by $4$ instead of $2$. Choice C wrongly assumes that because some inputs are NULL, the entire SUM and AVG collapse to NULL; that would only happen if all inputs were NULL. A handy rule to memorize: NULL propagates through arithmetic but is ignored by aggregates. When you see COUNT, SUM, or AVG applied to an expression that might produce NULLs, always identify how many non-NULL results that expression yields — that's the denominator AVG (and COUNT) will actually use.
9. The query SELECT region, MIN(shipped_on), MAX(shipped_on) FROM shipment GROUP BY region ORDER BY region; is executed. Which result is correct?
   Answer: East has 2026-01-04 and 2026-01-10; West has NULL and NULL. When working with aggregate functions like MIN() and MAX() in SQL, the critical concept to understand is how these functions handle NULL values within a group. SQL aggregate functions ignore NULL values during their calculations. So if a group contains the values 2026-01-10, NULL, and 2026-01-04, SQL skips the NULL and evaluates only the non-null dates. The minimum becomes 2026-01-04 and the maximum becomes 2026-01-10. This makes A the correct answer — East correctly returns 2026-01-04 and 2026-01-10 after nulls are ignored. Now, what about West, where all values are NULL? When every value in a group is NULL, there are no non-null values to aggregate, so MIN() and MAX() both return NULL. West still appears as a row because GROUP BY produces one row per distinct group regardless — it just has no non-null data to aggregate. Answer A correctly reflects this. B is wrong because it drops the West row entirely. GROUP BY never eliminates groups just because their aggregate results are null — you'd need a HAVING clause to filter groups. C is wrong because it assumes the NULL in East's data contaminates the entire group, which is not how SQL aggregation works. D is wrong because it swaps MIN and MAX for East — 2026-01-10 is the maximum, not the minimum. Remember this rule: aggregate functions silently skip NULLs, but a group itself is never dropped just for having them.
10. What row is returned by SELECT COUNT(*), COUNT(DISTINCT o.order_id), SUM(o.total) FROM orders AS o JOIN payments AS p ON p.order_id = o.order_id;? The choices list values in select-list order.
    Answer: $(3, 2, 180)$ When a JOIN produces more rows than the original table, aggregate functions operate on those expanded rows — not the original table. That's the key concept here. After the JOIN, here's what the result set looks like: order $1$ (total = $50$) appears twice (once per payment row), and order $2$ (total = $80$) appears once. That gives you 3 joined rows total. Now apply each aggregate to those 3 rows: - $\text{COUNT(*)}$ counts all rows → $3$ - $\text{COUNT(DISTINCT o.order\_id)}$ counts unique order IDs ($1$ and $2$) → $2$ - $\text{SUM(o.total)}$ adds the total column across all 3 rows: $50 + 50 + 80 = 180$ So the correct answer is C $(3, 2, 180)$. A $(3, 2, 130)$ gets $\text{COUNT(*)}$ and $\text{COUNT(DISTINCT)}$ right but calculates $\text{SUM}$ as if each order appeared only once ($50 + 80 = 130$), ignoring that order $1$ is duplicated in the join. B $(2, 2, 130)$ makes two mistakes: it counts only the distinct orders for $\text{COUNT(*)}$ instead of total rows, and it also under-calculates the SUM. D $(3, 3, 180)$ gets the SUM right but miscounts $\text{COUNT(DISTINCT o.order\_id)}$ as $3$, confusing row count with distinct order count. Study tip: Whenever you join a one-to-many relationship, mentally sketch the expanded result set first. Aggregate functions always operate on that expanded set, which is why $\text{SUM}$ can produce seemingly inflated values unless you account for duplicated rows.

Start this quiz