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.