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.

Practice

1. What does the SQL AVG() function do?

easy

A. Calculates the average value of a numeric column

B. Counts the number of rows in a table

C. Finds the maximum value in a column

D. Returns the sum of all values in a 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 A

4. Quick Check:

   AVG() = average calculation [OK]

Hint: AVG() always returns the mean of numbers, not counts or sums [OK]

Common Mistakes:

• Confusing AVG() with COUNT()
• Thinking AVG() sums values without dividing
• Assuming AVG() works on non-numeric columns

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 AVG() function requires parentheses around the column name: AVG(column_name).

2. Step 2: Check each option

   Correct syntax uses AVG(salary) with parentheses. Missing parentheses, AVERAGE(salary), or salary AVG() cause syntax errors.

3. Final Answer:

   SELECT AVG(salary) FROM Employees; -> Option B

4. Quick Check:

   AVG(column) uses parentheses [OK]

Hint: AVG() always needs 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:
Score
90
80
NULL
70
What will the query SELECT AVG(Score) FROM Scores; return?

medium

A. 240

B. NULL

C. 80

D. 75

Solution

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

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

2. Step 2: Calculate the average

   (90 + 80 + 70) / 3 = 240 / 3 = 80.

3. Final Answer:

   80 -> Option C

4. Quick Check:

   AVG ignores NULL, average = 80 [OK]

Hint: AVG() skips NULLs automatically when averaging [OK]

Common Mistakes:

• Including NULL as zero in average
• Returning NULL if any NULL exists
• Summing values without dividing

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

medium

A. The WHERE clause excludes all rows because price > 0 filters out zero prices

B. AVG() cannot be used with WHERE clause

C. The price column contains only NULL values

D. AVG() requires GROUP BY to work

Solution

1. Step 1: Analyze the WHERE clause condition

   The condition price > 0 excludes prices equal to zero, so only prices greater than zero are included.

2. Step 2: Consider data and NULL result

   If no prices are greater than zero, the filtered set is empty, so AVG() returns NULL.

3. Final Answer:

   The WHERE clause excludes all rows because price > 0 filters out zero prices -> Option A

4. Quick Check:

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

Hint: Check WHERE filters exclude all rows causing NULL AVG() [OK]

Common Mistakes:

• Thinking AVG() can't use WHERE
• Assuming AVG() needs GROUP BY always
• Ignoring that empty sets return NULL

5. You have a table Sales 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 WHERE Amount IS NOT NULL;

B. SELECT Region, AVG(Amount) FROM Sales WHERE Amount > 0;

C. SELECT Region, AVG(Amount) FROM Sales GROUP BY Region WHERE Amount > 0;

D. SELECT Region, AVG(Amount) FROM Sales GROUP BY Region HAVING AVG(Amount) IS NOT NULL;

Solution

1. Step 1: Group sales by region

   Use GROUP BY Region to calculate average per region.

2. Step 2: Exclude regions with no sales

   Regions with no sales have AVG(Amount) as NULL, so use HAVING AVG(Amount) IS NOT NULL to filter them out.

3. Final Answer:

   SELECT Region, AVG(Amount) FROM Sales GROUP BY Region HAVING AVG(Amount) IS NOT NULL; -> Option D

4. Quick Check:

   GROUP BY + HAVING filters NULL averages [OK]

Hint: Use HAVING to exclude groups with NULL averages [OK]

Common Mistakes:

• Using WHERE after GROUP BY (invalid syntax)
• Not filtering NULL averages with HAVING
• Filtering rows before grouping instead of after