SQL Aggregation MCQs

Aggregate functions generally ignore NULL inputs, but COUNT(*) counts rows regardless of NULL values.

Question 1

Query

SELECT region, SUM(amount)
FROM transactions
GROUP BY region;

Correct Answer

North → 1500, South → 1500, East → 3200

Explanation

Step 1 — Group rows by region

North

1000
500

SUM:

1000 + 500 = 1500

South

1500
NULL

SUM ignores NULL values.

1500 = 1500

East

2000
1200

SUM:

2000 + 1200 = 3200

Important Concept

Aggregate functions like:

• SUM()
• AVG()
• MIN()
• MAX()

ignore NULL values.

Question 2

Query

SELECT user_id, COUNT(*)
FROM transactions
GROUP BY user_id;

Correct Answer

User 101 has count 2, others 1

Explanation

COUNT(*) counts ALL rows.

User 101 appears twice

txn_id 1
txn_id 3

So:

101 → 2

All others appear once.

Final Output

Important Concept

COUNT(*)

counts rows regardless of:

• NULL
• duplicates
• missing values

Question 3

Query

SELECT MIN(amount), MAX(amount)
FROM transactions;

Correct Answer

500, 2000

Explanation

Amounts:

1000
1500
500
2000
NULL
1200

NULL ignored.

MIN(amount)

Smallest non-null value:

500

MAX(amount)

Largest non-null value:

2000

Important Concept

MIN and MAX automatically ignore NULL values.

Question 4

Query

SELECT region, MIN(tax)
FROM transactions
GROUP BY region;

Correct Answer

North → 50, South → 80, East → 200

Explanation

North

Taxes:

100
50

MIN:

50

South

Taxes:

NULL
80

NULL ignored.

MIN:

80

East

Taxes:

200
NULL

NULL ignored.

MIN:

200

Important Concept

Aggregate functions ignore NULL.

If at least one non-null value exists, aggregation works normally.

Question 5

Query

SELECT SUM(amount * tax)
FROM transactions;

Correct Answer

525000

Explanation

Row Calculations

SUM ignores NULL

100000 + 25000 + 400000
= 525000

Final Answer

525000

Important Concept

If arithmetic contains NULL:

number * NULL = NULL

Then SUM ignores those NULL results.

Question 6

Query

SELECT region, COUNT(*)
FROM transactions
GROUP BY region
HAVING COUNT(*) > 1;

Correct Answer

All regions

Explanation

Count per region

All are greater than 1.

So all regions remain.

Important Concept

HAVING filters AFTER grouping and aggregation.

Question 7

Query

SELECT region, AVG(amount)
FROM transactions
GROUP BY region
HAVING AVG(amount) > 1200;

Correct Answer

HAVING filters aggregated results

Explanation

AVG(amount)

is an aggregate function.

Aggregate results are created AFTER GROUP BY.

WHERE executes before aggregation.

So aggregate filtering must happen in HAVING.

SQL Execution Order

FROM
WHERE
GROUP BY
AGGREGATION
HAVING
SELECT
ORDER BY

Important Rule

WHERE  → filters rows
HAVING → filters groups

Question 8

Query

SELECT COUNT(*)
FROM orders
WHERE price IS NULL;

Correct Answer

1

Explanation

Check rows where:

price IS NULL

Only this row matches:

So:

COUNT(*) = 1

Important Concept

To check NULL:

Correct:

IS NULL

Wrong:

= NULL

Because NULL means unknown.