AVG function in SQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to calculate the average salary from the employees table.

SQL

SELECT [1](salary) FROM employees;

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

ASUM

BAVG

CMAX

DCOUNT

Attempts:

3 left

💡 Hint

Common Mistakes

Using SUM instead of AVG will give the total, not the average.

Using COUNT counts rows, not averages values.

The AVG function calculates the average value of a numeric column.

2fill in blank

medium

Complete the code to find the average age of users who are active.

SQL

SELECT AVG(age) FROM users WHERE status = [1];

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

A'active'

B'inactive'

C'pending'

D'banned'

Attempts:

3 left

💡 Hint

Common Mistakes

Using the wrong status value will give incorrect or empty results.

Forgetting quotes around string values causes syntax errors.

The condition filters users with status 'active' to calculate their average age.

3fill in blank

hard

Fix the error in the code to correctly calculate the average price from products.

SQL

SELECT [1]price) FROM products;

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

AAVG

BSUM

CAVG(

DCOUNT

Attempts:

3 left

💡 Hint

Common Mistakes

Missing parentheses causes syntax errors.

Using SUM or COUNT changes the meaning of the query.

The function call is missing a closing parenthesis after AVG(. Adding it fixes the syntax.

4fill in blank

hard

Fill both blanks to calculate the average score for students with scores greater than 70.

SQL

SELECT [1](score) FROM students WHERE score [2] 70;

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

AAVG

BSUM

C>

D<

Attempts:

3 left

💡 Hint

Common Mistakes

Using SUM instead of AVG changes the result.

Using < instead of > selects wrong scores.

Use AVG to get the average, and > to filter scores greater than 70.

5fill in blank

hard

Fill all three blanks to calculate the average rating for products with rating above 4 and category 'electronics'.

SQL

SELECT [1](rating) FROM products WHERE rating [2] 4 AND category = [3];

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

AAVG

B>

C'electronics'

D<

Attempts:

3 left

💡 Hint

Common Mistakes

Using < instead of > for rating condition.

Forgetting quotes around the category string.

Using SUM instead of AVG.

Use AVG to calculate average rating, > to filter ratings above 4, and 'electronics' as the category string.

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