SEQUENCE & NEXTVAL statements are used to insert values automatically PRIMAR and UNIQUE columns while adding new rows on table. SEQUENCE statement is used to generate UNIQUE values on particular column in existing table with starting value and increment by value. NEXTVAL statement is used to insert values on existing table by increasing old sequence value with increment by value and returns generated new value. SEQUENCE& NEXTVAL statements are used in ORACLE database.

Where we can use SEQUENCE values –

• In VALUES clause of INSERT statement while inserting values in table
• In SELECT list of SELECT statement that is not contained a subquery or a view.
• In SELECT list of subquery INSERT statement.
• In SET clause of UPDATE statement.

Where we cannot use SEQUENCE values –

• In DELETE, SELECT and UPDATE statements of a subquery.
• In the query of view.
• Along with DISTINCT operator in SELECT statement.
• In GROUP BY and ORDER BY clauses in SELECT statement.
• In SELECT statement when combined with another SELECT statement by using UNION, INTERSECT, and MINUS operators.
• In SELECT statement which consists WHERE clause.
• In SELECT, UPDATE, DELETE statements which consists of CHECK constraint.
• Either CREATE TABLE or ALTER TABLE statements when contain DEFAULT value.

CREATE SEQUENCE sequence_name 
START WITH (initial_value)
INCREMENT BY (interval_value);  

INSERT INTO table_name VALUES (sequence_name, value1, value2); 

ALTER SEQUENCE sequence_name
INCREMENT BY value; 

DROP SEQUENCE sequence_name; 

Let us consider below table(s) as an example table(s) to frame the SQL query for getting the desired results.

Scenario – Creating Sequence on existing table.

Requirement – Creating SEQUENCE on employee_details table. The query was as follows –

CREATE SEQUENCE seq_employee_details START WITH 1 INCREMENT BY 2;

By executing above query we can create sequence on employee_details table.

Scenario – Inserting rows in existing table by using NEXTVAL statement.

Requirement - Inserting rows in employee_details table by using NEXTVAL statement. The query was as follows –

INSERT INTO employee_details(seq_employee_details,' Employee1',
'Director', '2019-07-11', 45000, 1000); 

INSERT INTO employee_details(seq_employee_details,'Employee2',
'Director','2019-07-11', 40000, 2000); 

By executing above query we can get emp_id by default value 1for Employee1 and the. The output was as follows -

Scenario – Finding next value from the SEQUENCE.

Requirement – Finding next value in the SEQUENCE from seq_employee_details. The query was as follows –

SELECT seq_employee_details.NEXTVAL FROM DUAL; 

By executing above query we can get next sequence value from seq_employee_details. The output was as follows –

Scenario – Changing SEQUENCE value by using ALTER SEQUENCE statement.

Requirement – Changing SEQUENCE value on seq_employee_details by using ALTER SEQUENCE statement. The query was as follows –

ALTER SEQUENCE seq_employee_details 
INCREMENT BY 100; 

By executing above query we can change the sequence value to 100.To know that 100 value is the next sequence value by using below query -

SELECT  seq_employee_details.NEXTVAL  FROM  DUAL;

Scenario - Deleting existing SEQUENCE in ORACLE .

Requirement - Deleting seq_employee_details sequence in ORACLE.The query was follows

DROP SEQUENCE seq_employee_details;

By executing above query we can drop existing sequence.