Sample Exam Questions

From the objective of OSS-DB Exam Silver
S3.2 Functions and operators (Aggregate functions)

Sample Question


The following SQL statement was executed:

create table sample(val int);
insert into sample(val) values(null), (1), (2), (3), (4), (5);
select count(*), count(val), sum(val), avg(val), max(val) from sample;

Choose all correct statements from the following.

  1. The value of count(*) is 6.

  2. The value of count(val) is 6.

  3. The value of sum(val) is 15.

  4. The value of avg(val) is 2.5.

  5. The value of max(val) is NULL.

※This sample exam is different from those that appear in the actual OSS-DB Exam.

Answer and Explanation

SQL provides aggregate functions to determine the number, sum, average, maximum, minimum, etc. of data. One thing to note when using aggregate functions is how NULL values are handled. Except for count(*), NULLs are treated as if there is no data and are not included in calculations such as count or avg. When sorting with a column that contains NULL values using ORDER BY, NULLs are displayed as if they were larger than the maximum value. However, max only considers the maximum value among non-NULL data.  If all the target data is NULL, then the sum or average will also be NULL (count will be 0). An exception is when * is specified instead of a column name for count, which returns the number of rows regardless of the column value.

In the example, there are six rows of data with the val column value being NULL and ranging from 1 to 5.
count(*) returns the number of rows, including those where the column value is NULL, so it is 6.
count(val) is the number of rows excluding those where the val value is NULL, so it is 5.
sum(val) simply adds up the values excluding NULLs, so it is 15.
avg(val) is the sum of non-NULL data (15) divided by the number of non-NULL data (5), so it is 3.0.
max(val) returns the largest value excluding NULLs, so it is 5.

Therefore, the correct answers are A and C.