Aggregate functions (COUNT, SUM, AVG, MAX, MIN) in DBMS Theory - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to count all rows in the employees table.

DBMS Theory

SELECT [1](*) FROM employees;

Drag options to blanks, or click blank then click option'

AAVG

BSUM

CCOUNT

DMAX

Attempts:

3 left

💡 Hint

Common Mistakes

Using SUM instead of COUNT will cause an error or wrong result.

Using AVG or MAX does not count rows.

The COUNT function counts the number of rows in a table or group.

2fill in blank

medium

Complete the code to find the total salary from the salaries table.

DBMS Theory

SELECT [1](salary) FROM salaries;

Drag options to blanks, or click blank then click option'

ASUM

BCOUNT

CAVG

DMIN

Attempts:

3 left

💡 Hint

Common Mistakes

Using COUNT will count rows, not add values.

Using AVG will give average, not total.

The SUM function adds all values in a column.

3fill in blank

hard

Fix the error in the code to find the average age from the people table.

DBMS Theory

SELECT [1](age) FROM people;

Drag options to blanks, or click blank then click option'

ACOUNT

BAVG

CSUM

DMAX

Attempts:

3 left

💡 Hint

Common Mistakes

Using SUM or COUNT will not calculate average.

Using MAX returns the highest value, not average.

The AVG function calculates the average value of a column.

4fill in blank

hard

Fill both blanks to find the maximum and minimum prices from the products table.

DBMS Theory

SELECT [1](price) AS max_price, [2](price) AS min_price FROM products;

Drag options to blanks, or click blank then click option'

AMAX

BSUM

CMIN

DCOUNT

Attempts:

3 left

💡 Hint

Common Mistakes

Using SUM or COUNT instead of MAX or MIN.

Mixing up MAX and MIN functions.

MAX finds the highest value, and MIN finds the lowest value in a column.

5fill in blank

hard

Fill all three blanks to create a query that counts employees, sums their salaries, and finds the average age.

DBMS Theory

SELECT [1](*) AS total_employees, [2](salary) AS total_salary, [3](age) AS average_age FROM employees;

Drag options to blanks, or click blank then click option'

ACOUNT

BSUM

CAVG

DMIN

Attempts:

3 left

💡 Hint

Common Mistakes

Mixing up COUNT and SUM functions.

Using MIN instead of AVG for average calculation.

This query uses COUNT to count employees, SUM to add salaries, and AVG to find average age.

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