Mastering Sequences in Oracle SQL: NEXTVAL, CURRVAL, and Practical Example.

Note: All SQL queries used in the Case study is tested on Oracle.

1.How would you define a sequence called cust_seq which starts generating customer IDs from 1000 incrementing by 1 each time?
CREATE SEQUENCE cust_seq
START WITH 1000
INCREMENT BY 1;

In the above query a sequence is defined in the database and named cust_seq. The sequence will start generating numbers from 1000 and will increment by 1 every time a new value is fetched. They are mainly used for unique identifiers especially primary keys. The START WITH clause specifies the initial value and the INCREMENT BY clause specifies the step increment for each subsequent value. Thus this sequence ensures automatically generated unique IDs with zero effort, thus the chances of duplication of database entries are reduced.

2.Which of the commands would you use to modify an existing sequence named acct_seq so that instead of the default 1 it increases by 5?
ALTER SEQUENCE acct_seq
INCREMENT BY 5;
This statement modifies the current sequence of the name acct_seq such that it increments by 5. The ALTER SEQUENCE command is used to alter attributes assigned to an existing sequence. It means that by setting the increment value to 5 every call of NEXTVAL on this sequence will now create numbers increasing by 5. For example, if the current value is 100 the next call would provide 105. Such a change is of good use when you would want to vary the step size at which sequence numbers can be generated especially if you require larger intervals between generated IDs.

3.What is the statement you'll use to retrieve the next number in a sequence named trans_seq?
SELECT trans_seq.NEXTVAL FROM DUAL;
The statement retrieves the next number available in the sequence named trans_seq. The NEXTVAL keyword is used to retrieve the next number from the sequence and FROM DUAL is a dummy table in Oracle that is typically used for a query that does not directly reference a specific table. When you run this statement it moves the sequence by its defined increment and returns that new value. It is extremely useful to have auto_incremented unique ids created for each new row going into a table particularly in any transaction or ids field.

4.You have to create a sequence named order_seq that needs to run from 500 through 10,000 then cycle back around to 500.
CREATE SEQUENCE order_seq
START WITH 500
INCREMENT BY 1
MAXVALUE 10000
CYCLE;
This is a sequence named order_seq created from the question with an interval starting at 500 and increments with a step value of 1. MAXVALUE is used to set the number to 10,000. CYCLE is a feature that makes the sequence cycle back to 500 once it reaches 10,000. This is a great configuration for those occasions when you want the sequence to keep producing values to fall within a range defined by you without exceeding the number specified as maximum. It would allow using the CYCLE option meaning that the ID numbers could wrap around instead of returning an error after reaching the maximum value.

5.What is the command to drop a sequence called emp_seq in your database?
DROP SEQUENCE emp_seq;
Removes sequences defined by the names passed as arguments to the DROP SEQUENCE statement. Dropping a sequence irreversibly removes it and thus it can no longer be used to generate values. The dropping of a sequence is simple but should be executed with care especially if other tables or operations depend on it. This is usually done when the sequence is no longer necessary or when it is replaced by a new sequence configuration. Once deleted, the sequence and its properties are lost so they have to be re-generated if they are needed again.

Illustration of using Oracle SQL Sequences with NEXTVAL and CURRVAL in database operation

6.You want to get the current value of a sequence named invoice_seq without incrementing its value.
SELECT invoice_seq.CURRVAL FROM DUAL;
This returns the current value of the sequence invoice_seq by using the CURRVAL keyword. It returns the last value generated but it does not advance the sequence. The FROM DUAL portion includes Oracle's dummy table. You need to have called NEXTVAL at least once in this session or CURRVAL will not return a value. You often want to refer to the current sequence number but without generating a new one. for example, for integrity checks or logging reasons.

7.How would you define a sequence named txn_seq whose first value is 1 and that has no maximum limit?
CREATE SEQUENCE txn_seq
START WITH 1
INCREMENT BY 1
NOMAXVALUE;
This statement defines a sequence known as txn_seq starting at 1 and incrementing each time by 1 and does not provide an upper limit NOMAXVALUE. START WITH specifies the starting value, while INCREMENT BY specifies the step size. The NOMAXVALUE clause means that there is no boundary on the maximum so the sequence will carry on indefinitely until it hits its limit. This could be in any scenario where you need a pretty straightforward increasing identifier, say on a transaction ID or an order number, making this a unique and uninterrupted stream of numbers.

