AVG function in MySQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Calculate average salary of employees

Given the Employees table with columns id, name, and salary, what is the output of this query?

SELECT AVG(salary) AS avg_salary FROM Employees;

MySQL

CREATE TABLE Employees (id INT, name VARCHAR(50), salary INT);
INSERT INTO Employees VALUES (1, 'Alice', 5000), (2, 'Bob', 7000), (3, 'Charlie', 6000);

A 3

B 18000

C 6000

D NULL

💡 Hint

AVG calculates the average of numeric values in a column.

The average salary is (5000 + 7000 + 6000) / 3 = 6000.

❓ query_result

intermediate

Average with NULL values

Consider the Scores table with columns student_id and score. Some scores are NULL.
What is the result of this query?

SELECT AVG(score) AS average_score FROM Scores;

MySQL

CREATE TABLE Scores (student_id INT, score INT);
INSERT INTO Scores VALUES (1, 80), (2, NULL), (3, 90);

A 56.6667

B 85

C NULL

D 170

💡 Hint

AVG ignores NULL values when calculating the average.

Only scores 80 and 90 are counted, so average is (80 + 90) / 2 = 85.

📝 Syntax

advanced

Identify the syntax error in AVG usage

Which option contains a syntax error when using the AVG function in MySQL?

A SELECT AVG salary FROM Employees;

B SELECT AVG(salary) AS average_salary FROM Employees;

C SELECT AVG(salary) FROM Employees;

D SELECT AVG(salary + bonus) FROM Employees;

💡 Hint

AVG requires parentheses around the column or expression.

Option A misses parentheses around salary, causing syntax error.

❓ optimization

advanced

Optimizing AVG with WHERE clause

You want to calculate the average salary of employees who joined after 2020. Which query is the most efficient?

MySQL

CREATE TABLE Employees (id INT, name VARCHAR(50), salary INT, join_year INT);
INSERT INTO Employees VALUES (1, 'Alice', 5000, 2019), (2, 'Bob', 7000, 2021), (3, 'Charlie', 6000, 2022);

A SELECT AVG(salary) FROM Employees WHERE join_year >= 2020;

B SELECT AVG(salary) FROM (SELECT * FROM Employees WHERE join_year > 2020) AS filtered;

C SELECT AVG(salary) FROM Employees;

D SELECT AVG(salary) FROM Employees WHERE join_year > 2020;

💡 Hint

Filtering rows before aggregation is more efficient than subqueries.

Option D filters rows directly in WHERE clause before AVG calculation.

🧠 Conceptual

expert

AVG function with GROUP BY and NULLs

Given the Sales table with columns region and amount, some amounts are NULL. What is the output of this query?

SELECT region, AVG(amount) AS avg_amount FROM Sales GROUP BY region ORDER BY region;

Table data:

region | amount
North  | 100
North  | NULL
South  | 200
South  | 300
East   | NULL
East   | NULL

A [{"region": "East", "avg_amount": null}, {"region": "North", "avg_amount": 100}, {"region": "South", "avg_amount": 250}]

B [{"region": "East", "avg_amount": 0}, {"region": "North", "avg_amount": 50}, {"region": "South", "avg_amount": 250}]

C [{"region": "East", "avg_amount": null}, {"region": "North", "avg_amount": 50}, {"region": "South", "avg_amount": 250}]

D [{"region": "East", "avg_amount": 0}, {"region": "North", "avg_amount": 100}, {"region": "South", "avg_amount": 250}]

💡 Hint

AVG ignores NULLs; if all values are NULL, AVG returns NULL for that group.

East has only NULLs so avg_amount is NULL; North average is 100; South average is (200+300)/2=250.

Practice

1. What does the AVG() function do in MySQL?

easy

A. Returns the sum of all values in a column

B. Counts the number of rows in a table

C. Finds the maximum value in a column

D. Calculates the average value of a numeric column

Solution

1. Step 1: Understand the purpose of AVG()

   The AVG() function is designed to calculate the average (mean) of numeric values in a column.

2. Step 2: Compare with other aggregate functions

   Unlike COUNT(), MAX(), or SUM(), AVG() specifically returns the average value.

3. Final Answer:

   Calculates the average value of a numeric column -> Option D

4. Quick Check:

   AVG() = average [OK]

Hint: AVG() always returns the mean of numeric values [OK]

Common Mistakes:

