Sample Exam Questions

From the objective of OSS-DB Exam Silver
S3.1 SQL commands (INSERT statement)

Sample Question


The following sequence of SQL commands is executed. What value does the final SELECT statement return as 'count'?

create table test(id integer primary key, val varchar);
insert into test(id, val) values(1, 'aaa'), (2, 'bbb'), (3, 'ccc');
insert into test(id, val) values(2, 'ddd'), (4, 'eee');
insert into test(id, val) values(3, 'fff'), (6, 'ggg');
insert into test(id, val) values(4, 'hhh'), (8, 'iii');
select count(*) from test;

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

Answer and Explanation

When inserting rows into a table with an INSERT statement, you can specify the data to be inserted either with a SELECT statement from another table or with a VALUES clause, as in this example. When using the VALUES clause, you can insert multiple rows at once by specifying the list of multiple data separated by commas.

There are four INSERT statements in the example, each attempting to insert three rows, two rows, two rows, and two rows, respectively. However, since the initial CREATE TABLE statement applies a primary key constraint to the 'id' column, specifying duplicate values for the 'id' column results in an error.

The first INSERT statement executes without issues, creating rows with 'id' values of 1, 2, and 3.
The second INSERT statement attempts to insert rows with 'id' values of 2 and 4. However, since a row with 'id' = 2 already exists, this INSERT statement results in an error and no row is inserted.

The third INSERT statement also results in an error because a row with 'id' = 3 already exists.
The fourth INSERT statement does not encounter such an error, so it creates rows with 'id' values of 4 and 8.

In other words, after executing the four INSERT statements, five rows with 'id' values of 1, 2, 3, 4, and 8 have been created. Therefore, the final SELECT statement returns 5 as the count.
Therefore, the correct answer is 5.