8.How would you reset an existing sequence named sale_seq so that it generates numbers starting from 100 again?
ALTER SEQUENCE sale_seq RESTART START WITH 100;
This statement resets the sequence sale_seq so that it starts generating values from 100 again. ALTER SEQUENCE invoked with the option RESTART START WITH is used to reset the current value of the sequence to the value specified with this option in this case 100. This is useful when you want to have reusing of sequence numbers or resets for a set of operational requirements like the annual resetting of IDs. A reset of a sequence however must be done with care such that it does not conflict values already in use.

9.Write a sequence named prod_seq that starts at 10 and increments by 2 without cycling back.
CREATE SEQUENCE prod_seq
START WITH 10
INCREMENT BY 2
NOCYCLE;
This generates a sequence called prod_seq starting at 10 and incremented by 2 each time it is used. The NOCYCLE option prevents the sequence from cycling or being restarted once its maximum value is reached. This means that a sequence can never be used on a counter with a limit. It is useful where unique fixed intervals must be produced between values such as product codes where non repeating numbers are needed. It does not allow the cycle sequence to be directed eventually to the initial value thereby avoiding redundancy.

10.How would you disable the caching of a predefined sequence by name cust_seq
ALTER SEQUENCE cust_seq NOCACHE;
The above statement alters the sequence cust_seq by switching off its cache with the NOCACHE clause. Sequences have cache and it pre-allocates a range of sequence numbers for helping in performance and avoiding disk I/O. However, there are scenarios where the count of sequence can be skipped in case of a system crash or reset. NOCACHE ensures that each and every sequence value is fetched straight from the database with no prefetching thus guaranteeing that the sequences are not interrupted in case of a failure.

11.What does it mean to have a sequence called pay_seq starting with 2000, incrementing with 10 and having a cache of 20 values?
CREATE SEQUENCE pay_seq
START WITH 2000
INCREMENT BY 10
CACHE 20;
This statement defines a named sequence called pay_seq that begins at 2000, with an increment of 10. The CACHE 20 clause implies there ought to be in memory space for 20 values so that it can fetch them more quickly. This allows the performance to increase because access to disk storage is reduced in retrieving sequence values. For instance the first set of values from this sequence would be 2000, 2010, 2020 and so on. Caching is useful in high-throughput systems where several values have to be returned almost successively with minimum latencies within producing sequence numbers.

12.How do you rename an existing sequence from old_seq to new_seq?
CREATE VIEW CustomerAverageTransaction AS
ALTER SEQUENCE old_seq
RENAME TO new_seq;
The above query renames the existing sequence old_seq to a new sequence named new_seq using ALTER SEQUENCE statement. Renaming a sequence is very much a simple operation. It is useful if there is any need to update the name of a sequence for some changes in business requirements or naming conventions but without losing its current values. All other sequence properties like starting point increment and cache are still not affected at all. This operation is handy while recreating a database schema and maintaining the sequence references consistent among one table to another or between applications.

13.How would you get next five values of a sequence named inv_seq?
SELECT inv_seq.NEXTVAL FROM DUAL CONNECT BY LEVEL <= 5;
This query gets the next five values of the sequence named inv_seq. This type of question will leverage the CONNECT BY LEVEL <= 5 clause to produce a sequence of values from 1 to 5 while in effect calling NEXTVAL five times. This list the result is returned as the next five incremental values from the sequence. It makes it possible to advance preview sequence numbers or to pre-compute multiple values in batch. It also helps in testing sequence behavior without having to directly insert these values in a table.