• Confusing AVG() with COUNT()
• Thinking AVG() sums values instead of averaging
• Assuming AVG() includes NULL values in calculation

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

easy

A. SELECT AVG(salary) FROM employees;

B. SELECT AVG salary FROM employees;

C. SELECT AVERAGE(salary) FROM employees;

D. SELECT salary AVG() FROM employees;

Solution

1. Step 1: Recall correct AVG() syntax

   The correct syntax uses AVG() as a function with the column name inside parentheses.

2. Step 2: Check each option

   SELECT AVG(salary) FROM employees; uses AVG(salary) correctly. Options A, C, and D have syntax errors or wrong function names.

3. Final Answer:

   SELECT AVG(salary) FROM employees; -> Option A

4. Quick Check:

   AVG(column) syntax [OK]

Hint: AVG() requires parentheses around the column name [OK]

Common Mistakes:

• Omitting parentheses in AVG()
• Using wrong function name like AVERAGE()
• Placing AVG() after column name incorrectly

3. Given the table scores with values:
id | score
1 | 80
2 | 90
3 | NULL
4 | 70
What will the query SELECT AVG(score) FROM scores; return?

medium

A. 80

B. NULL

C. 80.0

D. 75

Solution

1. Step 1: Identify values considered by AVG()

   AVG() ignores NULL values, so it averages 80, 90, and 70.

2. Step 2: Calculate average of non-NULL scores

   (80 + 90 + 70) / 3 = 240 / 3 = 80.0. MySQL's AVG() returns a floating-point value.

3. Final Answer:

   80.0 -> Option C

4. Quick Check:

   AVG ignores NULL, average = 80.0 [OK]

Hint: AVG ignores NULLs and returns float average [OK]

Common Mistakes:

• Including NULL in average calculation
• Expecting integer instead of float result
• Confusing AVG with SUM or COUNT

4. Consider this query:
SELECT AVG(price) FROM products WHERE price > 0;
It returns NULL even though there are products with positive prices. What is the likely cause?

medium

A. The price column contains only NULL values

B. The WHERE clause filters out all rows

C. AVG() cannot be used with WHERE clause

D. The table products does not exist

Solution

1. Step 1: Understand AVG() behavior with WHERE clause

   The WHERE clause filters rows before AVG() calculates the average.

2. Step 2: Analyze why AVG() returns NULL

   If the WHERE clause excludes all rows (no price > 0), AVG() has no values to average and returns NULL.

3. Final Answer:

   The WHERE clause filters out all rows -> Option B

4. Quick Check:

   Empty filtered rows cause AVG() to return NULL [OK]

Hint: Check WHERE filters; no rows means AVG() returns NULL [OK]

Common Mistakes:

• Assuming AVG() ignores WHERE clause
• Thinking AVG() returns 0 if no rows
• Believing AVG() cannot be combined with WHERE

5. You have a sales table with columns region and amount. How do you write a query to find the average sales amount per region, excluding regions with no sales?

hard

A. SELECT region, AVG(amount) FROM sales GROUP BY region HAVING AVG(amount) > 0;

B. SELECT region, AVG(amount) FROM sales WHERE amount IS NOT NULL GROUP BY region;

C. SELECT region, AVG(amount) FROM sales GROUP BY region WHERE amount > 0;

D. SELECT region, AVG(amount) FROM sales GROUP BY region HAVING amount > 0;

Solution

1. Step 1: Group sales by region

   Use GROUP BY region to calculate averages per region.

2. Step 2: Filter out regions with no sales

   Use HAVING AVG(amount) > 0 to exclude groups where average is NULL or 0 (no sales).

3. Step 3: Check other options

   SELECT region, AVG(amount) FROM sales WHERE amount IS NOT NULL GROUP BY region; (SELECT region, AVG(amount) FROM sales WHERE amount IS NOT NULL GROUP BY region;) filters NULL rows before grouping but includes regions where non-NULL amounts average to 0. Options C and D have syntax errors or misuse WHERE with GROUP BY.

4. Final Answer:

   SELECT region, AVG(amount) FROM sales GROUP BY region HAVING AVG(amount) > 0; -> Option A

5. Quick Check:

   Use HAVING to filter groups by AVG() [OK]

Hint: Use HAVING to filter groups after GROUP BY [OK]

Common Mistakes:

• Using WHERE instead of HAVING to filter groups
• Not excluding NULL averages
• Misplacing WHERE clause after GROUP BY