Sample Exam Questions

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

Sample Question


A user A executed the following SQL:

create sequence testseq;
select nextval('testseq');

This SELECT statement returned 1. Choose three appropriate options for subsequent operations and their results. It is assumed that there are no issues with access permissions to this sequence and that no operations other than those described in each option have been performed.

  1. If the same user A continues to execute 'select currval('testseq');', it will return 1.

  2. If another user B connects to the database and executes 'select currval('testseq');', it will return 1.

  3. It returned 2 when another user C connected to the database and executed 'select nextval('testseq');'. Immediately after this, if user A executes 'select currval('testseq');', it will return 2.

  4. It returned 3 when another user D connected to the database and executed 'select nextval('testseq')' at exactly the same time as user C in option C. Immediately after this, if user C executes 'select currval('testseq');', it will return 2.

  5. If another user E connects to the database and sequentially executes 'select setval('testseq', 10); select nextval('testseq');', the second SELECT statement 'nextval' will return 11.

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

Answer and Explanation

Sequences are often used to generate values for columns that require uniqueness, such as ID columns. For instance, if you specify nextval('testseq') as a column value in an INSERT statement, the value inserted will be one greater than the previously used value in the 'testseq' sequence.
In addition to nextval, which retrieves the next value, there are functions like currval, which retrieves the most recent value, and setval, which modifies the current value.
While currval stands for 'current value', it can be easily misunderstood as the 'latest value'. However, it's important to note that currval returns the value produced by the most recent nextval function executed in the current session. This means it is not affected by the execution of nextval in other sessions. Also, if currval is executed without ever running nextval in the current session, it will result in an error.

Let's examine the options one by one.
Firstly, since User A initially executes nextval('testseq'), option A's currval('testseq') returns the same value as the previous nextval, which is 1.
User B in option B executes currval without running nextval, which, as previously explained, results in an error.
When User C in option C executed nextval('testseq'), it returned 2. However, User A's currval is not affected by this and continues to return the same value as User A's last executed nextval, which is 1.
User D in option D is supposed to have executed nextval('testseq') simultaneously with User C. However, in reality, the database does not execute them exactly at the same time, but sequentially. Therefore, one returns 2 and the other returns 3. If User D gets 3 as described in the option, it means User C gets 2. Therefore, the subsequent currval also returns 2.
Option E's setval changes the current value of the sequence, and the next nextval adds 1 to that value. Therefore, nextval returns 11.

Therefore, the correct answers are A, D, and E.