Sample Exam Questions

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

Sample Question

3.14

The following sequence of operations was performed. What value does the last SELECT statement return as currval?

create table test1(id integer, val varchar(10));
create table test2(id integer, val varchar(10));
create sequence seq1;
insert into test1(id, val) values (1, 'abc'), (2, 'def');
insert into test2(id, val) values (nextval('seq1'), 'xyz');
begin;
insert into test2(id, val) select nextval('seq1'), val from test1;
rollback;
insert into test2(id, val) values (nextval('seq1'), 'pqr');
select currval('seq1');

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

Answer and Explanation

In the example sequence, we first create the tables test1 and test2 with the same structure (lines 1-2), and then create the sequence seq1 (line 3). Let's look at what happens to seq1 and see the subsequent operations.
The fourth line inserts two rows of data into table test1.
Line 5 inserts a row of data into table test2, where nextval is used. When a sequence is created with the default settings, the first nextval returns 1. Each subsequent call to nextval increases the value by 1, as in 2, 3, 4, and so on.
The sixth line, begin, starts a new transaction.
The insert in line 7 specifies the select statement as the data for the row to be inserted, using nextval in it. Since there are two rows of data in table test1, the select statement in insert will also return two rows. Therefore, nextval is executed twice, and nextval for select returns 2 and 3.
The rollback on line 8 cancels the previous insert, but does not cancel the nextval execution of the sequence.
The insert on line 9 also uses nextval, which becomes 4, since it is the next value of 3.
The last currval returns the last value used in that session, which is 4.

So the correct answer is 4.

At this stage, the table test2 looks like this:
 

=> select * from test2;
id | val
----+-----
1 | xyz
4 | pqr
(2 lines)