🔢 Oracle SQL Practice: Use Sequence for Auto-ID

🎯 Practice: Use Sequences for Auto-Generated IDs in Oracle SQL

In Oracle SQL, sequences are commonly used to generate unique IDs automatically, especially for primary keys. Follow this practice to create and use a sequence for inserting employee records.

1. Create a sample table

CREATE TABLE EMPLOYEE (
  EMP_ID     NUMBER PRIMARY KEY,
  EMP_NAME   VARCHAR2(50),
  DEPT_NAME  VARCHAR2(50)
);

2. Create a sequence for EMP_ID

CREATE SEQUENCE emp_id_seq
START WITH 1001
INCREMENT BY 1
NOCACHE
NOCYCLE;

This sequence starts from 1001 and will generate a new number for every employee insert.

3. Insert employee records using the sequence

INSERT INTO EMPLOYEE (EMP_ID, EMP_NAME, DEPT_NAME)
VALUES (emp_id_seq.NEXTVAL, 'Shahriar Kabir', 'Finance');

INSERT INTO EMPLOYEE (EMP_ID, EMP_NAME, DEPT_NAME)
VALUES (emp_id_seq.NEXTVAL, 'Nasima Akter', 'HR');

Each time emp_id_seq.NEXTVAL is called, it generates a new unique ID.

4. Query the EMPLOYEE table

SELECT * FROM EMPLOYEE;

5. (Optional) Drop the sequence after use

DROP SEQUENCE emp_id_seq;

---

✅ Summary:

• Sequences generate unique values on demand.
• Commonly used for primary keys.
• NEXTVAL retrieves the next number; CURRVAL shows the current number.

---

🔢 Use sequences for clean, automatic ID generation in multi-user environments — essential for scalable database applications.