14.How can you check the details of a sequence named user_seq in Oracle, such as the starting value, increment and cache size?
SELECT SEQUENCE_NAME, MIN_VALUE, MAX_VALUE, INCREMENT_BY, CACHE_SIZE
FROM USER_SEQUENCES
WHERE SEQUENCE_NAME = 'USER_SEQ';
This will retrieve information from the Oracle data dictionary view USER_SEQUENCES regarding a sequence called user_seq. In addition to retrieving a minimum value and a maximum value, it will retrieve a step for increment by as well as how much of the sequence (CACHE_SIZE) will be cached into memory. The database administrator and developer analyze the configuration of sequences in the data dictionary view to ensure they reflect the specifications desired. Information obtained about the way a sequence works is vital while making changes in configuration.

15.How would you prevent a sequence named log_seq from cycling when it reaches its maximum?
ALTER SEQUENCE log_seq
NOCYCLE;
This SQL statement modifies the sequence log_seq so it cannot cycle anymore by utilizing the NOCYCLE keyword. A sequence set with the CYCLE option must be reset to its minimum value after reaching its maximum limit. The use of NOCYCLE ensures that the sequence does not cycle and instead raises an error if a request is made for another value after it has reached its maximum. The usage of NOCYCLE will be predominant when the number generated by the sequence should not repeat any number, like generating unique primary keys for specific tables.

16.Create a sequence rev_seq returning numbers starting from 100 in decreasing order, stating how does the above create a sequence rev_seq that generates numbers in descending order starting from 100?
CREATE SEQUENCE rev_seq
START WITH 100
INCREMENT BY -1
MINVALUE 1;
This statement creates a sequence called rev_seq that starts at 100 and decrements by 1 on each call. The clause INCREMENT BY -1 defines the sequence to count downward. Finally, MINVALUE 1 ensures that the lower limit for such a sequence is set to 1. Such a sequence could be helpful, for example, when you are asked to produce identifiers or numbers in reverse order. for example countdowns or reverse item listings. It guarantees the decrease in numbers with every request until the defined minimum value of the sequence is reached.

17.How can you display all the sequences created by the user in the current Oracle database session?
SELECT SEQUENCE_NAME FROM USER_SEQUENCES;
The above query fetches the names of all sequences defined by the current user from the USER_SEQUENCES view. The syntax provides a list of sequence names with no extra information about their properties. This is useful when you wish to identify instantly which sequences are defined in the schema for use in generating unique IDs or otherwise for auto-number generation. Normally, database administrators and developers employ it for the purpose of ensuring that sequences needed exist and have appropriate names for the application.

18.Define how you might create a sequence called emp_id_seq that should start at 200 and increment by 3?
CREATE SEQUENCE emp_id_seq
START WITH 200
INCREMENT BY 3;
It creates a sequence called emp_id_seq starting at 200 so each time the NEXTVAL function is called, it will increment by 3. The START WITH clause specifies where the sequence will start in this case, it starts at 200 and the INCREMENT BY clause specifies the increment or step size. This is helpful for example, in the case of ID spacing, or when someone wishes to have an interval between sequences generated. For example, the first values in this series would be 200, 203, 206 and so on. In this mode, different and distributed employee data identifiers can be created.

19.How would you temporarily disable a sequence called audit_seq without dropping it?
ALTER SEQUENCE audit_seq NOCACHE;
This SQL statement alters the sequence audit_seq by making it NOCACHE which reduces the performance of the sequence but it doesn't hold up pre-fetched values in memory. While not supported natively to drop them from being disabled there may be a critical use case for developers to temporarily stop using a sequence stopping a call in transactions instead of disabling the sequence. NOCACHE makes sure any fetched value is explicitly handled without being cached, which would make the sequence a little less efficient but much safer to be used in temporary restriction use cases.

20.How can you check the last value generated by a sequence named inv_id_seq?
SELECT inv_id_seq.CURRVAL FROM DUAL;
This fetches the last value generated by the sequence inv_id_seq using the CURRVAL keyword. This returns the last value generated from the sequence in the current session without its value being incremented. For this query to return a valid result the sequence must already have been used at least once, in the current session with the NEXTVAL keyword. That can be used to obtain the current value without moving the sequence forward-it is rather useful for applications in fetching the current value for reference or display without altering the state of the sequence.

DDL in SQL for Account Management Managing Indexes and Synonyms

Other Topics for Account Management
Joins With Group by Having Equi Join Joins with Subqueries Self Join Outer Join