- Development/SQL - Built-in functions

(aggregate functions)

A table is created and data is inserted using the

following sequence of SQL statements.

CREATE TABLE sample1 (val INTEGER);

INSERT INTO sample1 (val) VALUES (1), (2), (3), (4), (NULL);

The following SELECT statement is executed.

SELECT count(*), count(val), avg(val) FROM sample1;

Which of the following is the correct combination of returned values?

- 5, 5, 2
- 5, 4, 2
- 5, 4, 2.5
- 4, 4, 2.5
- 5, 4, NULL

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

2011/11/04

The NULL value used in a RDBMS is an extremely unique value and it should be used very carefully. For example, when arithmetic operations, such as addition and multiplication, are performed using an ordinary numerical value and NULL, the result will always be NULL. We can understand the difference by executing the following.

SELECT 2 + NULL; and SELECT 2 + 0;

The RDBMS includes aggregate functions for calculating the total value and average value of the column.

When executing these aggregate functions, NULL data will be ignored when the calculation is performed.

The count() function outputs the number of data items, and as the NULL data is ignored, count(val) will give 4 as the result.

However, count(*) is special in this case and even if all columns are null this will give the number of rows, so count(*) returns the answer as 5 .

The avg() function calculates the average value of the data. There are five rows of data in the sample1 table and four items of non-NULL data in the val column. As avg(val) calculates the average value of the four items of non-NULL data, it returns 2.5.

**Therefore, the correct answer is C.**