3.30

Consider the following SQL query on the university schema:

SELECT AVG(salary) - (SUM(salary)/COUNT(*))
FROM instructor

We might expect that the result of this query is zero since the average of a set of numbers is defined to be the sum of the numbers divided by the number of numbers. Indeed this is true for the example instructor relation in Figure 2.1. However, there are other possible instances of that relation for which the result would not be zero. Give one such instance, and explain why the result would not be zero.

---

Consider the following instance of the instructor relation.

university=# SELECT * FROM instructor;
  id   |    name    | dept_name  |  salary   
-------+------------+------------+-----------
 12121 | Wu         | Finance    |  90000.00
 15151 | Mozart     | Music      |  40000.00
 1     | Tesla      | Elec. Eng. |   NULL       
(3 rows)

Then using the previous query will not give 0 as shown below:-

university=# SELECT AVG(salary) - (SUM(salary)/COUNT(*))
university-# FROM instructor
university-# ;
      ?column?      
--------------------
 21666.666666666667
(1 row)

university=# 

This is because of the NULL salary of the instructor ‘Tesla’.

All aggregate functions except **count(*)** ignore null values in their input collection.

Thus AVG(salary) computes:

\[ \frac {90000 + 40000} {2} = \frac {130000} {2} = 65000.0 \]

While _SUM(salary)/COUNT(*)_ computes:

\[ \frac {SUM(salary)}{COUNT(*)} = \frac {90000 + 40000} {3} = \frac {130000} {3} = 43333.333333333336 \]

Which are not equal. Which explains the non-zero answer of the query in question.