AVG function in SQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Calculate average salary of employees

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

SELECT AVG(salary) FROM Employees;

SQL

CREATE TABLE Employees (id INT, name VARCHAR(50), salary INT);
INSERT INTO Employees VALUES (1, 'Alice', 50000), (2, 'Bob', 60000), (3, 'Charlie', 70000);

A 60000

B 180000

C NULL

D 3

💡 Hint

AVG calculates the average value of a numeric column.

The average salary is (50000 + 60000 + 70000) / 3 = 60000.

🧠 Conceptual

intermediate

AVG function behavior with NULL values

Consider a table Scores with a column score containing values: 10, NULL, 20. What does SELECT AVG(score) FROM Scores; return?

A NULL

B 30

C 10

D 15

💡 Hint

AVG ignores NULL values when calculating the average.

AVG calculates average of non-NULL values only: (10 + 20) / 2 = 15.

📝 Syntax

advanced

Identify the syntax error in AVG usage

Which option contains a syntax error when using AVG in SQL?

SQL

SELECT AVG salary FROM Employees;

A SELECT AVG(salary) FROM Employees;

B SELECT AVG(salary) Employees;

C SELECT AVG salary FROM Employees;

D SELECT AVG(salary) FROM Employees WHERE salary > 50000;

💡 Hint

AVG requires parentheses around the column name.

Option C misses parentheses around salary, causing syntax error.

❓ optimization

advanced

Optimizing AVG calculation with conditions

You want to calculate the average salary of employees who joined after 2020 from table Employees with columns salary and join_year. Which query is most efficient?

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

B SELECT AVG(CASE WHEN join_year > 2020 THEN salary ELSE NULL END) FROM Employees;

C SELECT AVG(salary) FROM Employees;

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

💡 Hint

Filtering rows before aggregation is more efficient.

Option A filters rows first, then calculates AVG, which is efficient.

🔧 Debug

expert

Debugging unexpected AVG result with GROUP BY

Given the table Sales with columns region, sales_amount, and sales_rep, what is the output of this query?

SELECT region, AVG(sales_amount) FROM Sales GROUP BY sales_rep;

A Average sales_amount per sales_rep with region repeated

B Syntax error due to GROUP BY columns mismatch

C Average sales_amount per region

D NULL for all rows

💡 Hint

GROUP BY columns must match selected non-aggregated columns.

Query groups by sales_rep but selects region without aggregation, causing syntax error.