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.