What is displayed by the SELECT statement?

Evaluate the following CREATE SEQUENCE statement:
CREATE SEQUENCE seq1
START WITH 100
INCREMENT BY 10
MAXVALUE 200
CYCLE
NOCACHE;
The SEQ1 sequence has generated numbers up to the maximum limit of 200. You issue the
following SQL statement:
SELECT seq1.nextval FROM dual;
What is displayed by the SELECT statement?

Evaluate the following CREATE SEQUENCE statement:
CREATE SEQUENCE seq1
START WITH 100
INCREMENT BY 10
MAXVALUE 200
CYCLE
NOCACHE;
The SEQ1 sequence has generated numbers up to the maximum limit of 200. You issue the
following SQL statement:
SELECT seq1.nextval FROM dual;
What is displayed by the SELECT statement?

A.
1

B.
10

C.
100

D.
an error

Explanation:
But why the answer is not “C” ?
Because you didn’t specify the MINVALUE for the sequence. If you check the sequence definition
that you created it will have the default value of 1, which it reverts to when cycling.
If you wanted to keep the minimum value you would need to specify it in the sequence creation.
sequenceIs the name of the sequence generator
INCREMENT BY n Specifies the interval between sequence numbers, where n is an integer (If this
clause is omitted, the sequence increments by 1.)
START WITH n Specifies the first sequence number to be generated (If this clause is omitted, the
sequence starts with 1.)
MAXVALUE n Specifies the maximum value the sequence can generate
NOMAXVALUE Specifies a maximum value of 10^27 for an ascending sequence and 1 for a
descending sequence (This is the default option.)
MINVALUE n Specifies the minimum sequence value
NOMINVALUE Specifies a minimum value of 1 for an ascending sequence and (10^26) for a

descending sequence (This is the default option.)
CYCLE | NOCYCLE Specifies whether the sequence continues to generate values after reaching
its maximum or minimum value
(NOCYCLE is the default option.)
CACHE n | NOCACHE Specifies how many values the Oracle server preallocates and keeps in
memory (By default, the Oracle server caches 20 values.)



Leave a Reply 10

Your email address will not be published. Required fields are marked *


reach

reach

Since startwith is specified, the ans should be C

Vietnam (anti communism)

Vietnam (anti communism)

You should create a Sequence object and Test, in stead of guessing … Practice is a good teacher than theory.

Justyna

Justyna

See the question 34 from 1Z0-051 (the same question)

“ans is 1. Once the max value 200 is reached then when u execute
SQL>SELECT seq1.nextval FROM dual;

it will return 1 “

dj

dj

enter the code in sql developer and you will see that the answer is 100

Vietnam (anti communism)

Vietnam (anti communism)

I don’t think so!
You should try this code by yourself!
create sequence seq1
start with 100
increment by 10
maxvalue 200
cycle
nocache;

select seq1.nextval from dual;

dj

dj

you are right, sorry

Sagar

Sagar

Don’t know the reason, but after testing, got the answer as “1”. So option A is correct.
Can anyone explain, why?

kaushal

kaushal

start with specify only first starting value, once the cycle repeat it will be set to default which is one

Dilliadis

Dilliadis

I agree with “A”. After 200 was generated the “ONE” will come.