Aggregate functions (COUNT, SUM, AVG, MAX, MIN) in DBMS Theory - Practice Problems & Coding Challenges

Challenge - 5 Problems

🧠 Conceptual

intermediate

Understanding COUNT with NULL values

Consider a table Employees with a column Bonus that contains some NULL values. What will be the result of the query SELECT COUNT(Bonus) FROM Employees;?

A Counts only rows where Bonus is NOT NULL

B Counts only rows where Bonus is NULL

C Counts all rows including those where Bonus is NULL

D Returns the total number of NULL values in Bonus

💡 Hint

COUNT(column) ignores NULL values.

The COUNT function when used with a column counts only the rows where that column is NOT NULL. NULL values are ignored.

📋 Factual

intermediate

SUM function behavior with negative numbers

Given a table Sales with a column Amount containing positive and negative values, what does SELECT SUM(Amount) FROM Sales; return?

A Count of all rows with non-NULL Amount

B Sum of all negative values only

C Sum of all positive values only

D Sum of all values including negatives and positives

💡 Hint

SUM adds all values including negatives.

The SUM function adds all values in the column, including negative numbers, resulting in the total net sum.

🔍 Analysis

advanced

AVG function with NULL values

What will be the output of the query SELECT AVG(Score) FROM Results; if the Score column contains some NULL values?

A Average excluding NULL values

B Count of non-NULL Score values

C NULL as output if any NULL exists

D Average including NULL values counted as zero

💡 Hint

AVG ignores NULL values in calculation.

The AVG function calculates the average of non-NULL values only. NULLs are ignored and do not affect the average.

❓ Comparison

advanced

Difference between MAX and MIN with text data

Given a table Books with a column Title containing book names, what do SELECT MAX(Title) FROM Books; and SELECT MIN(Title) FROM Books; return?

A MAX returns longest title; MIN returns shortest title

B MAX returns title with highest alphabetical order; MIN returns lowest alphabetical order

C MAX returns last inserted title; MIN returns first inserted title

D Both return NULL if any title is NULL

💡 Hint

MAX and MIN on text use alphabetical order.

MAX and MIN when used on text columns return the values that come last and first alphabetically, respectively.

❓ Reasoning

expert

Combining aggregate functions with GROUP BY

Consider a table Orders with columns CustomerID and OrderAmount. What does the query SELECT CustomerID, COUNT(*), SUM(OrderAmount) FROM Orders GROUP BY CustomerID; produce?

A Total number of orders and total amount for all customers combined

B Total number of orders and average amount for all customers combined

C Total number of orders and total amount for each customer

D Total number of customers and total amount of all orders

💡 Hint

GROUP BY groups rows by customer.

The query groups orders by each customer, then counts orders and sums amounts per customer.

Practice

1. Which aggregate function in SQL is used to find the total number of rows in a table?

easy

A. COUNT

B. SUM

C. AVG

D. MAX

Solution

1. Step 1: Understand the purpose of each aggregate function

   COUNT counts the number of rows, SUM adds values, AVG calculates average, and MAX finds the highest value.

2. Step 2: Identify the function that counts rows

   The function that counts rows is COUNT.

3. Final Answer:

   COUNT -> Option A

4. Quick Check:

   Counting rows = COUNT [OK]

Hint: COUNT counts rows, SUM adds numbers [OK]

Common Mistakes:

• Confusing SUM with COUNT
• Using AVG to count rows
• Thinking MAX counts rows

2. Which of the following is the correct syntax to find the average salary from a table named Employees?

easy

A. SELECT SUM(salary) / COUNT(*) FROM Employees;

B. SELECT AVG(salary) FROM Employees;

C. SELECT MEAN(salary) FROM Employees;

D. SELECT AVERAGE(salary) FROM Employees;

Solution

1. Step 1: Recall the standard SQL function for average

   The standard SQL function to calculate average is AVG().

2. Step 2: Check the syntax correctness

   SELECT AVG(salary) FROM Employees; is the correct syntax. Other options use non-standard functions or expressions.

3. Final Answer:

   SELECT AVG(salary) FROM Employees; -> Option B

4. Quick Check:

   Average function = AVG() [OK]

Hint: Use AVG() for average, not AVERAGE or MEAN [OK]

Common Mistakes:

• Using non-standard function names like AVERAGE or MEAN
• Trying to calculate average manually with SUM/COUNT
• Missing parentheses in function call

3. Consider the table Sales with a column amount having values: 100, 200, 300, 400. What will be the result of the query SELECT MAX(amount) FROM Sales;?

medium

A. 400

B. 100

C. 250

D. 1000

Solution

1. Step 1: Identify the values in the column

   The amount column has values 100, 200, 300, and 400.

2. Step 2: Apply the MAX function

   MAX(amount) returns the highest value, which is 400.

3. Final Answer:

   400 -> Option A

4. Quick Check:

   Maximum of 100,200,300,400 = 400 [OK]

Hint: MAX returns highest value in the column [OK]

Common Mistakes:

• Choosing SUM or AVG instead of MAX
• Picking the lowest value by mistake
• Adding values instead of finding max

4. What is wrong with this SQL query?
SELECT SUM(price), COUNT FROM Products;

medium

A. Products table does not exist

B. SUM cannot be used with price column

C. Missing WHERE clause

D. COUNT requires parentheses with a column or *

Solution

1. Step 1: Analyze the use of COUNT function

   The COUNT function must be called with parentheses, like COUNT(*) or COUNT(column_name).

2. Step 2: Identify the syntax error

   The query uses COUNT without parentheses, which is invalid syntax.

3. Final Answer:

   COUNT requires parentheses with a column or * -> Option D

4. Quick Check:

   COUNT() needs parentheses [OK]

Hint: Always use COUNT() with parentheses [OK]

Common Mistakes:

• Writing COUNT without parentheses
• Assuming SUM can't be used on price
• Thinking WHERE clause is mandatory

5. You have a table Orders with columns order_id, customer_id, and amount. You want to find the average order amount for each customer. Which SQL query correctly uses aggregate functions to achieve this?

hard

A. SELECT AVG(amount) FROM Orders WHERE customer_id;

B. SELECT customer_id, SUM(amount) FROM Orders;

C. SELECT customer_id, AVG(amount) FROM Orders GROUP BY customer_id;

D. SELECT customer_id, COUNT(amount) FROM Orders GROUP BY order_id;

Solution

1. Step 1: Understand the requirement

   We want the average order amount per customer, so we need to group data by customer_id and calculate average amount.

2. Step 2: Check each query's correctness

   SELECT customer_id, AVG(amount) FROM Orders GROUP BY customer_id; groups by customer_id and uses AVG(amount), which is correct. SELECT customer_id, SUM(amount) FROM Orders; sums amounts but does not group, so it gives total sum only. SELECT AVG(amount) FROM Orders WHERE customer_id; has invalid WHERE clause usage. SELECT customer_id, COUNT(amount) FROM Orders GROUP BY order_id; groups by order_id, which is wrong for per customer aggregation.

3. Final Answer:

   SELECT customer_id, AVG(amount) FROM Orders GROUP BY customer_id; -> Option C

4. Quick Check:

   Group by customer_id + AVG(amount) [OK]

Hint: Use GROUP BY with AVG for per-group averages [OK]

Common Mistakes:

• Not using GROUP BY when aggregating per group
• Using SUM instead of AVG for average
• Incorrect WHERE clause instead of GROUP BY