Exercise #11 (tips and solutions)
The existence of the standard AVG aggregate function helps to fix all issues:
However, the corresponding forum thread on the website shows the attempts to stand out from the "faceless mass. Here is quite a tantamount solution which, however, adds an extra operation to the query plan:
The correctness of the following solution depends on the available restrictions:
Namely, while SUM (speed) ignores rows with NULL values in the speed column, SUM(1) counts up all rows in the PC table and is essentially equivalent to COUNT(*). Hence, the numerator will contain the sum of speeds of all personal computers except for rows with unknown speed, which will be divided by the total number of rows. Therefore, the third solution will be equivalent to the other two only if no NULL values are allowed, i.e. a NOT NULL constraint is applied to the speed column.
Return to discussion of exercise #11